全宇宙最棒的关系型数据库环境能力EXADATA AWR一例

by

抛开政治， 就这环境，要啥分布式! 省点机房机位租金和电费不好吗？

DB Name DB Id Unique Name Role Edition Release RAC CDB
ANBOB 6666666 anbob PRIMARY EE 12.2.0.1.0 NO NO
Instance Inst Num Startup Time
anbob 1 27-Jun-20 21:06
Host Name Platform CPUs Cores Sockets Memory (GB)
localhost Linux x86 64-bit 224 112 4 754.52

 

Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 31 27-Jun-20 22:06:06 292 5.3
End Snap: 32 27-Jun-20 22:08:08 292 5.3
Elapsed: 2.03 (mins)
DB Time: 411.37 (mins)

Report Summary

Top ADDM Findings by Average Active Sessions

Finding Name Avg active sessions of the task Percent active sessions of finding Task Name Begin Snap Time End Snap Time
Shared Pool Latches 202.32 17.53 ADDM:1571660006_1_32 27-Jun-20 22:06 27-Jun-20 22:08

Load Profile

Per Second Per Transaction Per Exec Per Call
DB Time(s): 202.2 0.0 0.00 0.00
DB CPU(s): 168.9 0.0 0.00 0.00
Background CPU(s): 0.0 0.0 0.00 0.00
Redo size (bytes): 846,668,408.4 5,454.1
Logical read (blocks): 9,976,388.9 64.3
Block changes: 4,923,468.1 31.7
Physical read (blocks): 906.4 0.0
Physical write (blocks): 0.0 0.0
Read IO requests: 816.3 0.0
Write IO requests: 0.0 0.0
Read IO (MB): 7.1 0.0
Write IO (MB): 0.0 0.0
IM scan rows: 0.0 0.0
Session Logical Read IM: 0.0 0.0
User calls: 119,047.2 0.8
Parses (SQL): 71,862.0 0.5
Hard parses (SQL): 2.0 0.0
SQL Work Area (MB): 33.2 0.0
Logons: 0.1 0.0
Executes (SQL): 3,206,168.7 20.7
Rollbacks: 263.2 0.0
Transactions: 155,234.5

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 99.94 Redo NoWait %: 100.00
Buffer Hit %: 99.99 In-memory Sort %: 100.00
Library Hit %: 100.04 Soft Parse %: 100.00
Execute to Parse %: 97.76 Latch Hit %: 99.77
Parse CPU to Parse Elapsd %: 49.31 % Non-Parse CPU: 99.97
Flash Cache Hit %: 0.00

Top 10 Foreground Events by Total Wait Time

Event Waits Total Wait Time (sec) Avg Wait % DB time Wait Class
DB CPU 20.6K 83.5
library cache: mutex X 1,031,636 4148.5 4.02ms 16.8 Concurrency
enq: TX – row lock contention 321,283 279.4 869.56us 1.1 Application
cursor: mutex X 43,135 179.6 4.16ms .7 Concurrency
buffer busy waits 639,719 129.7 202.82us .5 Concurrency
latch: In memory undo latch 1,255,205 95.1 75.80us .4 Concurrency
log file sync 32,142 43.3 1.35ms .2 Commit
PGA memory operation 3,932,968 25.5 6.47us .1 Other
latch: cache buffers chains 42,869 21.8 508.12us .1 Concurrency
db file sequential read 97,189 18 185.29us .1 User I/O

Wait Classes by Total Wait Time

Wait Class Waits Total Wait Time (sec) Avg Wait Time % DB time Avg Active Sessions
DB CPU 20,613 83.5 168.9
Concurrency 3,012,712 4,575 1.52ms 18.5 37.5
Application 321,283 279 869.56us 1.1 2.3
System I/O 136,854 98 714.85us .4 0.8
Other 4,097,485 95 23.26us .4 0.8
Commit 32,142 43 1.35ms .2 0.4
User I/O 101,424 19 185.67us .1 0.2
Network 14,472,547 9 627.61ns .0 0.1
Configuration 403 4 9.34ms .0 0.0

Host CPU

CPUs Cores Sockets Load Average Begin Load Average End %User %System %WIO %Idle
224 112 4 99.33 144.32 66.9 8.7 0.0 23.8

Instance CPU

%Total CPU %Busy CPU %DB time waiting for CPU (Resource Manager)
76.2 100.0 0.0

IO Profile

Read+Write Per Second Read per Second Write Per Second
Total Requests: 4,733.8 821.7 3,912.2
Database Requests: 816.3 816.3 0.0
Optimized Requests: 0.0 0.0 0.0
Redo Requests: 3,818.1 0.0 3,818.1
Total (MB): 825.6 7.2 818.4
Database (MB): 7.1 7.1 0.0
Optimized Total (MB): 0.0 0.0 0.0
Redo (MB): 794.6 0.0 794.6
Database (blocks): 906.4 906.4 0.0
Via Buffer Cache (blocks): 906.4 906.4 0.0
Direct (blocks): 0.0 0.0 0.0

Memory Statistics

Begin End
Host Mem (MB): 772,624.4 772,624.4
SGA use (MB): 409,600.0 409,600.0
PGA use (MB): 1,632.6 1,638.8
% Host Mem used for SGA+PGA: 53.23 53.23

Cache Sizes

Begin End
Buffer Cache: 101,888M 101,888M Std Block Size: 8K
Shared Pool Size: 19,907M 19,895M Log Buffer: 1,543,672K
In-Memory Area: 0M 0M

Shared Pool Statistics

Begin End
Memory Usage %: 7.10 7.11
% SQL with executions>1: 82.32 81.45
% Memory for SQL w/exec>1: 62.31 57.67

init.ora Parameters

Parameter Name Begin value End value (if different)
_ash_enable FALSE
_awr_restrict_mode FALSE
_check_block_after_checksum FALSE
_collect_undo_stats FALSE
_db_block_check_objtyp FALSE
_db_block_prefetch_limit 0
_db_writer_flush_imu FALSE
_disable_highres_ticks TRUE
_disable_logging FALSE
_disable_selftune_checkpointing TRUE
_disk_sector_size_override TRUE
_fast_cursor_reexecute TRUE
_first_spare_parameter 1
_in_memory_undo TRUE
_kgl_hot_object_copies 4
_numa_shift_enabled FALSE
_resource_manager_always_off TRUE
_trace_pool_size 0
_undo_autotune FALSE
_use_adaptive_log_file_sync FALSE
aq_tm_processes 0
audit_file_dest /u01/app/12.2.0.1/oracle/admin/orcl/adump
audit_trail NONE
commit_logging BATCH
commit_wait NOWAIT
compatible 12.2.0
connection_brokers ((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1))
control_files +DATA/ORCL/CONTROLFILE/current.307.1043879273
db_block_checking false
db_block_checksum false
db_block_size 8192
db_cache_size 106837311488
db_create_file_dest +DATA
db_name orcl
db_writer_processes 4
diagnostic_dest /u01/app/12.2.0.1/oracle
disk_asynch_io TRUE
dispatchers (PROTOCOL=TCP) (SERVICE=anbobXDB)
dml_locks 500
fast_start_mttr_target 0
java_pool_size 12884901888
large_pool_size 12884901888
local_listener LISTENER
lock_sga TRUE
log_buffer 1073709056
log_checkpoint_interval 0
log_checkpoint_timeout 0
log_checkpoints_to_alert TRUE
nls_language AMERICAN
nls_territory AMERICA
open_cursors 2000
optimizer_mode CHOOSE
parallel_max_servers 0
parallel_min_servers 0
pga_aggregate_target 8589934592
plsql_code_type NATIVE
plsql_optimize_level 3
plsql_warnings DISABLE:ALL
pre_page_sga FALSE
processes 1000
query_rewrite_enabled FALSE
remote_login_passwordfile EXCLUSIVE
replication_dependency_tracking FALSE
result_cache_max_size 0
sga_max_size 429496729600
shared_pool_size 21474836480
statistics_level TYPICAL
timed_statistics TRUE
trace_enabled FALSE
transactions 2000
transactions_per_rollback_segment 1
undo_management AUTO
undo_retention 2
undo_tablespace UNDOTBS1 
          Findings and Recommendations
          ----------------------------

Finding 1: Shared Pool Latches
Impact is 35.48 active sessions, 17.53% of total activity.
----------------------------------------------------------
Contention for latches related to the shared pool was consuming significant
database time.
Waits for "library cache: mutex X" amounted to 16% of database time.

   No recommendations are available.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Concurrency" was consuming significant database time.
      Impact is 37.5 active sessions, 18.53% of total activity.