2014年03月_scan724

原创利用集群因子优化

create index END_DT_IDX1 on F_AGT_BUSINESS_CONTRACT_H(end_dt);SQL> explain plan for select * from F_AGT_BUSINESS_CONTRACT_H t where t.end_dt = date '2999-12-31';Explained.SQL> select * from ta

2014-03-31 10:02:24 924 1

原创 PL/SQL 动态SQL

declaremsql varchar2(200);row_id varchar2(200);beginlooprow_id := 'AAATGiAAEAAAuLLAAA';msql := 'select * from t3 where rowid='''||row_id || ''''; execute immediate msql;end loop;end;

2014-03-23 12:22:24 566

原创 PLSQL死循环

beginloopinsert into for1 values('a');commit;end loop;end;

2014-03-22 20:28:16 914

原创 dba_hist_active_sess_history

dba_hist_active_sess_history

2014-03-22 16:22:49 1242

原创 cursor pin S wait on X

cursor pin S wait on X；这是10.2版本提出的mutex(互斥)机制用来解决library cache bin latch争夺问题引入的新事件,是否使用这种机制受到隐含参数_kks_use_mutex_pin的限制,从10.2.0.2开始该参数default为true,使用这种机制oracle是为了解决library cache bin latch的串行使用问题,但是m

2014-03-22 15:13:51 852

原创 v$session_wait p1 p1raw p1_16

SQL> select * from v$mystat where rownum<2; SID STATISTIC# VALUE---------- ---------- ---------- 1 0 0SQL> exec test_prc ;SQL> select * from v$mystat where rownum<2;

2014-03-22 10:14:14 1060

原创 latch:library cache

一：硬解析造成的shared pool latch 争用:每一个sql被执行之前，先要到library cache中根据hash_value查找parent cursor,这就需要先获得library cache latch；也就是说硬解析和软解析都有可能造成latch 争用查看这些非常相似的语句:select orguser0_.ID as ID3

2014-03-21 19:46:31 738

原创 direct path read

SQL> select SESSION_ID,NAME,P1,P2,P3,WAIT_TIME,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK# from v$active_session_history ash, v$event_name enm where ash.event#=enm.event#

2014-03-21 19:39:16 556

原创 library cahce pin

library cahce pin(库高速缓存pin)等待事件与库高速缓存并发性有关,当会话尝试在库高速缓存中钉住对象以修改或检查该对象时产生该事件。会话必须获取(pin)以确保此时对象没有被其他会话更新。当会话正在编译或分析PL/SQL过程和视图时,Oracle提交该事件。create or replace procedure test_prc asbegininsert i

2014-03-21 11:05:00 767

原创利用分析函数删除重复数据

delete from dupes where rowid in (select rowid from (select a.*,row_number() over(partition by name order by name) as rn　from dupes a)where rn>1);

2014-03-19 19:50:51 1347

原创组合索引避免索引扫描后在过滤

SQL> select corp_org,count(*) from test_2 where end_dt > date'2013-05-01' 2 group by corp_org order by count(*) desc;CORP_ORG COUNT(*)---------- ----------15601 2367343117

2014-03-19 19:45:27 636

原创 || 连接运算符

||--批量修改sequence: select 'alter sequence ' || SEQUENCE_OWNER ||'.'||sequence_name|| ' cache 1000;' from dba_sequences;---批量drop 表:SQL> select 'drop table '||table_name||';' from user_table

2014-03-19 19:39:37 1001

原创组合索引

一: 创建的索引1create index f_agt_comr_intdist_h_idx2 on f_agt_comr_intdist_h(agmt_id,intt,payable_int_amt,dw_data_dt) tablespace EDWFIDXTBS nologging SQL> select aa.acct_org,aa.loan_acct_no,aa.FU

2014-03-19 19:38:54 582

原创 INDEX RANG SCAN无需回表的情况

create table a3 as select * from dba_objectscreate index a3_idx1 on a3(owner);select owner from a3 where owner='SCOTT';SQL> set linesize 200SQL> set pagesize 200SQL> set autot traceSQL> sel

2014-03-19 16:44:20 763

原创 sql union代替or

---原始SQLSQL> SELECT deptno FROM emp WHERE empno = 7788 OR job = 'SALESMAN' ORDER BY 1; DEPTNO---------- 20 30 30 30 30SQL> SELECT deptno FROM emp WHERE empno = 7788UNIONSELECT deptno F

2014-03-18 19:31:30 1195 2

原创创建组合索引SQL从1个多小时到1S的案例

select aa.acct_org,aa.loan_acct_no,aa.FUNCTIONARY,aa.cust_no,sum(dwm.pkg_tools.currcdtran(bb.INTT,aa.trans_cur_cd,'T00CNY','2014-02-10')) as jtlx from dwf.F_AGT_LONC_BASIC_INFO_H aa left jo

2014-03-18 19:29:51 869

原创慎用位图索引

/* Formatted on 2014/03/18 09:48 (Formatter Plus v4.8.8) */SELECT c.fee_type_desc, i.order_code, b.chn_name AS rexp_code_name, i.rexp_code, (CASE (SUBSTR ((f.order_name || '/' ||

2014-03-18 10:59:02 691

原创关于索引degree设置的问题

--并行查询可以使用并行查询的情况1. Full table scans, full partition scans, and fast full index scans2. Index full and range scans, but only if the index is partitioned (at a given time, a partition can be acc

2014-03-17 16:23:45 1697

原创 db file sequential read等待事件的一点研究

db file sequential read等待事件有3个参数：file＃，first block＃，和block数量。这个等待事件有3个参数P1，P2，P3，其中P1代表Oracle要读取的文件的绝对文件号，P2代表Oracle从这个文件中开始读取的起始数据块号，P3代表读取的BLOCK数量，通常这个值为1，表明是道单个BLOCK被读取。 SQL> select SESS

2014-03-17 14:31:55 728

原创多个hints 怎么写？

/*+ ordered use_nl(t2) index(t2) */

2014-03-13 13:34:47 780

原创再次遭遇笛卡尔积

改为MERGE /*+ordered*/ INTO YJBZH_GRXDFHZMXJL GRXDFHZMXJL USING ( SELECT A.AGMT_ID AS AGMT_ID, B.CUST_NAME AS CUST_NAME FROM DWF.F_AGT_CADB_BOOK_H A, DWF.F_AGT_SAVB_BASICINFO_H B WHERE A.START_DT

2014-03-13 11:30:47 744

原创 pl/sql 在一个程序块里打印日志输出到表格

declare v_number NUMBER; v_number2 NUMBER;beginexecute immediate 'truncate table t2';insert into t2 select count(*) from dual;commit;begininsert into t2 select count(*) from t1;com

2014-03-12 21:39:45 2312

原创 pl/sql 程序块里打印问题

declare v_number NUMBER; v_number2 NUMBER;beginselect count(*) into v_number from dual;DBMS_OUTPUT.PUT_LINE('table YJBZH_GRXDFHZMXJL此时记录数为'||v_number);select count(*) into v_number2 from t

2014-03-12 21:22:03 936

原创利用ordered hints优化SQL

SQL_ID 4g70n3k9bqc5v, child number 0-------------------------------------MERGE INTO YJBZH_GRXDFHZMXJL GRXDFHZMXJL USING ( SELECT A.AGMT_ID AS AGMT_ID, B.CUST_NAME AS CUST_NAME FROM DWF.F_AGT_CA

2014-03-12 13:03:17 859 1

原创全表扫描引发的db file sequential read

今天我要做一个SQL调优，监控该SQL，利用ASH 监控该SQL是在sid=4848 上面跑的 SQL> select SESSION_ID,NAME,P1,P2,P3,WAIT_TIME,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK# 2 from v$active_session_history ash, v$ev

2014-03-12 11:06:10 1784

原创读undo问题

SQL> drop table test1 purge;Table dropped.SQL> create table test1 as select * from dba_objects where 1=2;Table created.SQL> insert into test1 select * from dba_objects;74506 rows

2014-03-12 10:32:50 538

原创关于拼接引号的问题

SQL> select ename||q'['aa']' from emp where deptno=10;ENAME||Q'['AA'--------------CLARK'aa'KING'aa'MILLER'aa'SQL> select ename||''''||'aa'||'''' from emp where deptno=10;ENAME||''''||'

2014-03-11 14:14:08 693

原创 UNDO 100%

另外查了下v$undostat，发现begin_time已经很久没有改变，BEGIN_TIME END_TIME MAXQUERYLEN MAXCONCURRENCY UNEXPIREDBLKS EXPIREDBLKSBEGIN_TIME DATE Identifies the beginning of the time interval

2014-03-11 10:18:18 739

原创类似NL的update更新

update b set b.object_name=(select a.object_name from a where a.object_id=b.object_id);create table a as (select * from (select * from dba_objects order by object_id) where rownum<100)create

2014-03-09 21:19:10 646

原创如何启用并行？

如何启用并行？可以用hint、alter session或者设置对象并行属性三种方式设置启用并行。三种方式任意一种就可以使并行生效，如果多种方式同时存在的话，则优先级顺序是：hint -> alter session -> table/index degree。 hint alter session table/index degree Q

2014-03-09 20:32:26 869

原创排序过程

如果排序需要的内存大于sort_area_size, 那么ORACLE就会把排序操作分成2个块，典型的ONE-PASS Sort run 1第一块排序在内存里排序排完后把排序结果放到TEMP表空间 Sort run 2 先临时放到temp segment里,等到第一块排序完后,把第一块排序的结果放到临时表空间,在把第2块读到排序区进行排序,排序结果放到temp

2014-03-08 18:26:50 686

原创死锁

如果你有两个会话,每个会话都持有另一个会话想要的资源,此时就会出现死锁(deadlock)。例如,如果我的数据库中有两个A和B,每个表都只有一行,就可以很容易地展示什么是死锁。我要做的知识打开两个回话(例如,两个SQL*PLUS会话).在会话A中更更新A表,在会话B中更新B。现在,如果我想在会话B中更新A,就会堵塞。会话A已经锁定了这一行,这不是死锁:只是堵塞而已。

2014-03-07 16:08:15 670

原创唯一索引引发的行锁

SQL> create table t2(id int,name char(10));Table created.SQL> create unique index te_idx1 on t2(id);Index created.SQL> select * from v$mystat where rownum<2; SID STATISTIC#

2014-03-07 16:07:48 1565

原创查看ASM 使用率

有两种方法：1.查看v$asm_diskgroup视图SQL> select group_number,name,total_mb,free_mb from v$asm_diskgroup;GROUP_NUMBER NAME TOTAL_MB FREE_MB------------ ---------

2014-03-07 11:09:40 3678

原创修改一行和修改全表的TX锁

SQL> select * from v$mystat where rownum<2; SID STATISTIC# VALUE---------- ---------- ---------- 25 0 0SQL> update t1 set id=100 where id=1;1 row updated.

2014-03-07 10:57:36 620

原创关于打开游标的那些事

----------------------------------------------------------------------SQL> begin 2 for i in 1 .. 50000 3 loop 4 insert into t1 values(i,'a'); 5 commit; 6 end loop; 7 end;

2014-03-06 10:24:14 715

转载 CentOS Linux解决Device eth0 does not seem to be present

CentOS Linux解决Device eth0 does not seem to be present一，问题描述在VMware里克隆出来的CentOS Linux。。ifconfig...没有看到eth0.。然后重启网卡又报下面错误。故障现象：service network restartShutting down loopback insterface: [ OK ]Brin

2014-03-05 14:13:30 962

原创 cognos启动报错

[ ERROR ] Content Manager is unable to process your request because an unexpected event occurred in the contentstore database subsystemsolution:删除dw cognos用户,重建用户 DPR-DPR-1035 Dispatcher det

2014-03-05 11:47:15 2397

原创 iostat 使用说明

LINUX[oracle@perass back]$ iostat -m 1 10Linux 2.6.18-194.el5 (perass) 03/01/2014avg-cpu: %user %nice %system %iowait %steal %idle 0.04 0.00 1.21 38.28 0.00 60.

2014-03-05 09:03:36 656

原创 Oracle 多版本控制

SESSION 1:SQL> create table t 2 as 3 select * from all_users;Table created.SQL> variable x refcursorSQL> begin 2 open :x for select * from t; 3 end; 4 /PL/SQL

2014-03-03 16:18:20 885

空空如也

空空如也