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: firstname.lastname@example.org
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
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/，如需转载，请注明出处，否则将追究法律责任。