{"id":5427,"date":"2026-02-15T14:50:16","date_gmt":"2026-02-15T09:20:16","guid":{"rendered":"https:\/\/w3buddy.com\/?p=5427"},"modified":"2026-02-15T14:50:18","modified_gmt":"2026-02-15T09:20:18","slug":"oracle-select-statement-behind-the-scenes","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/oracle-select-statement-behind-the-scenes\/","title":{"rendered":"Oracle SELECT Statement: Behind the Scenes"},"content":{"rendered":"\n

When you execute a simple SELECT * FROM employees WHERE department_id = 10;<\/code> query, have you ever wondered what happens behind the scenes? Understanding the internal execution process of a SELECT statement is crucial for database administrators and developers to write efficient queries and troubleshoot performance issues.<\/p>\n\n\n\n

In this comprehensive guide, we’ll explore the complete journey of a SELECT statement from the moment you hit “Enter” to when results appear on your screen.<\/p>\n\n\n\n

The Complete SELECT Statement Execution Flow<\/h2>\n\n\n\n
\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                    USER SUBMITS SQL QUERY                       \u2502\n\u2502              SELECT * FROM employees WHERE dept_id=10           \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                    STEP 1: SYNTAX CHECK                         \u2502\n\u2502  \u2022 Is SQL syntax correct?                                       \u2502\n\u2502  \u2022 Are keywords spelled properly?                               \u2502\n\u2502  \u2022 Are commas and parentheses balanced?                         \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                    [Valid Syntax?]\n                           \u2502\n                    Yes \u2500\u2500\u2500\u253c\u2500\u2500\u2500 No \u2192 Error: ORA-00900\n                           \u2502         (Invalid SQL Statement)\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                    STEP 2: SEMANTIC CHECK                       \u2502\n\u2502  \u2022 Do tables exist?                                             \u2502\n\u2502  \u2022 Do columns exist?                                            \u2502\n\u2502  \u2022 Does user have privileges?                                   \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                    [Objects Valid?]\n                           \u2502\n                    Yes \u2500\u2500\u2500\u253c\u2500\u2500\u2500 No \u2192 Error: ORA-00942\n                           \u2502         (Table or view does not exist)\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                    STEP 3: SHARED POOL CHECK                    \u2502\n\u2502  \u2022 Generate SQL Hash Value                                      \u2502\n\u2502  \u2022 Search in Library Cache                                      \u2502\n\u2502  \u2022 Is execution plan already cached?                            \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                    [Plan Found?]\n                           \u2502\n            \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n            \u2502                             \u2502\n           Yes                           No\n            \u2502                             \u2502\n            \u2502                             \u25bc\n            \u2502              \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n            \u2502              \u2502    STEP 4: HARD PARSE (Expensive)   \u2502\n            \u2502              \u2502  \u2022 Parse the SQL statement          \u2502\n            \u2502              \u2502  \u2022 Generate multiple execution plans\u2502\n            \u2502              \u2502  \u2022 Cost-based optimization          \u2502\n            \u2502              \u2502  \u2022 Choose best execution plan       \u2502\n            \u2502              \u2502  \u2022 Store plan in Library Cache      \u2502\n            \u2502              \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n            \u2502                             \u2502\n            \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                    [SOFT PARSE]\n                    (Reuse Plan)\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                    STEP 5: OPTIMIZER PLANNING                   \u2502\n\u2502  Execution Plan Selected:                                       \u2502\n\u2502  \u2022 Full Table Scan vs Index Scan                                \u2502\n\u2502  \u2022 Join Methods (Nested Loop, Hash, Sort-Merge)                 \u2502\n\u2502  \u2022 Access Paths determined                                      \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                    STEP 6: BIND VARIABLE CHECK                  \u2502\n\u2502  \u2022 If query has bind variables (:dept_id)                       \u2502\n\u2502  \u2022 Substitute actual values                                     \u2502\n\u2502  \u2022 Peek at bind values for optimization                         \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                    STEP 7: EXECUTION ENGINE                     \u2502\n\u2502                   (Row Source Generation)                       \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                    STEP 8: DATA BLOCK ACCESS                    \u2502\n\u2502  \u2022 Check Database Buffer Cache first                            \u2502\n\u2502  \u2022 If not found (Cache Miss), read from datafiles               \u2502\n\u2502  \u2022 Load blocks into Buffer Cache                                \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                    STEP 9: FETCH ROWS                           \u2502\n\u2502  \u2022 Apply WHERE clause filters                                   \u2502\n\u2502  \u2022 Perform joins (if any)                                       \u2502\n\u2502  \u2022 Sort\/Group data (if needed)                                  \u2502\n\u2502  \u2022 Fetch rows in batches (array fetch)                          \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                    STEP 10: RETURN RESULTS                      \u2502\n\u2502  \u2022 Format output                                                \u2502\n\u2502  \u2022 Send result set to client                                    \u2502\n\u2502  \u2022 Display on screen                                            \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
Detailed Step-by-Step Breakdown<\/h2>\n\n\n\n
Step 1: Syntax Check (Parsing Phase)<\/h3>\n\n\n\n
When you submit a SQL statement, Oracle first checks if the syntax is correct.<\/p>\n\n\n\n
What Oracle Checks:<\/strong><\/p>\n\n\n\n
    \n
  • Proper SQL syntax and grammar<\/li>\n\n\n\n
  • Keywords are spelled correctly<\/li>\n\n\n\n
  • Commas, parentheses, and quotes are balanced<\/li>\n\n\n\n
  • Statement structure is valid<\/li>\n<\/ul>\n\n\n\n
    Example of Syntax Errors:<\/strong><\/p>\n\n\n\n
    -- Missing comma\nSELECT employee_id first_name FROM employees;  -- Error!\n\n-- Correct syntax\nSELECT employee_id, first_name FROM employees;  -- Success<\/code><\/pre>\n\n\n\n
    Common Errors:<\/strong><\/p>\n\n\n\n
      \n
    • ORA-00900<\/code>: Invalid SQL statement<\/li>\n\n\n\n
    • ORA-00936<\/code>: Missing expression<\/li>\n\n\n\n
    • ORA-00907<\/code>: Missing right parenthesis<\/li>\n<\/ul>\n\n\n\n
      Step 2: Semantic Check<\/h3>\n\n\n\n
      After syntax validation, Oracle verifies that the objects referenced in the query actually exist and you have proper permissions.<\/p>\n\n\n\n
      What Oracle Verifies:<\/strong><\/p>\n\n\n\n
        \n
      • Tables and views exist in the database<\/li>\n\n\n\n
      • Columns exist in the specified tables<\/li>\n\n\n\n
      • User has SELECT privileges on the objects<\/li>\n\n\n\n
      • Data types are compatible<\/li>\n<\/ul>\n\n\n\n
        Example:<\/strong><\/p>\n\n\n\n
        -- Table doesn't exist\nSELECT * FROM employee_data;  \n-- ORA-00942: table or view does not exist\n\n-- Column doesn't exist\nSELECT emp_name FROM employees;  \n-- ORA-00904: \"EMP_NAME\": invalid identifier\n\n-- No privileges\nSELECT * FROM hr.salaries;  \n-- ORA-00942: table or view does not exist (if no access)<\/code><\/pre>\n\n\n\n
        Common Errors:<\/strong><\/p>\n\n\n\n
          \n
        • ORA-00942<\/code>: Table or view does not exist<\/li>\n\n\n\n
        • ORA-00904<\/code>: Invalid identifier (column name)<\/li>\n\n\n\n
        • ORA-01031<\/code>: Insufficient privileges<\/li>\n<\/ul>\n\n\n\n
          Step 3: Shared Pool Check (Library Cache Lookup)<\/h3>\n\n\n\n
          Oracle generates a hash value for your SQL statement and searches the Shared Pool to see if an identical statement has been executed before.<\/p>\n\n\n\n
          Two Scenarios:<\/strong><\/p>\n\n\n\n
          A) Soft Parse (Fast – Cache Hit)<\/h4>\n\n\n\n
          -- First execution\nSELECT * FROM employees WHERE department_id = 10;\n\n-- Second execution (same query)\nSELECT * FROM employees WHERE department_id = 10;\n-- \u2713 Execution plan found in cache\n-- \u2713 Reuses existing plan\n-- \u2713 Very fast<\/code><\/pre>\n\n\n\n
          B) Hard Parse (Slow – Cache Miss)<\/h4>\n\n\n\n
          -- Slightly different query (space difference)\nSELECT * FROM employees WHERE department_id=10;  -- No space before =\n-- \u2717 Not found in cache\n-- \u2717 Must create new execution plan\n-- \u2717 Resource intensive<\/code><\/pre>\n\n\n\n
          Why This Matters:<\/strong><\/p>\n\n\n\n
            \n
          • Hard parsing consumes significant CPU and memory<\/li>\n\n\n\n
          • Soft parsing is 10-100 times faster<\/li>\n\n\n\n
          • Use bind variables to improve cache hit ratio<\/li>\n<\/ul>\n\n\n\n
            Check Parse Statistics:<\/strong><\/p>\n\n\n\n
            SET LINESIZE 150\nCOLUMN name FORMAT A30\nCOLUMN value FORMAT 999,999,999\n\nSELECT name, value\nFROM v$sysstat\nWHERE name IN ('parse count (hard)', 'parse count (total)');<\/code><\/pre>\n\n\n\n
            Step 4: Hard Parse Process (If Cache Miss)<\/h3>\n\n\n\n
            When Oracle cannot find the execution plan in cache, it must create one from scratch.<\/p>\n\n\n\n
            Hard Parse Steps:<\/strong><\/p>\n\n\n\n
              \n
            1. Parse Tree Generation<\/strong>: Convert SQL into internal representation<\/li>\n\n\n\n
            2. Query Transformation<\/strong>: Optimize the query logically<\/li>\n\n\n\n
            3. Cost Estimation<\/strong>: Calculate costs for different execution methods<\/li>\n\n\n\n
            4. Plan Generation<\/strong>: Create multiple possible execution plans<\/li>\n\n\n\n
            5. Plan Selection<\/strong>: Choose the plan with lowest cost<\/li>\n\n\n\n
            6. Plan Storage<\/strong>: Store plan in Library Cache<\/li>\n<\/ol>\n\n\n\n
              Example – Monitoring Hard Parses:<\/strong><\/p>\n\n\n\n
              SET LINESIZE 180\nCOLUMN sql_text FORMAT A60\nCOLUMN parse_calls FORMAT 999,999\nCOLUMN executions FORMAT 999,999\n\nSELECT sql_text,\n       parse_calls,\n       executions,\n       ROUND(parse_calls\/executions, 2) parse_exec_ratio\nFROM v$sqlarea\nWHERE executions > 0\n  AND parsing_schema_name = USER\nORDER BY parse_calls DESC\nFETCH FIRST 10 ROWS ONLY;<\/code><\/pre>\n\n\n\n
              Tip: A parse-to-execution ratio close to 1 indicates excessive hard parsing.<\/p>\n\n\n\n
              Step 5: Optimizer Creates Execution Plan<\/h3>\n\n\n\n
              The Cost-Based Optimizer (CBO) determines the most efficient way to retrieve data.<\/p>\n\n\n\n
              Optimizer Decisions:<\/p>\n\n\n\n
              Access Methods<\/p>\n\n\n\n
              \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502      ACCESS METHOD DECISION         \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502                                     \u2502\n\u2502  1. Full Table Scan                 \u2502\n\u2502     \u2514\u2500 Read entire table            \u2502\n\u2502                                     \u2502\n\u2502  2. Index Range Scan                \u2502\n\u2502     \u2514\u2500 Use index to find rows       \u2502\n\u2502                                     \u2502\n\u2502  3. Index Unique Scan               \u2502\n\u2502     \u2514\u2500 Find single row via index    \u2502\n\u2502                                     \u2502\n\u2502  4. Index Full Scan                 \u2502\n\u2502     \u2514\u2500 Read entire index            \u2502\n\u2502                                     \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
              Example – Full Table Scan:<\/strong><\/p>\n\n\n\n
              -- Query without index or WHERE clause\nSELECT * FROM employees;\n\n-- Execution Plan:\n-- TABLE ACCESS FULL (employees)<\/code><\/pre>\n\n\n\n
              Example – Index Range Scan:<\/strong><\/p>\n\n\n\n
              -- Assuming index on department_id\nSELECT * FROM employees WHERE department_id = 10;\n\n-- Execution Plan:\n-- TABLE ACCESS BY INDEX ROWID (employees)\n--   INDEX RANGE SCAN (emp_dept_idx)\n\nJoin Methods\n\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502       JOIN METHOD DECISION           \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502                                      \u2502\n\u2502  1. Nested Loop Join                 \u2502\n\u2502     \u2514\u2500 Small tables, indexed columns \u2502\n\u2502                                      \u2502\n\u2502  2. Hash Join                        \u2502\n\u2502     \u2514\u2500 Large tables, no indexes      \u2502\n\u2502                                      \u2502\n\u2502  3. Sort-Merge Join                  \u2502\n\u2502     \u2514\u2500 Sorted data, equality joins   \u2502\n\u2502                                      \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
              View Execution Plan:<\/strong><\/p>\n\n\n\n
              SET LINESIZE 200\nSET PAGESIZE 100\n\nEXPLAIN PLAN FOR\nSELECT e.first_name, d.department_name\nFROM employees e\nJOIN departments d ON e.department_id = d.department_id\nWHERE e.salary > 10000;\n\nSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);<\/code><\/pre>\n\n\n\n
              Step 6: Bind Variable Substitution<\/h3>\n\n\n\n
              If your query uses bind variables, Oracle substitutes the actual values at execution time.<\/p>\n\n\n\n
              Without Bind Variables (Bad Practice):<\/strong><\/p>\n\n\n\n
              -- Each query creates a new execution plan\nSELECT * FROM employees WHERE employee_id = 100;\nSELECT * FROM employees WHERE employee_id = 101;\nSELECT * FROM employees WHERE employee_id = 102;\n-- Result: 3 hard parses<\/code><\/pre>\n\n\n\n
              With Bind Variables (Best Practice):<\/strong><\/p>\n\n\n\n
              -- Same execution plan reused\nVARIABLE emp_id NUMBER;\nEXEC :emp_id := 100;\nSELECT * FROM employees WHERE employee_id = :emp_id;\n\nEXEC :emp_id := 101;\nSELECT * FROM employees WHERE employee_id = :emp_id;\n\nEXEC :emp_id := 102;\nSELECT * FROM employees WHERE employee_id = :emp_id;\n-- Result: 1 hard parse, 2 soft parses<\/code><\/pre>\n\n\n\n
              Check Bind Variable Usage:<\/strong><\/p>\n\n\n\n
              SET LINESIZE 180\nCOLUMN sql_text FORMAT A70\nCOLUMN child_number FORMAT 99\n\nSELECT sql_id, \n       child_number,\n       sql_text,\n       executions\nFROM v$sql\nWHERE sql_text LIKE '%employees%'\n  AND sql_text NOT LIKE '%v$sql%'\nORDER BY sql_id, child_number;<\/code><\/pre>\n\n\n\n
              Step 7: Execution Engine (Row Source Generation)<\/h3>\n\n\n\n
              The execution engine converts the execution plan into executable code called “row sources.”<\/p>\n\n\n\n
              Row Source Tree Example:<\/p>\n\n\n\n
              SELECT e.first_name, d.department_name\nFROM employees e\nJOIN departments d ON e.department_id = d.department_id\nWHERE e.salary > 10000;\n\nRow Source Tree:\n\u2514\u2500 NESTED LOOPS\n   \u251c\u2500 TABLE ACCESS BY INDEX ROWID (employees)\n   \u2502  \u2514\u2500 INDEX RANGE SCAN (emp_salary_idx)\n   \u2514\u2500 TABLE ACCESS BY INDEX ROWID (departments)\n      \u2514\u2500 INDEX UNIQUE SCAN (dept_pk)<\/code><\/pre>\n\n\n\n
              Each row source performs specific operations:<\/p>\n\n\n\n
                \n
              • Scan tables or indexes <\/li>\n\n\n\n
              • Filter rows<\/li>\n\n\n\n
              • Join datasets<\/li>\n\n\n\n
              • Sort or group results<\/li>\n\n\n\n
              • Aggregate data<\/li>\n<\/ul>\n\n\n\n
                Step 8: Data Block Access (Buffer Cache)<\/h3>\n\n\n\n
                Oracle retrieves data blocks from either memory (Buffer Cache) or disk (datafiles).<\/p>\n\n\n\n
                Data Access Flow:<\/p>\n\n\n\n
                \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502         REQUEST DATA BLOCK                   \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n               \u2502\n               \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502   Check Database Buffer Cache (Memory)      \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n               \u2502\n        [Block Found?]\n               \u2502\n       \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n       \u2502               \u2502\n      Yes             No\n       \u2502               \u2502\n       \u2502               \u25bc\n       \u2502    \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n       \u2502    \u2502  Physical Read from Disk     \u2502\n       \u2502    \u2502  (I\/O Operation)             \u2502\n       \u2502    \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n       \u2502               \u2502\n       \u2502               \u25bc\n       \u2502    \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n       \u2502    \u2502  Load Block into Buffer Cache\u2502\n       \u2502    \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n       \u2502               \u2502\n       \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n               \u2502\n               \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502        Return Data Block to User            \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
                Monitor Buffer Cache Efficiency:<\/strong><\/p>\n\n\n\n
                SET LINESIZE 150\nCOLUMN name FORMAT A30\nCOLUMN value FORMAT 999,999,999,999\n\nSELECT name, value\nFROM v$sysstat\nWHERE name IN ('physical reads', \n               'db block gets', \n               'consistent gets',\n               'physical reads cache');\n\n-- Calculate Hit Ratio\nSELECT ROUND((1 - (phy.value \/ (bg.value + cg.value))) * 100, 2) || '%' AS \"Buffer Cache Hit Ratio\"\nFROM v$sysstat phy,\n     v$sysstat bg,\n     v$sysstat cg\nWHERE phy.name = 'physical reads'\n  AND bg.name = 'db block gets'\n  AND cg.name = 'consistent gets';<\/code><\/pre>\n\n\n\n
                Ideal Hit Ratio:<\/strong> Above 90%<\/p>\n\n\n\n
                Step 9: Fetch Rows and Apply Filters<\/h3>\n\n\n\n
                Oracle fetches rows in batches and applies WHERE clause conditions.<\/p>\n\n\n\n
                Fetch Process:<\/strong><\/p>\n\n\n\n
                  \n
                1. Row Filtering<\/strong>: Apply WHERE clause predicates<\/li>\n\n\n\n
                2. Join Operations<\/strong>: Combine data from multiple tables<\/li>\n\n\n\n
                3. Sorting<\/strong>: ORDER BY operations<\/li>\n\n\n\n
                4. Grouping<\/strong>: GROUP BY aggregations<\/li>\n\n\n\n
                5. Array Fetch<\/strong>: Retrieve multiple rows at once (batch processing)<\/li>\n<\/ol>\n\n\n\n
                  Example – Monitoring Fetch Operations:<\/strong><\/p>\n\n\n\n
                  SET LINESIZE 180\nCOLUMN sql_text FORMAT A60\nCOLUMN fetches FORMAT 999,999\nCOLUMN rows_processed FORMAT 999,999,999\n\nSELECT sql_id,\n       sql_text,\n       fetches,\n       rows_processed,\n       ROUND(rows_processed\/NULLIF(fetches,0), 2) rows_per_fetch\nFROM v$sql\nWHERE sql_text LIKE '%employees%'\n  AND sql_text NOT LIKE '%v$sql%'\n  AND fetches > 0\nORDER BY rows_per_fetch DESC\nFETCH FIRST 10 ROWS ONLY;<\/code><\/pre>\n\n\n\n
                  Array Fetch Configuration:<\/strong><\/p>\n\n\n\n
                  -- Check current array size\nSHOW ARRAYSIZE\n\n-- Set optimal array size (default is 15)\nSET ARRAYSIZE 100\n\n-- This reduces round trips between client and server<\/code><\/pre>\n\n\n\n
                  Step 10: Return Results to User<\/h3>\n\n\n\n
                  Finally, Oracle formats the result set and returns it to your SQL client.<\/p>\n\n\n\n
                  Return Process:<\/strong><\/p>\n\n\n\n
                    \n
                  • Format columns according to data types<\/li>\n\n\n\n
                  • Apply NLS settings (date format, number format)<\/li>\n\n\n\n
                  • Send data through network layers<\/li>\n\n\n\n
                  • Display in client application<\/li>\n<\/ul>\n\n\n\n
                    Monitor Query Performance:<\/strong><\/p>\n\n\n\n
                    SET LINESIZE 180\nSET PAGESIZE 100\nCOLUMN sql_text FORMAT A70\nCOLUMN elapsed_time_sec FORMAT 999,990.99\n\nSELECT sql_id,\n       sql_text,\n       executions,\n       ROUND(elapsed_time\/1000000, 2) elapsed_time_sec,\n       ROUND(cpu_time\/1000000, 2) cpu_time_sec,\n       buffer_gets,\n       disk_reads,\n       rows_processed\nFROM v$sql\nWHERE sql_text LIKE '%employees%'\n  AND sql_text NOT LIKE '%v$sql%'\nORDER BY elapsed_time DESC\nFETCH FIRST 10 ROWS ONLY;<\/code><\/pre>\n\n\n\n
                    Performance Optimization Tips<\/h2>\n\n\n\n
                    1. Minimize Hard Parsing<\/h3>\n\n\n\n
                    Problem:<\/strong><\/p>\n\n\n\n
                    -- Different literals cause hard parsing\nSELECT * FROM employees WHERE department_id = 10;\nSELECT * FROM employees WHERE department_id = 20;\nSELECT * FROM employees WHERE department_id = 30;<\/code><\/pre>\n\n\n\n
                    Solution:<\/strong><\/p>\n\n\n\n
                    -- Use bind variables\nSELECT * FROM employees WHERE department_id = :dept_id;<\/code><\/pre>\n\n\n\n
                    2. Proper Indexing<\/h3>\n\n\n\n
                    Check Missing Indexes:<\/strong><\/p>\n\n\n\n
                    SET LINESIZE 180\nCOLUMN table_name FORMAT A30\nCOLUMN column_name FORMAT A30\n\nSELECT table_name, column_name, num_distinct, density\nFROM dba_tab_columns\nWHERE owner = 'HR'\n  AND table_name = 'EMPLOYEES'\n  AND column_name IN ('DEPARTMENT_ID', 'SALARY', 'HIRE_DATE')\nORDER BY num_distinct DESC;<\/code><\/pre>\n\n\n\n
                    Create Appropriate Indexes:<\/strong><\/p>\n\n\n\n
                    -- For selective columns\nCREATE INDEX emp_dept_idx ON employees(department_id);\n\n-- For range queries\nCREATE INDEX emp_salary_idx ON employees(salary);\n\n-- For multiple columns (composite index)\nCREATE INDEX emp_dept_sal_idx ON employees(department_id, salary);<\/code><\/pre>\n\n\n\n
                    3. Update Statistics Regularly<\/h3>\n\n\n\n
                    -- Gather table statistics\nEXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', CASCADE => TRUE);\n\n-- Check last analyzed date\nSET LINESIZE 150\nCOLUMN table_name FORMAT A30\nCOLUMN last_analyzed FORMAT A20\n\nSELECT table_name,\n       num_rows,\n       TO_CHAR(last_analyzed, 'DD-MON-YYYY HH24:MI:SS') last_analyzed\nFROM dba_tables\nWHERE owner = 'HR'\n  AND table_name = 'EMPLOYEES';<\/code><\/pre>\n\n\n\n
                    4. Monitor Shared Pool Usage<\/h3>\n\n\n\n
                    SET LINESIZE 150\nCOLUMN pool FORMAT A15\nCOLUMN name FORMAT A30\nCOLUMN bytes_mb FORMAT 999,999.99\n\nSELECT pool,\n       name,\n       bytes\/1024\/1024 bytes_mb\nFROM v$sgastat\nWHERE pool = 'shared pool'\nORDER BY bytes DESC\nFETCH FIRST 15 ROWS ONLY;<\/code><\/pre>\n\n\n\n
                    5. Use EXPLAIN PLAN<\/h3>\n\n\n\n
                    Always analyze your queries:<\/strong><\/p>\n\n\n\n
                    -- Generate execution plan\nEXPLAIN PLAN FOR\nSELECT e.first_name, e.salary, d.department_name\nFROM employees e\nJOIN departments d ON e.department_id = d.department_id\nWHERE e.salary > 5000\nORDER BY e.salary DESC;\n\n-- View the plan\nSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);\n\n-- View plan with statistics\nSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALLSTATS LAST'));<\/code><\/pre>\n\n\n\n
                    Monitoring Tools and Scripts<\/h2>\n\n\n\n
                    Complete Monitoring Script<\/h3>\n\n\n\n
                    -- SQL Performance Monitoring Script\nSET ECHO OFF\nSET FEEDBACK OFF\nSET LINESIZE 200\nSET PAGESIZE 100\n\nPROMPT ========================================\nPROMPT   SQL EXECUTION MONITORING REPORT\nPROMPT ========================================\nPROMPT \n\nPROMPT Report Time:\nSELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') FROM DUAL;\nPROMPT \n\nPROMPT ========================================\nPROMPT   1. Parse Statistics\nPROMPT ========================================\nCOLUMN name FORMAT A30\nCOLUMN value FORMAT 999,999,999\n\nSELECT name, value\nFROM v$sysstat\nWHERE name IN ('parse count (hard)', \n               'parse count (total)',\n               'execute count')\nORDER BY name;\n\nPROMPT \nPROMPT ========================================\nPROMPT   2. Buffer Cache Hit Ratio\nPROMPT ========================================\n\nSELECT ROUND((1 - (phy.value \/ (bg.value + cg.value))) * 100, 2) || '%' \n       AS \"Buffer Cache Hit Ratio\"\nFROM v$sysstat phy,\n     v$sysstat bg,\n     v$sysstat cg\nWHERE phy.name = 'physical reads'\n  AND bg.name = 'db block gets'\n  AND cg.name = 'consistent gets';\n\nPROMPT \nPROMPT ========================================\nPROMPT   3. Top 10 SQL by Elapsed Time\nPROMPT ========================================\nCOLUMN sql_id FORMAT A15\nCOLUMN sql_text FORMAT A60 TRUNCATE\nCOLUMN elapsed_sec FORMAT 999,990.99\nCOLUMN executions FORMAT 999,999\n\nSELECT sql_id,\n       sql_text,\n       executions,\n       ROUND(elapsed_time\/1000000, 2) elapsed_sec,\n       ROUND(cpu_time\/1000000, 2) cpu_sec,\n       buffer_gets,\n       disk_reads\nFROM v$sql\nWHERE sql_text NOT LIKE '%v$sql%'\n  AND executions > 0\nORDER BY elapsed_time DESC\nFETCH FIRST 10 ROWS ONLY;\n\nPROMPT \nPROMPT ========================================\nPROMPT   4. Shared Pool Usage\nPROMPT ========================================\nCOLUMN pool FORMAT A15\nCOLUMN name FORMAT A25\nCOLUMN mb FORMAT 999,999.99\n\nSELECT pool,\n       name,\n       bytes\/1024\/1024 mb\nFROM v$sgastat\nWHERE pool = 'shared pool'\n  AND bytes > 1048576\nORDER BY bytes DESC\nFETCH FIRST 10 ROWS ONLY;\n\nPROMPT \nPROMPT ========================================\nPROMPT   End of Report\nPROMPT ========================================\n\nSET FEEDBACK ON<\/code><\/pre>\n\n\n\n
                    Quick Reference: Execution Plan Operations<\/h2>\n\n\n\n
                    Common Plan Operations<\/h3>\n\n\n\n
                    Operation<\/th>Description<\/th>When Used<\/th><\/tr><\/thead>
                    TABLE ACCESS FULL<\/td>Reads entire table<\/td>No index or small table<\/td><\/tr>
                    TABLE ACCESS BY INDEX ROWID<\/td>Accesses table via index<\/td>Selective query with index<\/td><\/tr>
                    INDEX RANGE SCAN<\/td>Scans index range<\/td>Range conditions (>, <, BETWEEN)<\/td><\/tr>
                    INDEX UNIQUE SCAN<\/td>Single index entry<\/td>Primary key or unique constraint<\/td><\/tr>
                    INDEX FULL SCAN<\/td>Reads entire index<\/td>ORDER BY indexed column<\/td><\/tr>
                    NESTED LOOPS<\/td>Join method<\/td>Small result sets, indexed joins<\/td><\/tr>
                    HASH JOIN<\/td>Join method<\/td>Large tables, equality joins<\/td><\/tr>
                    SORT ORDER BY<\/td>Sorts result set<\/td>ORDER BY clause<\/td><\/tr>
                    SORT GROUP BY<\/td>Groups and sorts<\/td>GROUP BY clause<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
                    Reading Execution Plans<\/h3>\n\n\n\n
                    -- Example query\nSELECT e.first_name, d.department_name\nFROM employees e\nJOIN departments d ON e.department_id = d.department_id\nWHERE e.salary > 10000;\n\n-- Execution plan output:\n--------------------------------------------------------------\n| Id  | Operation                     | Name           | Rows |\n--------------------------------------------------------------\n|   0 | SELECT STATEMENT              |                |   10 |\n|   1 |  NESTED LOOPS                 |                |   10 |\n|   2 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES      |   10 |\n|*  3 |    INDEX RANGE SCAN           | EMP_SALARY_IDX |   10 |\n|   4 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS    |    1 |\n|*  5 |    INDEX UNIQUE SCAN          | DEPT_PK        |    1 |\n--------------------------------------------------------------\n\nPredicate Information:\n   3 - access(\"E\".\"SALARY\">10000)\n   5 - access(\"E\".\"DEPARTMENT_ID\"=\"D\".\"DEPARTMENT_ID\")<\/code><\/pre>\n\n\n\n
                    How to Read:<\/strong><\/p>\n\n\n\n
                      \n
                    • Operations are executed from bottom to top (innermost first)<\/li>\n\n\n\n
                    • Indentation shows parent-child relationships<\/li>\n\n\n\n
                    • Asterisk (*) indicates a filter or access predicate<\/li>\n\n\n\n
                    • Rows estimate helps identify cardinality issues<\/li>\n<\/ul>\n\n\n\n
                      Common Performance Issues and Solutions<\/h2>\n\n\n\n
                      Issue 1: Excessive Hard Parsing<\/h3>\n\n\n\n
                      Symptom:<\/strong><\/p>\n\n\n\n
                      -- High parse-to-execution ratio\nSELECT sql_text,\n       parse_calls,\n       executions,\n       ROUND(parse_calls\/NULLIF(executions,0), 2) ratio\nFROM v$sqlarea\nWHERE ratio > 0.5\nORDER BY parse_calls DESC;<\/code><\/pre>\n\n\n\n
                      Solution:<\/strong> Use bind variables consistently<\/p>\n\n\n\n
                      Issue 2: Full Table Scans on Large Tables<\/h3>\n\n\n\n
                      Symptom:<\/strong><\/p>\n\n\n\n
                      -- Plans showing TABLE ACCESS FULL on large tables\nEXPLAIN PLAN FOR\nSELECT * FROM large_table WHERE search_column = 'value';<\/code><\/pre>\n\n\n\n
                      Solution:<\/strong> Create appropriate indexes<\/p>\n\n\n\n
                      CREATE INDEX idx_search_col ON large_table(search_column);<\/code><\/pre>\n\n\n\n
                      Issue 3: High Logical Reads<\/h3>\n\n\n\n
                      Symptom:<\/strong><\/p>\n\n\n\n
                      -- Queries with high buffer_gets\nSELECT sql_text,\n       executions,\n       buffer_gets,\n       ROUND(buffer_gets\/NULLIF(executions,0)) gets_per_exec\nFROM v$sql\nWHERE gets_per_exec > 10000\nORDER BY buffer_gets DESC;<\/code><\/pre>\n\n\n\n
                      Solution:<\/strong> Review and optimize query logic, add indexes, or partition tables<\/p>\n\n\n\n
                      Issue 4: Cartesian Joins<\/h3>\n\n\n\n
                      Symptom:<\/strong><\/p>\n\n\n\n
                      -- Missing join condition\nSELECT *\nFROM employees e, departments d;\n-- Results in rows_emp \u00d7 rows_dept<\/code><\/pre>\n\n\n\n
                      Solution:<\/strong> Always include join conditions<\/p>\n\n\n\n
                      SELECT *\nFROM employees e\nJOIN departments d ON e.department_id = d.department_id;<\/code><\/pre>\n\n\n\n
                      Best Practices Summary<\/h2>\n\n\n\n
                      Development Phase<\/h3>\n\n\n\n
                        \n
                      1. Always use bind variables<\/strong> in application code<\/li>\n\n\n\n
                      2. Write selective WHERE clauses<\/strong> to reduce rows processed<\/li>\n\n\n\n
                      3. Test with realistic data volumes<\/strong> before production<\/li>\n\n\n\n
                      4. Use EXPLAIN PLAN<\/strong> to verify execution paths<\/li>\n\n\n\n
                      5. **Avoid SELECT *** – specify only needed columns<\/li>\n<\/ol>\n\n\n\n
                        Database Administration<\/h3>\n\n\n\n
                          \n
                        1. Keep statistics current<\/strong> with DBMS_STATS<\/li>\n\n\n\n
                        2. Monitor parse ratios<\/strong> and Shared Pool usage<\/li>\n\n\n\n
                        3. Review execution plans<\/strong> for frequently executed queries<\/li>\n\n\n\n
                        4. Set appropriate init parameters<\/strong> (SHARED_POOL_SIZE, PGA_AGGREGATE_TARGET)<\/li>\n\n\n\n
                        5. Implement proper indexing strategy<\/strong><\/li>\n<\/ol>\n\n\n\n
                          Query Optimization<\/h3>\n\n\n\n
                            \n
                          1. Use indexes wisely<\/strong> – not too many, not too few<\/li>\n\n\n\n
                          2. Leverage partitioning<\/strong> for very large tables<\/li>\n\n\n\n
                          3. Use appropriate join methods<\/strong> based on data volume<\/li>\n\n\n\n
                          4. Avoid implicit data type conversions<\/strong><\/li>\n\n\n\n
                          5. Consider materialized views<\/strong> for complex aggregations<\/li>\n<\/ol>\n\n\n\n
                            Conclusion<\/h2>\n\n\n\n
                            Understanding how Oracle executes a SELECT statement empowers you to write more efficient queries and diagnose performance problems effectively. The journey from your SQL statement to the final result set involves multiple complex steps, each offering opportunities for optimization.<\/p>\n\n\n\n
                            Key takeaways:<\/p>\n\n\n\n
                              \n
                            • Soft parsing is crucial<\/strong> – use bind variables to maximize cache hits<\/li>\n\n\n\n
                            • The optimizer is your friend<\/strong> – keep statistics fresh and understand execution plans<\/li>\n\n\n\n
                            • Memory is faster than disk<\/strong> – monitor buffer cache hit ratios<\/li>\n\n\n\n
                            • Indexes are powerful<\/strong> – but use them judiciously<\/li>\n\n\n\n
                            • Monitor, measure, optimize<\/strong> – use the provided scripts to track performance<\/li>\n<\/ul>\n\n\n\n
                              By mastering these concepts and regularly monitoring your database using the scripts provided, you’ll be well-equipped to maintain optimal query performance and quickly resolve issues when they arise.<\/p>\n\n\n\n
                              Remember: A well-understood execution process is the foundation of database performance tuning!<\/p>\n","protected":false},"excerpt":{"rendered":"
                              When you execute a simple SELECT * FROM employees WHERE department_id = 10; query, have you ever wondered what happens behind the scenes? Understanding the internal execution process of a SELECT statement is crucial for database administrators and developers to write efficient queries and troubleshoot performance issues. In this comprehensive guide, we’ll explore the complete […]<\/p>\n","protected":false},"author":1,"featured_media":5428,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-5427","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5427","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=5427"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5427\/revisions"}],"predecessor-version":[{"id":5429,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5427\/revisions\/5429"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media\/5428"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=5427"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=5427"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=5427"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}