Oracle - 随笔分类 - Oracle

ORACLE中搜索任意字符串

摘要：1createorreplaceproceduresearch(v_colnameinvarchar2,v_findinvarchar2)as2v_sqlvarchar2(2000);3v_resultvarchar2(1000);4v_cursornumber;5v_statnumber;6begin7dbms_output.put_line('表名,列名,结果');8forain(select... 阅读全文

posted @ 2009-04-08 22:48 Oracle 阅读(1753) 评论(0) 推荐(0) 编辑

Oracle's Query Transformer

摘要：Oracle优化器的工作主要分成3步。。。Oracle优化器的工作主要分成3步: 首先, 优化器会尝试把复杂的SQL语句转化较为简单的SQL语句, 通常倾向于转化为表的连接方式.然后, 优化器会对依据统计信息对SQL语句进行估量, 这些估量主要包括3个方面: Selectivity, Cardinality和Cost, 这三个方面是相互相关的.最后, 优化器会尝试各种执行计划并给出一个代价最低的计... 阅读全文

posted @ 2009-03-23 22:26 Oracle 阅读(684) 评论(0) 推荐(0) 编辑

Oracle的几个优化模式

摘要：CHOOSE The optimizer chooses between a cost-based approach and a rule-based approach, depending on whether statistics are available. This is the default value. If the data dictionary contains statisti... 阅读全文

posted @ 2009-03-18 23:14 Oracle 阅读(590) 评论(0) 推荐(0) 编辑

Linux中sar命令

摘要：sar这东西，一开始还以为是内部有的，原来是外部的工具,可以到http://pagesperso-orange.fr/sebastien.godard/download.html去下载1 安装 tar zxvf xxx.tar.gz./configure make make install2 使用　　　pidstat25//每隔2秒，显示5次，所有活动进程的CPU使用情况pidstat-p313... 阅读全文

posted @ 2009-03-18 21:41 Oracle 阅读(1090) 评论(0) 推荐(0) 编辑

Oracle 复制

摘要：我们经常希望把各地的数据入库后进行统一的应用。现在可以用复制技术来解决这个问题。但实现数据库复制也是要有一些条件的。 首先，数据库要具备高级复制功能（用system身份登录数据库，查看v$option视图，如果其中Advanced replication为TRUE，则支持高级复制功能；否则不支持）。 如果具备高级复制功能，数据库要进行一些参数初始化。 db_domain = tes... 阅读全文

posted @ 2009-03-16 18:07 Oracle 阅读(424) 评论(1) 推荐(0) 编辑

oracle 中grouping函数的应用

摘要：select * from test100001大515200001中2575300001小8525400002大6535500002中9585600002小8525701001大1614801001中29075901001小8322SELECT num ,flg ,(CASE WHEN GROUPING(num)=1 THEN 'TOTAL' WHEN GROUPING(type)=1 THE... 阅读全文

posted @ 2009-03-13 09:52 Oracle 阅读(3332) 评论(0) 推荐(0) 编辑

DUMP块的分析

摘要：Dump file C:\oracle\admin\ORCL\udump\ORA03560.TRCThu Mar 12 15:45:29 2009ORACLE V8.1.7.0.0 - Production vsnsta=0vsnsql=e vsnxtr=3Windows 2000 Version 5.1 Service Pack 2, CPU type 586Oracle8i Enterpris... 阅读全文

posted @ 2009-03-12 17:06 Oracle 阅读(1150) 评论(0) 推荐(0) 编辑

enqueue_stats.sql

摘要：This script looks at X$KSQST, which contains a breakdown of enqueue gets and enqueue waits by lock type. Unfortunately, there is no indication of the relative duration of these waits, so care must be ... 阅读全文

posted @ 2009-03-12 09:15 Oracle 阅读(248) 评论(0) 推荐(0) 编辑

enqueue_locks.sql

摘要：Most blocking lock detection scripts fail to consider that processes waiting for, but not yet holding, a lock can block other processes that need a conflicting lock on the same resource. To resolve su... 阅读全文

posted @ 2009-03-12 09:14 Oracle 阅读(297) 评论(0) 推荐(0) 编辑

latch_sleeps.sql

摘要：If there are latch free waits, then this script can be used to further diagnose the problem. For each latch type, it reports the estimated impact of sleeps, and the number of sleeps per get. It also r... 阅读全文

posted @ 2009-03-11 18:06 Oracle 阅读(218) 评论(0) 推荐(0) 编辑

latch_spin.sql

摘要：This script compares spin gets to sleep gets as an indicator of the effectiveness of spinning, for each latch type. column name format a39 heading "LATCH TYPE" column spin_gets ... 阅读全文

posted @ 2009-03-11 17:39 Oracle 阅读(210) 评论(0) 推荐(0) 编辑

latch_get.sql

摘要：This script reports the breakdown of willing-to-wait gets for each latch type, into simple gets, spin gets and sleep gets. Spin gets and sleep gets are latch gets that require spinning or sleeping res... 阅读全文

posted @ 2009-03-11 17:28 Oracle 阅读(226) 评论(0) 推荐(0) 编辑

latch_types.sql

摘要：This scripts prints a list of the latch types, and reports the number of child latches for each type. If a latch type has multiple child latches, V$LATCH_CHILDREN can be used to determine whether ac... 阅读全文

posted @ 2009-03-11 16:44 Oracle 阅读(232) 评论(0) 推荐(0) 编辑

trace_waits.sql

摘要：This script is one of our favorites. It finds the top N sessions that have been affected by a particular type of resource wait, and enables event 10046, level 8 in those sessions for the specified per... 阅读全文

posted @ 2009-03-11 16:25 Oracle 阅读(217) 评论(0) 推荐(0) 编辑

session_waits.sql

摘要：select e.event, e.time_waited from sys.v_$session_event e where e.sid = &Sid union all select n.name, s.value from sys.v_$statname n, sys.v_$sesstat s where s.sid = &Sid and n.s... 阅读全文

posted @ 2009-03-11 16:21 Oracle 阅读(180) 评论(0) 推荐(0) 编辑

resource_waits.sql

摘要：reselect substr(e.event, 1, 40) event, e.time_waited, e.time_waited / decode( e.event, 'latch free', e.total_waits, decode( e.total_waits - e.total_timeouts, 0, 1, ... 阅读全文

posted @ 2009-03-11 16:18 Oracle 阅读(190) 评论(0) 推荐(0) 编辑

routine_waits.sql

摘要：select substr(e.event, 1, 40) event, e.average_wait from sys.v_$system_event e, sys.v_$instance i where e.event = 'DFS lock handle' or e.event = 'rdbms ipc reply' or e.event like 'SQL... 阅读全文

posted @ 2009-03-11 16:17 Oracle 阅读(208) 评论(0) 推荐(0) 编辑

hidden_parameters.sql

摘要：select x.ksppinm name, decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') sesmod, decode( bitand(ksppiflg/65536,3), 1,'IMMEDIATE', 2,'DEFERRED', 3,'IMMEDIATE', 'FALSE' ) s... 阅读全文

posted @ 2009-03-11 16:02 Oracle 阅读(135) 评论(0) 推荐(0) 编辑

all_parameters.sql

摘要：select x.ksppinm name, y.kspftctxvl value, y.kspftctxdf isdefault, decode(bitand(y.kspftctxvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, decode(bitand(y.kspftctxvf,2),2,'TRUE','FALSE... 阅读全文

posted @ 2009-03-11 16:02 Oracle 阅读(147) 评论(0) 推荐(0) 编辑

Oracle sql 性能优化调整

摘要：1. 选用适合的ORACLE优化器 ORACLE的优化器共有3种: a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . ... 阅读全文

posted @ 2009-03-11 11:40 Oracle 阅读(271) 评论(0) 推荐(0) 编辑