모든 오라클 데이타베이스를 정밀하게 튜닝하기 위해서는 히스토리컬 데이타 수집 메커니즘이 필요하며, 또 변경사항들이 데이타베이스 성능에 미치는 영향을 보여주는 리포트로 이 데이타를 번역하는 기능이 필요하다. 오라클의 새로운 STATSPACK 유틸리티는 STATSPACK 테이블에 대한 트렌드 분석 리포트를 작성하는 기능과 함께 필요한 데이타를 수집할 수 있는 기능을 제공한다.
토탈 시계열 리포트
이들 리포트들은 지정된 기간 중에 발생한 데이타베이스의 전반적인 트렌드를 보여주고 있다. MS-Excel로 작성한 |그림1|의 예제는 데이타베이스 증가율을 나타내기 위해 추가한 선형 회귀선으로 표시한, 데이타베이스의 총 바이트 수에 대한 리포트이다.
[그림1] 데이타베이스 크기에 대한 시계열 리포트
일간 리포트
일간 리포트는 시간별로 묶은 시간별 트렌드를 나타내는 것이다. |그림 2|의 예제에서는 그 날의 시간별로 평균을 낸 평균 읽기 I/O와 쓰기 I/O에 대한 사인을 볼 수있다. 이 예제에서는 이 데이타베이스가 오전 7시에 최고점의 읽기 I/O 사인을 갖고 있으며, 다시 오후 8시에 최고점에 도달했다는 사실을 확인할 수 있다. 또한 이 데이타베이스는 상대적으로 일정한 쓰기 사인을 가지고 있다는 사실도 알 수 있다.
[그림2] 시간대별 평균 I/O 플롯
주간 리포트
|그림 3|에서는 요일별로 평균을 낸 디스크 정렬을 볼 수 있다. 이 그림에서는 디스크 정렬이 월요일에 높고, 화요일에 최고점에 도달하며, 수요일에 높았다가 목요일과 금요일에는 떨어지는 확실한 사인을 볼 수 있다. 이 리포트는 DBA에게 잠재적으로 TEMP 테이블스페이스에 높은 영향을 미칠 수 있는 시간대를 신속하게 알려 줄 것이다.
[그림3]’요일별’ 평균 STATSPACK Query
트렌드 분석에 가장 적합한 STATSPACK 데이타
STATSPACK에서 사용할 수 있는 통계가 수백 개이기 때문에 과연 어떤 메트릭스가 가장 유용한가라는 의문이 제기된다. 필자는 다음과 같은 메트릭스를 선정했다.
* 데이타 버퍼 적중률
이는 init.ora 파일의 db_block_buffers 패러미터로 정의된 대로 오라클 데이타 버퍼의 효율성을 측정한다.
• 정렬(Sort)
데이타베이스에서의 정렬 규모를 모니터링하는 것이 중요하며 특히 TEMP 테이블스페이스(정렬(디스크))에서 수행된 정렬 규모를 더욱 철저하게 모니터링해야 한다.
• 물리적 디스크 읽기
I/O 감축의 일차적 목적은 오라클 튜닝이기 때문에 시간별 디스크 읽기를 보여주는
트렌드 리포트는 재순차(Re-sequence) 로우를 인덱스 순서로 테이블을 재구성하는데 따른 영향을 측정하는 데 매우 유용할 수 있다.
• 물리적 디스크 쓰기
디스크 쓰기 규모는 오라클 데이타베이스의 전체 처리 성능에 상당한 영향을 미치게되며, 롤백 세그먼트 활동이 중요한 경우 특히 중요하다.
• I/O 대기
이는 I/O 경합(Contention)을 확인하고 수정하는 데 사용될 수 있는 매우 중요한메트릭스이다. 높은 I/O 대기 상태는 2개 이상의 데이타 파일에 동시에 액세스되는경우에 발생하며, DASD의 읽기-쓰기 헤드 이동은 디스크가 I/O 종료를 대기시키는 원인이 되기도 한다.
• 버퍼 비지 웨이트(Buffer Busy Waits)
버퍼 버스트(Bust) 대기는 오라클 데이타 블록이 버퍼에 상주하지만 필요한 프로그램이 데이타 블록을 읽어들이기 위해 대기해야만 하는 경우 일어난다. 버퍼 비지 웨이트는 일반적으로 1개 테이블이 동시에‘UPDATE’혹은‘INSERT DML’을 갖고 있으며 단지 1 개의 프리리스트(Freelist)만이 테이블용으로 정의돼 있는 경우 일어날 수 있다.
• 리두 로그 공간 요구(Redo Log Space Request)
리두 로그 공간 요구는 로그 버퍼가 리두 로그 활동 볼륨을 수용하기에는 너무 작을
때 발생한다.
• 래치 핀 적중률(Latch Pin Hit Ratio)
데이타베이스 인스턴스의 핀 적중률은 init.ora 파일의 shared_pool_size의 크기조정에 대한 좋은 아이디어를 제공한다.
• 테이블 페치 컨티뉴드 로우(Table Fetch Continued Row)
이 메트릭스는 데이타베이스와 연결된 로우들에 대한 전반적인 지시를 제공한다. 연결이 과도한 경우, 질의 서비스에 추가 I/O가 요청되며 DBA는 테이블의 PCTFREE를 늘리고 테이블이 체인들을 해제하도록 재구성해야 한다.
STATSPACK 테이블의 구조를 이해하고 약간의 연습을 거치면, 사용자 정의형 리포트를 손쉽게 작성할 수 있다. 일단 성능 데이타가 포착되면, STATSPACK 리포트를 테이블에 대해 실행시킬 수 있다. 또한 선형 회귀와 예측 모델을 결합하고 있기 때문에 DBA는 이전 소모율을 토대로 디스크 메모리를 더 주문해야 하는 시기를 예측할 수 있다.
< STATSPACK 테이블에서 성능 트랜드 데이타 추출 쿼리>
|
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 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 |
alter session set nls_date_format = 'YYYY-MM-DD-HH24:MI:SS' ; prompt ******************************************************* prompt * * prompt * Purpose : DATABASE BUFFER CACHE * prompt * Genarated : 2013/12/26 by haisins * prompt * * prompt ******************************************************* prompt spool DATABASE_BUFFER_CACHE.csv prompt SNAP_TIME,DB BLOCK GETS,CONSISTENT GETS,LOGICAL READS,PHYSICAL READS,BUFFER CACHE HIT RATIO,BUFFER BUSY WAITS,BUFFER BUSY WAIT RATIO SELECT snap_time ||','|| MAX(DECODE(name, 'db block gets', change))||','|| MAX(DECODE(name, 'consistent gets', change)) ||','|| (MAX(DECODE(name, 'db block gets', change))+MAX(DECODE(name, 'consistent gets', change)))||','|| MAX(DECODE(name, 'physical reads', change)) ||','|| ROUND((MAX(DECODE(name, 'db block gets', change)) + MAX(DECODE(name, 'consistent gets', change)) - MAX(DECODE(name, 'physical reads', change))) /(MAX(DECODE(name, 'db block gets', change)) + MAX(DECODE(name, 'consistent gets', change))), 4) ||','|| MAX(DECODE(name, 'buffer busy waits', change)) ||','|| ROUND(MAX(DECODE(name, 'buffer busy waits', change)) /(MAX(DECODE(name, 'db block gets', change)) + MAX(DECODE(name, 'consistent gets', change))), 4) FROM (SELECT t.snap_time , s.name , s.value change FROM perfstat.stats$sysstat s , perfstat.stats$SNAPSHOT t WHERE s.snap_id = t.snap_id and name in ('db block gets' , 'consistent gets', 'physical reads') ) GROUP BY snap_time order by 1 / spool off prompt ******************************************************* prompt * * prompt * Purpose : DATABASE WAIT STAT * prompt * Genarated : 2013/12/26 by haisins * prompt * * prompt ******************************************************* prompt spool WAIT_STAT.csv prompt SNAP_TIME,CLASS,WAIT_COUNT,TIME SELECT SNAP_TIME||','||CLASS||','||WAIT_COUNT||','||TIME FROM (select decode(row_number() over(partition by class order by snap_time),1,'TOTAL' ,snap_time || ' ~ ' || lag(snap_time) over(partition by class order by snap_time)) snap_time ,class ,decode(row_number() over(partition by class order by snap_time) ,1,substr(max(lpad(snap_time,6,'0')||wait_count) over(partition by class),7) - wait_count ,wait_count - lag(wait_count) over(partition by class order by snap_time)) wait_count ,decode(row_number() over(partition by class order by snap_time) ,1,substr(max(lpad(snap_time,6,'0')||time) over(partition by class),7) - time ,time - lag(time) over(partition by class order by snap_time)) time from perfstat.stats$waitstat s , perfstat.stats$SNAPSHOT t WHERE s.snap_id = t.snap_id and s.wait_count <> 0 ) ORDER BY snap_time, wait_count DESC, upper(class) / spool off prompt ******************************************************* prompt * * prompt * Purpose : Library Cache Miss Ratio * prompt * Genarated : 2013/12/26 by haisins * prompt * * prompt ******************************************************* prompt spool Library_Cache_Miss_Ratio.csv prompt SNAP_TIME,Library_Cache_Hit_Ratio SELECT snap_time ||','||ROUND(DECODE(gets,0,1,gethits / gets) * 100,2) FROM (SELECT DECODE(ROW_NUMBER() OVER(ORDER BY snap_time),1,'TOTAL' ,snap_time || ' ~ ' || lag(snap_time) OVER(ORDER BY snap_time)) snap_time ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||SUM(gets)) OVER(),7)-SUM(gets) ,SUM(gets) - lag(SUM(gets)) OVER(ORDER BY snap_time)) gets ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||SUM(gethits)) OVER(),7)-SUM(gethits) ,SUM(gethits) - lag(SUM(gethits)) OVER(ORDER BY snap_time)) gethits FROM perfstat.stats$librarycache s, perfstat.stats$SNAPSHOT t WHERE s.snap_id = t.snap_id GROUP BY snap_time ) order by 1; spool off prompt ******************************************************* prompt * * prompt * Purpose : Dictionary Cache Miss Ratio * prompt * Genarated : 2013/12/26 by haisins * prompt * * prompt ******************************************************* prompt spool Dictionary_Cache_Miss_Ratio.csv prompt SNAP_TIME,Dictionary_Cache_Hit_Ratio SELECT snap_time ||','||ROUND(DECODE(gets,0,1,1-(getmisses/gets))*100,2) FROM (SELECT DECODE(ROW_NUMBER() OVER(ORDER BY snap_time),1,'TOTAL' ,snap_time || ' ~ ' || lag(snap_time) OVER(ORDER BY snap_time)) snap_time ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||SUM(gets)) OVER(),7)-SUM(gets) ,SUM(gets) - lag(SUM(gets)) OVER(ORDER BY snap_time)) gets ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||SUM(getmisses)) OVER(),7)-SUM(getmisses) ,SUM(getmisses) - lag(SUM(getmisses)) OVER(ORDER BY snap_time)) getmisses FROM perfstat.stats$rowcache_summary s, perfstat.stats$SNAPSHOT t WHERE s.snap_id = t.snap_id GROUP BY snap_time ) order by 1; spool off prompt ******************************************************* prompt * * prompt * Purpose : 서버별 I/O당 평균 블록 읽기 * prompt * Genarated : 2013/12/26 by haisins * prompt * * prompt ******************************************************* prompt spool Physical_Io_Reads_Average.csv prompt SNAP_TIME,Phys_Blks_Rd/Phys_Reads SELECT snap_time ||','||ROUND(phyblkrd/phyrds,2) FROM (SELECT DECODE(ROW_NUMBER() OVER(ORDER BY snap_time),1,'TOTAL' ,snap_time || ' ~ ' || lag(snap_time) OVER(ORDER BY snap_time)) snap_time ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||SUM(phyblkrd)) OVER(),7)-SUM(phyblkrd) ,SUM(phyblkrd) - lag(SUM(phyblkrd)) OVER(ORDER BY snap_time)) phyblkrd ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||SUM(phyrds)) OVER(),7)-SUM(phyrds) ,SUM(phyrds) - lag(SUM(phyrds)) OVER(ORDER BY snap_time)) phyrds FROM perfstat.stats$filestatxs s, perfstat.stats$SNAPSHOT t WHERE s.snap_id = t.snap_id GROUP BY snap_time ) order by 1 ; spool off prompt ******************************************************* prompt * * prompt * Purpose : Tablespace별 I/O Operation * prompt * Genarated : 2013/12/26 by haisins * prompt * * prompt ******************************************************* prompt spool TABLESPACE_IO_OPERATION.csv prompt SNAP_TIME,TABLESPACE,READS,BLKS_READ,READ_TIME,WRITES,BLKS_WRT,WRITE_TIME,WAIT_COUNT SELECT snap_time ||','||tsname ||','||phyrds ||','||phyblkrd ||','||readtim ||','||phywrts ||','||phyblkwrt ||','||writetim ||','||wait_count FROM (SELECT DECODE(ROW_NUMBER() OVER(PARTITION BY tsname ORDER BY tsname,snap_time),1,'TOTAL' ,snap_time || ' - ' || lag(snap_time) OVER(PARTITION BY tsname ORDER BY snap_time)) snap_time ,tsname ,DECODE(ROW_NUMBER() OVER(PARTITION BY tsname ORDER BY tsname, snap_time) ,1,substr(max(lpad(snap_time,6,'0')||SUM(phyblkrd)) OVER(PARTITION BY tsname),7)-SUM(phyblkrd) ,SUM(phyblkrd) - lag(SUM(phyblkrd)) OVER(ORDER BY tsname, snap_time)) phyblkrd ,DECODE(ROW_NUMBER() OVER(PARTITION BY tsname ORDER BY tsname, snap_time) ,1,substr(max(lpad(snap_time,6,'0')||SUM(phyrds)) OVER(PARTITION BY tsname),7)-SUM(phyrds) ,SUM(phyrds) - lag(SUM(phyrds)) OVER(ORDER BY tsname, snap_time)) phyrds ,DECODE(ROW_NUMBER() OVER(PARTITION BY tsname ORDER BY tsname, snap_time) ,1,substr(max(lpad(snap_time,6,'0')||SUM(readtim)) OVER(PARTITION BY tsname),7)-SUM(readtim) ,SUM(readtim) - lag(SUM(readtim)) OVER(ORDER BY tsname, snap_time)) readtim ,DECODE(ROW_NUMBER() OVER(PARTITION BY tsname ORDER BY tsname, snap_time) ,1,substr(max(lpad(snap_time,6,'0')||SUM(phywrts)) OVER(PARTITION BY tsname),7)-SUM(phywrts) ,SUM(phywrts) - lag(SUM(phywrts)) OVER(ORDER BY tsname, snap_time)) phywrts ,DECODE(ROW_NUMBER() OVER(PARTITION BY tsname ORDER BY tsname, snap_time) ,1,substr(max(lpad(snap_time,6,'0')||SUM(phyblkwrt)) OVER(PARTITION BY tsname),7)-SUM(phyblkwrt) ,SUM(phyblkwrt) - lag(SUM(phyblkwrt)) OVER(ORDER BY tsname, snap_time)) phyblkwrt ,DECODE(ROW_NUMBER() OVER(PARTITION BY tsname ORDER BY tsname, snap_time) ,1,substr(max(lpad(snap_time,6,'0')||SUM(writetim)) OVER(PARTITION BY tsname),7)-SUM(writetim) ,SUM(writetim) - lag(SUM(writetim)) OVER(ORDER BY tsname, snap_time)) writetim ,DECODE(ROW_NUMBER() OVER(PARTITION BY tsname ORDER BY tsname, snap_time) ,1,substr(max(lpad(snap_time,6,'0')||SUM(wait_count)) OVER(PARTITION BY tsname),7)-SUM(wait_count) ,SUM(wait_count) - lag(SUM(wait_count)) OVER(ORDER BY tsname, snap_time)) wait_count FROM perfstat.stats$filestatxs s, perfstat.stats$SNAPSHOT t WHERE s.snap_id = t.snap_id GROUP BY snap_time, tsname ) order by 1 ; spool off prompt ******************************************************* prompt * * prompt * Purpose :DBMS LOAD * prompt * Genarated : 2013/12/26 by haisins * prompt * * prompt ******************************************************* prompt spool DBMS_LOAD.csv prompt SNAP_TIME,DBWR_Checkpoints,LRU_Queue_Avg_Reuse_Buffer,LRU_Buffer_Scan_Avg,Summed_Dirty_Queue_Length,Free_Buffer_Scan_Ratio SELECT snap_time ||','||DBWR_checkpoints ||','||DBWR_free_buffers_found || '/' || DBWR_make_free_requests ||','||DBWR_buffers_scanned || '/' || DBWR_lru_scans ||','||summed_dirty_queue_length ||','||free_buffer_inspected / free_buffer_requested FROM (SELECT DECODE(ROW_NUMBER() OVER(ORDER BY snap_time),1,'TOT' ,snap_time || ' - ' || lag(snap_time) OVER(ORDER BY snap_time)) snap_time ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'DBWR checkpoints',value))) OVER(),7) -MIN(DECODE(name,'DBWR checkpoints',value)) ,MIN(DECODE(name,'DBWR checkpoints',value)) -lag(MIN(DECODE(name,'DBWR checkpoints',value))) OVER(ORDER BY snap_time)) AS DBWR_Checkpoints ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'DBWR free buffers found',value))) OVER(),7) -MIN(DECODE(name,'DBWR free buffers found',value)) ,MIN(DECODE(name,'DBWR free buffers found',value)) -lag(MIN(DECODE(name,'DBWR free buffers found',value))) OVER(ORDER BY snap_time)) AS DBWR_free_buffers_found ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'DBWR make free requests',value))) OVER(),7) -MIN(DECODE(name,'DBWR make free requests',value)) ,MIN(DECODE(name,'DBWR make free requests',value)) -lag(MIN(DECODE(name,'DBWR make free requests',value))) OVER(ORDER BY snap_time)) AS DBWR_make_free_requests ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'DBWR buffers scanned',value))) OVER(),7) -MIN(DECODE(name,'DBWR buffers scanned',value)) ,MIN(DECODE(name,'DBWR buffers scanned',value)) -lag(MIN(DECODE(name,'DBWR buffers scanned',value))) OVER(ORDER BY snap_time)) AS DBWR_buffers_scanned ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'DBWR lru scans',value))) OVER(),7) -MIN(DECODE(name,'DBWR lru scans',value)) ,MIN(DECODE(name,'DBWR lru scans',value)) -lag(MIN(DECODE(name,'DBWR lru scans',value))) OVER(ORDER BY snap_time)) AS DBWR_lru_scans ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'summed dirty queue length',value))) OVER(),7) -MIN(DECODE(name,'summed dirty queue length',value)) ,MIN(DECODE(name,'summed dirty queue length',value)) -lag(MIN(DECODE(name,'summed dirty queue length',value))) OVER(ORDER BY snap_time)) AS summed_dirty_queue_length ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'free buffer inspected',value))) OVER(),7) -MIN(DECODE(name,'free buffer inspected',value)) ,MIN(DECODE(name,'free buffer inspected',value)) -lag(MIN(DECODE(name,'free buffer inspected',value))) OVER(ORDER BY snap_time)) AS free_buffer_inspected ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'free buffer requested',value))) OVER(),7) -MIN(DECODE(name,'free buffer requested',value)) ,MIN(DECODE(name,'free buffer requested',value)) -lag(MIN(DECODE(name,'free buffer requested',value))) OVER(ORDER BY snap_time)) AS free_buffer_requested FROM perfstat.stats$sysstat s, perfstat.stats$SNAPSHOT t WHERE s.snap_id = t.snap_id AND name in ('DBWR checkpoints' ,'DBWR free buffers found','DBWR make free requests' ,'DBWR buffers scanned','DBWR lru scans' ,'summed dirty queue length','write requests' ,'free buffer inspected', 'free buffer requested' ) GROUP BY snap_time ) order by 1 ; spool off prompt ******************************************************* prompt * * prompt * Purpose :Redo Latch Hit Ratios * prompt * Genarated : 2013/12/26 by haisins * prompt * * prompt ******************************************************* prompt spool Redo_Latch_Hit_Ratios.csv prompt snap_time,Redo_Latch_Hit_Ratios SELECT snap_time ||','||ROUND(DECODE(gets,0,0,(gets-misses)/gets),3) FROM (SELECT DECODE(ROW_NUMBER() OVER(ORDER BY snap_time),1,'TOTAL' ,snap_time || ' ~ ' || lag(snap_time) OVER(ORDER BY snap_time)) snap_time ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||SUM(gets)) OVER(),7)-SUM(gets) ,SUM(gets) - lag(SUM(gets)) OVER(ORDER BY snap_time)) gets ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||SUM(misses)) OVER(),7)-SUM(misses) ,SUM(misses) - lag(SUM(misses)) OVER(ORDER BY snap_time)) misses FROM perfstat.stats$latch s, perfstat.stats$SNAPSHOT t WHERE s.snap_id = t.snap_id AND name LIKE '%redo%' GROUP BY snap_time ) order by 1 ; spool off prompt ******************************************************* prompt * * prompt * Purpose : DBMS_PERPOMANCE * prompt * Genarated : 2013/12/26 by haisins * prompt * * prompt ******************************************************* prompt spool DBMS_PERPOMANCE_DETAIL.csv prompt snap_time,Log_Space_Request_Ratio,Log_Buffer_Retry_Ratio,Cluster_Key_Ratio,Sort_Overflow_Ratio,Index_Use_Ratio,Chained_Row_Ratio,Rows_Per_Sort_Ratio,Cursors_Opened_Per_Transaction,Recursive_to_User_Call_Ratio,Parse_Count_Ratio,Enqueue_timeouts,Enqueue_releases,Enqueue_requests,Nowait_enqueue_request SELECT snap_time ||','||ROUND(DECODE(redo_entries,0,NULL,redo_log_space_requests / redo_entries),2) ||','||ROUND(DECODE(redo_entries,0,NULL,redo_buffer_allocation_retries / redo_entries),2) ||','||ROUND(DECODE(cluster_key_scans,0,NULL,cluster_key_scan_block_gets / cluster_key_scans),2) ||','||ROUND(DECODE(sorts_disk+sorts_memory,0,NULL,sorts_disk / (sorts_disk+sorts_memory)),2) ||','||ROUND(DECODE(table_fetch_by_rowid+table_scan_rows_gotten,0,NULL,table_fetch_by_rowid / table_fetch_by_rowid+table_scan_rows_gotten),2) ||','||ROUND(DECODE(table_fetch_by_rowid+table_scan_rows_gotten,0,NULL,table_fetch_continued_row / table_fetch_by_rowid+table_scan_rows_gotten),2) ||','||ROUND(DECODE(sorts_disk+sorts_memory,0,NULL,sorts_rows / (sorts_disk+sorts_memory)),2) ||','||ROUND(DECODE(user_calls,0,NULL,opened_cursors_cumulative / user_calls),2) ||','||ROUND(DECODE(user_calls,0,NULL,recursive_calls / user_calls),2) ||','||ROUND(DECODE(user_calls,0,NULL,parse_count_total / user_calls),2) ||','||enqueue_timeouts ||','||enqueue_releases ||','||enqueue_requests ||','||(1 - ROUND(DECODE(enqueue_requests,0,NULL,enqueue_waits / enqueue_requests),2) ) FROM (SELECT DECODE(ROW_NUMBER() OVER(ORDER BY snap_time),1,'TOTAL' ,snap_time || ' ~ ' || lag(snap_time) OVER(ORDER BY snap_time)) snap_time ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'redo log space requests',value))) OVER(),7) -MIN(DECODE(name,'redo log space requests',value)) ,MIN(DECODE(name,'redo log space requests',value)) -lag(MIN(DECODE(name,'redo log space requests',value))) OVER(ORDER BY snap_time)) AS redo_log_space_requests ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'redo entries',value))) OVER(),7) -MIN(DECODE(name,'redo entries',value)) ,MIN(DECODE(name,'redo entries',value)) -lag(MIN(DECODE(name,'redo entries',value))) OVER(ORDER BY snap_time)) AS redo_entries ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'redo buffer allocation retries',value))) OVER(),7) -MIN(DECODE(name,'redo buffer allocation retries',value)) ,MIN(DECODE(name,'redo buffer allocation retries',value)) -lag(MIN(DECODE(name,'redo buffer allocation retries',value))) OVER(ORDER BY snap_time)) AS redo_buffer_allocation_retries ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'cluster key scan block gets',value))) OVER(),7) -MIN(DECODE(name,'cluster key scan block gets',value)) ,MIN(DECODE(name,'cluster key scan block gets',value)) -lag(MIN(DECODE(name,'cluster key scan block gets',value))) OVER(ORDER BY snap_time)) AS cluster_key_scan_block_gets ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'cluster key scans',value))) OVER(),7) -MIN(DECODE(name,'cluster key scans',value)) ,MIN(DECODE(name,'cluster key scans',value)) -lag(MIN(DECODE(name,'cluster key scans',value))) OVER(ORDER BY snap_time)) AS cluster_key_scans ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'sorts (disk)',value))) OVER(),7) -MIN(DECODE(name,'sorts (disk)',value)) ,MIN(DECODE(name,'sorts (disk)',value)) -lag(MIN(DECODE(name,'sorts (disk)',value))) OVER(ORDER BY snap_time)) AS sorts_disk ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'sorts (memory)',value))) OVER(),7) -MIN(DECODE(name,'sorts (memory)',value)) ,MIN(DECODE(name,'sorts (memory)',value)) -lag(MIN(DECODE(name,'sorts (memory)',value))) OVER(ORDER BY snap_time)) AS sorts_memory ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time)--- ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'table fetch continued row',value))) OVER(),7) -MIN(DECODE(name,'table fetch continued row',value)) ,MIN(DECODE(name,'table fetch continued row',value)) -lag(MIN(DECODE(name,'table fetch continued row',value))) OVER(ORDER BY snap_time)) AS table_fetch_continued_row ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'table fetch by rowid',value))) OVER(),7) -MIN(DECODE(name,'table fetch by rowid',value)) ,MIN(DECODE(name,'table fetch by rowid',value)) -lag(MIN(DECODE(name,'table fetch by rowid',value))) OVER(ORDER BY snap_time)) AS table_fetch_by_rowid ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'table scan rows gotten',value))) OVER(),7) -MIN(DECODE(name,'table scan rows gotten',value)) ,MIN(DECODE(name,'table scan rows gotten',value)) -lag(MIN(DECODE(name,'table scan rows gotten',value))) OVER(ORDER BY snap_time)) AS table_scan_rows_gotten ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'sorts (rows)',value))) OVER(),7) -MIN(DECODE(name,'sorts (rows)',value)) ,MIN(DECODE(name,'sorts (rows)',value)) -lag(MIN(DECODE(name,'sorts (rows)',value))) OVER(ORDER BY snap_time)) AS sorts_rows ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'opened cursors cumulative',value))) OVER(),7) -MIN(DECODE(name,'opened cursors cumulative',value)) ,MIN(DECODE(name,'opened cursors cumulative',value)) -lag(MIN(DECODE(name,'opened cursors cumulative',value))) OVER(ORDER BY snap_time)) AS opened_cursors_cumulative ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'user calls',value))) OVER(),7) -MIN(DECODE(name,'user calls',value)) ,MIN(DECODE(name,'user calls',value)) -lag(MIN(DECODE(name,'user calls',value))) OVER(ORDER BY snap_time)) AS user_calls ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'recursive calls',value))) OVER(),7) -MIN(DECODE(name,'recursive calls',value)) ,MIN(DECODE(name,'recursive calls',value)) -lag(MIN(DECODE(name,'recursive calls',value))) OVER(ORDER BY snap_time)) AS recursive_calls ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'parse count (total)',value))) OVER(),7) -MIN(DECODE(name,'parse count (total)',value)) ,MIN(DECODE(name,'parse count (total)',value)) -lag(MIN(DECODE(name,'parse count (total)',value))) OVER(ORDER BY snap_time)) AS parse_count_total ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'enqueue timeouts',value))) OVER(),7) -MIN(DECODE(name,'enqueue timeouts',value)) ,MIN(DECODE(name,'enqueue timeouts',value)) -lag(MIN(DECODE(name,'enqueue timeouts',value))) OVER(ORDER BY snap_time)) AS enqueue_timeouts ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'enqueue releases',value))) OVER(),7) -MIN(DECODE(name,'enqueue releases',value)) ,MIN(DECODE(name,'enqueue releases',value)) -lag(MIN(DECODE(name,'enqueue releases',value))) OVER(ORDER BY snap_time)) AS enqueue_releases ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'enqueue requests',value))) OVER(),7) -MIN(DECODE(name,'enqueue requests',value)) ,MIN(DECODE(name,'enqueue requests',value)) -lag(MIN(DECODE(name,'enqueue requests',value))) OVER(ORDER BY snap_time)) AS enqueue_requests ,DECODE(ROW_NUMBER() OVER(ORDER BY snap_time) ,1,substr(max(lpad(snap_time,6,'0')||MIN(DECODE(name,'enqueue waits',value))) OVER(),7) -MIN(DECODE(name,'enqueue waits',value)) ,MIN(DECODE(name,'enqueue waits',value)) -lag(MIN(DECODE(name,'enqueue waits',value))) OVER(ORDER BY snap_time)) AS enqueue_waits FROM perfstat.stats$sysstat s, perfstat.stats$SNAPSHOT t WHERE s.snap_id = t.snap_id AND name in ('redo log space requests','redo entries' ,'redo buffer allocation retries' ,'cluster key scan block gets','cluster key scans' ,'sorts (disk)', 'sorts (memory)' ,'table fetch by rowid', 'table scan rows gotten' ,'table fetch continued row', 'table fetch by rowid', 'table scan rows gotten' ,'sorts (rows)', 'sorts (memory)', 'sorts (disk)' ,'opened cursors cumulative', 'user calls' ,'recursive calls', 'user calls' ,'parse count (total)', 'user calls' ,'enqueue timeouts' ,'enqueue releases', 'enqueue requests', 'enqueue waits' ) GROUP BY snap_time ) order by 1; spool off |
< STATSPACK 데이타의 시계열 추출 결과에서 트랜드 성능 분석 예시>
