'1. IT Story/Scripts'에 해당되는 글 75건

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

set echo off

set lines 132

set pages 5000

set scan on

set verify off

set feedback off

set head on

prompt

accept owner prompt 'Table owner: '

accept table_name prompt 'Table name: '

col owner for a15

col synonym_name for a15

col table_owner for a15

col db_link for a15

col table_name for a30

col tablespace_name for a15

col mb for 999,990

col num_rows for 999,999,990

col blocks for 999,999,990

col empty_blocks for 999,990

col avg_space for 9,990

col chain_cnt for 990

col avg_row_len for 999,990

col avg_col_len for 990

col column_name for a30

col nullable for a4

col num_distinct for 999,999,990

col density for 9.999999

col index_name for a30

col uniq for a4

col blev for 90

col leaf_blocks for 9,999,990

col distinct_keys for 999,999,990

col key_lblocks for 99,990

col key_dblocks for 9,999,990

col clustering_factor for 999,999,990

col column_position format 999

col col for a25

col column_length for 990

col num_buckets for 990

col num_nulls for 999,999,990

select  t.table_name,

        t.tablespace_name,

        t.num_rows,

        t.blocks,

        s.bytes/1048576 mb,

        t.empty_blocks,

        t.chain_cnt,

        t.avg_row_len,

        t.last_analyzed

from    dba_tables t, dba_segments s

where   t.owner=upper(NVL('&&owner',USER)) and t.table_name=upper('&&table_name')

  and   s.owner=t.owner and s.segment_name=t.table_name

  and   s.segment_type <> 'TABLE PARTITION'

union all

select  p.partition_name||' (P)' table_name,

        p.tablespace_name,

        p.num_rows,

        p.blocks,

        s.bytes/1048576 mb,

        p.empty_blocks,

        p.chain_cnt,

        p.avg_row_len,

        p.last_analyzed

from    dba_segments s, dba_tab_partitions p

where   s.segment_type='TABLE PARTITION'

  and   p.table_owner=upper(NVL('&&owner',USER)) and p.table_name=upper('&&table_name')

  and   s.owner=p.table_owner and s.segment_name=p.table_name and s.partition_name=p.partition_name

order by table_name

/

select  column_name, decode(t.data_type,'NUMBER',t.data_type||'('||

        decode(t.data_precision,null,t.data_length||')',

                t.data_precision||','||t.data_scale||')'),

                'DATA',t.data_type,'LONG',t.data_type,

                'ROWID',t.data_type,'MLSLABEL',t.data_type,

                t.data_type||'('||t.data_length||')')||' '||

                decode(t.nullable,'N','NOT NULL','n','NOT NULL',null) col,

        num_distinct,num_nulls,density,num_buckets,avg_col_len,last_analyzed

from    dba_tab_columns t

where   table_name = upper('&table_name') and owner=upper(nvl('&owner',user))

order   by column_name;

select  i.index_name,i.tablespace_name,substr(i.uniqueness,1,4) uniq,i.blevel blev,i.leaf_blocks,i.distinct_keys,

        i.clustering_factor,i.last_analyzed

from    dba_indexes i, dba_segments s

where   i.table_name = upper('&table_name') and i.owner=upper(nvl('&owner',user))

  and   i.owner=s.owner and i.index_name=s.segment_name

  and   s.segment_type <> 'INDEX PARTITION'

union all

select  p.partition_name||' (P)' index_name,p.tablespace_name,substr(i.uniqueness,1,4) uniq,p.blevel blev,p.leaf_blocks,p.distinct_keys,

        p.clustering_factor,p.last_analyzed

from    dba_ind_partitions p, dba_segments s, dba_indexes i

where   i.table_name = upper('&table_name') and i.owner=upper(nvl('&owner',user))

  and   s.segment_name=i.index_name and s.owner=i.owner

  and   p.index_owner=s.owner and p.index_name=s.segment_name and p.partition_name=s.partition_name

  and   s.segment_type = 'INDEX PARTITION'

order   by index_name

/

break on index_name

select  i.index_name,i.column_name,i.column_position,t.num_distinct,t.num_nulls,t.num_buckets

from    dba_ind_columns i, dba_tab_columns t

where   t.owner=upper(nvl('&owner',user)) and i.table_name=upper('&table_name')

  and   i.index_owner=t.owner and i.table_name=t.table_name and t.column_name=i.column_name

order   by index_name,column_position;

clear breaks

set feedback on

1. EXTENSION 설치

2. FOREIGN SERVER 생성

3. PG LOG FILE 테이블 생성

4. PG LOG 조회

select log_time, user_name, database_name, connection_from, command_tag, session_start_time, 

error_severity, sql_state_code,  message, detail, hint, application_name, internal_query,query, query_pos, location 
from dba_pglog_201901
where command_tag != 'idle'
and     database_name = 'TESTDB'
order by 1 desc

https://www.postgresql.org/docs/current/file-fdw.html