Last Updated on 1월 21, 2024 by Jade(정현호)
AWR 은 Oracle SE(Standard Edition) 에서 사용이 불가 합니다.
정확히 DIAGNOSTIC+TUNING PACK 옵션이 EE 의 옵션이기에, SE에서는 선택 불가하기에
SE에서는 AWR(ASH) 의 사용이 불가 합니다.
최근에 DB에 저장된 통계데이터를 살펴보던 중 "DBA_HIST_SYSMETRIC_SUMMARY" 뷰를 알게 되었고 해당 뷰의 꽤 괜찮은 데이터가 누적되어 있음을 알게 되었습니다.
METRIC 에는 CPU_USAGE 나 "IO Requests Per Second" 등 DB에서 수행된 행동(Action) 이나 성능 적인 History 가 잘 수집 되어있었습니다.
Enterprise Edition DB 의 경우 30일치가 보관 되어있으며, Standard Edition 인 DB도 살펴보니, 8일치의 데이터가 존재함을 알게 되었습니다.
(운영중인 사이트의 Enterprise Edition DB의 정책이 30일, Standard Edition 은 Default 인 8일로 설정 되어있었음)
오라클 SR 을 통해서, SE 라도 AWR 보관일자에 따라서 해당 뷰가 영향을 받는지 알게 되었으며, 지금까지는 "DBA_HIST_SYSMETRIC_SUMMARY" 뷰만 알고 있으나, 그외 다른 성능데이터도 AWR 보관일자에 따라 영향을 받아서 SE에서도 좋은 성능데이터를 이용할 수 있을 거란 예상이 됩니다.
해서 결론 내리면, SE 라도 AWR 데이터 보관일자를 충분히 늘려주면, AWR이나 ASH 데이터는 이용하지 못하더라도 이렇게 숨어 있는 DB 통계 데이터를 이용할 수 있어서, 필요시 적절하게 사용이 가능 할 것 같습니다.
19c 기준으로 측정되는 지표(metric) 수는 161개 로 확인됩니다.
METRIC_NAME
-----------------------------------------
Cursor Cache Hit Ratio
User Rollback UndoRec Applied Per Sec
PX downgraded 1 to 25% Per Sec
PX downgraded 50 to 75% Per Sec
Global Cache Average Current Get Time
Process Limit %
Session Limit %
Streams Pool Usage Percentage
Background CPU Usage Per Sec
Background Time Per Sec
Temp Space Used
User Transaction Per Sec
Physical Reads Per Sec
Physical Writes Per Sec
Physical Writes Direct Per Sec
Total Parse Count Per Sec
Hard Parse Count Per Sec
User Calls Ratio
Enqueue Timeouts Per Sec
Enqueue Waits Per Txn
Consistent Read Changes Per Txn
CPU Usage Per Txn
CR Blocks Created Per Txn
Leaf Node Splits Per Sec
PX downgraded 25 to 50% Per Sec
PX downgraded to serial Per Sec
GC CR Block Received Per Second
GC CR Block Received Per Txn
Current Open Cursors Count
SQL Service Response Time
Database Wait Time Ratio
Shared Pool Free %
Executions Per User Call
Current OS Load
I/O Megabytes per Second
I/O Requests per Second
Active Parallel Sessions
Captured user calls
Workload Capture and Replay status
Total PGA Allocated
Total PGA Used by SQL Workareas
Redo Allocation Hit Ratio
Physical Reads Direct Per Txn
Redo Generated Per Txn
User Rollbacks Per Sec
Logical Reads Per Sec
Redo Writes Per Txn
Long Table Scans Per Txn
Full Index Scans Per Txn
Rows Per Sort
Network Traffic Volume Per Sec
Enqueue Deadlocks Per Txn
PX downgraded 75 to 99% Per Sec
GC Current Block Received Per Txn
Row Cache Hit Ratio
Physical Read Bytes Per Sec
Physical Reads Direct Per Sec
Open Cursors Per Sec
DBWR Checkpoints Per Sec
Total Table Scans Per Sec
Parse Failure Count Per Sec
Parse Failure Count Per Txn
Disk Sort Per Sec
Execute Without Parse Ratio
Enqueue Requests Per Sec
DB Block Changes Per Txn
User Rollback Undo Records Applied Per Txn
Leaf Node Splits Per Txn
Global Cache Average CR Get Time
Global Cache Blocks Corrupted
Database CPU Time Ratio
Executions Per Txn
Logical Reads Per User Call
Total Sorts Per User Call
Total Table Scans Per User Call
DML statements parallelized Per Sec
PX operations not downgraded Per Sec
VM out bytes Per Sec
Background Checkpoints Per Sec
Hard Parse Count Per Txn
Soft Parse Ratio
Enqueue Waits Per Sec
DB Block Gets Per Sec
Consistent Read Gets Per Txn
Consistent Read Changes Per Sec
Branch Node Splits Per Sec
Physical Read Total Bytes Per Sec
GC Current Block Received Per Second
PGA Cache Hit %
Executions Per Sec
Txns Per Logon
Physical Write Bytes Per Sec
DB Block Changes Per User Call
PQ QC Session Count
DDL statements parallelized Per Sec
Session Count
VM in bytes Per Sec
Logons Per Txn
User Commits Per Sec
Recursive Calls Per Txn
Total Index Scans Per Sec
Enqueue Timeouts Per Txn
DB Block Changes Per Sec
CPU Usage Per Sec
CR Blocks Created Per Sec
Physical Write Total IO Requests Per Sec
Global Cache Blocks Lost
Response Time Per Txn
Row Cache Miss Ratio
Library Cache Miss Ratio
DB Block Gets Per User Call
PQ Slave Session Count
Average Synchronous Single-Block Read Latency
Memory Sorts Ratio
Redo Generated Per Sec
Open Cursors Per Txn
User Commits Percentage
Recursive Calls Per Sec
Total Parse Count Per Txn
Disk Sort Per Txn
Enqueue Requests Per Txn
Consistent Read Gets Per Sec
Branch Node Splits Per Txn
Physical Read Total IO Requests Per Sec
Library Cache Hit Ratio
Physical Write Total Bytes Per Sec
Physical Write IO Requests Per Sec
Average Active Sessions
Replayed user calls
Host CPU Usage Per Sec
Cell Physical IO Interconnect Bytes
Physical Reads Per Txn
Physical Reads Direct Lobs Per Sec
Physical Writes Direct Lobs Per Txn
User Calls Per Txn
Logical Reads Per Txn
Total Index Scans Per Txn
Host CPU Utilization (%)
Enqueue Deadlocks Per Sec
DB Block Gets Per Txn
CR Undo Records Applied Per Sec
CR Undo Records Applied Per Txn
Current Logons Count
User Limit %
Database Time Per Sec
Physical Read IO Requests Per Sec
Queries parallelized Per Sec
Active Serial Sessions
Run Queue Per Sec
Buffer Cache Hit Ratio
Physical Writes Per Txn
Physical Writes Direct Per Txn
Physical Reads Direct Lobs Per Txn
Physical Writes Direct Lobs Per Sec
Logons Per Sec
User Rollbacks Percentage
User Calls Per Sec
Redo Writes Per Sec
Long Table Scans Per Sec
Total Table Scans Per Txn
Full Index Scans Per Sec
연관된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
