"Performance Tuning" Oracle AWR reporting metrics full resolution
What is AWR?
AWR (Automatic workload Repository)
A bunch of historical energy data, placed on the Sysaux table space, AWR and Sysaux are all 10g, are the key features of Oracle tuning; About 1999 years began to develop, has been 15 years of history
Default snapshot interval 1 hours, 10g save 7 days, 11g save 8 days; Can pass dbms_workload_repository. Modify_snapshot_settings modification
AWR program Core is Dbms_workload_repository package
@?/rdbms/admin/awrrpt This example
Select instance number in @?/rdbms/admin/awrrpti RAC
WHO maintains awr?
Mainly Mmon (manageability Monitor process) and its coolie process (m00x)
The Mmon features include:
1. Start slave process m00x to do awr snapshots
2. Alert alert when a metric threshold is exceeded
3. Capture metrics information for recently changed SQL objects
To perform a snapshot manually:
Exec Dbms_workload_repository.create_snapshot; (this to recite Oh, use the time to go over the manual, humiliated Oh j!)
Create a AWR baseline
Exec dbms_workload_repository. Create_baseline (start_snap_id,end_snap_id, baseline_name);
@?/rdbms/admin/awrddrpt AWR Report
@?/rdbms/admin/awrgrpt RAC Global AWR
Automatically generate AWR HTML reports:
1. Summary of the report
[SQL] View plain copy workload repository report for db name DB Id Instance inst num startup time release rac ------------ ----------- ------------ -------- --------------- ---------- - --- mac 2629627371 askmaclean.com 1 22-jan-13 16:49 126.96.36.199.0 yes host name Platform cpus cores sockets memory (GB) ---------------- -------------------------------- ---- ----- ------- ---------- mac10 aix-based systems (64- Bit) 128 32 320.00 Snap Id snap time sessions curs/sess --------- ------------------- -------- -------- - begin snap: 5853 23-jan-13 15:00:56 3,520 1.8 end snap: 5854 23-jan-13 15:30:41 3,765 1.9 Elapsed: 29.75 (mins) DB Time: 7,633.76 (mins)
Elapsed the time span of the AWR performance report (the natural time span, for example, the previous snapshot snapshot is generated at 4, and the latter snapshot snapshot is generated at 6), if you use the @?/rdbms/admin/awrrpt script to specify these 2 snapshots, Then its elapsed = (6-4) = 2 hours), a AWR performance report requires at least 2 awr snapshot performance snapshots to generate (note that 2 snapshot time instances can not be restarted, otherwise specify that 2 snapshots generate AWR performance report Errors), AWR performance reports Metrics tend to be the deltas of the last snapshot and the index of the previous snapshot, because the cumulative value does not reflect the system workload for a certain period of time.
DB time= Total time spent on database calls by all foreground sessions: Note that the foreground process foreground sessions includes CPU time, IO times, and a series of other non idle wait times, and don't forget CPU on queue time
DB time is not equal to response times, DB is not slow to respond, db time low may not respond fast
DB time depicts the overall load of the database, but it will be combined with other elapsed times.
Average Active session aas= DB time/elapsed time
DB time =60 min, Elapsed time =60 min aas=60/60=1 load General
DB time= 1min, Elapsed time= min aas= 1/60 load very light
DB time= 60000 min,elapsed time= min aas=1000 system hang it.
DB time= DB CPU + Non-idle wait + in CPU queue
If there are only 2 logical CPUs, and 2 sessions are not waiting for events for 60 minutes, running on the CPU, then:
DB cpu= 2 * mins, db time = 2* 60 + 0 + 0 =120
AAS = 120/60=2 is exactly equal to OS load 2.
If there are 3 sessions with 100% consuming only CPU, then there is always a wait on queue
DB CPU = 2* mins, wait on CPU queue= mins
Aas= (120+ 60)/60=3 host load is also 3, at this point Vmstat see waiting for run time
The real world. DB Cpu = xx mins, non-idle wait= enq:tx + cursor pin S on X + latch:xxx + db file sequential Read + ... Puppy[SQL] View plain copy 1-1 memory parameter size cache sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 49,152M 49,152m std block size: 8k Shared Pool Size: 13,312m 13,312m log buffer: 334,848k memory management mode: MSMM, ASMM (Sga_ Target), AMM (memory_target) Small memory has small memory problems, large memory has large memory trouble. ora-04031???!! buffer cache and Shared pool size begin /end values can move under ASMM, Amm and 11GR2&NBSP;MSMM. here says shared pool has been shrinking, then some row in the shrink process cache object is locked may lead to the front desk row cache lock wait, it is best not to let Shared pool shrink. If the shared pool has been grow, it shows that Shared pool's original size is insufficient to meet the requirements (possibly a large number of hard parsing), combining the parsing information below with the Sga breakdown to diagnose the problem. 1-2 load Profile load profile per second per transaction per exec Per Call ~~~~~~~~~~~~ ------- -------- --------------- ---------- ---------- db time (s): 256.6 0.2 0.07 0.03 &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;DB&NBSP;CPU (s): 3.7 0.0 0.00 0.00 Redo size: 1,020,943.0 826.5 Logical reads: 196,888.0 159.4 Block changes: 6,339.4 5.1 Physical reads: 5,076.7 4.1 Physical writes: 379.2 0.3 User calls: 10,157.4 8.2 Parses: 204.0 0.2 Hard parses: 0.9 0.0 w/a mb processed: 5.0 0.0 Logons: 1.7 0.0 executes: 3,936.6 3.2 Rollbacks: 1,126.3 0.9 Transactions: 1,235.3 % blocks changed per read: 53.49 Recursive Call %: 98.04 rollback per transaction %: 36.57 Rows per sort: 73.70 & nbsp; index index meaning redo size unit bytes,redo Size can be used to measure the frequency of update/insert/delete, large redo size tend to lgwr write logs, and arch archiving caused I/O pressure, per transaction can be used to distinguish is a large number of small business, or a small number of large business. redo approximately 1mb  per second, per transaction 800 byte, OLTP characteristics logical read Unit number of blocks * , equivalent to "people * times", The above example 196,888 * db_block_size=1538mb/s , logic read CPU, the main frequency and CPU core is very important, logical reading high db cpu often high, also can often see latch: cache buffer Chains wait. a large number of OLTP systems (such as Siebel) can be as high as dozens of or hundreds of gbytes. block changes Units TIMES * block number , depict data change frequency physical read per Unit number * Block Number, above example 5076 * 8k = 39MB/s, physical read consumption IO read, reflected in IOPS and throughput and other latitude , but reducing physical reading may mean consuming more CPUs. Good storage Physical reading capacity of up to several gigabytes per second, such as Exadata. This physical read contains Physical reads cache and physical reads direct . physical writes Units times * block number, mainly DBWR write datafile, there are direct path write. Long-term &NBSP;DBWR will lead to regular Log file switch (checkpoint no complete) Checkpoints that cannot be completed at the front desk waiting. this physical write contains physical writes direct +physical writes . from cache user calls number of units, user calls, more details from internal parses resolution times, including soft resolution + hard parsing, soft resolution optimizationBad, it is almost equal to the number of SQL executions per second. is performing resolution 1:1, and what we want is parsing once running around OH. hard parses The root of all evils. Cursor pin s on x, library cache: mutex x , latch: row cache objects /shared pool ............ Hard resolution preferably less than 20 times per second w/a mb processed Unit mb w/a workarea Number of data processed in workarea combined In-memory Sort%, sorts (disk) PGA Aggr watch logons landing times, logon storm Landing storm, combined with audit audit data to see. The incidental effect of short connection is cursor cache useless executes execution times, reaction frequency rollback rollback times, reaction rollback frequency, But this indicator is not very accurate, reference just, don't take it too seriously transactions per second transaction number, is the database layer of TPS, can be seen as a stress test or performance when compared to an indicator, isolated look meaningless % blocks changed per read The ratio of data block changes per logical reading; if ' redo size ', ' blocks changes ' ' pct of blocks changed per read ' three indicators are veryHigh, it indicates that the system is performing a large number of insert/update/delete; pct of blocks changed per read = (block changes ) /( logical reads) recursive Call % the ratio of recursive calls; recursive call % = (recursive calls)/(user calls) Rollback per transaction % transaction rollback ratio. Rollback per transaction %= (Rollback)/(transactions) Rows per Sort average number of rows involved in each sort ; Rows per Sort= ( sorts rows ) / ( sorts (disk) + sorts (memory)) Note that these load profile load metrics provide 2 dimensions per second and per in this link Transaction per second: is primarily to divide the delta value in snapshots by the number of seconds in fast-stand time , for example V$sysstat View responses in a snapshots &NBSP;TABLE&NBsp;scans (long tables) This metric is 100 , in B-snapshot v$sysstat view reaction table scans (long tables) This metric is 3700, and a snapshot and B snapshot between interval of one hours 3600 seconds, then for table scans (Long tables) per second is ( 3700 - 100) /3600=1. Pert second is the main dimension of our data Any performance data that is out of the time model is meaningless. Prior to the advent of Statspack/awr tuning Time, There are many dba dependent v$sysstat views of the cumulative Statistical information to tune, to the current tuning point of view, that is tantamount to slash and burn. per transaction : transaction based dimension, Compared to Per second is to change the divisor from the number of seconds to the number of transactions in the period. The large user of this dimension is used to identify the changes in the application characteristics of  , if the 2 awr performance reports in this dimension metric have changed dramatically, such as redo size from the original per transaction 1k changes to 10k per transaction, the SQL business logic must have changed some. Note AWThese metrics in R are not only used to isolate the oracle database load situation, implement tuning work. for fault diagnosis such as hang, crash, etc., can be compared with the performance report of the problem period and the regular time, through the comparison of various indicators can often find foci. select value from dba_hist_sysstat where snap_id = :b4 and dbid = :b3 and instance_number = :b2 and stat_name in ( "Db block changes", "User calls", "user Rollbacks "," User commits ", Redo size", "Physical reads direct", "Physical writes", "Parse count (Hard) "," parse count (total) "," Session logical reads "," recursive Calls "," redo log space requests "," Redo entries "," sorts (Memory) "," sorts (disk) ", "sorts (rows)", "logons cumulative", "Parse time cpu", "parse time elapsed", " Execute count "," Logons current "," Opened cursors current "," DBWr fusion writes "," Gcs messages sent "," Ges messages sent "," Global enqueue gets sync "," Global enqueue get time "," gc cr blocks received "," GC cr block receive time "," gc current blocks received "," gc current Block receive time "," gc cr blocks served "," Gc cr block build time "," Gc cr block flush time, "Gc cr block send time", "gc current blocks served "," Gc current block pin time "," gc current block Flush time "," Gc current block send time "," Physical reads "," physical reads direct (LOB) ", select total_waits from&