Brief Introduction to Oracle AWR Single Instance

AWR in Oracle, all known as Automatic Workload Repository, is an automated load repository.

AWR is one of the key tools for DBAs to understand the state of their operations, and the AWR report provides an overall understanding and targeted optimization of Oracle database performance, which is primarily about the content of the AWR-related section.

DB Name db Id Instance Inst Num Startup time Release RAC

------------ ----------- ------------ -------- --------------- ----------- ---

1 16-jan-17 09:27 11.2.0.4.0 NO

Host Name Platform CPUs cores Sockets Memory (GB)

---------------- -------------------------------- ---- ----- ------- ----------

Linux x86 64-bit 8 8 2 7.81

Snap Id snap Time Sessions curs/sess

--------- ------------------- -------- ---------

Begin snap:10848 14-MAR-17 09:00:51 66 1.4

End snap:10849 14-MAR-17 10:00:55 66 1.5

elapsed:60.07 (mins)

DB time:0.93 (mins)

Sessions

When collecting performance information, the number of sessions linked by the Oracle instance helps to determine the class of DB

Cursors/session

Average number of cursors open for a single session

Elapsed

DB Actual use time

DB time

The time it takes for a database operation, including the CPU and wait Event time,db the higher the database, the higher the database load.

The higher the ratio of DB time/elapsed ratio, the busier the database is.

DB time = CPU time + wait time (excluding background processes and idle waits)

corresponding to the elapsed_time in V$session

Load profile per Second per Transaction per Exec per call

~~~~~~~~~~~~~~~            ---------------   --------------- --------- ---------

DB time (s): 0.0 0.0 0.00 0.00

DB CPU (s): 0.0 0.0 0.00 0.00

Redo size (bytes): 1,343.6 3,388.8

Logical Read (blocks): 394.1 993.9

Block changes:5.4 13.6

Physical read (blocks): 0.4 1.1

Physical Write (Blocks): 0.6 1.4

Read IO requests:0.4 1.1

Write IO requests:0.4 1.1

Read IO (MB): 0.0 0.0

Write IO (MB): 0.0 0.0

User calls:64.8 163.4

Parses (SQL): 21.0 52.9

Hard parses (SQL): 0.0 0.1

SQL work Area (MB): 0.2 0.5

logons:0.1 0.2

Executes (SQL): 22.2 55.9

rollbacks:0.0 0.0

transactions:0.4

DB Time DB CPU

DB time 3.3s DB CPU 1.4s Wait Event 3.3-1.4=1.9s, DB CPU takes up db time as a proportion of 1.4/3.3=42%

You can see that this DB system has a large non-CPU wait

DB CPU accounted for 42.55%

DB file sequential read/db file scattered read//libary Cache:mutex x/latch:shared pool for CPU waiting for top 4 wait Event

(DB time > DB CPU + FG Wait event DB hour calculates queue times waiting for CPU when CPU is busy)

Continue analysis

Redo the amount of size log generated

Logical reads logical reading, unit is block

Good OLTP logical reads/executes at around 50

Block changes

Block of data changed per second, transaction

Physical Reads

Physical Reading

User Calls

Number of user calls per second (per transaction). User Calls/executes basically represents the number of requests per statement, executes the closer the user calls the better

Pasre

Number of resolutions, not including fast soft parse (the SQL statement that executes 3 times on the MOS will cache the cursor to the PGA, which is always open, and when there is the same SQL execution, skip all the parsing processes and go directly to the execution plan)

Excessive soft parsing means that the application is not efficient

Hard Parse

The number of hard parsing, this indicator is too high to indicate that the bound variable is not well

Sorts

Number of orders

w/a MB processed

The amount of data processed in the unit MB w/a workarea Workarea combined with in-memory sort%, sorts (disk) PGA Aggr look together

Logon

Number of log-in databases

Executes

Number of executions

Rollbacks

Rollback times

Transactions

Number of transactions

Instance efficiency percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Buffer Nowait%: 100.00 Redo Nowait%: 100.00

Buffer hit%: 100.00 in-memory Sort%: 100.00

Library hit%: 99.30 Soft Parse%: 99.79

Execute to Parse%: 5.27 Latch hit%: 100.00

Parse CPU to parse elapsd%: 78.31% non-parse cpu:94.25

This module records Oracle instance memory usage information, the target is 100%, for the OLTP system, this module information is more important, for the OLAP system, the meaning is not significant.

Buffer nowait%

Percentage of data blocks that are not waiting to be fetched

This value is small, indicating that a block of data is being read into memory by another session when a SQL access block is occurring, and that it needs to wait for the operation to complete. What happens is that some chunks of data become hot blocks.

Buffer nowait<95% indicates that there may be hot blocks (find X$BH tch and V$latch_children cache buffers chains).

Redo Nowait

Not waiting to get redo data

Buffer hit%

Data cache Hit Ratio

In-memory sort%

Percent of data exclusion operations in memory

Library hit%

Hit ratio of SQL resolution in shared pool

(Related parameters shared_pool_size\bingd value\cursor_sharing)

Soft Parse%

Soft resolution accounted for the ratio of resolution value low means that most SQL in the DB is not reused <95% consider bound variables

Latch hit%

Latch's hit rate.

The low value is because the shared_pool_size is too large or does not use a binding variable to cause too much hard parsing. To ensure >99%, there are serious performance issues, such as binding, that can affect the parameter.

Parse CPU to parse elapsd%

Resolves the percentage of time that the CPU is consumed in total time. That is: 100* (Parse time cpu/parse time elapsed)

Parse the actual run event/(parse actual run time + wait for resource time in parsing), the higher the better.

%non-parse CPU

The percentage of the CPU's non-profiling time over the entire CPU time.

Shared Pool Statistics Begin End

~~~~~~~~~~~~~~~~~~~~~~~~~~~~  ------  ------

Memory Usage%: 87.44 87.82

% SQL with executions>1:98.06 97.25

% Memory for SQL w/exec>1:92.56 92.46

Memory Usage%

Shared pool memory utilization.

Should be stable between 70%-90%, too small to waste memory, too large is not enough memory.

% SQL with executions>1

An SQL ratio that executes more than 1 times.

If it is too small, the binding variable may not be used.

% Memory for SQL w/exec>1

SQL consumed by more than 1 executions/memory consumed by all SQL (i.e. memories for SQL with execution > 1).

About Oracle AWR Single Instance one