Oracle AWR Reporting Index full resolution ____oracle

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

Workload REPOSITORY for DB Name db Id Instance Inst Num Startup time release RAC----------- -------------------------------------------------------------MAC 2629627371 askmaclean.com 1 2 2-JAN-13 16:49 11.2.0.3.0 YES Host Name Platform CPUs cores Sockets Memory (GB)---------- ----------------------------------------------------------------MAC10 aix-based Systems (64-bit) 1 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 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

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 problem, large memory has large memory trouble. ORA-04031???!!

The Begin/end value of Buffer cache and shared pool size is moving under ASMM, AMM, and 11gr2 msmm.

It says here that shared pool has been shrinking, and that some of the row cache objects in the shrink process may cause parsing waits such as front row cache lock, preferably not shared pool shrink. If the shared pool has been grow here, it means that the shared pool is not large enough 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/Second per Transaction/Exec/Call ~~~~~~~~~~~~------------------- -------------------------------db Time (s): 256.6 0.2 0.07 0.03 db CPU (s): 3.7 0.0 0.00 0.00 Redo 826. 5 Logical reads:196,888.0 159.4 block changes:6,339.4 5.1 physic           Al 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 by Rows per sort:73.70

Index	Indicator meaning
Redo Size	Unit Bytes,redo size can be used to measure the frequency of update/insert/delete, large redo size is often to LGWR write log, and arch archive caused I/O pressure, per transaction can be used to distinguish a large number of small transactions, Or a small amount of big business. For example, redo is about 1MB per second, 800 bytes per transaction, with OLTP characteristics
Logical Read	Number of units per block, equivalent to "people * times", such as the 196,888 * db_block_size=1538mb/s, logical reading CPU, the main frequency and CPU core is very important, logical reading high DB CPU is often high, you can often see the Latch:cache buff  ER chains wait. A large number of OLTP systems (such as Siebel) can be as high as dozens of or hundreds of gbytes.
Block changes	Number of units per block, depicting the frequency of data changes
Physical Read	Number of units per block, as in the previous 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 has a few gigabytes of physical reading per second, such as Exadata. This physical read contains physical reads cache and physical reads Direct
Physical writes	Number of units per block, mainly DBWR write datafile, also have direct path write.  DBWR long writes slow will cause the regular log file switch (checkpoint no complete) checkpoint to be unable to complete the foreground wait. This physical write contains the physical writes direct +physical writes from cache
User Calls	Number of units, user calls, more details from internal
Parses	Analytic times, including soft resolution + hard parsing, soft resolution optimization is not good, it is exaggerated that almost equal to the number of SQL execution per second. That is, the execution resolution is 1:1, and we want to parse it running everywhere.
Hard parses	The source of all evils. Cursor pin s on x, library Cache:mutex x, Latch:row cache objects/shared pool ... and so on ..... Hard resolution is best less than 20 times per second
w/a MB processed	Number of data processed in the unit MB w/a workarea Workarea Combined with in-memory sort%, sorts (disk) PGA Aggr look together
Logons	Login times, logon Storm storm, combined with audit audit data to see. The incidental effect of a short connection is that the cursor cache is useless
Executes	Number of executions, frequency of reaction execution
Rollback	Rollback times, reaction rollback frequency, but this indicator is not very accurate, reference, don't take it too seriously.
Transactions	Transaction per second, 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 rate at which the data block changes with each logical reading; if ' redo size ', ' block changes ' pct of blocks changed per read ' Three indicators are high, 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	The average number of rows involved in each order; Rows per sort= (sorts (rows))/(sorts (disk) + sorts (memory))

Note that these load profile payload metrics provide 2 dimensions per second and per transaction in this link.

Per Second: The main point is to divide the delta value within the snapshot by the number of seconds in the fast-stand time, for example, in a snapshot v$sysstat view reaction table scans (long tables) This metric is 100, in B-snapshot v$sysstat view reaction t Able scans (long table) This metric is 3700, and a snapshot and B-snapshot between one hours and 3,600 seconds, then for tables scans (long) per second is (3700- 100)/3600=1.

Pert second is the primary dimension in which we look at data, and any performance data that is disconnected from the time model is meaningless.

Before the advent of the Statspack/awr, there are many DBAs rely on v$sysstat and other views of the cumulative statistical information to tune, in the current tuning perspective, that is tantamount to slash and burn.

Per transaction: A transaction based dimension that changes the divisor from the seconds of the time to the number of transactions in that period compared to per second. The large user of this dimension is used to identify changes in the application features, if the dimensions of the 2 AWR performance reports change significantly, for example, redo size from the original per transaction 1k to 10k/transaction, then the SQL business logic will definitely occur Some changes.

Note that these metrics in AWR are not only used to isolate the Oracle database load, but also to implement tuning efforts. For fault diagnosis, such as hang, crash, and so on, can be compared with the performance report of the problem period and regular time, through the comparison of various indicators can often find the location of the lesion.

SELECT VALUE from Dba_hist_sysstat WHERE snap_id =: B4 and DBID =: B3 and Instance_number =: B2 and stat_name  in (&N BSP; " DB block Changes "," User calls "," User rollbacks "," User commits ", Redo size", "physical reads direct", "Physical writes", " Parse count (hard) "," Parse Count "," session logical reads "," Recursive calls "," Redo log spaces Requests "," Redo Entri Es "," Sorts (memory) "," Sorts (disk) "," sorts (rows) "," Logons Cumulative "," Parse Time CPU "," Parse Time elapsed "," execute Count "," Logons Current "," opened Cursors "," 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 receive D ", GC current blocks receive time", "GC CR blocks served", "GC CR blocks build Time", "GC CR blocks flush Time", "GC CR block SE nd time ", GC current blocks served", "GC Current block pin time", "GC Current blocks flush Time", "GC Current block send" , "physical reads", "physical reads Direct (LOB), select Total_waits from dba_hist_system_event WHERE snap_id =: B4 and DBID =: B3 and Instance_number =: B 2 and Event_Name in ("GC buffer Busy", "Buffer busy waits" Select VALUE from Dba_hist_sys_time_model WHERE DBID =: B4 and snap_id =: B3 and Instance_number =: B2 and stat_name  in  ("db CPU", "SQL Execute Elapsed Time", "DB Time" Selec T VALUE from dba_hist_parameter WHERE snap_id =: B4 and DBID =: B3 and Instance_number =: B2 and parameter_name  in ( "__db_cache_size", "__shared_pool_size", "Sga_target", "Pga_aggregate_target", "Undo_management", "Db_block_size", " Log_buffer "," Timed_statistics "," Statistics_level "SELECT BYTES from dba_hist_sgastat WHERE snap_id =: B4 and DBID =: B3 A ND Instance_number =: B2 and pool in (' Shared POOL ', ' all Pools ') and name  in ("Free memory", SELECT BYTES from DBA _hist_sgastat WHERE snap_id =: B4 and DBID =: B3 and Instance_number =: B2 and NAME =: B1 and POOL is NULL SELECT (e.byte s_processed-b.bytes_processed) FROM dba_hist_pga_target_advice B, dba_hist_pga_target_advice E WHERE b.dbid =: B4 and b.snap_id =: B3 and B.instance_num BER =: B2 and b.advice_status = ' on ' and e.dbid = b.dbid and e.snap_id =: B1 and E.instance_number = B.instance_number and E. Pga_target_factor = 1 and B.pga_target_fact OR = 1 and e.advice_status = ' on ' SELECT SUM (E.TOTAL_WAITS-NVL (b.total_wait S, 0)) from Dba_hist_system_event B, dba_hist_system_event E WHERE b.snap_id (+) =: B4 and e.snap_id =: B3 and b.dbid (+) = : B2 and E.dbid =: B2 and b.instance_number (+) =: B1 and E.instance_number =: B1 and b.event_id (+) = e.event_id and (e.even T_name = ' latch free ' OR e.event_name like ' latch:% ') SELECT DECODE (b.total_sql, 0, 0, 100* (1-b.single_use_sql/b.total_s QL)), DECODE (e.total_sql, 0, 0, 100* (1-e.single_use_sql/e.total_sql)), DECODE (b.total_sql_mem, 0, 0, 1 00* (1-b.single_ USE_SQL_MEM/B.TOTAL_SQL_MEM)), DECODE (e.total_sql_mem, 0, 0, 100* (1-e.single_use_sql_mem/e.total_sql_mem)) from DBA_ Hist_sql_summary B, dba_hist_sql_sUmm ARY E WHERE b.snap_id =: B4 and e.snap_id =: B3 and B.instance_number =: B2 and E.instance_number =: B2 and b.dbid =: B1 and E.dbid =: B1 SELECT EVENT, WAITS, Time, DECODE (WAITS, NULL, To_number (NULL), 0, to_number (null), time/waits*1000) AVGWT, PCTWTT, Wait_class from (select event, WAITS, Time, PCTWTT, Wait_class from (select E.event_name event, E.total_wai TS-NVL (b.total_waits,0) WAITS, (E.TIME_WAITED_MICRO-NVL (b.time_waited_micro,0))/1000000 time (E.time _WAITED_ MICRO-NVL (b.time_waited_micro,0))/: B1 pctwtt, E.wait_class wait_class from Dba_hist_system_event B, DBA_HIST_SYSTEM_E VENT E WHERE b.snap_id (+) =: B5 and e.s nap_id =: B4 and b.dbid (+) =: B3 and e.dbid =: B3 and b.instance_number (+) =: B2 A  ND E.instance_number =: B2 and b.event_id (+) = e.event_id and E.total_waits > Nvl (b.to tal_waits,0) and E.wait_class!= ' Idle ' UNION all SELECT ' CPU time ' EVENT, To_number (NULL) WAITS,: b6/1000000 Time, MB *: B6/: B1 pctwtt, null WAIT_CLA SS from DUAL W Here:b6 &Gt 0 ORDER by Time DESC, WAITS DESC) WHERE rownum <=: B7 SELECT SUM (E.TIME_WAITED_MICRO-NVL (b.time_waited_micro,0)) FR OM dba_hist_system_event B, dba_hist_system_event E WHERE b.snap_id (+) =: B4 and e.snap_id =: B3 and b.db ID (+) =: B2 and E.dbid =: B2 and b.instance_number (+) =: B1 and E.instance_number =: B1 and b.event_id (+) = e.event_id and E.wait_class = ' User I/O ' SELECT (e.estd_lc_time_saved-b.estd_lc_time_saved) from Dba_hist_shared_pool_advice B, dba_hist_shared_  Pool_advice E WHERE b.dbid =: B3 and B.instance_number =: B2 an D b.snap_id =: B4 and e.dbid =: B3 and E.instance_number = : B2 and e.snap_id =: B1 and e.shared_pool_size_factor = 1 and b.shared_pool_size_factor = 1

1-3 Instance Efficiency percentages (Target 100%)

Instance efficiency percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer nowait%:   99.97       Redo nowait%:  100.00
            Buffer  Hit   %:   97.43 in-memory    Sort%:  100.00
            Library Hit   %:   99.88        Soft Parse%:   99.58 Execute to Parse%: 94.82 Latch Hit         %:   99.95
Parse CPU to Parse elapsd%:    1.75     % non-parse CPU:   99.85

The goal for all of these metrics is 100%, the bigger the better, which may be more than 100% or negative in a few bug cases.

More than 80%%non-parse CPU more than 90% buffer hit%, in-memory sort%, Soft parse% more than 95% Library hit%, Redo nowait%, buffer nowait% 9 More than 8% Latch hit%

1, buffer nowait% session request a buffer (compatibility mode) does not wait for the proportion of times. Need to access buffer immediately accessible ratio, incompatible situation in 9i is buffer busy waits, from 10g after buffer busy waits separated for buffer busy wait and read by other Sessio N2 A wait event:

The total number of waitstat in 9i is basically equal to the number of buffer busy WAITS wait events

sql> select SUM (total_waits) from v$system_event where event= ' Buffer busy waits ';
SUM (total_waits)
—————-
33070394

sql> Select SUM (count) from V$waitstat;
SUM (COUNT)
———-

The total number of 33069335 10g waitstat is basically equal to the  sum

of the number of times the buffer busy waits and read by the other sessions wait sql> select SUM (total_waits) from v$system_event where event= ' buffer busy WAITS ' or event= ' read by other sessions '; 
  
   sum (total_waits)
—————-
60675815

sql> Select SUM (count) from V$waitstat;

SUM (COUNT)
———-
60423739
  

The calculation formula for Buffer nowait% is sum (v$waitstat.wait_count)/(V$sysstat statistic session logical reads), for example, in AWR:

Total

Class	Waits	(s)	AVG Time (ms)
Data block	24,543	2,267	92
Undo Header	743	2	3
Undo Block	1,116	0	0
1st level BMB	35	0	0

Session logical Reads

40,769,800

22,544.84

204