What to look for a slow query in oracle

 

• Query execution plan: This is a list of steps the database takes when running the query. 
• Indexes: Indexes are often most useful when created on columns as part of the Join and Where clauses. Check which columns are used, check if they have indexes, and create them if they don't.
• Statistics: Ensure that the tables and indexes used by the query have up-to-date statistics. You can use the DBMS_STATS package to gather statistics.
• To get actual cardinality  SELECT /*+ GATHER_PLAN_STATISTICS */ 
• CPU usage: Use the V$SESSTAT view to determine which session is using the most CPU.  Use V$STATNAME for statistics-id description.
• Long-running queries: Use the v$session_longops view to find long-running queries.

System-wide response time: Use V$sysmetric;  v$active_session_history, v$sqlArea;

v$sysmetric_summary to measure system-wide response time.

------------------------------------------------------------------------------

To display the execution plan for a specific SQL ID: Use  the dbms_xplan.display_awr procedure.

To Display the execution plan of the last SQL statement 

SET LINESIZE 150 SET PAGESIZE 2000 

SELECT * FROM Table(DBMS_XPLAN.DISPLAY_CURSOR);

 

A db file sequential read wait event occurs when a process requests a single block of data from a datafile and is waiting for the operation to complete. This event is initiated by SQL statements that perform single-block read operations. 

A db file scattered read wait event is a wait for a physical read of multiple Oracle blocks from the disk. It's similar to a db file sequential read, but the session is waiting for a multi-block I/O read request to complete. 

A db file scattered read is usually caused by scanning the entire or a sub-range of a table, index, table partition, or index partition. It's associated with full table scans and index fast full scans (FFS) operations