SQL Analyze Utility

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.

Oracle Expert automates the database tuning process. Oracle Expert provides a methodology that is used to collect, evaluate, verify, and implement database tuning changes. Tuning areas covered by Oracle Expert include instance parameter tuning, database structure and placement tuning, index tuning, and SQL statement reuse evaluation.

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.