60 lines
2.4 KiB
MySQL
60 lines
2.4 KiB
MySQL
|
/* Statistics details for table partitions/subpartitios
|
||
|
|
|
||
|
|
Usage: @tspstat <owner> <table> <partition>
|
||
|
|
|
||
|
|
vplesnila 2024-01-29: creation
|
||
|
|
*/
|
||
|
|
|
||
|
|
col owner for a20 head "Owner"
|
||
|
|
col table_name for a25 head "Table name"
|
||
|
|
col PARTITION_POSITION for 999 heading "P#"
|
||
|
|
col PARTITION_NAME for a25 head "Partition name"
|
||
|
|
col SUBPARTITION_NAME for a25 head "Subpartition name"
|
||
|
|
col last_DML for a20 head "Last DML"
|
||
|
|
col last_analyzed for a20 head "Last Analyzed"
|
||
|
|
col STALE_STATS for a7 head "Stale"
|
||
|
|
col NUM_ROWS head "#Rows"
|
||
|
|
col INSERTS head "Table|INS"
|
||
|
|
col UPDATES head "Table|UPD"
|
||
|
|
col DELETES head "Table|DEL"
|
||
|
|
col TRUNCATED head "Table|TRUN"
|
||
|
|
|
||
|
|
break on report skip 1
|
||
|
|
compute sum of INSERTS on report
|
||
|
|
compute sum of UPDATES on report
|
||
|
|
compute sum of DELETES on report
|
||
|
|
|
||
|
|
select
|
||
|
|
tabstat.OWNER
|
||
|
|
,tabstat.TABLE_NAME
|
||
|
|
,tabstat.PARTITION_POSITION
|
||
|
|
,tabstat.PARTITION_NAME
|
||
|
|
,tabstat.SUBPARTITION_NAME
|
||
|
|
,tabstat.NUM_ROWS
|
||
|
|
,tabstat.STALE_STATS
|
||
|
|
,tabstat.LAST_ANALYZED
|
||
|
|
,tabmod.INSERTS
|
||
|
|
,tabmod.UPDATES
|
||
|
|
,tabmod.DELETES
|
||
|
|
,tabmod.TRUNCATED
|
||
|
|
,tabmod.TIMESTAMP last_DML
|
||
|
|
from
|
||
|
|
dba_tab_statistics tabstat
|
||
|
|
,dba_tab_modifications tabmod
|
||
|
|
WHERE
|
||
|
|
tabstat.owner = '&&1'
|
||
|
|
AND tabstat.table_name = '&&2'
|
||
|
|
AND (tabstat.partition_name like '%&&3%' or tabstat.partition_name is NULL)
|
||
|
|
AND (tabstat.subpartition_name like '%&&4%' or tabstat.subpartition_name is NULL)
|
||
|
|
AND tabstat.owner = tabmod.table_owner(+)
|
||
|
|
AND tabstat.table_name = tabmod.table_name(+)
|
||
|
|
AND tabstat.partition_name = tabmod.partition_name(+)
|
||
|
|
AND tabstat.subpartition_name = tabmod.subpartition_name(+)
|
||
|
|
ORDER BY
|
||
|
|
tabstat.owner asc,
|
||
|
|
tabstat.table_name asc,
|
||
|
|
tabstat.partition_position asc,
|
||
|
|
tabstat.subpartition_position asc
|
||
|
|
/
|
||
|
|
|