2009-12-07

SQL Tuning reading plans and temp usage

What the heck is using so much temp space? Luckily we have an environment with

1. Diagnostic pack purchased

2. 11.2.0.1 version - TEMP_SPACE_ALLOCATED in v$active_session_history

3. software using dbms_application info - action and module populated

And following queries will help. First to get overview and the last queries give pointers inside execution of the queries. Now we will have something where to start fixing our temp wasters.

Trying to figure out stuff from execution plans? Here are some presentations worth reading from Tanel Poder, Christian Antognini and Kyle Hailey.


select sql_id,action,module
,min(sql_exec_start),max(sql_exec_start),max(sample_time)
,min(TEMP_SPACE_ALLOCATED) mi
,trunc(avg(TEMP_SPACE_ALLOCATED)) av
,max(TEMP_SPACE_ALLOCATED) mx
from v$active_session_history h
group by sql_id,action,module
having max(TEMP_SPACE_ALLOCATED) > 0
order by mx desc;

select *
from v$active_session_history h
order by TEMP_SPACE_ALLOCATED desc nulls last;

select sql_id,action,module
,o.object_type,o.object_name,h.sql_plan_operation,h.sql_plan_options
,min(sample_time),max(sample_time)
,min(TEMP_SPACE_ALLOCATED) mi
,trunc(avg(TEMP_SPACE_ALLOCATED)) av
,max(TEMP_SPACE_ALLOCATED) mx
from v$active_session_history h
left outer join dba_objects o on o.object_id = h.current_obj#
group by sql_id,action,module,o.object_type,o.object_name
,SQL_PLAN_OPERATION,SQL_PLAN_OPTIONS
having max(TEMP_SPACE_ALLOCATED) > 0
order by mx desc;


DBA_HIST_ACTIVE_SESS_HISTORY for searching temp users from a longer period.


select sql_opname,sql_id,module,action
,count(distinct sql_exec_start),max(temp_space_allocated) mx
from DBA_HIST_ACTIVE_SESS_HISTORY
where temp_space_allocated > 0
group by sql_opname,sql_id,module,action
order by mx desc
;

No comments:

Post a Comment

About Me

My Photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.