71 lines
1.6 KiB
SQL
71 lines
1.6 KiB
SQL
@@header
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Tablespace usage
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 05-Aug-04 Vishal Gupta First Draft
|
|
*/
|
|
set term on
|
|
|
|
DEFINE dbid="&&1"
|
|
DEFINE instance_number="&&2"
|
|
|
|
|
|
DELETE FROM stats$sql_summary
|
|
WHERE(snap_id, dbid, instance_number, old_hash_value, text_subset) IN
|
|
(SELECT ss.snap_id,
|
|
ss.dbid,
|
|
ss.instance_number,
|
|
ss.old_hash_value,
|
|
ss.text_subset
|
|
FROM stats$sql_summary ss,
|
|
stats$statspack_parameter sp
|
|
WHERE ss.dbid = sp.dbid
|
|
AND ss.instance_number = sp.instance_number
|
|
AND ss.dbid = &dbid
|
|
AND ss.instance_number = &instance_number
|
|
AND ss.executions < sp.executions_th *1
|
|
AND ss.parse_calls < sp.parse_calls_th *1
|
|
AND ss.disk_reads < sp.disk_reads_th *1
|
|
AND ss.buffer_gets < sp.buffer_gets_th *1
|
|
AND ss.sharable_mem < sp.sharable_mem_th *1
|
|
AND ss.version_count < sp.version_count_th *1)
|
|
;
|
|
|
|
commit;
|
|
|
|
DELETE /*+ index(spu )*/
|
|
FROM stats$sql_plan_usage spu
|
|
WHERE NOT EXISTS
|
|
(SELECT 1
|
|
FROM stats$sql_summary ss
|
|
WHERE ss.dbid = spu.dbid
|
|
AND ss.instance_number = spu.instance_number
|
|
AND ss.dbid = &dbid
|
|
AND ss.instance_number = &instance_number
|
|
AND ss.snap_id = spu.snap_id
|
|
AND ss.old_hash_value = spu.old_hash_value
|
|
AND ss.text_subset = spu.text_subset)
|
|
;
|
|
|
|
commit;
|
|
|
|
|
|
DELETE FROM stats$sql_plan sp
|
|
WHERE NOT EXISTS
|
|
(SELECT 1
|
|
FROM stats$sql_plan_usage spu
|
|
WHERE spu.plan_hash_value = sp.plan_hash_value)
|
|
;
|
|
|
|
COMMIT;
|
|
|
|
@@footer
|