Oracle SQL Tuning Tools

One of the primary problems with tuning SQL is that we cannot easily see how Oracle is accessing the data tables and indexes. Fortunately, Oracle has provided a host of utilities that can help us see what is happening within the database as the SQL is being executed.
These utilities include:

Utilities
The EXPLAIN PLAN utility (utlxplan.sql)	This utility allows the DBA to see the actual path to the data.
The TKPROF utility	TKPROF allows the DBA to trace through the actual execution of an SQL statement.
The Oracle SQL ANALYZE utility	This is a graphic tool that aids in the debugging of Oracle SQL. We will be using screens from this tool extensively in this course.
The SQL*Plus utility	SQL*Plus provides various commands (set timing on) that allow the DBA to get execution times for SQL statements.

Read the paragraphs below to understand how the SQL Analyzer utility functions.

1. With the Oracle Enterprise Manager open, click the Oracle SQL Analyzer button.
2. Read the opening screen, and click OK.
3. Now we are ready to begin. Select the bottommost database under the Databases folder.
4. Select Top SQL from the mon1 database.
5. The TopSQL Options box will allow you to control the number of disk reads as well as apply various filters. Click OK.
6. Choose our database to view the init.ora parameters for SQL values. Select select/*+rule*/ a.db_name.
7. Click the SQL Text tab to view the full SQL text.
8. Place your cursor anywhere in the SQL text field and click to highlight the text.
9. In an actual Oracle environment, you would press Ctrl+C to copy this text and Ctrl+N to move to a new screen. For the purposes of this simulation, press Enter to move to a new SQL screen.
10. For the purposes of this simulation, place your cursor in the SQL statement window and hit Enter to paste your SQL text. You would normally accomplish this by pressing Ctrl+V.
11. Typically, you would press F9 to see the rule-based explain plan, but for the purposes of this simulation press Enter.

Oracle SQL Analyze can be used to identify and correct problem SQL statements. SQL Analyze allows you to sort SQL statements by various performance metrics, and acquire detailed tuning information for any SQL statements. Wizards are included which guide the user through a SQL optimization methodology or add hints to existing SQL statements.

The Index Tuning Wizard provides an easy solution to the Index Tuning problem. It automatically identifies tables which would benefit from index changes, presents its findings for verification, and allows you to implement the index tuning recommendations.
In later lessons, we will be going deeper into SQL analyzer to show how it can be used to tune SQL queries.
In the next lesson we will look at how SQL is executed within the Oracle engine.

Oracle Expert is no longer a standalone feature in current Oracle database versions It was a separate tool available in earlier versions (up to Oracle 10g) to analyze performance issues and provide tuning recommendations.
Here's how Oracle has evolved its tuning capabilities:

1. Integrated Functionality: Oracle has integrated many of Oracle Expert's features into its core database management tools, including:
   • Automatic Workload Repository (AWR): Collects and analyzes performance statistics.
   • Automatic Database Diagnostic Monitor (ADDM): Identifies performance bottlenecks and suggests corrective actions.
   • SQL Tuning Advisor: Analyzes SQL statements and recommends tuning actions.
   • SQL Performance Analyzer (SPA): Tests the impact of tuning changes before implementation.
2. Cloud-Based Tools: Oracle also offers cloud-based tools for database management and tuning, such as:
   • Oracle Enterprise Manager Cloud Control: Provides a comprehensive view of database performance and allows for proactive tuning.
   • Oracle Cloud Infrastructure (OCI) Database Management: Offers automated tuning and optimization features for databases in the Oracle Cloud.

Key Advantages of the Integrated Approach:

• Streamlined Workflow: Tuning tools are directly embedded within the database management environment, simplifying the process.
• Real-Time Analysis: Performance data is continuously collected and analyzed, enabling proactive tuning.
• Cloud Integration: Cloud-based tools offer additional automation and scalability for database management.

Recommendations for Tuning Oracle Databases:

• Leverage the Integrated Tools: Utilize AWR, ADDM, SQL Tuning Advisor, and SPA to identify and address performance issues.
• Consider Cloud-Based Tools: Explore Oracle Enterprise Manager Cloud Control or OCI Database Management for enhanced tuning capabilities in cloud environments.
• Stay Updated: Keep your database software up-to-date to benefit from the latest tuning features and best practices.
• Consult Documentation: Refer to Oracle's official documentation for detailed guidance on using the available tuning tools effectively.

Use the EXPLAIN PLAN statement to determine the execution plan Oracle Database follows to execute a specified SQL statement. This statement inserts a row describing each step of the execution plan into a specified table. You can also issue the EXPLAIN PLAN statement as part of the SQL trace facility. This statement also determines the cost of executing the statement. If any domain indexes are defined on the table, then user-defined CPU and I/O costs will also be inserted. The definition of a sample output table PLAN_TABLE is available in a SQL script on your distribution media. Your output table must have the same column names and datatypes as this table. The common name of this script is UTLXPLAN.SQL. The exact name and location depend on your operating system. Oracle Database provides information on cached cursors through several dynamic performance views:

1. For information on the work areas used by SQL cursors, query V$SQL_WORKAREA.
2. For information on the execution plan for a cached cursor, query V$SQL_PLAN.
3. For execution statistics at each step or operation of an execution plan of cached cursors (for example, number of produced rows, number of blocks read), query V$SQL_PLAN_STATISTICS.
4. For a selective precomputed join of the preceding three views, query V$SQL_PLAN_STATISTICS_ALL.
5. Execution statistics at each step or operation of an execution plan of cached cursors are displayed in V$SQL_PLAN_MONITOR if the statement execution is monitored. You can force monitoring using the MONITOR hint.