SQL Performance issues basic Troubleshooting Stpes
SQL Performance Troubleshooting In order to troubleshoot SQL Performance , you will need to do some basic troubleshooting in systematic approach Steps : (Request apps user to provide Request id/session details and Level 12 Tkprof for problematic SQL) a)Find Top SQL from Gv$session/gv$active_session_history/dba_hist_active_sess_history(by giving Sid serial #and inst id) b) SQL Text for Top sql C) Execution or online history for the SQL also check for CPU usage LIO PIO Row executed No of Executions for SQL(time per single execution) and also check for IO latency if any d) check plan_table for plans opted by the SQL(dbms-xplain-awr/cursor) e)check Table and index Stats and data distribution on columns f) if SQL is currently running check How many rows it's processing g)check previous history for the SQL using hash_value h)find bind variable passing in the Query I)find Row source info if SQL is running 1)Get the Execution Statistics for given SQL_ID 2)Get the Ex...