ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 详细解V$SQL视图

详细解V$SQL视图

原创 Linux操作系统 作者:oracleclub 时间:2011-12-09 08:34:14 0 删除 编辑


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博客 ” ,链接:http://blog.itpub.net/26465805/viewspace-713008/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 实例优化—PGA
请登录后发表评论 登录
全部评论

注册时间:2011-11-22

  • 博文量
    61
  • 访问量
    82205