How to find Top Hard Parsing SQL Statements?

Posted on by Ahmed Abdel Fattah

Background:A hard parse occurs when a SQL statement has to be loaded into the shared pool. In this case, the Oracle Server has to allocate memory in the shared pool and parse the statement.A hard parse is a heavy process in terms of resources (CPU & Memory), and we should avoid whenever possible.Excessive hard parsing … Continue reading How to find Top Hard Parsing SQL Statements?

Formatting a Wrapped Execution Plan

Posted on by Ahmed Abdel Fattah

If you received or produced an execution plan like the following unreadable one: ---------------------------------------------------------------------------------------------------| Id  | Operation                   | Name                    | E-Rows |  OMem |1Mem | Used-Mem |---------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                         |        |       ||          ||   1 |  SORT AGGREGATE             |                         |      1 |       ||          ||*  2 |   HASH JOIN                 |                         |     13 |  1102K|1102K|  … Continue reading Formatting a Wrapped Execution Plan

Why Query is not using the Index?

Posted on by Ahmed Abdel Fattah

This post answers the following specific question:                 Why “distinct query” is not using the index (in some cases)? Let us try the following test case: Create sample table “t_distinct” and add an index to the “ID” column and collect statistics for table and index: Let us run the following basic query: It is clear … Continue reading Why Query is not using the Index?