MK's Oracle DBA and performance tuning blog

  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...

O racle Database Wait Events I will describe some of the wait events that cause performance problems in Oracle Databases. You can find the top 5 wait events in the AWR reports. In this article, I will tell you about the most frequently encountered Wait events in AWR reports.   racle Wait Events and Their Solutions b file sequential read Db file sequential read:  This event occurs when a user tries to perform a Physical I/O while waiting for sequential reads from the Buffer cache. This type of situation usually occurs when the data on the table is accessed by using index, not full table scan, as a result of single block reading.   If this event occurs,  possible reasons are wrong index usage, index fragmentation, excessive I/O traffic on specific disks. To Solve this problem, Query should use Right index and fragmented indexes should be defragmented with Rebuild Index operation. When you encounter this wait event, which appears very frequently in AWR and ADDM reports,...

  SQL Tune Query 1) To tune particular sql_id. DECLARE   l_sql_tune_task_id  VARCHAR2(100); BEGIN   l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (                           sql_id      => '&&1',                           scope       => DBMS_SQLTUNE.scope_comprehensive,                           time_limit  => 4000,                           task_name   => '&&1'||'_manual_...