MK's Oracle DBA and performance tuning blog

  spool check.out set timing on alter session set tracefile_identifier='NAME_OF_TRACE'; alter session set timed_statistics = true; alter session set statistics_level=all; alter session set max_dump_file_size = unlimited; alter session set events '10046 trace name context forever, level 12'; ###### Sql query or the code here Ex: select ename from emp where eid=100 ##### select 'close the cursor' from dual; alter session set events '10046 trace name context off'; spool off exit; sample output of Tkprof: Key callouts: Note : 1) session details like sid/serial # 2)Top problematic SQL and Plan hash value 3)read parse excute fetch i.e fetches /cpu /elapsed time/disk(PIO)/Query(LIO)/current/Rows processed 4) Read Row source info for the query Where it's processing more Rows Types of Joins Access paths picking What are cost and cardinality By noticing all these details we will come to know where the issue is and try to fix TKPROF: Release 10.1....

  PGA , SGA , MEMORY Advisors PGA Advisor Sql>select pga_target_for_estimate,pga_target_factor,estd_extra_bytes_rw from v$pga_target_advice; In the above figure, look at 5 th  row Value=1358954496 is the current orcldb.__pga_aggregate_target  value  (set in initorcldb.ora) So, Subtract pga_target_for_estimate - estd_extra_bytes_rw for 5 th  row , 1358954496-954314752 equals to 404639744 i.e eqals to 386 MB. So, conclusion is that adding extra 386 MB will improve performance( less I/O would be needed ). SGA Advisor Sql>select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice; DB_TIME is an overall figure for the amount of time spent taken within the database to execute SQL; Minimizing DB_TIME is the overall objective of all tuning. Look at the 3 rd  row i.e value 1952 MB , this is the value of parameter orcldb.__sga_target being set in initorcldb.ora. So,It can be seen that if the SGA is raised from its current value of 1952 MB to 3904 MB ...