Performance Tuning 2: Using Autotrace

By



In Oracle SQL*Plus and SQL Developer, AUTOTRACE is a powerful tool used to analyze the execution of SQL statements. It provides valuable insights into how the Oracle database processes your queries, helping you identify potential performance bottlenecks.

What AUTOTRACE Does:

  • Execution Plan:
    • AUTOTRACE displays the execution plan, which shows the sequence of operations the Oracle optimizer uses to execute a SQL statement. This allows you to see how Oracle accesses the data, such as whether it's performing a full table scan or using an index.   
  • Statistics:
    • It also provides statistics about the execution, including:
    • Recursive calls: The number of internal SQL statements executed by Oracle.
    • DB block gets and consistent gets: Measures of how Oracle retrieves data blocks.   
    • Physical reads: The number of data blocks read from disk. 
    • Redo size: The amount of redo log generated.
    • And other valuable performance metrics.

SQL*Plus Autotrace:

SET AUTOTRACE ON; (it includes execution plan and statistics)

SET AUTOTRACE ON EXPLAIN; (show report only the optimizer execution path.)

SET AUTOTRACE ON STATISTICS; (show on execution statistics)

SET AUTOTRACE OFF; (to disable autotrace)




However the best is to use the autotrace from SQL Developer as SQL Plus might not always give the acurate results.

SQL Developer Autotrace:

SQL Developer's "Autotrace" feature integrates the functionality in a graphical way.

Here's a breakdown of how to use the Autotrace feature in SQL Developer, along with explanations:

Steps to use Autotrace in SQL Developer:

  1. Open SQL Developer and a SQL Worksheet:
    • Launch Oracle SQL Developer.
    • Open a new or existing SQL Worksheet where you can enter your SQL queries.
      2. Write Your SQL Query:
  • Type the SQL query you want to analyze into the SQL Worksheet.

      3. Enable Autotrace:

  • Look for the "Autotrace" button in the SQL Worksheet toolbar. It often looks like a small icon with a plan or statistics symbol.

  • Click the "Autotrace" button.

     4. Execute the Query:

  • Once Autotrace is enabled, execute your SQL query by clicking the "Run Statement" button (or pressing F9).

     5. View the Autotrace Results:

    • SQL Developer will display the Autotrace results in a separate tab within the SQL Worksheet results pane.
    • This tab will show:
      • Execution Plan: A hierarchical representation of the query's execution plan.
      • Statistics: Performance statistics related to the query's execution, such as:
        • Logical reads (consistent gets)
        • Physical reads
        • CPU time
        • Elapsed tim
        • And more.

Key Points:

  • SQL Developer's Autotrace provides a graphical and well-organized view of the execution plan and statistics, making it easier to analyze than the raw output from SQL*Plus.
  • SQL developer automatically gathers the information, so there is no need to run a set command like in SQL plus.
  • The information displayed is very similar to the information gained from SQL*Plus autotrace.


 

No comments:

Post a Comment