101 lines
3.2 KiB
MySQL
101 lines
3.2 KiB
MySQL
|
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
|
||
|
|
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
|
||
|
|
|
||
|
|
SET LINES 1000 PAGES 5000 TRIMSPOOL ON TRIMOUT ON TAB OFF
|
||
|
|
|
||
|
|
COL owner FOR A15
|
||
|
|
COL object_name FOR A30
|
||
|
|
|
||
|
|
WITH trends AS (
|
||
|
|
SELECT
|
||
|
|
o.owner
|
||
|
|
, o.object_name
|
||
|
|
, o.subobject_name
|
||
|
|
, o.object_type
|
||
|
|
, MIN(ih.savtime) first_sample
|
||
|
|
, MAX(ih.savtime) last_sample
|
||
|
|
, REGR_SLOPE(ih.rowcnt / NULLIF(ih.leafcnt,0), (SYSDATE-CAST(ih.savtime AS DATE)) ) regr1
|
||
|
|
, REGR_SLOPE(ih.rowcnt, ih.leafcnt) regr2
|
||
|
|
, ROUND(MIN(ih.rowcnt / NULLIF(ih.leafcnt,0))) min_avg_rows_per_block
|
||
|
|
, ROUND(MAX(ih.rowcnt / NULLIF(ih.leafcnt,0))) max_avg_rows_per_block
|
||
|
|
, MIN(ih.rowcnt) min_rowcnt
|
||
|
|
, MAX(ih.rowcnt) max_rowcnt
|
||
|
|
, MIN(ih.leafcnt) min_leafcnt
|
||
|
|
, MAX(ih.leafcnt) max_leafcnt
|
||
|
|
, MIN(ih.lblkkey) min_lblkkey
|
||
|
|
, MAX(ih.lblkkey) max_lblkkey
|
||
|
|
, MIN(ih.dblkkey) min_dblkkey
|
||
|
|
, MAX(ih.dblkkey) max_dblkkey
|
||
|
|
, MIN(ih.blevel)+1 min_height
|
||
|
|
, MAX(ih.blevel)+1 max_height
|
||
|
|
FROM
|
||
|
|
dba_objects o
|
||
|
|
, sys.wri$_optstat_ind_history ih
|
||
|
|
WHERE
|
||
|
|
o.object_id = ih.obj#
|
||
|
|
AND o.object_type LIKE 'INDEX%'
|
||
|
|
AND (
|
||
|
|
UPPER(o.object_name) LIKE
|
||
|
|
UPPER(CASE
|
||
|
|
WHEN INSTR('&1','.') > 0 THEN
|
||
|
|
SUBSTR('&1',INSTR('&1','.')+1)
|
||
|
|
ELSE
|
||
|
|
'&1'
|
||
|
|
END
|
||
|
|
)
|
||
|
|
AND UPPER(o.owner) LIKE
|
||
|
|
CASE WHEN INSTR('&1','.') > 0 THEN
|
||
|
|
UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
|
||
|
|
ELSE
|
||
|
|
user
|
||
|
|
END
|
||
|
|
)
|
||
|
|
GROUP BY
|
||
|
|
o.owner
|
||
|
|
, o.object_name
|
||
|
|
, o.subobject_name
|
||
|
|
, o.object_type
|
||
|
|
ORDER BY
|
||
|
|
-- ih.savtime
|
||
|
|
regr1 DESC NULLS LAST
|
||
|
|
)
|
||
|
|
SELECT * FROM (
|
||
|
|
SELECT
|
||
|
|
t.owner
|
||
|
|
, t.object_name
|
||
|
|
, t.subobject_name partition_name
|
||
|
|
, t.object_type
|
||
|
|
, ROUND(s.bytes / 1048576) current_mb
|
||
|
|
, CAST(first_sample AS DATE) first_sample
|
||
|
|
, CAST(last_sample AS DATE) last_sample
|
||
|
|
, min_avg_rows_per_block
|
||
|
|
, max_avg_rows_per_block
|
||
|
|
, min_leafcnt
|
||
|
|
, max_leafcnt
|
||
|
|
, min_lblkkey
|
||
|
|
, max_lblkkey
|
||
|
|
, min_dblkkey
|
||
|
|
, max_dblkkey
|
||
|
|
, t.regr1
|
||
|
|
, t.regr2
|
||
|
|
--, ROUND(SUM(s.bytes) / 1048576) mb_sum
|
||
|
|
--, COUNT(*)
|
||
|
|
FROM
|
||
|
|
trends t
|
||
|
|
, dba_segments s
|
||
|
|
WHERE
|
||
|
|
t.owner = s.owner
|
||
|
|
AND t.object_name = s.segment_name
|
||
|
|
AND t.object_type = s.segment_type
|
||
|
|
AND (t.subobject_name = s.partition_name OR (t.subobject_name IS NULL AND s.partition_name IS NULL))
|
||
|
|
--GROUP BY
|
||
|
|
-- t.owner
|
||
|
|
-- , t.object_name
|
||
|
|
-- , t.object_type
|
||
|
|
-- , t.subobject_name
|
||
|
|
ORDER BY regr1 DESC NULLS LAST
|
||
|
|
)
|
||
|
|
WHERE
|
||
|
|
ROWNUM<=20
|
||
|
|
/
|