ASH FROM AWR

SQL QUERY PERFORMANCE  TUNING STEPS FROM AWR

Some more queries

1.Find sql & stats 

2. SQL details 

3.SQL rowsource

4.Index Structure

5.Bad stats?

6.Replicate take 1

7.bind data type

8.Replicate (2nd try)

9.Visualizing the
10.improvement

11. Free script collections 

 1.Find sql & stats 

  

 Find slow SQL (ASH) With given Time Frame

 
SQL> SELECT sql_id , COUNT(*) as sample_count FROM v$active_session_history WHERE sample_time BETWEEN TO_DATE('2011-06-29 07:57', 'yyyy-mm-dd hh24:mi') AND TO_DATE('2011-06-30 04:30', 'yyyy-mm-dd hh24:mi') AND user_id = 61 AND program like 'pmdtm@%' GROUP BY sql_id ORDER BY 2 DESC ;
 

no rows selected

SQL> SELECT min(sample_time) FROM v$active_session_history;
MIN(SAMPLE_TIME)
----------------------------------------
2011-07-01     19:14:48
1 row selected. 

Find slow SQL id FROM (ASH in AWR)

 
SELECT sql_id, COUNT(*) as sample_count FROM v$active_session_history

where sample_time between to_date('2011-06-29 07:57'  'yyyy-mm-dd hh24:mi')

AND TO_DATE('2011-06-30 04:30', 'yyyy-mm-dd hh24:mi') 
AND user_id = 61
AND program like 'pmdtm@%‘

GROUP BY 2 SQL_ID

ORDER BY 2 DESC
;

SQL_ID SAMPLE_COUNT
------------- ------------
dkwucgaxxhm2z 7362
grksgbxxuacaf     6041 

READABLE FORMAT 

SQL>SELECT sql_id, sql_type
2 , round(sample_cnt_sql/sample_cnt_total*100) as sample_pct
3 , round(sample_cnt_sql /6/60,1) hours
4 FROM(SELECT DISTINCT sql_id
5 , aa.name AS sql_type
6 , COUNT(*) over (PARTITION BY sql_id) AS sample_cnt_sql
7 , COUNT(*) over () AS sample_cnt_total
8 FROM dba_hist_active_sess_history ash
9 JOIN dba_hist_snapshot s USING (dbid, INSTANCE_NUMBER, snap_id)
10 JOIN audit_actions aa on (ash.sql_opcode = aa.action)
11 WHERE begin_interval_time BETWEEN

--- may be --- between 132042 and 132124… snip …
18 ORDER BY 3 desc) WHERE rownum <= 3;

SQL_ID                         SQL_TYPE               SAMPLE_PCT             HOURS
------------- -------- ---------- -----
dkwucgaxxhm2z             SELECT                                 25                    20.4
grksgbxxuacaf                 SELECT                                 21                   16.8
gh6w2fm3avn3j                SELECT                              12                      10.1

3 rows selected.

AWR SQL stats OF 'dkwucgaxxhm2z' between 132042 and 132124  to get hash value

SQL>

SELECT PLAN_HASH_VALUE,

 , sum(EXECUTIONS_DELTA) as executions

 , sum(ROWS_PROCESSED_DELTA) as rows_processed

 , sum(ELAPSED_TIME_DELTA) as elapsed_time

 , sum(CPU_TIME_DELTA) as cpu_time, sum(IOWAIT_DELTA) as iowait_time

 , sum(DIRECT_WRITES_DELTA) as direct_writes

 , sum(BUFFER_GETS_DELTA) as LIO, sum(DISK_READS_DELTA) as PIO

 FROM dba_hist_sqlstat

 WHERE snap_id between 132042 and 132124

 AND sql_id = 'dkwucgaxxhm2z'

 GROUP BY sql_id, PLAN_HASH_VALUE, OPTIMIZER_ENV_HASH_VALUE;

O/P

 AWR SQL stats OF  dkwucgaxxhm2z  - readable to get hash value 

SELECT PLAN_HASH_VALUE, executions , round(elapsed_time /1000000/60/60,1) as elapsed_hours

 , rows_processed

 , round(cpu_time /elapsed_time * 100) as cpu_time_pct

 , round(iowait_time /elapsed_time * 100) as iowait_pct

 , LIO, PIO, direct_writes

, round(iowait_time /PIO/1000,1) as mili_sec_PIO

 FROM(SELECT PLAN_HASH_VALUE

 , sum(EXECUTIONS_DELTA) as executions

 , sum(ROWS_PROCESSED_DELTA) as rows_processed

 , sum(ELAPSED_TIME_DELTA) as elapsed_time

 , sum(CPU_TIME_DELTA) as cpu_time, sum(IOWAIT_DELTA) as iowait_time

 , sum(DIRECT_WRITES_DELTA) as direct_writes

 , sum(BUFFER_GETS_DELTA) as LIO, sum(DISK_READS_DELTA) as PIO

 FROM dba_hist_sqlstat

 WHERE snap_id between 132042 and 132124

 AND sql_id = 'dkwucgaxxhm2z'

 GROUP BY sql_id, PLAN_HASH_VALUE);

Find slow SQL (ASH in AWR)

1, SELECT sql_id, sql_type 

2 , round(sample_cnt_sql/sample_cnt_total*100) as sample_pct 

3 , round(sample_cnt_sql /6/60,1) hours 

4 FROM(SELECT DISTINCT sql_id 

5 , aa.name AS sql_type 

6 , COUNT(*) over (PARTITION BY sql_id) AS sample_cnt_sql 

7 , COUNT(*) over () AS sample_cnt_total 

8 FROM dba_hist_active_sess_history ash 

9 JOIN dba_hist_snapshot s USING (dbid, INSTANCE_NUMBER, snap_id) 

10 JOIN audit_actions aa on (ash.sql_opcode = aa.action) 

11 WHERE begin_interval_time BETWEEN… snip … 

18 ORDER BY 3 desc) 

19 WHERE rownum <= 3;

sql_id      SQL_TYPE          SAMPLE_PCT        HOURS
------- -------- ---------- -----
...axxhm2z      SELECT                 25                        20.4
...xxuacaf        SELECT                21                        16.8
...m3avn3j       SELECT                12                        10.1

AWR SQL plan of sqlid and hashvalue

  
SQL>select * from table(dbms_xplan.display_awr('dkwucgaxxhm2z', 1700726234, null, 'ALL LAST'));

ASH row source data (plan line id) between snap plan id's to to get spent minutes 

SELECT sql_id, sql_plan_line_id

  , round(sample_cnt_plan_line/sample_cnt_total*100) AS sql_plan_line_pct 

 , round(sample_cnt_plan_line/6) minutes 

 FROM( 

 SELECT DISTINCT sql_id, sql_plan_line_id

 , COUNT(*) over (PARTITION BY sql_plan_line_id) AS sample_cnt_plan_line 

 , COUNT(*) over () AS sample_cnt_total 

 FROM dba_hist_active_sess_history ash 

 JOIN dba_hist_snapshot s USING (dbid, INSTANCE_NUMBER, snap_id) 

 WHERE sql_id = 'dkwucgaxxhm2z' and begin_interval_time 

BETWEEN… snip 

---between 132042 and 132124

SQL_ID     SQL_PLAN_LINE_ID                 SQL_PLAN_LINE_PCT        MINUTES
-------                 ----------------                        -----------------                      ----------
axxhm2z           34                                                           48                              593
axxhm2z           35                                                           27                              331
axxhm2z           25                                                           17                              213

ASH rowsource (plan line id + event) 

SELECT sql_id, sql_plan_line_id, event 

 , round(sample_cnt_plan_line_event/sample_cnt_total*100) as plan_line_event_pct 

 , round(sample_cnt_plan_line_event/6) minutes 

 FROM (SELECT DISTINCT sql_id 

 , sql_plan_line_id, nvl(event, 'CPU') as event 

, COUNT(*) over (PARTITION BY sql_plan_line_id,event)AS sample_cnt_plan_line_event 

, COUNT(*) over () AS sample_cnt_total 

 FROM dba_hist_active_sess_history ash 

 JOIN dba_hist_snapshot s USING (dbid, INSTANCE_NUMBER, snap_id) 

WHERE sql_id = 'dkwucgaxxhm2z' and begin_interval_time 

BETWEEN …… snip 

 ----may be -- between 132042 and 132124

SQL_ID     SQL_PLAN_LINE_ID                 EVENT          PLAN_LINE_EVENT_PCT    MINUTES
-----                ----------------               -------------------    ------------------------              ----------
......xhm2z                34                    db file sequential read                 48                                        591
......xhm2z                35                    db file sequential read                 27                                        328
......xhm2z                25                    db file sequential read                 17                                        212

AWR SQL stats 

SQL> 

SELECT PLAN_HASH_VALUE, executions 

 , round(elapsed_time /1000000/60/60,1) as elapsed_hours 

 , rows_processed 

 , round(cpu_time /elapsed_time * 100) as cpu_time_pct 

 , round(iowait_time /elapsed_time * 100) as iowait_pct 

 , LIO, PIO, direct_writes 

 , round(iowait_time /PIO/1000,1) as mili_sec_PIO 

 FROM(SELECT PLAN_HASH_VALUE 

 , sum(EXECUTIONS_DELTA) as executions 

 , sum(ROWS_PROCESSED_DELTA) as rows_processed 

 , sum(ELAPSED_TIME_DELTA) as elapsed_time 

 , sum(CPU_TIME_DELTA) as cpu_time, sum(IOWAIT_DELTA) as iowait_time 

 , sum(DIRECT_WRITES_DELTA) as direct_writes 

 , sum(BUFFER_GETS_DELTA) as LIO, sum(DISK_READS_DELTA) as PIO 

 FROM dba_hist_sqlstat 

 WHERE snap_id between 132042 and 132124 

 AND sql_id = 'dkwucgaxxhm2z' 

 GROUP BY sql_id, PLAN_HASH_VALUE); 

  

AWR SQL Optimizer Env values :

select * from table(dbms_xplan.display_awr('dkwucgaxxhm2z', 1700726234, null, 'LAST +OUTLINE'));

BEGIN_OUTLINE_DATA 

IGNORE_OPTIM_EMBEDDED_HINTS 

OPTIMIZER_FEATURES_ENABLE(11.1.0.7) 

DB_VERSION(11.1.0.7) 

OPT_PARAM(_b_tree_bitmap_plans false) 

OPT_PARAM(_optim_peek_user_binds false) 

OPT_PARAM(optimizer_index_cost_adj 10) 

ALL_ROWS OUTLINE_LEAF(@"SEL$335DD26A") MERGE(@"SEL$3")

..snip

 AWR SQL plan

New SQL Plan

select * from table(dbms_xplan.display_cursor(null, null, ALLSTATS LAST)); 

Evaluating the changes

Use Case 2 – identifying and improving the problematic sql 

1.Find sql & stats 

a.find SQL for the time period

b.find Stats for SQL dvaxm0c9tdj80

2. SQL details  -- sqlplan and sql test

3. SQL rowsource

3. Index Structure

4. Bad stats?

5. Replicate take 1

6. bind data type

7. Replicate (2nd try)

8. Visualizing the  &    9. improvement

 

10. Free script collections 

Tanel Poder
• tech.e2sn.com/oracle-scripts-and-tools

• Kerry Osborne -
• kerryosborne.oracle-guy.com/papers/my_favorite_scripts_2010.zip

• Tim Gorman
• evdbt.com/tools.htm

• Adrian Billington
• oracle-developer.net/utilities.php

• Jonathan Lewis
• jonathanlewis.wordpress.com/2009/12/18/simple-scripts/