• No results found

Toad for Oracle 8.6 SQL Tuning

N/A
N/A
Protected

Academic year: 2021

Share "Toad for Oracle 8.6 SQL Tuning"

Copied!
8
0
0

Loading.... (view fulltext now)

Full text

(1)

Revised: October 2005 Page 1

Quick User Guide

for

Toad for Oracle 8.6 SQL Tuning

SQL Tuning Version 6.1.1

SQL Tuning definitively solves SQL bottlenecks through a unique methodology that scans code, without

executing programs, to isolate poor queries that are candidates for optimization. Those queries are

transformed in every way possible using automated rewrite technology, guaranteeing the same results as

the original query. Once every alternative has been generated the most efficient statement for your

environment can be identified. Similarly, SQL Tuning can generate virtual index scenarios to influence

SQL performance. SQL Tuning also offers Best Practices that provide tuning recommendations for other

factors that affect SQL performance such as statistics.

SQL Tuning provides the following functionality:

Module Function

Best Practices Provides database tuning guidelines for improving SQL performance.

SQL Optimizer Creates SQL statements that are alternatives to your source SQL statement. Validates the performance of the source and alternative statements on your system, using your indexes, and your memory configuration to find the best performing statement.

Rewrites SQL statements in a recursive manner by applying SQL

transformations so that every SQL statement is rewritten until all possible semantically equivalent SQL alternatives and alternative execution plans have been generated. The source and alternate SQL are then benchmarked to find the highest performing SQL statement for a database environment.

Index Expert Generates Index sets alternatives for a specific SQL statement. The Virtual Index Simulator functionality provided in Index Expert displays the execution plan for every index set combination that Oracle would generate if the indexes were physically created. These Index set alternatives can be benchmarked to identify the most efficient index set for a SQL statement.

SQL Inspector Collects SQL performance data from the Oracle SQL Area. Monitors currently running SQL, or takes snapshots to identify SQL statements that consume the largest amount of resources or that are most frequently executed.

SQL Scanner Extracts SQL statements embedded within the database objects, text/binary files, SQL Inspector files, and application source codes. It analyzes which of these SQL statements has performance bottlenecks. You can input problematic SQL

(2)

Toad for Oracle – SQL Tuning – Quick User Guide found here into the SQL Optimizer.

The SQL Inspector and the SQL Scanner are in an out-of-process module which runs in a separate window.

1.0 Best Practices

Best Practices does an overall analysis of a SQL statement and your database and then proposes common ways to improve performance. However, these recommendations may not be the correct solution for your application, so you need to understand and thoroughly test the recommendations before you apply them to your production system. A recommendation may help to improve a specific SQL performance, but it may affect other SQL statements as well. When evaluating the recommendations, you need to take into account that database performance is a result of the complex mix of the following:

• System resources (CPU, I/O, memory, database architecture, and more) • Data distribution

• System architecture • SQL execution plans • User's usage behavior

1. Enter a SQL statement into the SQL Text window. 2. Click the Get Best Practices button.

3. Review the recommendations in the Best Practices layout.

2.0 SQL Optimizer

In the SQL Optimizer, the SQL optimization is a two-step process. In the first step, the SQL Optimizer automatically transforms the original SQL statement and generates semantically equivalent alternative SQL

statements with unique execution plans. For every alternative execution plan, SQL Tuning displays the Oracle Cost estimation. Once the SQL alternatives have been generated, the second step consists of locating the most-efficient SQL alternatives for your database environment by benchmark testing the SQL alternatives against your database. The results obtained from the second step indicate the time required to execute each SQL statement, as well as, runtime statistics.

Tip: The Oracle Cost is only an estimate of the resources it takes to execute a SQL statement. It is essential to execute the alternative SQL statements in order to determine which statement performs the best in your environment.

1. In the SQL Text window of the SQL Details Layout, enter a SQL statement.

(3)

Toad for Oracle – SQL Tuning – Quick User Guide which matches your SQL statement.

3. Click the Optimize button.

This step launches the SQL Optimizer that automatically transforms the SQL statement. The use of hints and other optimization options such as transforming a view to an inline view and the ANSI JOIN syntax are optional. In the Preferences, the intensity of the SQL transformation process is controlled by the Intelligence Level. The Intelligence Levels control how many Oracle hints are applied to transform SQL and how many SQL alternatives are created.

4. Look at the progress bar in the bottom right corner of the SQL Optimization Console to see the progress optimization process.

Compare Scenarios

When the optimization is finished, the Compare Scenarios Layout is displayed. At this point since you have not yet run the SQL statements, the top window in the layout displays only the Oracle Cost values. These are only

estimations of how each statement will perform. You need to test each statement to obtain its actual runtime statistics.

5. To compare the original SQL statement and the execution plan to the alternative SQL statements, click the drop-down list at the top of the SQL Text windows to select the SQL statements you want to compare.

Test the Alternative SQL statements

The Execute function provides an efficient way of test running SQL. It runs the selected SQL statements in the database and the SQL statements that exceed the termination time are cancelled. For SQL statements such as INSERT, DELETE and UPDATE, each statement is run in a transaction that is ROLLBACK, therefore maintaining the consistency of your data.

6. To prepare to execute the source and the alternative SQL statements, click the Preferences button. Click the Execution button.

7. In the Scenario Execution section, select the following option that best suits your SQL statement.

All SQL Twice

Running all SQL twice enables you to eliminate some factor that can affect the accuracy of the results. If some SQL statements have been recently executed, then the SQL information is likely to be resident in the cache and it may execute faster because of that. Also, if the SQL statements use different indexes, one index may be resident in the cache and the other not. This option eliminates these factors since it runs all SQL statements twice, throws out the first execution time and uses the second one when all SQL statements and indexes should have the necessary items in the cache.

Original SQL Twice

The first time you access data from table, the data is cached into memory. This process takes a few

moments. The next time you access that data, it is already in memory so the following SQL statements run faster. So to have a comparable test, the first SQL is run twice and the time from the second run is

compared to the time from the other statements.

All SQL Once

For long running SQL there is no need to run any statement twice since the effect from caching is diminished over time.

(4)

Toad for Oracle – SQL Tuning – Quick User Guide

8. In the Row Retrieval section, select Run on server. With this option, the Execute function retrieves the time the SQL statement executes in the database and does not retrieve the result set from the database server to the client; so it does not create additional network traffic.

9. In the Execute Complete section, select Cancel scenarios that exceed 120% of the Original execution

time.

10. If your SQL statement runs in milliseconds, set the Execute each scenario n time to 10 in the Multi-Execute option. Click OK.

11. Click the down arrow on the right of the Execute Scenario button and select Execute All.

12. Look at the progress bar in the bottom right corner of the SQL Optimization Console to see the progress of the batch run process. When the batch run is finished, the Execution Statistics layout is displayed.

13. To test SQL alternatives under different user loads to simulate production activity, click on the Test SQL for

Scalability button. This feature requires Benchmark Factory for Databases version 4.6.

14. Once you have identified the alternative SQL statement you want to deploy, you can copy and paste it back in your application. If the SQL was submitted from Toad, you can click on the Return SQL button.

3.0 Index Expert

Index Expert analyzes the syntax of a SQL statement, the relation between tables, and selectivity of the data to identify columns as index candidates. Index candidates are combined into multiple index sets and it gives you all the alternative index sets that generate a unique execution plan for the SQL statement. It does this without

physically creating the indexes in the database. Index Expert provides performance estimations for every index set to assist you in selecting which index set alternatives to test, evaluate, or implement. Index alternatives can be benchmarked to identify which set will yield the greatest performance gain for the SQL statement.

1. Enter the SQL statement in the SQL Text window. 2. Click the Generate Virtual Indexes button.

3. For each index scenario the virtual plan is displayed which is the execution plan under simulated indexes. Use the Comparer layout to compare the execution plan of the original SQL and the virtual plan of the index scenario. This will allow you to determine if the index set would offer performance improvement.

Test the Index Sets with the Original SQL statement

The Execute function provides an efficient way of testing indexes. It physically creates the indexes on the database, runs the SQL statement, and then drops the indexes.

Note: This process may impact the performance of other SQL statements.

4. Select the index sets that you would like to test.

(5)

Toad for Oracle – SQL Tuning – Quick User Guide

6. Look at the progress bar in the bottom right corner of the SQL Optimization Console to see the progress of the batch run process. When the batch run is finished, the Execution Statistics layout is displayed.

4.0 SQL Inspector

You can retrieve executed SQL statements from the Oracle SQL Area or currently running SQL statements from Oracle’s open cursor. After you have captured the SQL statements and statistics according to your retrieval criteria, you may view all information graphically to help you identify the resource intensive SQL statements. After you have identified these SQL statements, you can then move a specific SQL statement to the SQL Editor so you can tune it. Or, you can add the Inspector to the SQL Scanner enabling you to identify potentially problematic SQL statements.

Note: To use the SQL Inspector, you must have the privileges view SYS.V_$SQLAREA, SYS.V_$SESSION,

SYS.V_$OPEN_CURSOR, and either SYS.V_$SQLTEXT_WITH_NEWLINES or SYS.V_$SQLTEXT.

4.1 Retrieve previously executed SQL statements from Oracle SQL Area

1. Click the SQL Inspector button or select Tools — SQL Inspector.

2. If this is the first time you have used the SQL Inspector, the Add Inspector wizard appears. Otherwise, the

Inspector Manager window appears. Click the Add Inspector button or select Inspector — Add Inspector

to bring up the Add Inspector wizard.

3. Under the General Information page, select Snapshot or the Inspector type. 4. Enter an Inspector name.

5. Under the Order By page, the first column in the Order By box is the column that is used to extract the SQL statements when you are not displaying all records. So if you are displaying only 100 records, it will extract the top 100 records based on the first column you select in the Order by box.

Note: If you have a larger SGA, it will take some time to sort the SQL statements before the extraction is done.

6. You can narrow the selection criteria for the SQL statements that the Inspector will extract. Under the

Collecting Criteria page, select Top n records and set the Number of records to be displayed to your desired number.

7. Under the Snapshot Setting page, adjust the date and time for the SQL statements you want to retrieve from the Oracle SQL Area.

8. Adjust any other settings in the Add Inspector wizard to satisfy your requirements. Click Finish.

9. Click the Inspect button or select Schedule — Inspect to start retrieving the SQL statements and statistics from the Oracle SQL Area. Once the SQL statements have been retrieved, a chart preview will also be

displayed.

10. Click the Inspected SQL Viewer button or select Inspector — Inspected SQL Viewer to view all retrieved SQL statements and statistics in more detail, or click the Show SQL button from the SQL Inspector window to view the selected range of SQL statements and statistics.

11. Select one SQL statement that you feel is problematic and should be tuned. Select the SQL statement, right click and select copy. Then go the SQL Optimization console, paste the SQL and then continue from the SQL

(6)

Toad for Oracle – SQL Tuning – Quick User Guide

Optimizer Section, Step 3. Another way of identifying problematic SQL statements is to scan the Inspector file using the SQL Scanner section.

Note: SQL statements need to be manually copied from the SQL Inspector and pasted in the SQL Optimization console.

4.2 Retrieve currently running SQL statements

12. Click the SQL Inspector button or select Tools — SQL Inspector.

13. If this is the first time you have used the SQL Inspector, the Add Inspector wizard appears. Otherwise, the

Inspector Manager window appears. Click the Add Inspector button or select Inspector — Add Inspector

to bring up the Add Inspector wizard.

14. Under the General Information page, select Monitor for the Inspector type. 15. Enter an Inspector name.

16. Under the Monitor Settings page, set the amount of time you want to monitor the incoming SQL statements. 17. Adjust the other settings under the tabs across the top of the Add Inspector window to satisfy your

requirements. Click Finish.

18. Click the Inspect button or select Schedule — Inspect to start the monitoring process.

19. The monitoring process will stop according to the duration or end time defined on the Add Inspector wizard. If you want to stop the monitor before the set time, click the Abort Inspect button.

20. After the monitoring function is complete, a chart preview will also be displayed.

21. Click the Inspected SQL Viewer button or select Inspector — Inspected SQL Viewer to view all retrieved SQL statements and statistics in more details, or click the Show SQL button from the SQL Inspector window to view the selected range of SQL statements and statistics.

22. Select one SQL statement that you feel is problematic and should be tuned. Select the SQL statement, right click and select copy. Then go the SQL Optimization console, paste the SQL and then continue from the SQL Optimizer Section, Step 3. Another way of identifying problematic SQL statements is to scan the Inspector file using the SQL Scanner section.

Note: SQL statements need to be manually copied from the SQL Inspector and pasted in the SQL Optimization console.

23. Another way of identifying a problematic SQL statement is to scan the Inspector file using the SQL Scanner

section.

5.0 SQL Scanner

Use the SQL Scanner to analyze SQL statements embedded within database objects, text/binary files, SQL Inspector files, and application source codes. The SQL Scanner extracts each SQL statement embedded within the scanned database objects and files, retrieves their respective execution plans from Oracle, and then performs an analysis that determines which of these SQL statements are likely to contain performance bottlenecks. You can copy the SQL statements analyzed as problematic (top priority) or complex (second priority) into the SQL Optimizer and/or examine the extracted SQL statements with their execution plans.

(7)

Toad for Oracle – SQL Tuning – Quick User Guide

Tip: An effective use of the SQL Scanner is to review existing code to proactively identify the SQL statements that

can potentially cause performance problems without the need of executing the applications. In this way you can prevent performance degradation.

Another effective use of the SQL Scanner is to locate the SQL that is causing performance problems in your applications. For example, if you know that you have a slow running report, you can scan the program text or binary file to extract the SQL statements that it contains without having to execute it. The SQL Scanner identifies SQL statements that are likely to create performance problems. You can then use the SQL Optimizer to provide alternative SQL statements that may improve the performance.

1. Click the SQL Scanner button or select Tools — SQL Scanner. The SQL Scanner is a separate module. It logs into Oracle using the same user logon that was used to connect to SQL Tuning.

2. When you scan the database objects or the application files, you first create a Group to store the items you want to scan.

If this is the first time you have used the SQL Scanner, the Create Group window appears. Otherwise, click the

Create button in the Group Manager window.

3. Enter a new Group name, e.g. “Test.” Click OK to close the Create Group window. 4. Check that your new Group name is highlighted in the list box. Click the Open button.

5. The selected group is opened in the Job Manager window. For a new Group, the Add Jobs wizard is automatically opened so you can select what files, database objects, or Inspector files you want to scan.

Note: If you are using an existing Group, click the Add Jobs button or select Group — Add Jobs.

6. In the Add Jobs wizard, click Next until you are at the page for the item that you want to scan. Database Objects page

a. Expand the database user branch on the left side of the window.

b. Highlight the schema, a database object type, or an individual database object, and click the Add

Database Objects button to move the item to the right pane. (Whether or not you can scan all of the selected database objects depends on your database privileges.)

SQL Inspector page

a. From the left pane, select the Inspector.

b. Click the Add SQL Inspector button to move the Inspector to the right pane.

c. Set the Scan using Parsing User in the drop-down boxes to correspond with the SQL that you are scanning.

Source Code page

a. Click the Text or binary files, Oracle SQL*Plus scripts, or COBOL programming source code option.

b. Click the Add button and select the files you want to scan. c. Click the Open button to insert the files in the Add Jobs wizard.

d. Set the Scan using Parsing User in the drop-down boxes to correspond with the SQL that you are scanning.

(8)

Toad for Oracle – SQL Tuning – Quick User Guide

a. If you want to scan for SCLD SQL statements, select the Scan as SCLD checkbox to use the scanning algorithm to find single command line dynamic (SCLD) SQL statements. An SCLD SQL statement is a statement that is all on one command line, is within a quoted string, and may have a parameter that is inserted by the application. This is a common format for programming languages like Java and Visual Basic.

7. Click the Scan button or select Job — Scan to start the scanning process.

8. Details are filled in the Job Manager grid as the scanning process completes each job. It shows you how many SQL statements were found in the Job and how each SQL statement is classified.

9. To view the scanned SQL statements, highlight the item by clicking the row and click the Scanned SQL

Viewer button or select Job — Scanned SQL Viewer.

10. The name of the file or database object appears at the top of the window in the drop down list box.

11. The first SQL statement found is shown in the top pane. Click the tabs, e.g. SQL1, SQL2, SQL3, etc., at the bottom left of the window to view the other SQL statements.

12. Notice the execution plan for each statement in the upper right pane. The lower right pane tells why the SQL statement is classified as simple, complex, problematic, or invalid.

13. You can narrow the number of source SQL statements to view only the problematic and/or complex statements with View — Problematic SQL and/or View — Complex SQL.

14. Select one SQL statement you would like to tune. Select the SQL statement, right click and select copy. Then go the SQL Optimization console, paste the SQL and then continue from the SQL Optimizer Section, Step 3

Note: SQL statements need to be manually copied from the SQL Scanner and pasted in the SQL Optimization console.

15. A new copy of SQL Tuning is opened and the SQL statement is copied to the SQL Text window of the SQL Optimization Console using the same user logon as the scanned SQL statement.

References

Related documents

Nanjing United Chemical Logistics Co., Ltd (UT) is a professional engaged in the third party logistics enterprises of chemical products, which has provide logistics services to

The parameters that will be studies are species of trees, ages of tree, band width of mangrove forest, density of trees and canopy closure, initial wave height, height of

For the healthy control group, correlations between behavioral performance and neural correlates showed an association between Stroop interference reaction time (Stroop

 Kertas soalan ini mengandungi 20  soalan. Jawab semua  soalan. Tulis)an awapan dalam ruang yang disedia)an dan tunu))an alan )era.. the se$"en%e2.  si)an tempat

The strength of each element in the chart can be determined by using table 8 above (e.g. In spring: wood prosperous, fire strong, water weak, metal trap, and earth dead).

For learning to predict the other’s intended action we apply genetic programming (GP) (Koza 1992; Banzhaf, Nordin, Keller, and Francone 1998)?. The strategy is fixed and does not

2 and Chinese markets, as represented by Hong Kong and the Hang Seng Index, not Shanghai and the Shanghai Composite Index, because the Spillover Index analysis, conducted in a VAR

The minimum requirements on the qualifications and experience of the key personnel of a registered specialist contractor in site formation works category (RSC(SF)) are given in