카테고리: BOOKS

실제 수행 된 SQL PLAN 을 trace + tkprof 를 수행 하지 않고 실시간으로 Execution Plan   을 확인 하는 쿼리 입니다.   STEP 별 rows 를 확인 하기 위한…

먼저 조회 해야 하는 sql id 의 Child Number 를 확인 합니다. select sql_id, PARSING_SCHEMA_NAME "USER", PLAN_HASH_VALUE, OPTIMIZER_MODE, child_number, parse_calls, USERS_OPENING, USERS_EXECUTING, loads, executions, invalidations, decode(sign(invalidations), 1, (loads-invalidations), 0) reloads,…

  PROMPT ============================= PROMPT ====== DEPENDENCY CHECK ===== PROMPT ============================= SELECT decode(name, UPPER('&&object_name'), 'REF', 'REF BY') ref, d.* FROM dba_dependencies d WHERE name=UPPER('&&object_name') OR referenced_name=UPPER('&&object_name') ORDER BY ref, owner, type,…

  select thread# "Thread#", substr(to_char(first_time,'yyyy/mm/dd'),1,10) "Day", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0))) "00", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0))) "01", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0))) "02", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0))) "03", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0))) "04", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0))) "05", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0))) "06", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0))) "07", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0))) "08", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0))) "09", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0))) "10", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0))) "11",…

SELECT T.NAME , T.BYTES,T.USED,T.FREE,T.PCT_FREE_USED, C.BLOCK_SIZE/1024||'k' BLOCK_SIZE, C.CONTENTS, C.LOGGING, C.EXTENT_MANAGEMENT, C.ALLOCATION_TYPE, C.SEGMENT_SPACE_MANAGEMENT, C.DEF_TAB_COMPRESSION FROM ( SELECT A.TABLESPACE_NAME NAME, SUM(B.BYTES/1048576)/COUNT( DISTINCT A.FILE_ID||'.'||A.BLOCK_ID ) BYTES, SUM(B.BYTES/1048576)/COUNT( DISTINCT A.FILE_ID||'.'||A.BLOCK_ID ) - SUM(A.BYTES/1048576)/COUNT( DISTINCT B.FILE_ID…

시퀀스 OWNER 를 넣고 USED 컬럼을 확인.   SQL> SELECT SEQUENCE_OWNER "OWNER", SEQUENCE_NAME "SEQUENCE", MIN_VALUE "MIN VALUE", MAX_VALUE "MAX VALUE", INCREMENT_BY "INCREASE", CYCLE_FLAG "CYCLE", ORDER_FLAG "ORDER", CACHE_SIZE "CACHE", LAST_NUMBER "LAST…