'IT & 인생'에 해당되는 글 473건

SET HEADING OFF

Set lines 200 pages 200

with AASSTAT as (

           select

                 decode(n.wait_class,'User I/O','User I/O',

                                     'Commit','Commit',

                                     'Wait')                               CLASS,

                 sum(round(m.time_waited/m.INTSIZE_CSEC,3))                AAS

           from  v$waitclassmetric  m,

                 v$system_wait_class n

           where m.wait_class_id=n.wait_class_id

             and n.wait_class != 'Idle'

           group by  decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait')

          union

             select 'CPU_ORA_CONSUMED'                                     CLASS,

                    round(value/100,3)                                     AAS

             from v$sysmetric

             where metric_name='CPU Usage Per Sec'

               and group_id=2

          union

            select 'CPU_OS'                                                CLASS ,

                    round((prcnt.busy*parameter.cpu_count)/100,3)          AAS

            from

              ( select value busy from v$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt,

              ( select value cpu_count from v$parameter where name='cpu_count' )  parameter

          union

             select

               'CPU_ORA_DEMAND'                                            CLASS,

               nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS

             from v$active_session_history ash

             where SAMPLE_TIME > sysdate - (60/(24*60*60))

)

select

       ( decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) +

       CPU_ORA_CONSUMED +

        decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED ))) CPU_TOTAL,

       decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) CPU_OS,

       CPU_ORA_CONSUMED CPU_ORA,

       decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED )) CPU_ORA_WAIT,

       COMMIT,

       USER_IO,

       WAIT

from (

select

       sum(decode(CLASS,'CPU_ORA_CONSUMED',AAS,0)) CPU_ORA_CONSUMED,

       sum(decode(CLASS,'CPU_ORA_DEMAND'  ,AAS,0)) CPU_ORA_DEMAND,

       sum(decode(CLASS,'CPU_OS'          ,AAS,0)) CPU_OS,

       sum(decode(CLASS,'Commit'          ,AAS,0)) COMMIT,

       sum(decode(CLASS,'User I/O'        ,AAS,0)) USER_IO,

       sum(decode(CLASS,'Wait'            ,AAS,0)) WAIT

from AASSTAT)

/

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple,
    pg_stat_get_live_tuples(c.oid) AS live_tuple,
    pg_stat_get_dead_tuples(c.oid) AS dead_tupple,
    round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) 

    + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
    round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) 

    + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
    pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size,
    pg_size_pretty(pg_relation_size(c.oid)) as relation_size
FROM pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
WHERE pg_stat_get_live_tuples(c.oid) > 0
AND c.relname NOT LIKE 'pg_%'
ORDER BY dead_tupple DESC;

1. 통계정보 백업테이블 DROP
EXEC DBMS_STATS.DROP_STAT_TABLE('EMP','STATS'); 

2. 통계정보 백업테이블 CREATE
EXEC DBMS_STATS.CREATE_STAT_TABLE('EMP','STATS'); 

-- 테이블 조회
SELECT * FROM STATS;

3. 통계정보 백업(해당유저의 모든 테이블과 인덱스)
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME=>'EMP',STATTAB=>'STATS'); 

4. 통계정보 백업(해당유저의 테이블)
--- TABLE
EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'SCOTT', TABNAME=>'테이블명', STATTAB=>'STATS', CASCADE=>TRUE); 

-- INDEX
EXEC DBMS_STATS.IMPORT_INDEX_STATS(OWNNAME=>'SCOTT', TABNAME=>'테이블명', STATTAB=>'STATS', CASCADE=>TRUE); 

5. 통계정보 백업 테이블을 다시 원복
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS(OWNNAME=>'SCOTT', STATTAB=>'STATS');

6. 통계정보삭제
EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'SCOTT', TABNAME=>'EMP_TABLE_STATS');
EXEC DBMS_STATS.DELETE_INDEX_STATS(OWNNAME=>'SCOTT', INDNAME=>'IDX_EMP_STATS');

7. 통계정보강제로 셋팅
EXEC DBMS_STATS.SET_COLUMN_STATS(OWNNAME => 'SCOTT', TABNAME => 'EMP', COLNAME => 'EMPNO'
, DISTCNT => 1
, DENSITY => 0.1
, NULLCNT => 1024252
, AVGCLEN => 1
);
 
EXEC DBMS_STATS.SET_INDEX_STATS(OWNNAME => 'SCOTT', INDNAME => 'IDX_EMP_PK'
, NUMROWS  => 100000
, NUMLBLKS => 100
, NUMDIST  => 100000
, CLSTFCT  => 200
, INDLEVEL => 2
);

 
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' 
;