ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 找出无效sql

找出无效sql

原创 Linux操作系统 作者:syzxlyx_cu 时间:2009-09-28 11:33:49 0 删除 编辑
Example 8-2. This script. reports on the apparent efficiency of SQL statements whose information presently resides in the shared pool
rem $Header: /usr/local/hostos/RCS/htopsql.sql,v 1.6 2001/11/19 22:31:35
rem  Author: jeff.holt@hotsos.com
rem          Copyright (c) 1999 by Hotsos Enterprises, Ltd.
             All rights reserved.
rem   Usage: This script. shows inefficient SQL by computing the ratio
rem          of logical_reads to rows_processed.  The user will have
rem          to press return to see the first page.  The user should
rem          be able to see the really bad stuff on the first page and
rem          therefore should press ^C and then press [Return] when the
rem          first page is completely displayed.
rem          SQL hash values are really statement identifiers. These
rem          identifiers are used as input to a hashing function to
rem          determine if a statement is in the shared pool.
rem          This script. shows only statement identifiers. Use hsqltxt.sql
rem          to display the text of interesting statements.
rem   Notes: This will return data for select,insert,update, and delete
rem          statements. We don't return rows for PL/SQL blocks because
rem          their reads are counted in their underlying SQL statements.
rem          There is value in knowing the PL/SQL routine that executes
rem          an inefficient statement but it's only important once you
rem          know what's wrong with the statment.
  
col stmtid      heading 'Stmt Id'               format    9999999999
col dr          heading 'PIO blks'              format   999,999,999
col bg          heading 'LIOs'                  format   999,999,999
col sr          heading 'Sorts'                 format       999,999
col exe         heading 'Runs'                  format   999,999,999
col rp          heading 'Rows'                  format 9,999,999,999
col rpr         heading 'LIOs|per Row'          format   999,999,999
col rpe         heading 'LIOs|per Run'          format   999,999,999
  
set termout   on
set pause     on
set pagesize  30
set pause     'More: '
set linesize  95
  
select  hash_value stmtid
       ,sum(disk_reads) dr
       ,sum(buffer_gets) bg
       ,sum(rows_processed) rp
       ,sum(buffer_gets)/greatest(sum(rows_processed),1) rpr
       ,sum(executions) exe
       ,sum(buffer_gets)/greatest(sum(executions),1) rpe
 from v$sql
where command_type in ( 2,3,6,7 )
group by hash_value
order by 5 desc
/
  
set pause off
The query sorts its

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20542911/viewspace-615755/,如需转载,请注明出处,否则将追究法律责任。

下一篇: beq协议
请登录后发表评论 登录
全部评论

注册时间:2009-09-28

  • 博文量
    125
  • 访问量
    124092