1，查询消耗磁盘I/O最多的SQL语句

SQL> select sql_text,executions,disk_reads

2 from v$sql

3 where disk_reads > &number

4 order by disk_reads desc;

Enter value for number: 200

old 3: where disk_reads > &number

new 3: where disk_reads > 200

EXECUTIONS：说明语句执行的时间量，包括语句的等待时间和服务时间

SQL_TEXT EXECUTIONS DISK_READS

-------------------------------------------------- ---------- ----------

select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, 40758 35

null_cnt, timestamp#, sample_size, minimum, maxim

um, distcnt, lowval, hival, density, col#, spare1,

spare2, avgcln from hist_head$ where obj#=:1 and

intcol#=:2

INSERT INTO RECENT_RESOURCE_INCARNATIONS$ ( RESOUR 1 13

CE_TYPE, RESOURCE_ID, RESOURCE_NAME, DB_UNIQUE_NAM

E, DB_DOMAIN, INSTANCE_NAME, HOST_NAME, STARTUP_TI

ME, LOCATION, INCARNATION ) SELECT 'INSTANCE', INS

TANCE_NUMBER, INSTANCE_NAME, :B2 , :B1 , INSTANCE_

2，分析buffer_gets读取次数最多的SQL语句

大量的缓冲区读取，说明该SQL语句耗费较多的CPU资源，降低逻辑读是SQL优化的重要方面，

逻辑读分为DB块读取(db_buffer中)和一致性读(undo回滚段)

但并不是高逻辑读就意味着该SQL语句影响性能，通过该视图给出的语句在结合autotrace等工具来分析

SQL> select sql_text,buffer_gets,parse_calls

2 from v$sql

3 where buffer_gets > &number

4 order by buffer_gets;

Enter value for number: 10000

old 3: where buffer_gets > &number

new 3: where buffer_gets > 10000

SQL_TEXT BUFFER_GETS PARSE_CALLS

-------------------------------------------------- ----------- -----------

DECLARE job BINARY_INTEGER := :job; next_date DATE 10889 47

:= :mydate; broken BOOLEAN := FALSE; BEGIN EMD_M

AINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate :=

next_date; IF broken THEN :b := 1; ELSE :b := 0;

END IF; END;

select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, 105037 545

null_cnt, timestamp#, sample_size, minimum, maxim

um, distcnt, lowval, hival, density, col#, spare1,

spare2, avgcln from hist_head$ where obj#=:1 and

intcol#=:2

3，当前系统耗费CPU资源最多的前5个SQL语句

SQL> select sql_text,cpu_time,buffer_gets

2 from (select sql_text,cpu_time,buffer_gets

3 from v$sql

4 order by cpu_time desc)

5 where rownum < 5;

SQL_TEXT CPU_TIME BUFFER_GETS

-------------------------------------------------- ---------- -----------

select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, 1259687 105039

null_cnt, timestamp#, sample_size, minimum, maxim

um, distcnt, lowval, hival, density, col#, spare1,

spare2, avgcln from hist_head$ where obj#=:1 and

intcol#=:2

DECLARE job BINARY_INTEGER := :job; next_date DATE 1238506 10889

:= :mydate; broken BOOLEAN := FALSE; BEGIN EMD_M

AINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate :=

next_date; IF broken THEN :b := 1; ELSE :b := 0;

END IF; END;

DECLARE job BINARY_INTEGER := :job; next_date DATE 847872 11127

:= :mydate; broken BOOLEAN := FALSE; BEGIN EMD_M

AINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate :=

next_date; IF broken THEN :b := 1; ELSE :b := 0;

END IF; END;

CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, 557388 4354

:mguid, :result)

ITPub博客

详细解V$SQL视图

最新文章