=======================
How to generate AWR Report
=======================
Step A - Get possible step_id
COL BEGIN_INTERVAL_TIME FOR A30
COL END_INTERVAL_TIME FOR A30
SELECT SNAP_ID, DBID, INSTANCE_NUMBER, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT
WHERE TO_CHAR(TRUNC(BEGIN_INTERVAL_TIME),'YYYYMMDD') = '20141206'
SNAP_ID DBID INSTANCE BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ---------- ------- --------------------------- --------------------------
33907 907411508 1 07-DEC-14 01.00.39.792 AM 07-DEC-14 02.00.42.754 AM
33913 907411508 1 07-DEC-14 07.00.57.651 AM 07-DEC-14 08.01.00.724 AM
33912 907411508 1 07-DEC-14 06.00.54.538 AM 07-DEC-14 07.00.57.651 AM
33914 907411508 1 07-DEC-14 08.01.00.724 AM 07-DEC-14 09.00.03.790 AM
How to generate AWR Report
=======================
Step A - Get possible step_id
COL BEGIN_INTERVAL_TIME FOR A30
COL END_INTERVAL_TIME FOR A30
SELECT SNAP_ID, DBID, INSTANCE_NUMBER, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT
WHERE TO_CHAR(TRUNC(BEGIN_INTERVAL_TIME),'YYYYMMDD') = '20141206'
SNAP_ID DBID INSTANCE BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ---------- ------- --------------------------- --------------------------
33907 907411508 1 07-DEC-14 01.00.39.792 AM 07-DEC-14 02.00.42.754 AM
33913 907411508 1 07-DEC-14 07.00.57.651 AM 07-DEC-14 08.01.00.724 AM
33912 907411508 1 07-DEC-14 06.00.54.538 AM 07-DEC-14 07.00.57.651 AM
33914 907411508 1 07-DEC-14 08.01.00.724 AM 07-DEC-14 09.00.03.790 AM
Step B - Generate AWR Report
Option I - via sqlplus from Oracle server.
Option I - via sqlplus from Oracle server.
sqlplus system/xxx@orainst @/software/oracle/111/rdbms/admin/awrrpt.sql
====================
.set_profile
====================
export ORACLE_SID=orainst
export ORACLE_BASE=/software/oracle
export ORACLE_HOME=/software/oracle/111
export PATH=$PATH:$ORACLE_HOME/bin/
====================
./generate_awr.sh
====================
#! /bin/bash
. /etc/sh/orash/oracle_login.sh igt
sqlplus system/xxx@orainst @${ORACLE_HOME}/rdbms/admin/awrrpt.sql
Option II - via sqlplus from remote server.
SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html
( :DBID, :SID, :start_snap_id, :finish_snap_id);
For Example:
SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html
( 37534411, 1, 3001, 3007);
Where:
:DBID <= SELECT dbid FROM V$DATABASE;
:SID <= SELECT instance_number FROM V$INSTANCE;
:start_snap_id and :finish_snap_id <= SELECT * FROM DBA_HIST_SNAPSHOT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html
( :DBID, :SID, :start_snap_id, :finish_snap_id);
For Example:
SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html
( 37534411, 1, 3001, 3007);
Where:
:DBID <= SELECT dbid FROM V$DATABASE;
:SID <= SELECT instance_number FROM V$INSTANCE;
:start_snap_id and :finish_snap_id <= SELECT * FROM DBA_HIST_SNAPSHOT
The report would be generated to sqlplus standard output.
Create snap_id on demand
To create snap_id on demand:
EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
or
EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('ALL');
CREATE_SNAPSHOT function receives only one parameter: FLUSH_LEVEL
Default value is 'TYPICAL',
Alternative value is 'ALL'. Per Oracle documentation: "When the statistics level is set to ALL, the AWR gathers the maximum amount of performance data."
Create snap_id on demand
To create snap_id on demand:
EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
or
EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('ALL');
CREATE_SNAPSHOT function receives only one parameter: FLUSH_LEVEL
Default value is 'TYPICAL',
Alternative value is 'ALL'. Per Oracle documentation: "When the statistics level is set to ALL, the AWR gathers the maximum amount of performance data."
Parse in AWR Report.
Theory: Soft Parse vs Hard Parse.
Theory: Soft Parse vs Hard Parse.
The parsing process performs two main functions:
First Step:
- Statement Syntax Check.
- Statement Objects check (Does table exists, permissions, etc...)
Second Step:
The next step is to get the Execution Plan
- First check if the statement we are currently parsing has already in fact been processed by some other session.
- If YES - We get the execution plan and row source for execution from Library cache.
This is the Soft Parse.
This is the Soft Parse.
- If NO - Oracle must must parse, optimize and generate the plan for the query.
This is the Hard Parse.
This is the Hard Parse.
Parsing info in AWR Report
Load Profile section.
Load Profile
Load Profile section.
Load Profile
| Per Second | Per Transaction | Per Exec | Per Call | |
|---|---|---|---|---|
| DB Time(s): | 0.1 | 0.2 | 0.00 | 0.00 |
| DB CPU(s): | 0.1 | 0.1 | 0.00 | 0.00 |
| Redo size: | 156,764.6 | 217,590.6 | ||
| Logical reads: | 1,223.0 | 1,697.5 | ||
| Block changes: | 282.5 | 392.1 | ||
| Physical reads: | 206.2 | 286.3 | ||
| Physical writes: | 114.4 | 158.8 | ||
| User calls: | 59.5 | 82.6 | ||
| Parses: | 2.5 | 3.5 | ||
| Hard parses: | 0.2 | 0.3 | ||
| W/A MB processed: | 48,220.2 | 66,930.1 | ||
| Logons: | 0.1 | 0.1 | ||
| Executes: | 73.9 | 102.5 | ||
| Rollbacks: | 0.0 | 0.0 | ||
| Transactions: | 0.7 |
Parses: The total of all parses; both hard and soft.
Hard Parses: The parses requiring a completely new parse of the SQL statement. These consume both latches and shared pool area.
Soft Parses: Soft parses are not listed but derived by subtracting the hard parses from parses.
Instance Efficiency Percentages (Target 100%) section.
Instance Efficiency Percentages (Target 100%)
| Buffer Nowait %: | 100.00 | Redo NoWait %: | 100.00 |
| Buffer Hit %: | 92.47 | In-memory Sort %: | 100.00 |
| Library Hit %: | 99.24 | Soft Parse %: | 92.89 |
| Execute to Parse %: | 96.57 | Latch Hit %: | 99.98 |
| Parse CPU to Parse Elapsd %: | 0.00 | % Non-Parse CPU: | 99.47 |
Buffer Hit % – Percent of requested blocks read from the buffer cache vs blocks read from disk.
Library Hit % – Ratio that shows how often requested SQL was found in Library cache. 100 % means that where were no hard parses at all. Low value may indicate too small library cache, SQLs not using bind variables, frequent invalidation of referred objects or just that system was just started.
Execute to Parse % – Ratio that shows number of executions to number of parses.
Parse CPU to Parse Elapsd% – Parse Elapsd is sum of Parse CPU and Waits. This ratio show how much time was spent on waits for latches during parsing. If Waits are low then this ration will be high and vise versa. Target is 100%(no waits for latches).
Soft Parse % – Soft Parses to Hard Parses. Target is 100% – all sql is in the cache and no Hard Parse occurred.
Execute to Parse % Elaborated
This ration is calculated as following:
'Execute to Parse %:’, ROUND(100*(1-:prse/:exe),2)
From Tom Kyte article in Oracle Magazine:
"My preferred way to improve your execute-to-parse ratio, is to move all the SQL out of the client application and into stored procedures.
PL/SQL is a statement caching machine. When you say “close this cursor” in PL/SQL, PL/SQL tells you, “OK, it’s closed,” but it really isn’t. The PL/SQL engine smartly keeps the cursor open, knowing that you are just going to call that stored procedure and execute that SQL again."
AWR Report has a section "SQL ordered by Parse Calls"
Is it Soft Parse, or Hard Parse, or both?
AWR Does not have a good distinction between Soft Parse and Hard Parse
What to check?
AWR Does not have a good distinction between Soft Parse and Hard Parse
What to check?
1. Top SQL with Top Row Sources
This will show bottlenecks in instance.
Such as FULL SCANs
2. ADDM Task ADDM
This will show the top issues, as per ADDM report
For example:
ADDM Task ADDM:1255688165_1_14003
ADDM Report for Task 'ADDM:1255688165_1_14003'
----------------------------------------------
Analysis Period
---------------
AWR snapshot range from 14002 to 14003.
Time period starts at 01-JUN-21 02.00.48 PM
Time period ends at 01-JUN-21 03.00.16 PM
Analysis Target
---------------
Database 'IGT' with DB ID 1255688165.
Database version 12.2.0.1.0.
ADDM performed an analysis of instance igt, numbered 1 and hosted at
qanfv-1-dbs-1a.
Activity During the Analysis Period
-----------------------------------
Total database time was 68821 seconds.
The average number of active sessions was 19.29.
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
-------------------- ------------------- ---------------
1 Top SQL Statements 18.17 | 94.2 2
2 CPU Usage 2.49 | 12.89 2
3 Buffer Cache Latches .4 | 2.07 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Top SQL Statements
Impact is 18.17 active sessions, 94.2% of total activity.
---------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.
Recommendation 1: SQL Tuning
Estimated benefit is 9.14 active sessions, 47.4% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"1qkunjg7g6twp".
Related Object
SQL statement with SQL_ID 1qkunjg7g6twp.
select * from SGA_W_PSMS_SUBSCRIBER where IMSI = :1 AND AFFILIATE_ID
= :2 ORDER BY TS_E_LAST_LU ASC
Rationale
The SQL spent 88% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "1qkunjg7g6twp" was executed 4184399 times and
had an average elapsed time of 0.0086 seconds.
Recommendation 2: SQL Tuning
Estimated benefit is 7.8 active sessions, 40.43% of total activity.3. Top 5 Timed Foreground Events
Free Buffer waits
Large value indicate lack of memory.
Data blocks are read from disk, into the Buffer Cache.
In case there is not enough memory, Free Buffer wait occurs.
This could be a result of a combination of too low memory, and not effective application, doing not selective enough fetches from database.
Per Oracle documentation:
1. Increase the buffer cache.
2. Check that I/O is evenly distributed.
Run this query to see if the I/O is evenly distributed.
Below is a real example of an effect of increasing MAX_MEMORY and MAX_MEMORY_TARGET, from 4Gb to 8Gb, on free buffer waits wait event.
Before memory increase, "Free Buffer Waits" was constantly in the Top 5 Timed Foreground Events list, usually on 1st or 2nd place, along with "DB File Sequential Read".
Obviously Instance was having a heavy I/O load and not keeping up with I/O requests.
After memory increase, the "Free Buffer Wait" was significantly lower.
Before memory increase:
After memory increase:
Large value indicate lack of memory.
Data blocks are read from disk, into the Buffer Cache.
In case there is not enough memory, Free Buffer wait occurs.
This could be a result of a combination of too low memory, and not effective application, doing not selective enough fetches from database.
Per Oracle documentation:
This event occurs mainly when a server process is trying to read a new buffer into the buffer cache but too many buffers are either pinned or dirty and thus unavailable for reuse.
The session posts to DBWR then waits for DBWR to create free buffers by writing out dirty buffers to disk.
DBWR may not be keeping up with writing dirty buffers in the following situations:
- The I/O system is slow.
- There are resources it is waiting for, such as latches.
- The buffer cache is so small that DBWR spends most of it's time cleaning out buffers for server processes.
- The buffer cache is so big that one DBWR process is not enough to free enough buffers in the cache to satisfy requests.
1. Increase the buffer cache.
2. Check that I/O is evenly distributed.
Run this query to see if the I/O is evenly distributed.
SELECT name, phyrds, phywrts
FROM v$filestat a, v$datafile b
WHERE a.file# = b.file#
Before memory increase, "Free Buffer Waits" was constantly in the Top 5 Timed Foreground Events list, usually on 1st or 2nd place, along with "DB File Sequential Read".
Obviously Instance was having a heavy I/O load and not keeping up with I/O requests.
After memory increase, the "Free Buffer Wait" was significantly lower.
Before memory increase:
After memory increase:
Instance Efficiency Percentages(Target 100%)
The low value of Soft Parse - suggest to little shared Library Cache.
Buffer Busy Waits vs Free Buffer Waits
Free Buffer Waits
Free Buffer Waits wait event indicates that a server process was unable to find a free buffer and has posted the database writer to make free buffers by writing out dirty buffers.
Buffer Busy Waits
Buffer Busy Waits occur when an Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked.
Buffer busy wait condition can happen for either of the following reasons:
* The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.
* Another session has the buffer block locked in a mode that is incompatible with the waiting session's request.
The solution for Free Buffer Waits commonly includes tuning I/O, and increasing the size of the buffer cache.
The solution for Buffer Busy Waits includes tuning indexes to reduce amount of data blocks loaded into shared pool.
About Buffer Busy Waits
Instance Efficiency Percentages(Target 100%)
Free Buffer Waits
Free Buffer Waits wait event indicates that a server process was unable to find a free buffer and has posted the database writer to make free buffers by writing out dirty buffers.
Free Buffer Waits wait event indicates that a server process was unable to find a free buffer and has posted the database writer to make free buffers by writing out dirty buffers.
Buffer Busy Waits occur when an Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked.
* The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.
* Another session has the buffer block locked in a mode that is incompatible with the waiting session's request.
The solution for Buffer Busy Waits includes tuning indexes to reduce amount of data blocks loaded into shared pool.
About Buffer Busy Waits
SGA Target Advisory
In this example, Per Oracle SGA Target Advisory, performance should benefit from another 2Gb addition to SGA.
SQL ordered by Parse Calls
| e Calls | Executions | % Total Parses | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|
| 26,274 | 26,274 | 13.08 | 9tgj4g8y4rwy8 | select type#, blocks, extents,... | |
| 10,084 | 10,084 | 5.02 | 7ykawc7v0hc24 | Data Pump Worker | SELECT CONDITION FROM SYS.CDEF... |
| 8,404 | 8,404 | 4.19 | 0h6b2sajwb74n | select privilege#, level from ... | |
| 5,031 | 5,031 | 2.51 | bsa0wjtftg3uw | select file# from file$ where ... | |
| 3,418 | 3,418 | 1.70 | 15uz111g14124 | Data Pump Worker | SELECT ACTION# FROM SYS.TRIGGE... |
| 3,150 | 3,150 | 1.57 | 30hzffxbsfct8 | JDBC Thin Client | select producttre0_.ITEM_ID as... |
| 3,132 | 3,132 | 1.56 | 459f3z9u4fb3u | select value$ from props$ wher... | |
| 2,950 | 2,950 | 1.47 | 350f5yrnnmshs | lock table sys.mon_mods$ in ex... | |
| 2,950 | 2,950 | 1.47 | g00cj285jmgsw | update sys.mon_mods$ set inser... | |
| 2,940 | 2,940 | 1.46 | 0ws7ahf1d78qa | select SYS_CONTEXT('USERENV', ... | |
| 2,848 | 2,848 | 1.42 | crxwzb62uga8d | JDBC Thin Client | SELECT TRIGGER_NAME, TRIGGER_... |
| 2,610 | 2,610 | 1.30 | 0k8522rmdzg4k | select privilege# from sysauth... | |
| 2,421 | 2,421 | 1.21 | 5ur69atw3vfhj | select decode(failover_method,... | |
| 2,400 | 2,400 | 1.20 | 1uzhrfn574t12 | sqlplus@my_server (TNS V1-V3) | SELECT ATTRIBUTE, SCOPE, NUMER... |
| 2,400 | 2,400 | 1.20 | 4d4ayw5argrgz | dbrefresh@my_server (TNS V1-V3) | SELECT TABLE_ID, ISN, MAP_NA... |
| 2,400 | 2,400 | 1.20 | 5qgz1p0cut7mx | sqlplus@my_server (TNS V1-V3) | BEGIN DBMS_OUTPUT.DISABLE; END... |
| 2,400 | 2,400 | 1.20 | c4pc3jhzjcmc7 | sqlplus@my_server (TNS V1-V3) | SELECT CHAR_VALUE FROM SYSTEM.... |
| 2,400 | 2,400 | 1.20 | d6vwqbw6r2ffk | sqlplus@my_server (TNS V1-V3) | SELECT USER FROM DUAL |
| 2,400 | 2,400 | 1.20 | dyk4dprp70d74 | SQL*Plus | SELECT DECODE('A', 'A', '1', '... |
| 2,400 | 2,400 | 1.20 | g4y6nw3tts7cc | sqlplus@my_server (TNS V1-V3) | BEGIN DBMS_APPLICATION_INFO.SE... |
| 2,399 | 2,399 | 1.19 | 0u3dq44mn09pn | sqlplus@v(TNS V1-V3) | select INSTANCE_NAME||STATUS f... |
| 2,241 | 2,241 | 1.12 | 7qd215rsryu1u | rman@my_server (TNS V1-V3) | declare thread number; seque... |
In this section there is no definition between soft parse and hard parse.
SQL ordered by Version Count
| Version Count | Executions | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|
| 224 | 1,290 | 7pcmxut6628hd | JDBC Thin Client | MERGE into SGA_W_SUBSCRIBER o ... |
| 161 | 7,656 | 7ng34ruy5awxq | select i.obj#, i.ts#, i.file#,... | |
| 161 | 7,656 | 7ng34ruy5awxq | select i.obj#, i.ts#, i.file#,... | |
| 161 | 7,656 | 7ng34ruy5awxq | select i.obj#, i.ts#, i.file#,... | |
| 118 | 2,315 | a73wbv1yu8x5c | select con#, type#, condlength... | |
| 118 | 8,689 | dwypdxsjg4juq | select /*+ index_ss(obj$ i_obj... | |
| 118 | 8,689 | dwypdxsjg4juq | select /*+ index_ss(obj$ i_obj... | |
| 113 | 6,890 | 2q93zsrvbdw48 | select grantee#, privilege#, n... | |
| 103 | 3,061 | 1gu8t96d0bdmu | select t.ts#, t.file#, t.block... | |
| 103 | 3,061 | 1gu8t96d0bdmu | select t.ts#, t.file#, t.block... | |
| 103 | 18,843 | 96g93hntrzjtr | select /*+ rule */ bucket_cnt,... | |
| 96 | 10,078 | 5n1fs4m2n2y0r | select pos#, intcol#, col#, sp... | |
| 92 | 6,886 | 6aq34nj2zb2n7 | select col#, grantee#, privi... | |
| 90 | 426 | aq4js2gkfjru8 | update tsq$ set blocks=:3, max... | |
| 87 | 13,900 | 53saa2zkr6wc3 | select intcol#, nvl(pos#, 0), ... | |
| 87 | 13,900 | 53saa2zkr6wc3 | select intcol#, nvl(pos#, 0), ... | |
| 69 | 462 | dgtxysfq8yq97 | select o.owner#, o.name, ... | |
| 62 | 26,274 | 9tgj4g8y4rwy8 | select type#, blocks, extents,... |
Why would SQL have many versions?
For lack of obvious reasons, one possibility, is that the SQL statements are invalidated between each execution.
Tom Kyte note on "Why do we see several entries for same SQL statement in V$SQL"
Per Tom Kyte, all SQL versions should be listed in V$SQL_SHARED_CURSOR table.
However, the data displayed in AWR Report is not reflecting that.
For example: for sql_id 7pcmxut6628hd per AWR Report there are 224 version.
But in table V$SQL_SHARED_CURSOR there are only 8 entries.
And all 8, have bind_mismatch='Y'.
SQL Ordered by Gets
Buffer Gets (also called Logical Reads) means the data is already in the data buffer cache and Oracle is trying to locate the rows that match the WHERE clause.
A high number of Buffer Gets means two things:
(1) Oracle is working very hard to locate the matching rows because of an unrestrictive WHERE clause or bad/missing indexes;
(2) High CPU since Oracle counts the time it’s doing Buffer Gets as CPU time.
Real case scenario
The top SQL in the AWR Report is doing a Full Table Scan against a table that has only 12,000 rows.
But then, looking into USER_SEGMENTS:
SELECT SUM(bytes/1024/1024) AS Mb FROM USER_SEGMENTS WHERE segment_name ='MY_TABLE'
The result is 672 Mb!!!
The reason, is that this tables undergoes a lot of DML operations.
The steps to address this situation:
ALTER SESSION SET DDL_LOCK_TIMEOUT = 600;
ALTER TABLE MY_TABLE ENABLE ROW MOVEMENT;
ALTER TABLE MY_TABLE SHRINK SPACE;
Real case scenario - disabling an unused index - big effect on Index Tablespace IO.
A database has a large table, approx 10,000,000 rows, with 4 indexes on this table.
Two of the indexes on that table were not used by the Optimizer.
These indexes were on a YES/NO column, so obviously Oracle Optimizer choose not to use them.
(2 Distinct values, for 10,000,000 entries!)
So Oracle was constantly updating the unused index, splitting blocks, etc.
These two indexes were dropped.
Below are AWR Tablespace IO Stats from two runs, one week apart.
The first AWR screenshot shows DB Writes on Index Tablespace before dropping the indexes.
The first AWR screenshot shows DB Writes on Index Tablespace after dropping the indexes.
The time frame is 24 hours, 7 days apart.
There was approx 5% decrease in overall activity during the second run.
The I/O activity on Data Tablespace (IGT_TABLE) has decreased by 4%.
The I/O activity on Index Tablespace (IGT_INDEX) has decreased by nearly 90%.!!!
- ordered by IOs (Reads + Writes)
desc
Tablespace
|
Reads
|
Av Reads/s
|
Av Rd(ms)
|
Av Blks/Rd
|
Writes
|
Av Writes/s
|
Buffer Waits
|
Av Buf Wt(ms)
|
IGT_TABLE
|
13,357,884
|
161
|
0.24
|
1.10
|
8,140,422
|
98
|
334
|
1.29
|
IGT_INDEX
|
2,239,321
|
27
|
0.58
|
1.00
|
2,449,789
|
30
|
215
|
0.51
|
UNDOTBS
|
3,878
|
0
|
4.18
|
1.00
|
94,343
|
1
|
500
|
0.42
|
SYSAUX
|
40,405
|
0
|
1.22
|
2.56
|
13,181
|
0
|
0
|
0.00
|
WORKAREA
|
23,539
|
0
|
0.08
|
1.11
|
28,934
|
0
|
0
|
0.00
|
SYSTEM
|
24,958
|
0
|
2.10
|
3.65
|
2,943
|
0
|
123
|
1.06
|
DWH_TABLE
|
2,135
|
0
|
0.87
|
2.60
|
147
|
0
|
0
|
0.00
|
TEMPORARY
|
193
|
0
|
0.05
|
5.62
|
125
|
0
|
0
|
0.00
|
DWH_INDEX
|
147
|
0
|
5.44
|
1.00
|
147
|
0
|
0
|
0.00
|
GIN
|
147
|
0
|
0.54
|
1.00
|
147
|
0
|
0
|
0.00
|
GININDEX
|
147
|
0
|
0.54
|
1.00
|
147
|
0
|
0
|
0.00
|
Tablespace IO Stats
- ordered by IOs (Reads + Writes)
desc
Tablespace
|
Reads
|
Av Reads/s
|
Av Rd(ms)
|
Av Blks/Rd
|
Writes
|
Av Writes/s
|
Buffer Waits
|
Av Buf Wt(ms)
|
IGT_TABLE
|
13,205,936
|
167
|
0.19
|
1.10
|
7,813,737
|
99
|
408
|
0.78
|
IGT_INDEX
|
1,526,675
|
19
|
0.56
|
1.00
|
272,469
|
3
|
228
|
0.26
|
UNDOTBS
|
2,920
|
0
|
5.42
|
1.00
|
89,058
|
1
|
305
|
0.59
|
SYSAUX
|
41,172
|
1
|
1.07
|
2.45
|
13,093
|
0
|
0
|
0.00
|
WORKAREA
|
23,468
|
0
|
0.12
|
1.11
|
27,968
|
0
|
0
|
0.00
|
SYSTEM
|
25,279
|
0
|
1.71
|
3.48
|
3,188
|
0
|
129
|
0.54
|
DWH_TABLE
|
2,124
|
0
|
1.11
|
2.60
|
136
|
0
|
0
|
0.00
|
TEMPORARY
|
176
|
0
|
0.06
|
7.00
|
132
|
0
|
0
|
0.00
|
DWH_INDEX
|
136
|
0
|
5.07
|
1.00
|
136
|
0
|
0
|
0.00
|
GIN
|
136
|
0
|
0.29
|
1.00
|
136
|
0
|
0
|
0.00
|
GININDEX
|
136
|
0
|
0.29
|
1.00
|
136
|
0
|
0
|
0.00
|
In addition, check out the Buffer Wait Statistics section.
A lot of the waits are coming from UNDO TBS.
This is sync with high Buffer Wait in Undo TBS.
Solution: increase the size of UNTO TBS
SELECT tablespace_name, extent_management, segment_space_management
FROM DBA_TABLESPACES
WHERE tablespace_name = 'UNDOTBS';
TABLESPACE_NAME EXTENT_MAN SEGMEN
------------------------------ ---------- ------
UNDOTBS LOCAL MANUAL
SELECT tablespace_name, file_name,
ROUND(maxbytes/1024/1204) as MAX_MB,
ROUND(bytes/1024/1204) as USED_MB
FROM DBA_DATA_FILES
WHERE tablespace_name='UNDOTBS';
TABLESPACE_NAME FILE_NAME MAX_MB USED_MB
--------------- ---------------------------------------- ------ -------
UNDOTBS /oracle_db/db1/db_igt/ora_undotbs_01.dbf 1701 1446
ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_undotbs_01.dbf' RESIZE 3000M;
Database altered.
Appendix
Reading AWR Report
How to read AWR reports
Reads vs Gets
Gets are Logical Reads.
Reads in AWR are Physical Reads
===============================
No Data in AWR Report
===============================
1. Check out the value for parameter CONTROL_MANAGEMENT_PACK_ACCESS
When running AWR report, there is no data, and only these messages in the generated report file.
No data exists for this section of the report.
Check out the value for parameter CONTROL_MANAGEMENT_PACK_ACCESS
SQL> show parameter control_management
NAME TYPE VALUE
------------------------------------ ----------- ----------
control_management_pack_access string NONE
If it is set to NONE, set it to 'DIAGNOSTIC+TUNING'
ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS='DIAGNOSTIC+TUNING' scope=both;
System altered.
================================
Change the interval and retention settings
Tom Kyte note on "Why do we see several entries for same SQL statement in V$SQL"
===============================
Where is the path for alert.log
===============================
Query V$DIAG_INFO
INST_ID NAME VALUE
------- ------------------- -------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base d:\software\oracle
1 ADR Home d:\software\oracle\diag\rdbms\igt\igt
1 Diag Trace d:\software\oracle\diag\rdbms\igt\igt\trace
1 Diag Alert d:\software\oracle\diag\rdbms\igt\igt\alert
1 Diag Incident d:\software\oracle\diag\rdbms\igt\igt\incident
1 Diag Cdump d:\software\oracle\diag\rdbms\igt\igt\cdump
1 Health Monitor d:\software\oracle\diag\rdbms\igt\igt\hm
1 Default Trace File d:\software\oracle\diag\rdbms\igt\igt\trace\igt_ora_42388.trc
11 rows selected
Read Types and Read Wait Events.
There are three types of read WAIT events:
a) db file sequential read (single block IO).
This reads into the buffer cache, you do a logical IO after this read to get the block out.
b) db file scattered read (multi-block IO, we scatter the blocks in the buffer cache).
This reads into the buffer cache, you do logical IO's after this read to get the blocks out.
c) Direct path read (usually, but not always, done with parallel queries - can be done with serial queries these days as well).
This read bypasses the buffer cache, we read from disk directly into your pga avoiding the overhead of the buffer cache.
There are two types of reads:
a) single block IO
b) multi block IO
===============================
Where is the path for alert.log
===============================
Query V$DIAG_INFO
INST_ID NAME VALUE
------- ------------------- -------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base d:\software\oracle
1 ADR Home d:\software\oracle\diag\rdbms\igt\igt
1 Diag Trace d:\software\oracle\diag\rdbms\igt\igt\trace
1 Diag Alert d:\software\oracle\diag\rdbms\igt\igt\alert
1 Diag Incident d:\software\oracle\diag\rdbms\igt\igt\incident
1 Diag Cdump d:\software\oracle\diag\rdbms\igt\igt\cdump
1 Health Monitor d:\software\oracle\diag\rdbms\igt\igt\hm
1 Default Trace File d:\software\oracle\diag\rdbms\igt\igt\trace\igt_ora_42388.trc
11 rows selected
Read Types and Read Wait Events.
There are three types of read WAIT events:
a) db file sequential read (single block IO).
This reads into the buffer cache, you do a logical IO after this read to get the block out.
b) db file scattered read (multi-block IO, we scatter the blocks in the buffer cache).
This reads into the buffer cache, you do logical IO's after this read to get the blocks out.
c) Direct path read (usually, but not always, done with parallel queries - can be done with serial queries these days as well).
This read bypasses the buffer cache, we read from disk directly into your pga avoiding the overhead of the buffer cache.
There are two types of reads:
a) single block IO
b) multi block IO
Reads vs Gets
Gets are Logical Reads.
Reads in AWR are Physical Reads
===============================
No Data in AWR Report
===============================
1. Check out the value for parameter CONTROL_MANAGEMENT_PACK_ACCESS
When running AWR report, there is no data, and only these messages in the generated report file.
No data exists for this section of the report.
Check out the value for parameter CONTROL_MANAGEMENT_PACK_ACCESS
SQL> show parameter control_management
NAME TYPE VALUE
------------------------------------ ----------- ----------
control_management_pack_access string NONE
If it is set to NONE, set it to 'DIAGNOSTIC+TUNING'
ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS='DIAGNOSTIC+TUNING' scope=both;
System altered.
================================
Change the interval and retention settings
================================
2. Check out the Oracle Edition
oracle@my_host:~>% sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 23 02:17:58 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
This is Standard Edition (In EE there is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
In Standard Edition
Word of Warning
This functionality is separately licensed Oracle Database option.
Oracle 11 - Database Licensing Information
SELECT snap_interval, retention
FROM DBA_HIST_WR_CONTROL;
SNAP_INTERVAL RETENTION
------------------------------ ------------------------------
+00000 00:15:00.0 +00008 00:00:00.0
Change the interval to 30 minutes
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(interval => 30);
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT snap_interval, retention FROM DBA_HIST_WR_CONTROL;
SNAP_INTERVAL RETENTION
------------------------------ ------------------------------
+00000 00:30:00.0 +00008 00:00:00.0
Change the interval to 7 days
1440*7=10080 minutes
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 10080);
END;
/
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention
SELECT baseline_type, moving_window_size FROM DBA_HIST_BASELINE;
BASELINE_TYPE MOVING_WINDOW_SIZE
------------- ------------------
MOVING_WINDOW 8
First need to change the baseline window size to 7 days.
Then can lower retention to 7 days.
Change the Window size to equal to retention of 7 days
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(window_size=> 7);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 10080);
END;
/
PL/SQL procedure successfully completed.
SELECT snap_interval, retention FROM DBA_HIST_WR_CONTROL;
SNAP_INTERVAL RETENTION
------------------------------ ------------------------------
+00000 00:30:00.0 +00007 00:00:00.0
2. Check out the Oracle Edition
oracle@my_host:~>% sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 23 02:17:58 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
This is Standard Edition (In EE there is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
In Standard Edition
Word of Warning
This functionality is separately licensed Oracle Database option.
Oracle 11 - Database Licensing Information
No comments:
Post a Comment