246 lines
7.5 KiB
SQL
246 lines
7.5 KiB
SQL
COL table_owner FOR A30 HEA 'Table Owner';
|
|
COL table_name FOR A30 HEA 'Table Name';
|
|
COL index_name FOR A30 HEA 'Index Name';
|
|
COL partitioned FOR A4 HEA 'Part';
|
|
COL orphaned_entries FOR A8 HEA 'Orphaned|Entries';
|
|
COL degree FOR A10 HEA 'Degree';
|
|
COL index_type FOR A27 HEA 'Index Type';
|
|
COL uniqueness FOR A10 HEA 'Uniqueness';
|
|
COL columns FOR 999,999 HEA 'Columns';
|
|
COL status FOR A8 HEA 'Status';
|
|
COL visibility FOR A10 HEA 'Visibility';
|
|
COL blevel FOR 99,990 HEA 'BLevel';
|
|
COL leaf_blocks FOR 999,999,990 HEA 'Leaf Blocks';
|
|
COL size_MiB FOR 999,999,990.000 HEA 'Size MiB';
|
|
COL seg_size_MiB FOR 999,999,990.000 HEA 'Seg Size MiB';
|
|
COL size_MB FOR 999,999,990.000 HEA 'Size MB';
|
|
COL seg_size_MB FOR 999,999,990.000 HEA 'Seg Size MB';
|
|
COL distinct_keys FOR 999,999,999,990 HEA 'Dist Keys';
|
|
COL clustering_factor FOR 999,999,999,990 HEA 'Clust Fact';
|
|
COL num_rows FOR 999,999,999,990 HEA 'Num Rows';
|
|
COL sample_size FOR 999,999,999,990 HEA 'Sample Size';
|
|
COL last_analyzed FOR A19 HEA 'Last Analyzed';
|
|
COL compression FOR A13 HEA 'Compression';
|
|
COL tablespace_name FOR A30 HEA 'Tablespace';
|
|
COL rn FOR 999;
|
|
--
|
|
PRO
|
|
PRO INDEXES (dba_indexes)
|
|
PRO ~~~~~~~
|
|
WITH /* INDEXES */
|
|
v_sqlarea_m AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE QB_NAME(sqlarea) */
|
|
DISTINCT
|
|
hash_value, address
|
|
FROM v$sqlarea
|
|
WHERE sql_id = '&&cs_sql_id.'
|
|
),
|
|
v_object_dependency_m AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE QB_NAME(obj_dependency) */
|
|
DISTINCT
|
|
o.to_owner, o.to_name
|
|
FROM v$object_dependency o,
|
|
v_sqlarea_m s
|
|
WHERE o.from_hash = s.hash_value
|
|
AND o.from_address = s.address
|
|
AND o.to_type = 2 -- table
|
|
),
|
|
dba_tables_m AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE QB_NAME(dba_tables) */
|
|
t.owner,
|
|
t.table_name
|
|
FROM dba_tables t,
|
|
v_object_dependency_m o
|
|
WHERE t.owner = o.to_owner
|
|
AND t.table_name = o.to_name
|
|
)
|
|
SELECT /*+ QB_NAME(get_stats) */
|
|
i.table_owner,
|
|
i.table_name,
|
|
i.index_name,
|
|
CASE i.partitioned WHEN 'YES' THEN (SELECT TRIM(TO_CHAR(COUNT(*))) FROM dba_ind_partitions ip WHERE ip.index_owner = i.owner AND ip.index_name = i.index_name) ELSE i.partitioned END AS partitioned,
|
|
i.orphaned_entries,
|
|
i.degree,
|
|
i.index_type,
|
|
i.uniqueness,
|
|
(SELECT COUNT(*) FROM dba_ind_columns ic WHERE ic.index_owner = i.owner AND ic.index_name = i.index_name) AS columns,
|
|
i.status,
|
|
i.visibility,
|
|
i.blevel,
|
|
i.leaf_blocks,
|
|
i.leaf_blocks * COALESCE(b.block_size, TO_NUMBER(p.value)) / POWER(10,6) AS size_MB,
|
|
(SELECT SUM(s.bytes) / POWER(10,6) FROM dba_segments s WHERE s.owner = i.owner AND s.segment_name = i.index_name AND s.segment_type LIKE 'INDEX%') AS seg_size_MB,
|
|
i.distinct_keys,
|
|
i.clustering_factor,
|
|
i.num_rows,
|
|
i.sample_size,
|
|
TO_CHAR(i.last_analyzed, '&&cs_datetime_full_format.') AS last_analyzed,
|
|
i.compression,
|
|
i.tablespace_name
|
|
FROM dba_tables_m t,
|
|
dba_indexes i,
|
|
dba_tablespaces b,
|
|
v$parameter p
|
|
WHERE i.table_owner = t.owner
|
|
AND i.table_name = t.table_name
|
|
AND b.tablespace_name(+) = i.tablespace_name
|
|
AND p.name = 'db_block_size'
|
|
ORDER BY
|
|
i.table_owner,
|
|
i.table_name,
|
|
i.index_name
|
|
/
|
|
--
|
|
COL object_type HEA 'Object Type';
|
|
COL object_id FOR 999999999 HEA 'Object ID';
|
|
COL object_name FOR A30 HEA 'Object Name';
|
|
COL subobject_name FOR A30 HEA 'Sub Object Name';
|
|
COL created FOR A23 HEA 'Created';
|
|
COL last_ddl_time FOR A23 HEA 'Last DDL Time';
|
|
--
|
|
PRO
|
|
PRO INDEX OBJECTS (dba_objects) up to 100
|
|
PRO ~~~~~~~~~~~~~
|
|
WITH /* OBJECTS */
|
|
v_sqlarea_m AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE QB_NAME(sqlarea) */
|
|
DISTINCT
|
|
hash_value, address
|
|
FROM v$sqlarea
|
|
WHERE sql_id = '&&cs_sql_id.'
|
|
),
|
|
v_object_dependency_m AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE QB_NAME(obj_dependency) */
|
|
DISTINCT
|
|
o.to_owner, o.to_name
|
|
FROM v$object_dependency o,
|
|
v_sqlarea_m s
|
|
WHERE o.from_hash = s.hash_value
|
|
AND o.from_address = s.address
|
|
AND o.to_type = 2 -- table
|
|
),
|
|
dba_tables_m AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE QB_NAME(dba_tables) */
|
|
t.owner,
|
|
t.table_name
|
|
FROM dba_tables t,
|
|
v_object_dependency_m o
|
|
WHERE t.owner = o.to_owner
|
|
AND t.table_name = o.to_name
|
|
)
|
|
SELECT o.object_type,
|
|
o.owner,
|
|
o.object_name,
|
|
o.subobject_name,
|
|
o.object_id,
|
|
TO_CHAR(o.created, '&&cs_datetime_full_format.') AS created,
|
|
TO_CHAR(o.last_ddl_time, '&&cs_datetime_full_format.') AS last_ddl_time
|
|
FROM dba_tables_m t,
|
|
dba_indexes i,
|
|
dba_objects o
|
|
WHERE i.table_owner = t.owner
|
|
AND i.table_name = t.table_name
|
|
AND o.owner = i.owner
|
|
AND o.object_name = i.index_name
|
|
AND o.object_type LIKE 'INDEX%'
|
|
ORDER BY
|
|
o.object_type,
|
|
o.owner,
|
|
o.object_name,
|
|
o.subobject_name
|
|
FETCH FIRST 100 ROWS ONLY
|
|
/
|
|
--
|
|
COL object_type HEA 'Object Type';
|
|
COL owner FOR A30 HEA 'Owner';
|
|
COL object_id FOR 999999999 HEA 'Object ID';
|
|
COL object_name FOR A30 HEA 'Object Name';
|
|
COL subobject_name FOR A30 HEA 'Sub Object Name';
|
|
COL created FOR A23 HEA 'Created';
|
|
COL last_ddl_time FOR A23 HEA 'Last DDL Time';
|
|
COL analyzetime FOR A19 HEA 'Analyze Time';
|
|
COL savtime FOR A23 HEA 'Saved Time';
|
|
COL rowcnt FOR 999,999,999,990 HEA 'Row Count';
|
|
COL blevel FOR 99,990 HEA 'BLevel';
|
|
COL leafcnt FOR 999,999,990 HEA 'Leaf Blocks';
|
|
COL distkey FOR 999,999,999,990 HEA 'Dist Keys';
|
|
COL samplesize FOR 999,999,999,990 HEA 'Sample Size';
|
|
--
|
|
BREAK ON object_id SKIP 1 ON owner ON object_name ON created ON last_ddl_time;
|
|
PRO
|
|
PRO CBO STAT INDEX HISTORY (wri$_optstat_ind_history) up to 25 per Index
|
|
PRO ~~~~~~~~~~~~~~~~~~~~~~
|
|
WITH /* OBJECTS */
|
|
v_sqlarea_m AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE QB_NAME(sqlarea) */
|
|
DISTINCT
|
|
hash_value, address
|
|
FROM v$sqlarea
|
|
WHERE sql_id = '&&cs_sql_id.'
|
|
),
|
|
v_object_dependency_m AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE QB_NAME(obj_dependency) */
|
|
DISTINCT
|
|
o.to_owner, o.to_name
|
|
FROM v$object_dependency o,
|
|
v_sqlarea_m s
|
|
WHERE o.from_hash = s.hash_value
|
|
AND o.from_address = s.address
|
|
AND o.to_type = 2 -- table
|
|
),
|
|
dba_tables_m AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE QB_NAME(dba_tables) */
|
|
t.owner,
|
|
t.table_name
|
|
FROM dba_tables t,
|
|
v_object_dependency_m o
|
|
WHERE t.owner = o.to_owner
|
|
AND t.table_name = o.to_name
|
|
),
|
|
dba_indexes_o AS (
|
|
SELECT o.owner,
|
|
o.object_name,
|
|
o.subobject_name,
|
|
o.object_id,
|
|
o.created,
|
|
o.last_ddl_time,
|
|
h.analyzetime,
|
|
h.savtime,
|
|
h.rowcnt,
|
|
h.blevel,
|
|
h.leafcnt,
|
|
h.distkey,
|
|
h.samplesize,
|
|
ROW_NUMBER() OVER (PARTITION BY o.object_id ORDER BY h.analyzetime DESC NULLS LAST, h.savtime DESC NULLS LAST) AS rn
|
|
FROM dba_tables_m t,
|
|
dba_indexes i,
|
|
dba_objects o,
|
|
wri$_optstat_ind_history h
|
|
WHERE i.table_owner = t.owner
|
|
AND i.table_name = t.table_name
|
|
AND o.owner = i.owner
|
|
AND o.object_name = i.index_name
|
|
AND o.object_type = 'INDEX'
|
|
AND h.obj# = o.object_id
|
|
)
|
|
SELECT DISTINCT
|
|
o.owner,
|
|
o.object_name,
|
|
o.subobject_name,
|
|
o.object_id,
|
|
TO_CHAR(o.created, '&&cs_datetime_full_format.') AS created,
|
|
TO_CHAR(o.last_ddl_time, '&&cs_datetime_full_format.') AS last_ddl_time,
|
|
TO_CHAR(o.analyzetime, '&&cs_datetime_full_format.') AS analyzetime,
|
|
TO_CHAR(o.savtime, '&&cs_timestamp_full_format.') AS savtime,
|
|
o.rowcnt,
|
|
o.blevel,
|
|
o.leafcnt,
|
|
o.distkey,
|
|
o.samplesize
|
|
FROM dba_indexes_o o
|
|
WHERE o.rn <= 25
|
|
ORDER BY 1, 2, 3, 4, 5, 6, 7 NULLS FIRST, 8 NULLS FIRST
|
|
/
|
|
--
|
|
CLEAR BREAK;
|