Sql tuning Methodology

Hi All,

I have been working in Datawarehousing since past 7 years and Sql tuning is a task which is always there . It can be for ETL or It can be for reporting. Below is a methodology that i have developed over years. Its not yet fully developed but just writing it down will add more points as time progresses

Summary 1 - Best Way is to generate the retrieve actual execution plan by using dbms_xplan.display_cursor('sqlid') and then to go through each step slowly understanding the access method of table (full scan/indexes ) and Join methods. Table which comes handy in oracle is dba_ind_columsn to check column names corresponding to indexes 

Methodolody for Solving sql issues

1.     Run the sql with Autotrace on will give some general info. It will give the consistent gets and Physical reads. When we say we have to reduce logical reads we means we have to read overall less data you can do this by using index, partition any method

2.     Check in v$sql with below query that will give a lot of high level statistics about a query . ( Disk reads, CPU reads, Buffers) . Similar to TKPROF

3.     Check in v$sql_plan to check what the actual plan used for the query

4.     Check in V$active_session_history to get idea of what are the waits on and when the waits came

Results of Autotrace are Explain plan and Statistics. Important things to consider in Explain plan

1) The access mechanism used for fetching data. In datawarehouse mostly you will see full table scans
2) The order in which data is joined. Its better to always write the sql in order of the joins that will be performed in explain plan. Though now we use CBO and the order of writing join does not impact explain plan. The order is your understanding what the explain plan should be. In what order you want to join the table, factors impecting the order are the size of the table and whether its fact or dimension
3) The mechanism used for join. Example a nested loop , Fast full scan 

Below is Link to my article where i have shared some of my understanding of different joins which is very essential for figuring out whats going wrong in a explain plan. Basics always are important

Running Autotrace

Set  autotrace on

Then run the query as script

Checking in v$sql  (  Concentrate on highlighted columns)

select * from v$sql where sql_id = 'd6fr04z12yksa'

Select Sql_Id,

  (Elapsed_Time/1000000) Elapsed_Seconds,

  (Cpu_Time/1000000) Cpu_Seconds,

  (user_io_wait_time/1000000) user_io_wait_time_seconds,

  Fetches,

  executions,

  Buffer_gets,

  Disk_Reads,

  (Physical_Read_Bytes/1024)/1024 Physical_Read_Mb,

  rows_processed,

  SORTS,

  Direct_Writes,

  (Physical_write_bytes/1024)/1024 Physical_writes_MB,

  Concurrency_Wait_Time,

   optimizer_mode,

 ((Io_Interconnect_Bytes)/1024)/1024 Interconnect_Mb

From V$sql Where

sql_id = 'd6fr04z12yksa'

Actual plan for the sql from v$sql_plan. Below is for formated output

Select Plan_Table_Output From

TABLE(DBMS_XPLAN.DISPLAY_CURSOR('d6fr04z12yksa'));

Checking Active session history to get Step by step analysis

Select Sample_Time,Sql_Id, Event,Session_State,Time_Waited,Wait_Time,Blocking_Session,Wait_Class,Current_Obj#,

Current_File#,Current_Block#,Current_Row#,

P1,P2,((Pga_Allocated)/1024/1024) Pga_Mb,(((Temp_Space_Allocated)/1024)/1024) Temp_Space_Mb From  V$active_Session_History

Where  sql_id = 'd6fr04z12yksa'