"Performance Tuning" Oracle AWR reporting metrics full resolution __oracle

"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

Dba_hist_wr_control

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

AWR tips

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:

Http://www.oracle-base.com/dba/10g/generate_multiple_awr_reports.sql

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 11.2.0.3.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 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           DB 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&nbsp 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  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  TABLE&NBsp;scans  (long tables)   This metric is  100&nbsp, 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 &nbsp, 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&