MY COMPASS

-- The SHOW_SPACE routine prints detailed space utilization information for database segments.  desc show_space PROCEDURE show_space  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  P_SEGNAME                      VARCHAR2                IN  P_OWNER                        VARCHAR2                IN     DEFAULT  P_TYPE                         VARCHAR2                IN     DEFAULT  P_PARTITION                ...

--The mystat.sql and its companion, mystat2.sql, are used to show the increase in some Oracle “statistic” before and --after some operation. -- mystat.sql set echo off set verify off column value new_val V define S="&1" set autotrace off select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) = lower('&S') / set echo on --mystat2.sql -- mystat2.sql reports the difference (&V is populated by running the first script, mystat.sql—it uses the -- SQL*Plus NEW_VAL feature for that. It contains the last VALUE selected from the preceding query): set echo off set verify off select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) = lower('&S') / set echo on -- For example, to see how much redo is generated by an UPDATE statement, we can do the following: @mystat "r...

WITH    DBA_WITH_SNAPSHOT AS     (         SELECT  MIN(SNAP_ID) AS BEGIN_SNAP_ID, MAX(SNAP_ID) AS END_SNAP_ID         FROM    DBA_HIST_SNAPSHOT         WHERE   INSTANCE_NUMBER = 1         AND     END_INTERVAL_TIME   >= TO_DATE('2016/01/18 09:00:00', 'YYYY/MM/DD HH24:MI:SS')         AND     BEGIN_INTERVAL_TIME <= TO_DATE('2016/01/18 10:00:00', 'YYYY/MM/DD HH24:MI:SS')        )       ,  DBA_WITH_SQLSTAT AS     (         SELECT  /*+ INLINE PARALLEL(4) */                 A.*         FROM    DBA_HIST_SQLSTAT    A               , DBA_WITH_SNAPSHOT   X         WHERE   1 = 1     ...

/* Runstats Runstats is a tool to compare two different methods of doing the same thing and show which one is superior. You supply the two different methods and Runstats does the rest. Runstats simply measures three key things: • Wall clock or elapsed time: This is useful to know, but not the most important piece of information. • System statistics: This shows, side by side, how many times each approach did something (such as a parse call, for example) and the difference between the two. • Latching: This is the key output of this report. */ --In order to use Runstats, you need to set up access to several V$ views, create a table to hold the statistics, and --create the Runstats package. You will need access to four V$ tables (those magic, dynamic performance tables): --V$STATNAME, V$MYSTAT, V$TIMER and V$LATCH. Here is a view I use: create or replace view stats as select 'STAT...' || a.name name, b.value from v$statname a, v$mystat b where a.statistic# = b.st...

PL/SQL 1 PL/SQL 2

https://www.toadworld.com/platforms/oracle/w/wiki/4952.script-to-generate-ddl-for-synonyms REM ****************************************************************** REM REM FUNCTION: Generate DDL for synonyms. REM REM ****************************************************************** UNDEF ENTER_OWNER_NAME UNDEF ENTER_SYNONYM_NAME SET long 1000 SET serveroutput on SET verify off lines 132 DECLARE v_output CLOB := NULL; v_owner VARCHAR2 (30) := '&&ENTER_OWNER_NAME'; v_synonym_name VARCHAR2 (30) := '&&ENTER_SYNONYM_NAME'; BEGIN DBMS_OUTPUT.put_line ('DDL For Database Synonyms'); FOR tt IN (SELECT owner, synonym_name FROM dba_synonyms WHERE owner LIKE v_owner AND synonym_name LIKE v_synonym_name) LOOP SELECT DBMS_METADATA.get_ddl ('SYNONYM', tt.synonym_name, tt.owner) INTO v_output FROM DUAL; DBMS_OUTPUT.put_line (v_output); ...

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ( OWNNAME => 'USER' , CASCADE => TRUE , NO_INVALIDATE => FALSE , DEGREE => 8 );

-- EVENT MONITORING             SELECT  EVENT       , SUM(TIME_WAITED) SUM       , ROUND(RATIO_TO_REPORT(SUM(TIME_WAITED)) OVER (), 2) AS RATIO FROM    GV$ACTIVE_SESSION_HISTORY WHERE   SAMPLE_TIME BETWEEN SYSDATE - 3/1440 AND SYSDATE GROUP BY         EVENT ORDER BY         SUM DESC         ; -- FIND SQL_ID FROM EVENT SELECT  SQL_ID, EVENT       , SUM(TIME_WAITED) SUM       , ROUND(RATIO_TO_REPORT(SUM(TIME_WAITED)) OVER (), 2) AS RATIO FROM    GV$ACTIVE_SESSION_HISTORY WHERE   SAMPLE_TIME BETWEEN SYSDATE - 3/1440 AND SYSDATE AND     EVENT = 'latch: cache buffers chains' GROUP BY         SQL_ID, EVENT ORDER BY         SUM DESC ; -- FIND SQL TEXT FROM SQL_ID SELECT SQL_FULLTEXT FROM V$SQLAREA WHERE SQL_ID = '5frpptd8mtvx0' ...

SELECT EVENT, A.*     FROM V$ACTIVE_SESSION_HISTORY A    WHERE     SAMPLE_TIME BETWEEN TO_DATE ('20160106 120000',                                           'YYYYMMDD HH24MISS')                              AND TO_DATE ('20160106 163000',                                           'YYYYMMDD HH24MISS')          AND EVENT = 'enq: TX - row lock contention' ORDER BY SAMPLE_ID DESC;

-- AWR SQL Stat History 확인 with w_sqlstat as (     select /*+ inline use_nl(a,b,c) leading(a) index(a (sql_id))*/            a.*,            to_char(b.begin_interval_time,'mm/dd hh24:mi') snap_time     from   dba_hist_sqlstat a          , dba_hist_snapshot b     where  b.dbid             = a.dbid     and    b.instance_number  = a.instance_number     and    b.snap_id          = a.snap_id     and    a.sql_id = '7s4bffkwdq1r4' --    and    a.module like 'BTmapsc090%' --    and    b.instance_number  = 1 --    and    b.dbid = 3107085369 --    and    a.snap_id >= 15817     --and    b.snap_id = 55925     --and   ...

/*  **  특정 모듈에서 수행된 Top SQL 추출하기  */  WITH    DBA_WITH_SNAPSHOT  AS     (          SELECT  MIN(SNAP_ID) AS BEGIN_SNAP_ID, MAX(SNAP_ID) AS END_SNAP_ID          FROM    DBA_HIST_SNAPSHOT          WHERE   INSTANCE_NUMBER = 1          AND     END_INTERVAL_TIME   >= TO_DATE('2016/01/06 16:00:00', 'YYYY/MM/DD HH24:MI:SS')          AND     BEGIN_INTERVAL_TIME <= TO_DATE('2016/01/06 17:00:00', 'YYYY/MM/DD HH24:MI:SS')         )        ,  DBA_WITH_SQLSTAT  AS     (          SELECT  /*+ INLINE PARALLEL(4) */                  A.*          FROM    DBA_HIST_SQLSTAT    A    ...

http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_4201.htm#REFRN23342 SELECT  A.* FROM    DBA_RECYCLEBIN A ORDER BY         A.OWNER, A.ORIGINAL_NAME, A.DROPTIME ;

-------------------------------------------------------------------------------- SELECT GROUP#, STATUS, TYPE, MEMBER, IS_RECOVERY_DEST_FILE   FROM GV$LOGFILE  ORDER BY GROUP#, MEMBER; -------------------------------------------------------------------------------- SELECT *   FROM V$LOG  ORDER BY GROUP#; -------------------------------------------------------------------------------- SELECT A.GROUP#, A.MEMBER, B.STATUS, B.BYTES/1024/1024 AS MBytes   FROM V$LOGFILE A, V$LOG B WHERE A.GROUP# = B.GROUP#  ORDER BY A.GROUP#, A.MEMBER; -------------------------------------------------------------------------------- SELECT *   FROM V$LOG_HISTORY  ORDER BY SEQUENCE# DESC; --------------------------------------------------------------------------------

WITH    WITH_IND_COLUMNS AS     (         SELECT  TABLE_OWNER, TABLE_NAME, INDEX_OWNER, INDEX_NAME               , MIN(CASE WHEN COLUMN_POSITION =  1 THEN          COLUMN_NAME  || ' ' || DESCEND END)              || MIN(CASE WHEN COLUMN_POSITION =  2 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)              || MIN(CASE WHEN COLUMN_POSITION =  3 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)              || MIN(CASE WHEN COLUMN_POSITION =  4 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)              || MIN(CASE WHEN COLUMN_POSITION =  5 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)              || MIN(CASE WHEN COLUMN_POSITION =  6 T...

SELECT *     FROM DBA_SEQUENCES    WHERE     1 = 1          AND SEQUENCE_OWNER NOT IN ('ANONYMOUS',                                     'APEX_030200',                                     'APEX_PUBLIC_USER',                                     'APPQOSSYS',                                     'CTXSYS',                                     'DBSNMP',                                     'DIP', ...

col KSPPINM format a50 col KSPPSTVL format a10 col KSPPDESC format a100 SELECT  A.KSPPINM, B.KSPPSTVL, A.KSPPDESC FROM    X$KSPPI  A       , X$KSPPSV  B WHERE   SUBSTR(A.KSPPINM,1,1) = '_' AND     A.INDX = B.INDX AND     A.KSPPINM IN        (         '_PX_use_large_pool'       , '_b_tree_bitmap_plans'       , '_db_file_exec_read_count'       , '_db_file_optimizer_read_count'       , '_index_partition_large_extents'       , '_memory_imm_mode_without_autosga'       , '_object_statistics'       , '_optim_peek_user_binds'       , '_optimizer_adaptive_cursor_sharing'       , '_optimizer_join_factorization'       , '_optimizer_use_feedback'       , '_partition_large_extents'       , '_quer...

SELECT     a.sid, a.serial#, a.username, a.process, b.object_name, a.blocking_session blocker, a.event,     DECODE(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",     DECODE(a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",     DECODE(a.lockwait, NULL,'NO wait','Wait') "STATUS",     'alter system kill session '''||a.sid||','||a.serial#||''' immediate;' as kill_command_input     ,A.* FROM     v$session a, dba_objects b, v$lock c WHERE     a.sid=c.sid and b.object_id=c.id1 AND c.type in ('TM','TX');

SYSAUX 사용현황 select occupant_name, space_usage_kbytes/1024 "MB" from v$sysaux_occupants order by space_usage_kbytes; SYSAUX SEGMENT 정보 select owner, segment_name, segment_type, bytes/1024/1024 "MB" from dba_segments where tablespace_name = 'SYSAUX' order by bytes; 통계정보 보관주기 확인 select dbms_stats.get_stats_history_retention from dual; 보관주기 변경 exec dbms_stats.alter_stats_history_retention(8); 오래된 통계정보 삭제 (15/10/10 이전 삭제 예시 ) exec dbms_stats.purge_stats(to_timestamp_tz('10-10-2015 00:00:00 Asia/Seoul','DD-MM-YYYY HH24:MI:SS TZR')); AWR 현황 SELECT   snap_id, begin_interval_time, end_interval_time FROM   SYS.WRM$_SNAPSHOT WHERE   snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT) UNION SELECT   snap_id, begin_interval_time, end_interval_time FROM   SYS.WRM$_SNAPSHOT WHERE   snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT) / AWR 삭제 BEGIN    ...

Oracle 10g Partitioning 가이드