|
SELECT
A.OWNER,A.TABLE_NAME,B.MB, NVL(C.PAR_YN,'N') PAR_YN ,O.CREATED, A.LAST_ANALYZED,
'EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ''' || A.OWNER || '''
, TABNAME => ''' || A.TABLE_NAME || ''', CASCADE => FALSE, ESTIMATE_PERCENT =>''' ||
CASE
WHEN B.MB < 1000 THEN 100
WHEN B.MB < 5000 THEN 50
WHEN B.MB < 20000 THEN 25
ELSE 5
END || ''', DEGREE => ''' ||
CASE
WHEN B.MB < 500 THEN 4
WHEN B.MB < 5000 THEN 8
WHEN B.MB < 10000 THEN 12
WHEN B.MB < 20000 THEN 24
ELSE 30
END || CASE
WHEN PAR_YN = 'Y' THEN ''', GRANULARITY => ''GLOBAL''
,METHOD_OPT=>''FOR ALL COLUMNS SIZE 1'',NO_INVALIDATE=>false);'
ELSE ''',METHOD_OPT=>''FOR ALL COLUMNS SIZE 1'',NO_INVALIDATE=>false);'
END AS STAT_GET_DDL
FROM ( SELECT
OWNER
, TABLE_NAME
, LAST_ANALYZED
FROM DBA_TABLES A
WHERE OWNER = 'TESTDB' --IN ('SCOTT','TEST')
-- AND (LAST_ANALYZED IS NULL OR NUM_ROWS <1000)
-- AND TABLE_NAME ='테이블명'
-- AND TABLE_NAME NOT LIKE '%BAK'
-- AND TABLE_NAME NOT LIKE '%TEMP%'
) A
, ( SELECT OWNER
, SEGMENT_NAME, SUM(BYTES)/1024/1024 AS MB
FROM DBA_SEGMENTS
WHERE OWNER = 'TESTDB' --IN ('SCOTT','TEST')
GROUP BY OWNER, SEGMENT_NAME
) B
,( SELECT TABLE_OWNER,TABLE_NAME ,'Y' AS PAR_YN
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER = 'TESTDB' --IN ('SCOTT','TEST')
AND TABLE_NAME NOT LIKE 'BIN%'
GROUP BY TABLE_OWNER,TABLE_NAME
) C
, DBA_OBJECTS O
WHERE 1 = 1
AND A.OWNER = B.OWNER
AND A.TABLE_NAME = B.SEGMENT_NAME
AND A.TABLE_NAME = O.OBJECT_NAME
AND A.OWNER = C.TABLE_OWNER(+)
AND A.TABLE_NAME = C.TABLE_NAME(+)
AND O.OBJECT_TYPE = 'TABLE'
;
|
