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'

Installing Enterprise manager on Home PC

Hi All,

This post covers my efforts to install enterprise manager on my home machine. This is part of sql tuning exercise. In professional life you will not get licence for this Enterprise manager as only DBA will have access and its licence is expensive ( that what i got from my manager)

Developers only use the V$ views to solve issues. However even if you have access to v$sql_monitor which is again a special access . The graphical interface is much easier to solve issues. By getting some hands on this Enterprise manager you can request your DBA for graphical outputs so that it is easy to solve issues like ETL load taking long time. Report running for minutes .

So by default this should be installed if you installed oracle correctly but it was not the case with me i found below link help ful

http://blog.mclaughlinsoftware.com/oracle-architecture-configuration/changing-windows-hostname-and-oracle-enterprise-manager/

Basic point to be remembered is
1) try emctl start dbconsole
If you get error like the dbconsole is not configured

2) Try dropping the db console repository using

 emca -deconfig dbcontrol db -repos drop

3) then create the repository using 

emca -config dbcontrol db -repos create

https://localhost:1158/em/console