AWR and ASH commands

- Automatic Workload Repository (AWR)
- Basic ideas
- dba_hist% views
- 11.2 enhancements
- AWR use cases & limitations

- Active Session History (ASH)
- Basic ideas
- 11.2 enhancements
- Typical use cases

Oracle Database performance diagnostics tools evolution:
- UTLBSTAT/UTLESTAT
- STATSPACK (starting from Oracle 8i)
- AWR (starting from Oracle 10g)

Automatic Workload Repository

SQL> select inst_id, name, value from gv$sysstat
where name in ('physical reads','session logical reads','user commits','user rollbacks')
order by name, inst_id;

INST_ID NAME VALUE
---------- ---------------------- ----------
1 physical reads 219525005
2 physical reads 905189162
1 session logical reads 5925054313
2 session logical reads 1.7457E+10
1 user commits 5294595
2 user commits 28113236
1 user rollbacks 8455066
2 user rollbacks 150792358


-- All stattistics:

select * from v$statname;
10.2.0.4 = 387; 10.2.0.5 = 395
11.2.0.2 = 628


SQL> select inst_id, event, total_waits, time_waited, average_wait,
sum(time_waited_micro) over (partition by event) twm_total
from gv$system_event
where wait_class='User I/O'
order by twm_total desc, event, inst_id;


INST_ID EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT TWM_TOTAL
---------- ------------------------- ----------- ----------- ------------ ----------
1 db file sequential read 27577537 40274551 1.46 3.8091E+12
2 db file sequential read 159856155 340633564 2.13 3.8091E+12
1 direct path read temp 44837941 8919281 .2 2.9431E+11
2 direct path read temp 122808964 20511343 .17 2.9431E+11
1 direct path read 1435296 1511142 1.05 9.9823E+10
2 direct path read 12075539 8471152 .7 9.9823E+10


-- All events:
select count(*) from v$event_name;
10.2.0.4 = 889; 10.2.0.5 = 916
11.2.0.2 = 1142


SQL> select sql_id, execs, rw, LIOs, PhIOs from ( select sql_id, /*sql_text,*/ sum(executions) execs, sum(rows_processed) rw, sum(buffer_gets) LIOs, sum(disk_reads) PhIOs from gv$sql where module='SQL Developer' and rownum>=1 group by sql_id/*, sql_text*/ order by PhIOs desc) where rownum<=5;



SQL_ID EXECS RW LIOS PHIOS
------------- ---------- ---------- ---------- ----------
3w1fk8r93360m 1 1 8156 348
davt444np8yr7 6 6 3583 289
7b775q3tc69a9 22 22 2899 213
58kz178ugwm87 29 29 2982 96
ayaq4d3bbt7k8 1 3 106 88


AWR just makes a copy of system views with scheduled time interval…
… and gives a possibility to make a report (diff) based on two snapshots.


AWR configuration.
 
- Oracle Diagnostic Pack is required (Oracle EE additional option).
- STATISTICS_LEVEL = TYPICAL or ALL
- (11g) CONTROL_MANAGEMENT_PACK_ACCESS = DIAGNOSTIC or DIAGNOSTIC+TUNING


AWR configuration.
 
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------ ------------------ ----------
1486911308 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT

To change:
dbms_workload_repository.modify_snapshot_settings(
retention=>129600, -- New retention time (in minutes). 60*24*90=129600
interval=>30, -- New interval setting between each snapshot, in units of minutes
topnsql=>50 – Number 30..50000; 'DEFAULT', 'MAXIMUM') 


Create (additional) snapshot:
dbms_workload_repository.create_snapshot('ALL') -- flush_level = 'TYPICAL' or 'ALL‘

SQL> select dbms_workload_repository.create_snapshot from dual;

CREATE_SNAPSHOT
---------------
7762

SQL> select dbms_workload_repository.create_snapshot('ALL') from dual;

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('ALL')
-----------------------------------------------
7763

SQL> select snap_id, count(*) from dba_hist_sqlstat where snap_id in (7762,7763) group by snap_id;

SNAP_ID COUNT(*)
---------- ----------
7763 103
7762 59

Generate report across two snapshots:
 
select * from table(dbms_workload_repository.awr_report_text(2334238573, -- DBID
1, -- instance number
38392, -- ‘Begin Snapshot’ ID
38393, -- ‘End Snapshot’ ID
0) -- flag = 0 or 8 (display ADDM specific parts));



-- ADDM sections: 
Buffer Cache Advisory (for ADDM); 
SGA Target Advisory (for ADDM); 
Shared Pool Advisory (for ADDM); 
Streams Pool Advisory (for ADDM); 
PGA Target Advisory (for ADDM)
There is a similar awr_report_html() function.


AWR report (fragments) 1/8:
WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
XXXXX 207063651 ###### 1 09-Oct-11 20:43 11.2.0.2.0 YES
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
###### Linux x86 64-bit 8 8 4 31.38

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 46095 03-Feb-12 08:00:02 248 5.0
End Snap: 46096 03-Feb-12 09:00:12 249 5.0
Elapsed: 60.17 (mins)
DB Time: 183.68 (mins)
11.2 new data/format

AWR reports 2/8:
 
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 3.1 0.4 0.05 0.02
DB CPU(s): 1.1 0.1 0.02 0.01
Redo size: 167,379.4 20,585.9
Logical reads: 70,689.2 8,694.0
Block changes: 769.8 94.7
Physical reads: 3,480.6 428.1
Physical writes: 126.5 15.6
User calls: 138.9 17.1
Parses: 46.4 5.7
Hard parses: 0.1 0.0
W/A MB processed: 4.9 0.6
Logons: 0.1 0.0
Executes: 63.2 7.8
Rollbacks: 0.3 0.0
Transactions: 8.1

AWR report 3/8:
 
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                  Avg
                                 wait %   DB
Event Waits Time(s)  (ms)      time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db file sequential read 618,214 4,667 8 42.3 User I/O
DB CPU 4,085 37.1
direct path read temp 810,891 1,726 2 15.7 User I/O
enq: TX - row lock contention 693 208 300 1.9 Application
log file sync 29,137 104 4 .9 Commit
Host CPU (CPUs: 8 Cores: 8 Sockets: 4)
~~~~~~~~ Load Average
Begin End %User %System %WIO %Idle
--------- --------- --------- --------- --------- ---------
4.45 4.17 27.9 1.1 14.1 70.7
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 15.0
% of busy CPU for Instance: 51.3 



AWR reports 4/8:
Wait Event Histogram DB/Inst: ######/###### Snaps: 46095-46096
<…>
            % of Waits
            -----------------------------------------------
Total
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
db file sequential read 619.1 22.0 1.8 9.4 37.0 25.0 3.5 1.5 .0
<…>
            Waits 64ms
Event to 2s <32ms <64ms <1/8s <1/4s <1/2s <1s <2s >=2s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
db file sequential read 9020 98.5 1.0 .3 .1 .1 .0 .0 .0
<…>
           Waits 4s
Event to 2m <2s <4s <8s <16s <32s < 1m < 2m >=2m
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
db file sequential read 1 100.0 .0 




AWR report 5/8:
SQL ordered by Elapsed Time DB/Inst: ######/##### Snaps: 46095-46096
<…>
Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
3,610.8 4 902.69 32.8 3.8 96.6 3c8sz91kt214t
Module: JDBC Thin Client
MERGE INTO …
3,609.9 0 N/A 32.8 52.0 47.8 0q694rv7gzkzs
Module: SQL*Plus
SELECT count(*) FROM …



AWR report 6/8:
SQL ordered by Elapsed Time
SQL ordered by CPU Time
SQL ordered by User I/O Wait Time
SQL ordered by Gets
SQL ordered by Reads
SQL ordered by Physical Reads (UnOptimized)
-> UnOptimized Read Reqs = Physical Read Reqts - Optimized Read Reqs
SQL ordered by Executions
SQL ordered by Parse Calls
SQL ordered by Sharable Memory
SQL ordered by Version Count
SQL ordered by Cluster Wait Time



AWR report 7/8:
IOStat by Function summary DB/Inst: #####/##### Snaps: 46095-46096
Reads: Reqs Data Writes: Reqs Data Waits: Avg
Function Name Data per sec per sec Data per sec per sec Count Tm(ms)
--------------- ------- ------- ------- ------- ------- ------- ------- -------
Direct Reads 45.5G 228.3 12.9145 0M 0.0 0M 0 N/A
Buffer Cache Re 2.4G 171.7 .681144 0M 0.0 0M 619.1K 7.1
DBWR 0M 0.0 0M 1.7G 71.4 .493891 0 N/A
<…>
TOTAL: 48.9G 409.7 13.8572 2.8G 89.3 .800808 716.7K 6.3
IOStat by Filetype summary DB/Inst: #####/##### Snaps: 46095-46096
Reads: Reqs Data Writes: Reqs Data Small Large
Filetype Name Data per sec per sec Data per sec per sec Read Read
--------------- ------- ------- ------- ------- ------- ------- ------- -------
Temp File 39.6G 224.9 11.2232 0M 0.0 0M 1.6 N/A
Data File 8.4G 175.2 2.37223 1.7G 71.4 .493060 7.1 29.1
<…>
TOTAL: 48.9G 409.7 13.8569 2.8G 89.3 .799977 3.9 31.7
IOStat by Function/Filetype summary




AWR report 8/8:
Segments by Physical Reads
Segments by Physical Read Requests
Segments by UnOptimized Reads
Segments by Optimized Reads
Segments by Direct Physical Reads
Segments by Physical Writes
Segments by Physical Write Requests
Segments by Direct Physical Writes
Segments by Table Scans
Segments by DB Blocks Changes
+ enhanced RAC (interconnect, DRM) statistics




AWR baselines.
Baseline is a pair of snapshots…
Oracle10g: fixed baselines only:
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
start_snap_id,
end_snap_id,
baseline_name...
);
Oracle 11g: baseline templates for fixed date or for repeated timeframe.


AWR tables and views.
AWR data is stored in SYS schema in SYSAUX tablespace.
Looks like there are WRH$% and WRM$% tables.
DBA_HIST% views are created above the tables.
10.2.0.4 – 78 views; 11.2.0.2 – 111 views 



select <...>
from dba_hist_snapshot, dba_hist_sys_time_model
<...>
Date            min_snap_id max_id DB time DB CPU
31.01.2012 121851 121955 3582,46 1899,31
30.01.2012 121747 121850 3942,73 1787,58
29.01.2012 121643 121746 3225,62 1587,83
28.01.2012 121538 121642 2626,14 1410,22
27.01.2012 121435 121537 -705799,94 -284112,92
26.01.2012 121329 121434 3524,76 1934,44
25.01.2012 121226 121328 3935,66 2004,01



select <...>
from dba_hist_snapshot, dba_hist_system_event
<...>
Event_name   waits_1 duration_1 waits_2 duration_2
gc buffer busy       1378135         1,9 1    6189        0,4
gc cr grant 2-way     5830652  0,2         97805       0,2
gc current block 2-way 2783997 0,3     365330     0,3
gc cr multi block request 1339350 0,2    1850738   0,2
gc cr block 2-way 39967                0,3   948740    0,3
gc current grant busy 298901        0,7      20273     0,3
gc current grant 2-way 1027710    0,2    1164        0,3  


Example 1. Average and Skew.
So four executions took 278 seconds (69,5*4).
How long did each take?
69,5 in average. 




Example 2. TOPNSQL.
 
A query is the same (9g7dr4k9qj1r1). Usage pattern is the same.  


“Heavy” executions:
DD EXECS ELA_PER_EXEC GETS_PER_EXEC
-------------------- ---------- ------------ -------------
28.12.2011 14 1053 379809 21161
28.12.2011 13 12336 9043237 21148
28.12.2011 12 942 379609 21296
28.12.2011 11 1097 378855 21060
28.12.2011 10 1505 382856 21168
28.12.2011 09 6102 7229608 21604


“Light” executions:
-------------------- ---------- ------------ -------------
29.12.2011 14 5860 2003 89
29.12.2011 13 6723 2016 91
29.12.2011 10 6435 2008 86
There were no executions in some snaps.
ADD_COLORED_SQL/REMOVE_COLORED_SQL to mark sql_id. 


A kind of conclusion about AWR.
- It’s very useful tool for analyzing performance in past periods.
- Oracle11g AWR is better (more comprehensive and more convenient) than 10g.
- Selecting from AWR views gives even more powerful and flexible reports.
 

But…
AWR requires addition licensing.
It gives you only aggregated information.
Some data can be ‘omitted’. 


- Automatic Workload Repository (AWR)
- Basic ideas
- dba_hist% views
- 11.2 enhancements
- AWR use cases & limitations


- Active Session History (ASH)
- Basic ideas
- 11.2 enhancements
- Typical use cases 

Active Session History (ASH) is a new tool introduced in Oracle10g.
 
Once a second it makes snapshot of active sessions (on CPU or in non-Idle events).
Something close to…

select ... from gv$session
where wait_class<>'Idle';

ASH data is not complete but..
It is not aggregated (like other AWR data). 


dba_hist_active_sess_history
10.2.0.5 – 52 columns; 11.2.0.2 – 97 columns.
 
Some of the columns:
 
SNAP_ID, DBID, INSTANCE_NUMBER, SAMPLE_ID, SAMPLE_TIME – sample identification;
 
SESSION_ID, SESSION_SERIAL#, USER_ID, PROGRAM, MODULE, MACHINE, PORT – session 
identification;
 
SQL_ID, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, SQL_EXEC_ID – SQL information;
 
EVENT, P1, P2, P3, TIME_WAITED – wait event information;
 
BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#, BLOCKING_INST_ID – locking information;
 
TM_DELTA_CPU_TIME, TM_DELTA_DB_TIME, DELTA_READ_IO_REQUESTS. DELTA_WRITE_IO_REQUESTS, DELTA_READ_IO_BYTES, DELTA_WRITE_IO_BYTES, DELTA_INTERCONNECT_IO_BYTES – session activity info. 

 






ASH in memory – fixed memory consumption, variable time range.
SQL> select inst_id, bytes/(1024*1024) MB from gv$sgastat where name='ASH buffers';
 
INST_ID MB
---------- ----------
1 16
3 16
2 16
 

SQL> select inst_id, (max(sample_time)-min(sample_time)) ASH_window
from gv$active_session_history group by inst_id;
 
INST_ID ASH_WINDOW
---------- -------------------------------
1 +000000000 07:18:12.038
2 +000000000 07:01:26.268
3 +000000000 10:15:25.558
 
Samples for every second

ASH on disk – fixed time range controlled by AWR retention.
 
SQL> select instance_number, (max(sample_time)-min(sample_time)) ASH_window
from dba_hist_active_sess_history group by instance_number;

INSTANCE_NUMBER ASH_WINDOW
--------------- ----------------------------------
1 +000000032 04:59:50.479
2 +000000032 04:59:45.743
3 +000000032 04:59:40.818

SQL> select 'ASH in memory' what, count(distinct sample_id) samples_per_hour from v$active_session_history where sample_time>=trunc(sysdate) and sample_time<trunc(sysdate)+1/24 union all select 'ASH on disk', count(distinct sample_id) sample_id from dba_hist_active_sess_history where instance_number=1 and sample_time>=trunc(sysdate) and sample_time<trunc(sysdate)+1/24;


WHAT SAMPLES_PER_HOUR
------------- ----------------
ASH in memory 3592
ASH on disk 359
1/10 of samples from ‘ASH in memory’.

Each sample in ASH data represents a second of database activity.
Theoretically:
- Count of rows for one sample is DB Time.
- Count of rows with session_state=‘ON CPU’ is DB CPU.
In my experience the accuracy of the calculation hardly ever better than 15-20% on production systems with significant load.


Example 1. AWR report:
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 118264 27-Dec-11 13:30:23 1,410 49.8
Elapsed: 15.06 (mins)
DB Time: 2,438.18 (mins)
<…>
 
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
latch: cache buffers chains 409,488 51,930 127 35.5 Concurrenc
CPU time 6,341 4.3
db file sequential read 164,900 1,113 7 0.8 User I/O
<…>
 
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
139,282 4,307 11,574 12.0 95.2 9g7dr4k9qj1r1
140 000 (ela for the query) / 900 (15 minutes) =
= 155 concurrently executed queries.


Example 1. ASH content (1/3):
 
SQL> select to_char(trunc(sample_time,'mi'),'hh24:mi') dd, count(*) c, count(distinct h.session_id||':'||h.session_serial#) s from dba_hist_active_sess_history h where h.instance_number=1 and h.snap_id between 118265 and 118265 and h.sample_time>=to_date('27.12.2011 13:30','dd.mm.yyyy hh24:mi') and h.sample_time <to_date('27.12.2011 13:45','dd.mm.yyyy hh24:mi') group by trunc(sample_time,'mi') order by trunc(sample_time,'mi');


DD C S
--------------- ---------- ----------
13:30 33 24
13:31 3134 1044
13:32 4846 914
13:33 55 48
13:34 37 24
13:35 28 17
13:36 25 17
13:37 239 230
13:38 5242 1016
13:39 1188 873
13:40 28 26
13:41 19 19
<…>


Example 1. ASH content (2/3):
SQL> select sample_time, count(*) c, sum(case when sql_id='9g7dr4k9qj1r1' then 1 else 0 end) s from dba_hist_active_sess_history h where h.instance_number=1 and h.snap_id between 118265 and 118265 and h.sample_time>=to_date('27.12.2011 13:30','dd.mm.yyyy hh24:mi') and h.sample_time <to_date('27.12.2011 13:45','dd.mm.yyyy hh24:mi') group by sample_time order by sample_time;


Example 1. ASH content (3/3):
 
SAMPLE_TIME C S
------------------------------ ---------- ----------
27-DEC-11 01.30.05.551 PM 6 0
27-DEC-11 01.30.15.591 PM 5 0
<…>
27-DEC-11 01.31.15.850 PM 4 0
27-DEC-11 01.31.25.959 PM 410 395
27-DEC-11 01.31.36.069 PM 980 963
27-DEC-11 01.31.46.652 PM 887 857
27-DEC-11 01.31.56.951 PM 848 822
27-DEC-11 01.32.06.994 PM 862 844
27-DEC-11 01.32.17.056 PM 852 831
27-DEC-11 01.32.27.118 PM 869 842
27-DEC-11 01.32.37.165 PM 855 840
27-DEC-11 01.32.47.225 PM 800 776
27-DEC-11 01.32.57.516 PM 608 570
27-DEC-11 01.33.07.566 PM 25 10
27-DEC-11 01.33.17.613 PM 8 1
27-DEC-11 01.33.27.658 PM 5 1
27-DEC-11 01.33.37.690 PM 4 0
<…> 



Example 2. sql_exec_id (Oracle 11g) (1/2).
 
SQL_EXEC_ID is counter of SQL executions on a given instance.
(See Tanel Poder blog entry.)
 
SQL> declare j number;
begin
dbms_job.submit(job=>j,
what=>'declare p number; begin for i in 1..3 loop select count(*) into p from positions e_test where mrp=1; end loop; end;',
instance=>1);
end;
/

PL/SQL procedure successfully completed.

SQL> declare j number;
begin
dbms_job.submit(job=>j,
what=>'declare p number; begin for i in 1..2 loop select count(*) into p from positions e_test where mrp=1; end loop; end;',
instance=>2);
end;
/ 


Example 2. sql_exec_id (Oracle 11g) (2/2).

SQL> select sql_id, sql_text from v$sql where sql_id='2dxypmkd8p3qk';
-------------
2dxypmkd8p3qk
SELECT COUNT(*) FROM POSITIONS E_TEST WHERE MRP=1

SQL> select inst_id, sql_exec_id, to_char(sql_exec_id,'XXXXXXXX') sql_exec_id_h, count(*) s
from gv$active_session_history
where sql_id='2dxypmkd8p3qk'
group by inst_id, sql_exec_id
order by 1,2;

INST_ID SQL_EXEC_ID SQL_EXEC_ S
---------- ----------- --------- ----------
1 16777216 1000000 4
1 16777217 1000001 4
1 16777218 1000002 5
2 33554432 2000000 5
2 33554433 2000001 4 

ASH is great tool for:
- Investigate load spikes within an AWR snapshot.
- Analyze ‘long’ queries executions (especially in 11g).
- Investigate locking/blocking issues (if locking time not too short).

Limitations:
- The information is not complete. ASH on disk is more incomplete.
- DB activity spikes makes ‘retention window’ of ASH in memory shorter (sometimes dramatically).
- Tends to sample ‘long’ events.
- Licensing as part of AWR. 



References 
Oracle® Database Documentation, 11g Release 2 (11.2)
Oracle® Database Performance Tuning Guide
5 Automatic Performance Statistics
http://docs.oracle.com/cd/E11882_01/server.112/e16638/autostat.htm

Oracle® Database PL/SQL Packages and Types Reference
161 DBMS_WORKLOAD_REPOSITORY
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_workload_repos.htm

Mike Ault. AWR Report Detailed Analysis.
http://www.nyoug.org/Presentations/2008/Sep/Ault_AWR.pdf

John Beresniewicz. Practical Active Session History
Tanel Poder. What the heck is the SQL Execution ID – SQL_EXEC_ID?
http://blog.tanelpoder.com/2011/10/24/what-the-heck-is-the-sql-execution-id-sql_exec_id/

How to choose a correct index on table and the order of columns for the index?

How to choose a correct index on table and the order of columns for the index?

Creating correct index or choosing the order of columns for an index is a big challenge.

In Oracle, when a query is slow, first thing we check is if the query is using correct index or if any full table scan(FTS).
Please note that Full table scan is not a bad thing.Sometime ,Full table scan(FTS) perform better than index can when the no blocks it has to scan
through is lesser.fo ex ,when the no of data blocks( all blocks in a table) read for full table scan is lesser than the no of blocks read fron index + no of data blocks access
(Ref:How to find out the logical IO?)

A developer should know the below before writing a query

     " Logical IO is the scientific unit to measure oracle query performance. "
      " How many blocks the query supposed to read to satisfy the given criteria based on the current data or trend"
  
      " Creating index will create additional overhead to insert and more overhead to delete and update statements"
      " For the frequently used queries, based on the where critieria
         if the No of blocks read by the index access + data blocks >=  total no of blocks, then index creation may  give adverse effect"

Choosing columns for index creation

1.Consider indexing keys that are used frequently in WHERE clauses.
2. Consider indexing keys that are used frequently to join tables in SQL statements.
3.Choose index keys that have high selectivity. ( Ref: How to determine the Selectivity ? )
4. In a composite index, choose the order of column with more selectivity as leading columns.....
5.Do not use standard B-tree indexes on keys or expressions with few distinct values.

Such keys or expressions usually have poor selectivity and therefore do not optimize performance unless the frequently selected key values appear
less frequently than the other key values. You can use bitmap indexes effectively in such cases, unless the index is modified frequently,
  as in a high concurrency OLTP application.

6.Do not index columns that are modified frequently.

UPDATE statements that modify indexed columns and
INSERT and DELETE statements that modify indexed tables take longer than if there were no index.
Such SQL statements must modify data in indexes as well as data in tables. They also generate additional undo and redo.

7.Do not index keys that appear only in WHERE clauses with functions or operators.

  A WHERE clause that uses a function, other than MIN or MAX, or an operator with an indexed key does not make available the access path
  that uses the index except with function-based indexes.

8.Consider indexing foreign keys of referential integrity constraints in cases in which a large number of concurrent INSERT,
UPDATE, and DELETE statements access the parent and child tables. Such an index allows UPDATEs and DELETEs on the parent table without share locking the child table.
   ( This will avoid most of the deadlocks)

9. When choosing to index a key, consider whether the performance gain for queries is worth the performance loss for INSERTs,
  UPDATEs, and DELETEs and the use of the space required to store the index. You might want to experiment by
  comparing the processing times of the SQL statements with and without indexes. You can measure processing time with the SQL trace facility.

In 11g, you can mak ethe index invisible to test sql without indexes
    alter index owner.indexname invisible;
     - test query
    alter index owner.indexname visible;
 
10

  * Primary Keys - these are given an index automatically (unless you specify a suitable existing index for Oracle to use)
  * Unique Keys - these are given an index automatically (ditto)
  * Foreign Keys - these are not automatically indexed, but you should add one to avoid performance issues when the constraints are checked

11.Make use of Index Tuning using the SQLAccess Advisor to get some advices for additional indexes
     Seek OEM administrator to get this acccess in dev/uat if you want to run this advory though gui interface.

How to find out logical IOs?
  There are various methods to find out the logical IOs like tracing,awr report/autotrace.Let us see one of the easiest method using auto trace

case 1 How many logical ios for a full table scan
SQL> set linesize 200
SQL> set autotrace traceonly
SQL> select object_name from PERFORMANCE;
15972 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1331860161
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             | 15972 |   296K|    63   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| PERFORMANCE | 15972 |   296K|    63   (2)| 00:00:01 |
---------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1263  consistent gets
          0  physical reads
          0  redo size
     507677  bytes sent via SQL*Net to client
      12227  bytes received via SQL*Net from client
       1066  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      15972  rows processed

Case 2: Find out how many logical ios for an index scan

SQL> select object_name from PERFORMANCE where object_name='HELLO';
15972 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2578635553
-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          | 15972 | 95832 |    66   (0)| 00:00:01 |
|   1 |  INDEX RANGE SCAN| PERF_IDX | 15972 | 95832 |    66   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1132  consistent gets
          0  physical reads
          0  redo size
     275151  bytes sent via SQL*Net to client
      12227  bytes received via SQL*Net from client
       1066  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      15972  rows processed

the consistent reads ( logical IO ) for index access is 1132 where as for FTS it is 1263.( This is just an example)
 
How to determine the Selectivity ?

The selectivity of an index is the percentage of rows in a table having the same value for the indexed key. An index's selectivity is optimal if few rows have the same value.
Indexing low selectivity columns can be helpful if the data distribution is skewed so that one or two values occur much less often than other values.

Index Selectivity :

B*TREE Indexes improve the performance of queries that select a small percentage of rows from a table.

The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index.
The ideal selectivity is 1. Such a selectivity can be reached only by unique indexes on NOT NULL columns.

Example with good Selectivity

A table having 100'000 records and one of its indexed column has 88000 distinct values, then the selectivity of this index is 88'000 / 10'0000 = 0.88.
Oracle implicitly creates indexes on the columns of all unique and primary keys that you define with integrity constraints.
These indexes are the most selective and the most effective in optimizing performance. The selectivity of an index is the percentage of rows
in a table having the same value for the indexed column. An index's selectivity is good if few rows have the same value.

Example with bad Selectivity
lf an index on a table of 100'000 records had only 500 distinct values, then the index's selectivity is 500 / 100'000 = 0.005 and
in this case a query which uses the limitation of such an index will retum 100'000 / 500 = 200 records for each distinct value.
It is evident that a full table scan is more efficient as using such an index where much more I/O is needed to scan repeatedly the index and the table.

How to Measure Index Selectivity ?

Manually measure index selectivity
The ratio of the number of distinct values to the total number of rows is the selectivity of the columns.
This method is useful to estimate the selectivity of an index before creating it.
select count (distinct job) "Distinct Values" from emp;
Distinct Values
---------------
              5
select count(*) "Total Number Rows" from emp;
Total Number Rows
-----------------
               14
Selectivity = Distinct Values / Total Number Rows
            = 5 / 14
            = 0.35

Automatically measure index selectivity
We can determine the selectivity of an index by dividing the number of distinct indexed values by the number of rows in the table.
create index idx_emp_job on emp(job);
analyze table emp compute statistics;
select distinct_keys from user_indexes
where table_name = 'EMP'
   and index_name = 'IDX_EMP_JOB';
DISTINCT_KEYS
-------------
            5
select num_rows from user_tables
where table_name = 'EMP';
NUM_ROWS
---------
       14
Selectivity = DISTINCT_KEYS / NUM_ROWS = 0.35
Selectivity of each individual Column

Assuming that the table has been analyzed it is also possible to query USER_TAB_COLUMNS to investigate the selectivity of each column individually.
select column_name, num_distinct
from user_tab_columns
where table_name = 'EMP';
COLUMN_NAME                     NUM_DISTINCT
------------------------------ ------------
EMPNO                                     14
ENAME                                     14
JOB                                        5
MGR                                        2
HIREDATE                                  13
SAL                                       12
COMM                                       4
DEPTNO                                     3

How to choose Composite Indexes ?
A composite index contains more than one key column. Composite indexes can provide additional advantages over single column indexes.
Better Selectivity  Sometimes two or more columns, each with poor selectivity, can be combined to form a composite index with good selectivity.
Oracle can return these values from the index without accessing the table. However in this case, it's better to use an IOT (Index Only Table).

An SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index.
A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index.

The performance of any Oracle database heavily relies on query execution(s). The reasons for any SQL statement’s execution plan changes could include a variety of actions, like gathering optimizer statistics (table, index, schema etc.)  changing optimizer parameters, schema definitions, adding indexes etc.  As experienced Oracle DBAs, we should be aware of the fact that the above mentioned actions meant to improve SQL performance will not always guarantee positive results.
So in this case, many of us would try to freeze execution plans (Stored Outlines) or lock the optimizer statistics. However, doing so prevents such environments/databases of taking advantage of new optimizer functionality or access paths, which would improve the SQL statements performance. That is where SQL Plan Management comes in very handy…
SQL Plan Management (SPM) provides a framework for completely transparent controlled execution plan evolution. With SPM the optimizer automatically manages execution plans and ensures only known or verified plans are used. When a new plan is found for a SQL statement it will not be used until it has been verified by the database to have comparable or better performance than the current plan.
———————-
Next, I will explain the steps for forcing a bad query to use a better execution plan by loading SQL Plans into SPM using AWR.

Identifying the Slow Query

We have the following scenario:
– Oracle 11.2.0.3 version single instance database
– Performance issue caused by the following bad query:


with initial explain plan :


As shown in the Plan output, a full table scan was used, resulting in excessive IO for this query.  It seemed this query needed an index to reduce the IO. Therefore I have added two indexes on ‘status’ and ‘prevobjectid’ columns for the EMPLOYEES table, gathered table statistics and then checked again the explain plan. We will see now that due to index creation the DISPLAY_AWR program shows a newly generated explain plan with improved cost using an index range scan versus the full table scan used by the initial plan (Plan hash value: 2172072736).



Now we have obtained a new, better execution plan in AWR for the SQL statement, but our next question would be, “How can we make sure it will be the only plan picked by the Cost Based Optimizer for future executions”?
The answer:  “Create a SQL Tuning Set for the SQL, then create a new SQL Baseline from the STS so the Optimize will choose the preferred Execution Plan”.
Each time a SQL statement is compiled, the optimizer first uses a cost-based search method to build a best-cost plan, then tries to find a matching plan in the SQL plan baseline.  If a match is found, the optimizer will proceed using this plan. Otherwise, it evaluates the cost of each accepted plan in the SQL plan baseline and selects the plan with the lowest cost.
Here are the steps for loading SQL Plans into SPM using AWR by implementing SQL Baselines for the bad query.

Step 1: Set up a SQL Baseline using known-good plan, sourced from AWR snapshots.

To do so, SQL Plan Management must be active and the easiest condition to checking optimizer_use_sql_plan_baselines which needs to be TRUE.

 

Step 2: Create SQL Tuning Set (STS).

A SQL tuning set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking. You can load SQL statements into a SQL tuning set from different SQL sources, such as AWR, the shared SQL area, or customized SQL provided by the user. An STS includes:
–          A set of SQL statements
–          Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment
–          Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type
–          Associated execution plans and row source statistics for each SQL statement (optional)
The concept of SQL tuning sets, along with the DBMS_SQLTUNE package to manipulate them, was introduced in Oracle 10g as part of the Automatic SQL Tuning functionality. Oracle 11g makes further use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database change can be as major or minor as you like, such as:

• Database, operating system, or hardware upgrades.
• Database, operating system, or hardware configuration changes.
• Database initialization parameter changes.
• Schema changes, such as adding indexes or materialized views.
• Refreshing optimizer statistics.
• Creating or changing SQL profiles.

Now I create a SQL Tuning Set based on the slow query with a SQL_ID of 9kt723m2u5vna.

Step 3: Populate STS from AWR.

Now I will locate the AWR snapshots required to populate the STS, and load the STS based on those snapshot ID’s and the SQL_ID.



Step 4: List out SQL Tuning Set contents.

Now I can query the STS to verify it contains the expected data.



Step 5: List out SQL Tuning Set contents

Though I have created and verified the STS, the Baseline has not yet been created.



Step 6: Load desired plan from STS as SQL Plan Baseline
Now I will load the known good plan that uses the newly created index into the Baseline.


Step 7: List out the Baselines again.

Now verify the Baseline contains the desired plan.



Step 8. Flush the current bad SQL Plan.

After loading the baseline, the current cursor must be flushed from the cache to make sure the new plan will be used on next execution of the sql_id 9kt723m2u5vna

Conclusion

As this blog post demonstrates, SQL Plan Management (SPM) allows database users to maintain stable yet optimal performance for a set of SQL statements and baselines seem to be a definite step in the right direction. Baselines can be captured from multiple sources, SPM allowing new plans to be used if they perform better than the baseline fact that could improve the overall application/system functionality.


comment :

Instead of creating SQL tuning set, we can directly create baseline from cursor cache using dbms_spm.load_plans_from_cursor_cache
We can also use DBMS_SPM.evolve_sql_plan_baseline to verify new baseline and finally dbms_spm.alter_sql_plan_baseline to disable old baseline

Sql tuning sets 10g &11g

Sql Tuning Sets

Sql tuning sets is a basic building block for many oracle tuning features, built into the oracle 10g and 11g databases. A sql tuning set captures one or more sql statements issued by database users and their corresponding execution statistics (think, some of the statistics available in v$sqlarea),execution plans and row source statistics (think, some of the information from v$sql_plan etc), and execution context (think, bind varaibles etc…).

Sql tuning sets are used as inputs for the “Sql tuning advisor”. Sql tuning sets can be moved between databases. This comes in handy when you want to tune sql statements captured from a production environment in a development environment (So that you do not do development activities on production). sql tuning sets are used by the ‘sql performance analyzer’ component of ‘Real Application Testing’ and also the ‘sql plan stability’ feature.

Sql tuning sets can be created from the following four sources
– The database cursor cache (sqlarea)
– AWR reports
– An existing sql tuning set
– A sql trace file (generated using the 10046 database event)

Typically You would perform the following operations on a sql tuning set.
– Create a sql tuning set
– Add sql statements to the sql tuning set
– List the contents of a sql tuning set
– Modify a sql tuning set
– Transport a sql tuning set
– Drop a sql tuning set

 

Creating a sql tuning set

You can use the dbms_sqltune.create_sqlset procedure to create a new sql tuning set.
It takes 3 arguments.

DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name IN VARCHAR2,
description IN VARCHAR2 := NULL
sqlset_owner IN VARCHAR2 := NULL);

typically you can just specify the sqlset_name and a description, that lets you know why you created the sql tuning set in the first place.
Now i went ahead and used swingbench to run some transactions on the database for 10 minutes. This generates numerous sql statements in the shared pool.

 

Adding sql statements to a sql tuning set

Once we have run a load on the database, there will be sql statements in the shared pool that we want to analyze.
You can use the dbms_sqltune.load_sqlset procedure to popluate the sql tuning set with sql statements.

DBMS_SQLTUNE.LOAD_SQLSET (
sqlset_name IN VARCHAR2,
populate_cursor IN sqlset_cursor,
load_option IN VARCHAR2 := ‘INSERT’,
update_option IN VARCHAR2 := ‘REPLACE’,
update_condition IN VARCHAR2 := NULL,
update_attributes IN VARCHAR2 := NULL,
ignore_null IN BOOLEAN := TRUE,
commit_rows IN POSITIVE := NULL,
sqlset_owner IN VARCHAR2 := NULL);

For example you might want to create a sql tuning set with sql tuning statements that executed more than 5 times and had buffer_gets greater than 100000
So as you can see we used dbms_sqltune.select_cursor_cache to find the high resource using sql statements from the shared pool. So you have the following four different methods to get sql statements and populate the sql tuning set.
dbms_sqltune.select_cursor_cache
dbms_sqltune.select_sql_trace
dbms_sqltune.select_sql_set
dbms_sqltune.select_sql_workload_repository
All the above procedures return a table of object type sqlset_row

 

List the contents of a sql tuning set

You can list the statements that have been included in the sql tuning set by invoking the dbms_sqltune.select_sqlset procedure

 

Modify sql tuning sets

You can delete statements that have been included in the sql tuning set by invoking dbms_sqltune.delete_sqlset

You can update attributes of the sql tuning set by invoking dbms_sqltune.update_sqlset

 

Transporting a sql tuning set

If you need to move the sql tuning set from one database to another you can follow the procedure below
First create the sql tuning set staging table (This will be table where we temporarily store the information regarding the sql tuning set and then use exp/imp to move it to a different database).

dbms_sqltune.create_stgtab_sqlset
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET( table_name => ‘my_first_stage_tab’ );
END;
/

Copy the sql tuning set into the staging table

dbms_sqltune.pack_stgtab_sqlset
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => ‘my_first_sqlset’,
staging_table_name => ‘my_first_stage_tab’);
END;
/

Now you can exp/imp this staging table from production to dev or test.
Once the staging table is in the destination database, you have to move the sql tuning set from the staging table into the data dictionary.

dbms_sqltune.unpack_stgtab_sqlset
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => ‘%’,
replace => TRUE,
staging_table_name => ‘my_first_stage_tab’);
END;
/

You can also use enterprise manager to perform all the above operations.