You Asked

Hi Tom/Team,

I am aware of the definition of terms used in AWR Report. but i want to know that - how to calculate and on what basis we need to calculate values listed for points in below 2 section of AWR report

1. Top 5 timed foreground event
2. Sql statistics - sql orderby cpu time

Example: i know what is meaning of consistent gets, scattered reads, sequential reads.

I want to understand that how to calculate theirs values and on what basis we need to calculate their values.

another Example : I am clear on 'Instance efficiency should be 100%' because it clearly says that the below list should meet 100% or near to that.

Please explain

Thanks & Regards

and Connor said...

You will be familiar with "events", ie, time consumed by sessions, eg IO, waiting for locks etc.

So "foreground events" is those events for any session that is "one of yours", ie, a foreground connection, not a background process that belongs to the database.

SQL by cpu time is available to you already - check out v$sqlstats

SQL> desc v$sqlstats
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- --------------------------
 SQL_TEXT                                                                         VARCHAR2(1000)
 SQL_FULLTEXT                                                                     CLOB
 SQL_ID                                                                           VARCHAR2(13)
 LAST_ACTIVE_TIME                                                                 DATE
 LAST_ACTIVE_CHILD_ADDRESS                                                        RAW(8)
 PLAN_HASH_VALUE                                                                  NUMBER
 PARSE_CALLS                                                                      NUMBER
 DISK_READS                                                                       NUMBER
 DIRECT_WRITES                                                                    NUMBER
 DIRECT_READS                                                                     NUMBER
 BUFFER_GETS                                                                      NUMBER
 ROWS_PROCESSED                                                                   NUMBER
 SERIALIZABLE_ABORTS                                                              NUMBER
 FETCHES                                                                          NUMBER
 EXECUTIONS                                                                       NUMBER
 END_OF_FETCH_COUNT                                                               NUMBER
 LOADS                                                                            NUMBER
 VERSION_COUNT                                                                    NUMBER
 INVALIDATIONS                                                                    NUMBER
 PX_SERVERS_EXECUTIONS                                                            NUMBER
 CPU_TIME                                                                         NUMBER
 ELAPSED_TIME                                                                     NUMBER
 AVG_HARD_PARSE_TIME                                                              NUMBER
 APPLICATION_WAIT_TIME                                                            NUMBER
 CONCURRENCY_WAIT_TIME                                                            NUMBER
 CLUSTER_WAIT_TIME                                                                NUMBER
 USER_IO_WAIT_TIME                                                                NUMBER
 PLSQL_EXEC_TIME                                                                  NUMBER
 JAVA_EXEC_TIME                                                                   NUMBER
 SORTS                                                                            NUMBER
 SHARABLE_MEM                                                                     NUMBER
 TOTAL_SHARABLE_MEM                                                               NUMBER
 TYPECHECK_MEM                                                                    NUMBER
 IO_CELL_OFFLOAD_ELIGIBLE_BYTES                                                   NUMBER
 IO_INTERCONNECT_BYTES                                                            NUMBER
 PHYSICAL_READ_REQUESTS                                                           NUMBER
 PHYSICAL_READ_BYTES                                                              NUMBER
 PHYSICAL_WRITE_REQUESTS                                                          NUMBER
 PHYSICAL_WRITE_BYTES                                                             NUMBER
 EXACT_MATCHING_SIGNATURE                                                         NUMBER
 FORCE_MATCHING_SIGNATURE                                                         NUMBER
 IO_CELL_UNCOMPRESSED_BYTES                                                       NUMBER
 IO_CELL_OFFLOAD_RETURNED_BYTES                                                   NUMBER
 DELTA_PARSE_CALLS                                                                NUMBER
 DELTA_DISK_READS                                                                 NUMBER
 DELTA_DIRECT_WRITES                                                              NUMBER
 DELTA_DIRECT_READS                                                               NUMBER
 DELTA_BUFFER_GETS                                                                NUMBER
 DELTA_ROWS_PROCESSED                                                             NUMBER
 DELTA_FETCH_COUNT                                                                NUMBER
 DELTA_EXECUTION_COUNT                                                            NUMBER
 DELTA_PX_SERVERS_EXECUTIONS                                                      NUMBER
 DELTA_END_OF_FETCH_COUNT                                                         NUMBER
 DELTA_CPU_TIME                                                                   NUMBER
 DELTA_ELAPSED_TIME                                                               NUMBER
 DELTA_APPLICATION_WAIT_TIME                                                      NUMBER
 DELTA_CONCURRENCY_TIME                                                           NUMBER
 DELTA_CLUSTER_WAIT_TIME                                                          NUMBER
 DELTA_USER_IO_WAIT_TIME                                                          NUMBER
 DELTA_PLSQL_EXEC_TIME                                                            NUMBER
 DELTA_JAVA_EXEC_TIME                                                             NUMBER
 DELTA_SORTS                                                                      NUMBER
 DELTA_LOADS                                                                      NUMBER
 DELTA_INVALIDATIONS                                                              NUMBER
 DELTA_PHYSICAL_READ_REQUESTS                                                     NUMBER
 DELTA_PHYSICAL_READ_BYTES                                                        NUMBER
 DELTA_PHYSICAL_WRITE_REQUESTS                                                    NUMBER
 DELTA_PHYSICAL_WRITE_BYTES                                                       NUMBER
 DELTA_IO_INTERCONNECT_BYTES                                                      NUMBER
 DELTA_CELL_OFFLOAD_ELIG_BYTES                                                    NUMBER
 DELTA_CELL_UNCOMPRESSED_BYTES                                                    NUMBER
 CON_ID                                                                           NUMBER
 CON_DBID                                                                         NUMBER
 OBSOLETE_COUNT                                                                   NUMBER

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Surendar Patel, October 01, 2017 - 4:31 am UTC

Dear Connor,

I have generated AWR report but I have no idea what exactly to check and whether the values what it is showing is good or bad, as iam not aware what is the target value, like below (Instance Efficiency) says me that target is 100%, so here i can easily check thar 100% is the target,
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 99.64 Soft Parse %: 99.64
Execute to Parse %: 59.60 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 130.00 % Non-Parse CPU: 96.30

like vise for others sections of AWR report , I have no idea what value to check below and what calculations need to done on column values.

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
control file parallel write 1,220 18 15 331.7 System I/O
control file sequential read 6,508 6 1 110.9 System I/O
CPU time 4 64.4
CGS wait for IPC msg 422,253 1 0 20.9 Other
change tracking file synchrono 60 1 13 14.4 Other

Avg
%Time Total Wait wait Waits
Wait Class Waits -outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
System I/O 8,142 .0 25 3 10.9
Other 439,596 99.6 3 0 589.3
User I/O 112 .0 0 3 0.2
Cluster 443 .0 0 0 0.6
Concurrency 216 .0 0 0 0.3
Commit 16 .0 0 2 0.0
Network 3,526 .0 0 0 4.7
Application 13 .0 0 0 0.0

Instance Activity Stats

Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
consistent changes 9 0.0 0.0
consistent gets 70,445 19.5 94.4
consistent gets - examination 8,728 2.4 11.7
consistent gets direct 0 0.0 0.0
consistent gets from cache 70,445 19.5 94.4
cursor authentications 2 0.0 0.0
data blocks consistent reads - u 5 0.0 0.0
db block changes 1,809 0.5 2.4
db block gets 2,197 0.6 3.0
db block gets direct 0 0.0 0.0
db block gets from cache 2,033 0.6 2.7

what is the target values for above column data, and how to determine if these output values are good or bad.

What iam exactly looking is

how to determins if the values for "Top 5 timed out EVENTS" for below column are good or bad :
Waits : Time (s) : AVG wait(ms) : %TOTAL CALL Time : Wait Class

how to determins if the values for "Instance Activity Stats" for below column are good or bad :
Statistic : Total : per Second : per Trans :

October 05, 2017 - 4:30 pm UTC

lots of good info there

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer