170 lines
6.4 KiB
SQL
170 lines
6.4 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Statistics of a table (including part, sub-par, ind, ind-part, ind-subpart)
|
|
* Parameters : 1 - SQLId
|
|
* 2 - SQL Child Number
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ --------------------------------------------------------------------
|
|
* 14-Jul-15 Vishal Gupta Added last_ddl_time, last_spec_change and created columns to output
|
|
* 01-Oct-12 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE sql_id
|
|
UNDEFINE plan_hash_value
|
|
|
|
DEFINE sql_id="&&1"
|
|
DEFINE plan_hash_value="&&2"
|
|
|
|
|
|
|
|
COLUMN _child_number NEW_VALUE child_number NOPRINT
|
|
|
|
set term off
|
|
|
|
SELECT DECODE('&&child_number','','0','&&child_number') "_child_number"
|
|
FROM DUAL
|
|
/
|
|
|
|
set term on
|
|
|
|
|
|
PROMPT
|
|
PROMPT ***********************************************************************
|
|
PROMPT * AWR - SQL Statement Object Statistics
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - SQL Id = '&&sql_id'
|
|
PROMPT * - Plan Hash Value = '&&plan_hash_value'
|
|
PROMPT ***********************************************************************
|
|
|
|
COLUMN object_name HEADING "TableName" FORMAT a50
|
|
COLUMN Object_type HEADING "Object|Type" FORMAT a6
|
|
COLUMN stale_stats HEADING "Stale|Stats" FORMAT a5
|
|
COLUMN stattype_locked HEADING "Locked|Stats" FORMAT a5
|
|
COLUMN last_analyzed HEADING "LastAnalyzed" FORMAT a18
|
|
COLUMN user_stats HEADING "U|s|e|r" FORMAT a1 TRUNCATE
|
|
COLUMN sample_size HEADING "SampleSize" FORMAT 9,999,999,999
|
|
COLUMN num_rows HEADING "RowCount" FORMAT 9,999,999,999
|
|
COLUMN blocks HEADING "Blocks" FORMAT 9,999,999,999
|
|
COLUMN Size_MB HEADING "Size(MB)" FORMAT 9,999,999,999
|
|
COLUMN last_ddl_time HEADING "LastDDLTime" FORMAT a18
|
|
COLUMN last_specification_change HEADING "Last|Specification|Change" FORMAT a18
|
|
COLUMN created HEADING "Created" FORMAT a18
|
|
|
|
|
|
WITH objects AS
|
|
(
|
|
SELECT /*+ ALL_ROWS NO_MERGE LEADING (sp) MATERIALIZE */
|
|
DISTINCT
|
|
o.owner
|
|
, o.object_name
|
|
, o.subobject_name
|
|
, REPLACE(o.object_type,'MATERIALIZED','MAT') object_type
|
|
, sp.partition_id
|
|
, TO_CHAR(o.last_ddl_time,'DD-MON-YY HH24:MI:SS') last_ddl_time
|
|
, TO_CHAR(TO_DATE(o.timestamp,'YYYY-MM-DD HH24:MI:SS'),'DD-MON-YY HH24:MI:SS') last_specification_change
|
|
, TO_CHAR(o.created,'DD-MON-YY HH24:MI:SS') created
|
|
FROM dba_hist_sql_plan sp
|
|
JOIN dba_objects o ON o.owner = sp.object_owner AND o.object_name = sp.object_name
|
|
/* Dont join by object id, as it does not cover cases when object has been dropped and recreated*/
|
|
WHERE sp.object_type IS NOT NULL
|
|
AND sp.sql_id = '&&sql_id'
|
|
AND sp.plan_hash_value = '&&plan_hash_value'
|
|
)
|
|
,stats AS
|
|
(
|
|
SELECT /*+ ALL_ROWS NO_MERGE LEADING(o) MATERIALIZE */
|
|
s.owner
|
|
|| '.' || s.table_name
|
|
|| NVL2(s.partition_name,':' || s.partition_name, '')
|
|
|| NVL2(s.subpartition_name,':' || s.subpartition_name, '')
|
|
object_name
|
|
, o.object_type
|
|
, s.stale_stats
|
|
, s.stattype_locked
|
|
, s.last_analyzed
|
|
, s.sample_size
|
|
, s.num_rows
|
|
, s.blocks
|
|
, (seg.bytes)/1024/1024 Size_MB
|
|
, o.last_ddl_time
|
|
, o.last_specification_change
|
|
, o.created
|
|
FROM objects o
|
|
JOIN dba_tab_statistics s ON o.owner = s.owner
|
|
AND o.object_name = s.table_name
|
|
AND NVL(s.partition_name,'%') LIKE NVL(o.subobject_name,'%')
|
|
AND NVL(s.partition_position,0) LIKE NVL(o.partition_id,0)
|
|
JOIN dba_segments seg ON seg.owner = o.owner
|
|
AND seg.segment_name = o.object_name
|
|
--AND seg.segment_type = o.object_type
|
|
AND NVL(o.subobject_name,'%') LIKE NVL(seg.partition_name,'%')
|
|
AND NVL(s.partition_position,0) = NVL(o.partition_id,0)
|
|
WHERE s.table_name NOT LIKE 'BIN$%'
|
|
AND s.table_name NOT LIKE '%==%'
|
|
UNION ALL
|
|
SELECT /*+ ALL_ROWS NO_MERGE LEADING(o) MATERIALIZE */
|
|
s.owner
|
|
|| '.' || s.index_name
|
|
|| NVL2(s.partition_name,':' || s.partition_name, '')
|
|
|| NVL2(s.subpartition_name,':' || s.subpartition_name, '')
|
|
object_name
|
|
, o.object_type
|
|
, s.stale_stats
|
|
, s.stattype_locked
|
|
, s.last_analyzed
|
|
, s.sample_size
|
|
, s.num_rows
|
|
, s.leaf_blocks blocks
|
|
, (seg.bytes)/1024/1024 Size_MB
|
|
, o.last_ddl_time
|
|
, o.last_specification_change
|
|
, o.created
|
|
FROM objects o
|
|
JOIN dba_ind_statistics s ON o.owner = s.owner
|
|
AND o.object_name = s.index_name
|
|
AND NVL(o.subobject_name,'%') = NVL(s.partition_name,'%')
|
|
JOIN dba_segments seg ON seg.owner = o.owner
|
|
AND seg.segment_name = o.object_name
|
|
-- AND seg.segment_type = o.object_type
|
|
AND NVL(seg.partition_name,'%') = NVL(o.subobject_name,'%')
|
|
WHERE s.table_name NOT LIKE 'BIN$%'
|
|
AND s.index_name NOT LIKE 'BIN$%'
|
|
AND s.table_name NOT LIKE '%==%'
|
|
AND s.index_name NOT LIKE '%==%'
|
|
)
|
|
SELECT /*+ ALL_ROWS */
|
|
object_name
|
|
, object_type
|
|
, stale_stats
|
|
, stattype_locked
|
|
, to_char(s.last_analyzed,'DD-MON-YY HH24:MI:SS') last_analyzed
|
|
, sample_size
|
|
, num_rows
|
|
, blocks
|
|
, Size_MB
|
|
, last_ddl_time
|
|
, last_specification_change
|
|
, created
|
|
FROM stats s
|
|
--ORDER BY s.last_analyzed ASC
|
|
ORDER BY object_name
|
|
;
|
|
|
|
|
|
|
|
@@footer
|