OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : 로그스위치 과다 발생 시 확인 사항(Redo Log Switch)
사용자의 대용량 DML(INSERT, UPDATE, DELETE)로 인해
로그 스위치가 과도하게 발생 하게되면 아카이브로그가 많이 발생하고
그로 인해 파일시스템 FULL이 발생할 수 있음
FULL이 되기 전에 용량을 확보해주거나 과도한 로그 스위치를 유발하는 DML을 찾아서 조치 해줘야함
로그 스위치 발생현황 및 부하쿼리를 찾는 과정을 설명함
테스트용 데이터 생성
sys계정으로 샘플테이블 생성
1 2 3 | SQL> create table imsi as select * from dba_segments Table created. |
데이터 복제
1 2 3 4 5 | SQL> insert into imsi select * from imsi; SQL> / SQL> / . . |
커밋
1 2 3 | SQL> commit; Commit complete. |
권한 부여 및 imsi 유저로 접속
1 2 | SQL> grant sysdba, dba to imsi; SQL> conn imsi/imsi |
imsi 유저로 테이블 생성
1 2 3 | SQL> create table imsi as select * from sys.imsi; Table created. |
데이터 update 및 커밋
1 2 3 4 5 6 7 | SQL> update imsi set owner ='IMSI'; 3039232 rows updated. SQL> commit; Commit complete. |
alert log 확인
1 2 3 4 5 6 7 8 9 10 | $ vi alert_ORCL11.log Tue Aug 04 04:07:33 2020 Archived Log entry 83 added for thread 1 sequence 96 ID 0xff204402 dest 1: Thread 1 cannot allocate new log, sequence 98 Checkpoint not complete Current log# 1 seq# 97 mem# 0: /oracle/app/oracle/oradata/ORCL11/redo01.log Current log# 1 seq# 97 mem# 1: /oracle/app/oracle/oradata/ORCL11/redo01_2.log Thread 1 advanced to log sequence 98 (LGWR switch) Current log# 2 seq# 98 mem# 0: /oracle/app/oracle/oradata/ORCL11/redo02.log Current log# 2 seq# 98 mem# 1: /oracle/app/oracle/oradata/ORCL11/redo02_2.log |
Thread 1 cannot allocate new log, sequence 98
Checkpoint not complete
대량 인서트 또는 업데이트로 인해 로그스위치가 빠르게 일어나고 이후 위 메세지가 발생함
(해당 메세지 참조 : https://positivemh.tistory.com/196 )
리두 로그스위치 발생 현황 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | SQL> select to_char(first_time,'MM/DD') ||'|'|| to_char(count(first_time)) ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'00',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'01',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'02',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'03',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'04',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'05',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'06',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'07',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'08',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'09',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'10',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'11',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'12',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'13',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'14',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'15',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'16',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'17',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'18',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'19',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'20',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'21',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'22',1,0)),'99') ||'|'|| to_char(sum(decode(to_char(first_time,'hh24'),'23',1,0)),'99') ||'|' as log_history from v$log_history group by to_char(first_time,'MM/DD') order by to_char(first_time,'MM/DD') / LOG_HISTORY ----------------------------------------------------------------------------------------------------------------------------------------------- 05/31|1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 06/02|1| 0| 0| 0| 0| 0| 0| 0| 0| 1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 06/04|1| 1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 06/06|1| 1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 06/24|2| 0| 0| 0| 0| 0| 0| 2| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 07/03|1| 0| 1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 07/05|1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 07/08|1| 0| 0| 0| 0| 1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 07/11|1| 0| 0| 0| 0| 0| 0| 1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 07/13|1| 0| 0| 1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 07/14|3| 3| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 07/15|2| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 2| 0| 0| 0| 0| 0| 07/17|1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 1| 0| 07/22|3| 3| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 07/24|1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 1| 0| 0| 0| 0| 0| 07/26|1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 07/29|1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 08/04|120| 0| 0| 0| 0| 120| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 18 rows selected. |
평소에는 1~3개 정도의 로그 스위치가 일어나다가
08/04일에 120개의 로그 스위치가 발생함
부하 쿼리 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | SQL> select b.sql_text, a.parsing_schema_name as schema_name , a.elapsed_time_delta/1000/1000 as elapsed_sec , to_char(c.end_interval_time, 'YYYY-MM-DD HH24:MI:SS') as snap_time , a.buffer_gets_delta as buffer_gets , a.rows_processed_delta as rows_processed , a.disk_reads_delta as disk_reads , a.iowait_delta/1000/1000 as iowait_sec , a.cpu_time_delta/1000/1000 as cpu_time_sec , a.physical_write_requests_delta as writes , a.sharable_mem/1024 as mem_kb , a.snap_id, b.sql_id, b.command_type , a.executions_delta, a.sorts_delta, a.instance_number as inst_id from dba_hist_sqlstat a, dba_hist_sqltext b, dba_hist_snapshot c where a.sql_id = b.sql_id and a.snap_id = c.snap_id and c.begin_interval_time >= sysdate - 5 and a.parsing_schema_name not in ('SYS','MDSYS','ORACLE_OCM','DBSNMP') and rownum <= 100 order by elapsed_time_delta desc; SQL_TEXT SCHEMA_NAME ELAPSED_SEC SNAP_TIME BUFFER_GETS ROWS_PROCESSED DISK_READS IOWAIT_SEC CPU_TIME_SEC WRITES MEM_KB SNAP_ID SQL_ID COMMAND_TYPE EXECUTIONS_DELTA SORTS_DELTA INST_ID ---------------------------------------- ------------------------------ ----------- ------------------- ----------- -------------- ---------- ---------- ------------ ---------- ---------- ---------- ------------- ------------ ---------------- ----------- ---------- update imsi set owner ='IMSI' IMSI 44.976613 2020-08-04 05:00:59 9362443 3039232 78790 8.242368 26.003407 630 19.2324219 6137 gq12gkvz05gyc 6 1 0 1 create table imsi as IMSI 8.037251 2020-08-04 05:00:59 136162 3039232 0 .49231 2.116226 0 133.753906 6137 65jps4tmc021m 1 2 0 1 select IMSI .070441 2020-08-04 05:00:59 105 10 15 .004579 .06028 0 31.6269531 6137 f8v9jgjdvur8j 3 1 1 1 trunc(a.disk_reads/a.executions,0 |
27번째 줄에 update 구문이 발견됨 - 실행시간이 44초로 나옴
buffer get, disk_read등 정보를 한번에 확인 가능함
부하 시 wait 이벤트 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 | SQL> col sample_time for a30 col session_serial# for 9999 col event for a45 select to_char(sample_time,'YYYY-MM-DD HH24:MI:SS') session_id, session_serial# as "serial#", user_id, sql_id, sql_plan_operation, event, --p1, --p2, --p3, wait_time, time_waited from v$active_session_history where sample_time between to_date ('20200804 000000', 'yyyymmdd hh24miss') and to_date ('20200804 120000', 'yyyymmdd hh24miss') order by time_waited desc / SESSION_ID serial# USER_ID SQL_ID SQL_PLAN_OPERATION EVENT WAIT_TIME TIME_WAITED ------------------- ---------- ---------- ------------- ------------------------------ --------------------------------------------- ---------- ----------- 2020-08-04 04:04:39 3 0 dgr17gd77qm3p LOAD TABLE CONVENTIONAL log file switch (checkpoint incomplete) 0 2505614 2020-08-04 04:08:10 1 0 (null) (null) log file parallel write 0 1959758 2020-08-04 04:07:32 13 37 65jps4tmc021m LOAD AS SELECT log file switch (checkpoint incomplete) 0 1566919 2020-08-04 04:07:31 1 0 (null) (null) db file async I/O submit 0 1418910 2020-08-04 04:08:10 1 0 (null) (null) control file parallel write 0 1159080 2020-08-04 04:08:10 13 37 gq12gkvz05gyc TABLE ACCESS db file sequential read 0 1143170 2020-08-04 04:05:28 1 0 (null) (null) db file async I/O submit 0 1094747 2020-08-04 03:35:27 5 0 (null) (null) ARCH wait for process start 3 0 999387 2020-08-04 04:07:33 1 0 (null) (null) db file async I/O submit 0 977791 2020-08-04 04:08:27 1 0 (null) (null) db file async I/O submit 0 886591 2020-08-04 04:08:09 13 37 gq12gkvz05gyc TABLE ACCESS db file sequential read 0 796527 2020-08-04 04:08:01 1 0 (null) (null) db file async I/O submit 0 781692 2020-08-04 04:07:35 1 0 (null) (null) db file async I/O submit 0 754652 2020-08-04 04:07:33 1 0 (null) (null) log file parallel write 0 625019 2020-08-04 04:05:29 1 0 (null) (null) db file async I/O submit 0 619524 2020-08-04 04:07:33 13 37 65jps4tmc021m LOAD AS SELECT log buffer space 0 594511 2020-08-04 04:05:30 1 0 (null) (null) db file async I/O submit 0 536962 2020-08-04 04:07:30 1 0 (null) (null) log file parallel write 0 536795 2020-08-04 04:07:30 13 37 65jps4tmc021m LOAD AS SELECT log buffer space 0 535516 2020-08-04 04:07:36 1 0 (null) (null) log file parallel write 0 454389 2020-08-04 04:07:36 13 37 65jps4tmc021m LOAD AS SELECT log buffer space 0 449550 2020-08-04 04:08:00 13 37 gq12gkvz05gyc UPDATE STATEMENT rdbms ipc reply 0 435604 2020-08-04 04:08:27 1 0 (null) (null) log file parallel write 0 422366 2020-08-04 04:08:01 13 37 gq12gkvz05gyc UPDATE STATEMENT rdbms ipc reply 0 407689 2020-08-04 04:08:27 13 37 gq12gkvz05gyc UPDATE log buffer space 0 400846 2020-08-04 04:08:02 13 37 gq12gkvz05gyc UPDATE log file switch (checkpoint incomplete) 0 390364 2020-08-04 04:08:13 13 37 gq12gkvz05gyc TABLE ACCESS db file sequential read 0 365551 2020-08-04 04:08:22 1 0 (null) (null) log file parallel write 0 290746 2020-08-04 04:04:40 3 0 dgr17gd77qm3p INSERT STATEMENT control file parallel write 0 273268 2020-08-04 04:07:30 1 0 (null) (null) control file parallel write 0 262657 2020-08-04 04:08:31 1 0 (null) (null) log file parallel write 0 250827 2020-08-04 04:08:22 13 37 gq12gkvz05gyc TABLE ACCESS db file sequential read 0 249693 2020-08-04 04:07:34 1 0 (null) (null) db file async I/O submit 0 235038 2020-08-04 04:08:29 1 0 (null) (null) log file parallel write 0 232274 2020-08-04 04:07:33 1 0 (null) (null) log file sequential read 0 229696 2020-08-04 04:08:30 1 0 (null) (null) log file parallel write 0 206499 2020-08-04 04:08:29 1 0 (null) (null) db file async I/O submit 0 203019 2020-08-04 04:07:32 1 0 (null) (null) db file async I/O submit 0 192635 2020-08-04 04:08:00 1 0 (null) (null) db file async I/O submit 0 185639 2020-08-04 04:08:30 1 0 (null) (null) db file async I/O submit 0 178731 2020-08-04 04:07:53 1 0 (null) (null) db file async I/O submit 0 164405 2020-08-04 04:08:02 1 0 (null) (null) db file async I/O submit 0 154312 2020-08-04 04:08:29 1 0 (null) (null) control file sequential read 0 151783 2020-08-04 04:08:33 1 0 (null) (null) log file parallel write 0 141146 2020-08-04 04:07:54 1 0 (null) (null) db file async I/O submit 0 138498 2020-08-04 04:04:39 1 0 (null) (null) db file async I/O submit 0 130897 2020-08-04 04:07:57 1 0 (null) (null) log file parallel write 0 92483 2020-08-04 04:04:49 1 0 (null) (null) os thread startup 0 89631 2020-08-04 04:07:57 13 37 gq12gkvz05gyc TABLE ACCESS db file scattered read 0 75814 2020-08-04 03:35:27 1 0 (null) (null) os thread startup 0 68424 2020-08-04 04:07:25 1 0 (null) (null) os thread startup 0 58691 2020-08-04 04:08:07 1 0 (null) (null) log file parallel write 0 55330 2020-08-04 04:08:12 1 0 (null) (null) log file parallel write 0 33031 2020-08-04 04:08:08 1 0 (null) (null) log file parallel write 0 31268 2020-08-04 04:08:12 13 37 gq12gkvz05gyc TABLE ACCESS db file sequential read 0 29854 2020-08-04 05:05:26 1 0 (null) (null) os thread startup 0 27134 2020-08-04 04:07:34 1 0 (null) (null) log file parallel write 0 26816 2020-08-04 04:07:35 1 0 (null) (null) log file parallel write 0 21965 2020-08-04 04:08:03 1 0 (null) (null) db file async I/O submit 0 17052 2020-08-04 04:08:18 1 0 (null) (null) db file async I/O submit 0 16058 2020-08-04 04:07:34 13 37 65jps4tmc021m LOAD AS SELECT log buffer space 0 15955 2020-08-04 04:07:31 1 0 (null) (null) log file parallel write 0 15123 2020-08-04 04:08:07 13 37 gq12gkvz05gyc UPDATE log buffer space 0 14095 2020-08-04 04:08:11 13 37 gq12gkvz05gyc TABLE ACCESS db file sequential read 0 13252 2020-08-04 04:04:36 1 0 (null) (null) db file async I/O submit 0 11480 2020-08-04 04:08:03 13 37 gq12gkvz05gyc TABLE ACCESS db file scattered read 0 10969 2020-08-04 04:04:21 1 0 (null) (null) log file parallel write 0 7430 2020-08-04 04:08:18 1 0 (null) (null) log file parallel write 0 7114 2020-08-04 04:08:16 1 0 (null) (null) db file async I/O submit 0 7039 2020-08-04 04:07:29 1 0 (null) (null) log file parallel write 0 6956 2020-08-04 04:07:55 1 0 (null) (null) log file parallel write 0 5567 2020-08-04 04:07:55 13 37 gq12gkvz05gyc TABLE ACCESS db file scattered read 0 5537 2020-08-04 04:08:15 1 0 (null) (null) log file parallel write 0 5103 2020-08-04 04:07:53 13 37 gq12gkvz05gyc TABLE ACCESS db file scattered read 0 4400 2020-08-04 04:08:24 1 0 (null) (null) log file parallel write 0 4144 2020-08-04 04:04:22 3 0 (null) (null) Log archive I/O 0 3484 2020-08-04 04:07:29 3 0 (null) (null) Log archive I/O 0 2421 2020-08-04 04:04:27 1 0 (null) (null) Log archive I/O 0 2184 2020-08-04 04:08:24 13 37 gq12gkvz05gyc UPDATE STATEMENT Disk file operations I/O 0 2080 2020-08-04 04:08:26 3 0 (null) (null) Log archive I/O 0 1900 2020-08-04 03:35:35 11 0 39m4sx9k63ba2 SELECT STATEMENT db file sequential read 0 1440 2020-08-04 04:08:19 13 37 gq12gkvz05gyc UPDATE STATEMENT Data file init write 0 1400 2020-08-04 05:01:00 181 0 2zd224kurqsr9 (null) db file sequential read 0 1080 2020-08-04 05:06:29 1 0 (null) (null) control file parallel write 0 628 2020-08-04 04:13:28 1 0 (null) (null) db file async I/O submit 0 626 2020-08-04 05:06:32 1 0 (null) (null) control file parallel write 0 579 2020-08-04 04:05:29 65 0 7c2n3gmb50xv9 INDEX db file sequential read 0 510 2020-08-04 04:05:26 65 0 bzscyq07w79ab DELETE db file sequential read 0 452 2020-08-04 05:06:20 1 0 (null) (null) control file parallel write 0 441 2020-08-04 05:06:23 1 0 (null) (null) control file parallel write 0 436 2020-08-04 05:06:26 1 0 (null) (null) control file parallel write 0 414 2020-08-04 05:31:56 1 0 (null) (null) (null) 999650 0 2020-08-04 05:27:43 1 0 (null) (null) (null) 999630 0 2020-08-04 05:23:30 1 0 (null) (null) (null) 999695 0 2020-08-04 05:19:15 1 0 (null) (null) (null) 999827 0 2020-08-04 05:15:01 1 0 (null) (null) (null) 999733 0 2020-08-04 05:10:47 1 0 (null) (null) (null) 999610 0 2020-08-04 05:06:31 1 0 (null) (null) (null) 999674 0 2020-08-04 05:02:15 1 0 (null) (null) (null) 999611 0 2020-08-04 05:00:59 179 0 3fbwp7qdqxk9v LOAD TABLE CONVENTIONAL db file sequential read 0 0 2020-08-04 04:57:57 1 0 (null) (null) (null) 999650 0 2020-08-04 04:53:43 1 0 (null) (null) (null) 999717 0 2020-08-04 04:49:37 1 0 (null) (null) (null) 999774 0 2020-08-04 04:49:28 1 0 (null) (null) (null) 999720 0 2020-08-04 04:49:27 1 0 (null) (null) (null) 999746 0 2020-08-04 04:45:10 1 0 (null) (null) (null) 999667 0 2020-08-04 04:40:53 1 0 (null) (null) (null) 999637 0 2020-08-04 04:36:38 1 0 (null) (null) (null) 999581 0 2020-08-04 04:36:37 1 0 (null) (null) (null) 999730 0 2020-08-04 04:32:22 1 0 (null) (null) (null) 999655 0 2020-08-04 04:28:08 1 0 (null) (null) (null) 999669 0 2020-08-04 04:23:49 1 0 (null) (null) (null) 999640 0 2020-08-04 04:19:33 1 0 (null) (null) (null) 999684 0 2020-08-04 04:15:18 1 0 (null) (null) (null) 999735 0 2020-08-04 04:11:04 1 0 (null) (null) (null) 999689 0 2020-08-04 04:11:03 1 0 (null) (null) (null) 999679 0 2020-08-04 04:08:32 13 37 gq12gkvz05gyc UPDATE STATEMENT (null) 1 0 2020-08-04 04:08:31 13 37 gq12gkvz05gyc UPDATE (null) 4 0 2020-08-04 04:08:30 13 37 gq12gkvz05gyc UPDATE (null) 3 0 2020-08-04 04:08:29 13 37 gq12gkvz05gyc UPDATE STATEMENT (null) 1 0 2020-08-04 04:08:28 13 37 gq12gkvz05gyc TABLE ACCESS (null) 3 0 2020-08-04 04:08:27 3 0 (null) (null) (null) 25 0 2020-08-04 04:08:26 13 37 gq12gkvz05gyc UPDATE (null) 5 0 2020-08-04 04:08:25 13 37 gq12gkvz05gyc UPDATE (null) 4 0 2020-08-04 04:08:23 13 37 gq12gkvz05gyc TABLE ACCESS (null) 3 0 2020-08-04 04:08:22 5 0 (null) (null) (null) 18 0 2020-08-04 04:08:21 13 37 gq12gkvz05gyc UPDATE (null) 4 0 2020-08-04 04:08:20 13 37 gq12gkvz05gyc UPDATE (null) 3 0 2020-08-04 04:08:18 13 37 gq12gkvz05gyc UPDATE (null) 3 0 2020-08-04 04:08:17 13 37 gq12gkvz05gyc UPDATE (null) 7 0 2020-08-04 04:08:17 1 0 (null) (null) (null) 19 0 2020-08-04 04:08:16 13 37 gq12gkvz05gyc UPDATE (null) 4 0 2020-08-04 04:08:15 13 37 gq12gkvz05gyc UPDATE (null) 3 0 2020-08-04 04:08:14 13 37 gq12gkvz05gyc UPDATE (null) 4 0 2020-08-04 04:08:10 3 0 (null) (null) (null) 19 0 2020-08-04 04:08:09 1 0 (null) (null) log file parallel write 0 0 2020-08-04 04:08:09 3 0 (null) (null) (null) 19 0 2020-08-04 04:08:08 13 37 gq12gkvz05gyc UPDATE (null) 3 0 2020-08-04 04:08:06 13 37 gq12gkvz05gyc UPDATE (null) 1465 0 2020-08-04 04:08:05 13 37 gq12gkvz05gyc UPDATE (null) 1592 0 2020-08-04 04:08:04 13 37 gq12gkvz05gyc UPDATE (null) 5177 0 2020-08-04 04:08:04 31 0 buucf6b3gyxdb INDEX (null) 5214 0 2020-08-04 04:07:59 13 37 gq12gkvz05gyc UPDATE (null) 6783 0 2020-08-04 04:07:58 13 37 gq12gkvz05gyc UPDATE (null) 4210 0 2020-08-04 04:07:57 1 0 (null) (null) (null) 18 0 2020-08-04 04:07:56 13 37 gq12gkvz05gyc UPDATE (null) 1578 0 2020-08-04 04:07:54 13 37 gq12gkvz05gyc UPDATE (null) 1569 0 2020-08-04 04:07:52 13 37 gq12gkvz05gyc UPDATE (null) 1927 0 2020-08-04 04:07:51 13 37 gq12gkvz05gyc UPDATE (null) 116 0 2020-08-04 04:07:50 13 37 gq12gkvz05gyc UPDATE (null) 25 0 2020-08-04 04:07:49 13 37 gq12gkvz05gyc UPDATE (null) 2793 0 2020-08-04 04:07:48 13 37 gq12gkvz05gyc TABLE ACCESS (null) 594 0 2020-08-04 04:07:36 5 0 (null) (null) (null) 19 0 2020-08-04 04:07:35 13 37 65jps4tmc021m LOAD AS SELECT (null) 144 0 2020-08-04 04:07:31 13 37 65jps4tmc021m LOAD AS SELECT (null) 122 0 2020-08-04 04:07:30 1 0 (null) (null) db file async I/O submit 0 0 2020-08-04 04:07:30 3 0 (null) (null) (null) 18 0 2020-08-04 04:07:29 1 0 (null) (null) (null) 24792 0 2020-08-04 04:07:29 13 37 65jps4tmc021m CREATE TABLE STATEMENT (null) 142 0 2020-08-04 04:06:43 1 0 (null) (null) (null) 999649 0 2020-08-04 04:06:42 1 0 (null) (null) (null) 999587 0 2020-08-04 04:05:30 65 0 267s83vd49cfa (null) (null) 373 0 2020-08-04 04:05:28 65 0 acc988uzvjmmt DELETE (null) 69 0 2020-08-04 04:05:27 65 0 acc988uzvjmmt DELETE (null) 271 0 2020-08-04 04:04:49 17 0 bjy9j7g4f800f (null) (null) 237 0 2020-08-04 04:04:49 25 0 (null) (null) ADR block file read 0 0 2020-08-04 04:04:40 5 0 (null) (null) (null) 17 0 2020-08-04 04:04:38 3 0 dgr17gd77qm3p LOAD TABLE CONVENTIONAL log file switch (checkpoint incomplete) 0 0 2020-08-04 04:04:37 3 0 dgr17gd77qm3p LOAD TABLE CONVENTIONAL log file switch (checkpoint incomplete) 0 0 2020-08-04 04:04:36 3 0 dgr17gd77qm3p TABLE ACCESS (null) 4845751 0 2020-08-04 04:04:35 3 0 dgr17gd77qm3p LOAD TABLE CONVENTIONAL (null) 4845751 0 2020-08-04 04:04:24 3 0 dgr17gd77qm3p TABLE ACCESS (null) 328 0 2020-08-04 04:04:23 3 0 dgr17gd77qm3p LOAD TABLE CONVENTIONAL (null) 153135 0 2020-08-04 04:04:22 3 0 dgr17gd77qm3p LOAD TABLE CONVENTIONAL (null) 24 0 2020-08-04 04:04:21 3 0 dgr17gd77qm3p LOAD TABLE CONVENTIONAL (null) 177617 0 2020-08-04 04:04:11 3 0 am17fsbsvb2u1 TABLE ACCESS (null) 15 0 2020-08-04 04:02:33 1 0 (null) (null) (null) 999635 0 2020-08-04 04:02:32 1 0 (null) (null) (null) 999651 0 2020-08-04 03:58:17 1 0 (null) (null) (null) 999664 0 2020-08-04 03:54:00 1 0 (null) (null) (null) 999631 0 2020-08-04 03:53:59 1 0 (null) (null) (null) 999678 0 2020-08-04 03:49:44 1 0 (null) (null) (null) 999695 0 2020-08-04 03:48:40 1 0 (null) (null) (null) 999988 0 2020-08-04 03:45:28 1 0 (null) (null) (null) 999652 0 2020-08-04 03:45:26 1 0 (null) (null) (null) 929604 0 2020-08-04 03:45:08 3 0 bbx9x3d8pkvfn TABLE ACCESS (null) 46 0 2020-08-04 03:41:11 1 0 (null) (null) (null) 999642 0 2020-08-04 03:38:28 1 0 aykvshm7zsabd FIXED TABLE (null) 2999611 0 2020-08-04 03:36:57 1 0 (null) (null) (null) 999634 0 2020-08-04 03:35:36 11 0 4u5zq7r9y690a TABLE ACCESS (null) 3 0 2020-08-04 03:35:28 1 0 9x6ykmsbrq6kk (null) (null) 1219 0 2020-08-04 03:35:27 1 0 34bpxbc7c4b2z (null) (null) 8 0 2020-08-04 03:35:26 1 0 (null) (null) ARCH wait for process start 3 0 0 2020-08-04 03:35:26 3 0 1h50ks4ncswfn (null) rdbms ipc reply 0 0 2020-08-04 03:35:25 1 0 92b382ka0qgdt (null) control file heartbeat 0 0 2020-08-04 03:35:24 1 0 (null) (null) os thread startup 0 0 2020-08-04 03:35:24 1 0 92b382ka0qgdt (null) control file heartbeat 0 0 |
25번째 줄에 나오는 sql_id = dgr17gd77qm3p 에 대하여 sql full text 확인
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> set pages 1000 set lines 200 select sql_text from v$SQLTEXT_WITH_NEWLINES where sql_id ='dgr17gd77qm3p' order by piece / SQL_TEXT ---------------------------------------------------------------- insert into imsi select * from imsi |
insert 시 log file switch (checkpoint incomplete) 이벤트가 발생한 것을 알수있음
25번째 줄에 나오는 sql_id = 65jps4tmc021m 에 대하여 sql full text 확인
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> set pages 1000 set lines 200 select sql_text from v$SQLTEXT_WITH_NEWLINES where sql_id ='65jps4tmc021m' order by piece / SQL_TEXT ---------------------------------------------------------------- create table imsi as sys.imsi |
create 시 log file switch (checkpoint incomplete) 이벤트가 발생한 것을 알수있음
30번째 줄에 나오는 sql_id = gq12gkvz05gyc 에 대하여 sql full text 확인
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> set pages 1000 set lines 200 select sql_text from v$SQLTEXT_WITH_NEWLINES where sql_id ='gq12gkvz05gyc' order by piece / SQL_TEXT ---------------------------------------------------------------- update imsi set owner ='IMSI' |
해당 쿼리로 인해 db file sequential read 이벤트가 발생함
특정 시간대의 대기 이벤트를 집계, 분석하는 쿼리
wait 이벤트, sql_id 를 보여 이벤트를 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | SQL> select user_id, sql_id, event, count (*), sum (wait_time), sum (time_waited) from v$active_session_history where sample_time between to_date ('20200804 000000', 'yyyymmdd hh24miss') and to_date ('20200804 120000', 'yyyymmdd hh24miss') and session_type <> 'background' group by user_id, sql_id, event order by count (*) desc / USER_ID SQL_ID EVENT COUNT(*) SUM(WAIT_TIME) SUM(TIME_WAITED) ---------- ------------- --------------------------------------------- ---------- -------------- ---------------- 0 (null) (null) 50 39942206 0 37 gq12gkvz05gyc (null) 28 27884 0 0 (null) log file parallel write 24 0 5432149 0 (null) db file async I/O submit 23 0 8706298 0 (null) control file parallel write 7 0 1424235 37 gq12gkvz05gyc db file sequential read 6 0 2598047 0 dgr17gd77qm3p (null) 6 10022606 0 0 (null) os thread startup 5 0 243880 37 gq12gkvz05gyc db file scattered read 4 0 96720 37 65jps4tmc021m log buffer space 4 0 1595532 0 (null) Log archive I/O 4 0 9989 0 dgr17gd77qm3p log file switch (checkpoint incomplete) 3 0 2505614 37 65jps4tmc021m (null) 3 408 0 37 gq12gkvz05gyc log buffer space 2 0 414941 0 92b382ka0qgdt control file heartbeat 2 0 0 0 (null) ARCH wait for process start 3 2 0 999387 0 acc988uzvjmmt (null) 2 340 0 37 gq12gkvz05gyc rdbms ipc reply 2 0 843293 37 gq12gkvz05gyc Disk file operations I/O 1 0 2080 0 bbx9x3d8pkvfn (null) 1 46 0 0 4u5zq7r9y690a (null) 1 3 0 0 (null) control file sequential read 1 0 151783 0 1h50ks4ncswfn rdbms ipc reply 1 0 0 0 buucf6b3gyxdb (null) 1 5214 0 0 267s83vd49cfa (null) 1 373 0 0 dgr17gd77qm3p control file parallel write 1 0 273268 37 gq12gkvz05gyc log file switch (checkpoint incomplete) 1 0 390364 37 65jps4tmc021m log file switch (checkpoint incomplete) 1 0 1566919 0 (null) log file sequential read 1 0 229696 0 7c2n3gmb50xv9 db file sequential read 1 0 510 0 am17fsbsvb2u1 (null) 1 15 0 0 9x6ykmsbrq6kk (null) 1 1219 0 0 bzscyq07w79ab db file sequential read 1 0 452 0 3fbwp7qdqxk9v db file sequential read 1 0 0 0 34bpxbc7c4b2z (null) 1 8 0 0 aykvshm7zsabd (null) 1 2999611 0 0 (null) ADR block file read 1 0 0 0 bjy9j7g4f800f (null) 1 237 0 0 2zd224kurqsr9 db file sequential read 1 0 1080 0 39m4sx9k63ba2 db file sequential read 1 0 1440 37 gq12gkvz05gyc Data file init write 1 0 1400 41 rows selected. |
로그스위치가 빠르게 일어난 경우 해당 쿼리를 찾기위해선
이벤트 "log file switch (checkpoint incomplete)"로 조건을 줄여서 검색하는것도 방법임
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL> select user_id, sql_id, event, count (*), sum (wait_time), sum (time_waited) from v$active_session_history where sample_time between to_date ('20200804 000000', 'yyyymmdd hh24miss') and to_date ('20200804 120000', 'yyyymmdd hh24miss') and session_type <> 'background' and event = 'log file switch (checkpoint incomplete)' group by user_id, sql_id, event order by count (*) desc USER_ID SQL_ID EVENT COUNT(*) SUM(WAIT_TIME) SUM(TIME_WAITED) ---------- ------------- --------------------------------------------- ---------- -------------- ---------------- 0 dgr17gd77qm3p log file switch (checkpoint incomplete) 3 0 2505614 37 65jps4tmc021m log file switch (checkpoint incomplete) 1 0 1566919 37 gq12gkvz05gyc log file switch (checkpoint incomplete) 1 0 390364 |
여기에 나온 sql_id 3개는 위에서 확인한 create, insert, update 구문이 있는 쿼리임
v$active_session_history를 조회했을때 정보가 찾아지지 않는다면
이미 AWR에 쓰여진것으로 dba_hist_active_sess_history 뷰를 조회하면됨
위 쿼리를 순서대로 실행할 필요는 없음
편한 쿼리를 골라서 사용하면됨
참조 :
https://unioneinc.tistory.com/63
http://wiki.gurubee.net/display/DBSTUDY/ASH+(Active+Session+History)
https://m.blog.naver.com/kwoncharlie/10154315649
https://positivemh.tistory.com/196
https://positivemh.tistory.com/401
https://positivemh.tistory.com/402
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 11gR1 이상버전에서 보이는 Nested Loop 조인 실행계획(Prefetch, vector I/O) (0) | 2021.11.16 |
|---|---|
| 오라클 19c dbms_xplan.display_cursor Hint Report 포맷 (0) | 2021.10.23 |
| 오라클 19c Statspack 리포트 생성 가이드 (2) | 2020.07.28 |
| sql 실행계획 확인 및 cpu 등 성능확인 스크립트 (3) | 2020.03.26 |
| 오라클 데이터펌프 expdp 시 세부 trace log남기기 (0) | 2020.02.23 |