You can use Best Practices to analyze your SQL statement and database to recommend common techniques for improving database performance. Since the recommendations can also affect performance of other statements in your database, you should review and test the recommendations before implementing them. When evaluating the recommendations, take into account that database performance is affected by the following:
System resources (CPU, I/O, memory, database architecture, and more)
Data distribution
System architecture
SQL execution plans
User's usage behavior
Note: The Best Practices function is only available in SQL Rewrite mode in Optimize SQL.
To view best practices
Click .
Tip: To display the best practices tab, click , select Optimize SQL | Best Practices | General, and select the Display Best Practices tab in SQL Rewrite mode checkbox.
Enter a SQL statement into the Alternative Details pane.
Click . The Select Connection and Schema window displays.
Review the recommendations provided.
The Deploy Outline function in Optimize SQL improves SQL statement performance without changing your original source code. Using Optimize SQL, you can generate SQL statements that are semantically equivalent to your original SQL statement with alternative execution plans. Once you identify the best alternative for your database environment, you can deploy it as a stored outline to use with your original statement.
To deploy an outline
Click the arrow beside and select New SQL Rewrite Session.
Enter your original SQL statement in the Alternative Details pane and click . The Select Connection and Schema window displays.
Right-click the alternative you want to deploy as an outline in the Alternatives pane and select Deploy Outline. The Deploy Outline window displays.
Review the following for additional information:
Outline name | Enter a name for the stored outline |
Category |
Click Notes:
|
Note: You can use the Outline Management feature in Manage Plans to enable and disable categories or to move outlines to different categories.
To batch optimize SQL
Select the Batch Optimize tab in the main window.
Click Add Code to Optimize in the Batch Job List pane and select All Types. The Add Batch Optimize Jobs window displays.
Review the following for additional information:
Connection Page | Description |
Connection |
Click Tips:
|
Database Objects Page | Description |
Database objects |
Select a schema, database object type, or individual database object, and then click Tip:
|
Execute using schema |
Click |
Source Code Page | Description |
Source code type |
Select Text/Binary files, Oracle SQL *Plus Script, or COBOL programming source code to indicate the source code type for the file or directory you want to scan. |
Add by file |
Click |
Add by directory |
Click Note: Select the Include Sub-directory checkbox to scan sub-directories. |
Scan using schema |
Click |
Execute using schema |
Click |
SQL Text Page | Description |
SQL text |
Enter SQL statement text. |
Scan using schema |
Click |
Execute using schema |
Click |
Scan SQL Page | Description |
Group |
Select the Scanner group that contains the SQL statements you want to scan. |
Scan using schema |
Click |
Execute using schema |
Click |
Inspect SGA Page | Description |
Group |
Select the Inspector group that contains the SQL statements you want to scan. |
Scan using schema |
Click |
Execute using schema |
Click |
Foglight Performance Investigator for Oracle Page |
Description |
Select a database to search for the repository used to store captured SQL |
Click Tip: Click |
Note: Batch Optimize helps you manage jobs by organizing them into batches. Use the Batch Info page to create a new batch or to add the current job to an existing batch. |
Click Finish to start batch optimization.
Batch Optimize scans the job you created, classifies and optimizes the statements, and executes the SQL statement alternatives it generates.
Notes:
Scanning starts automatically if you select the Automatically start extracting SQL when job is added checkbox in the Batch Optimize options page. Batch Optimize selects this checkbox by default.
Batch Optimize selects the SQL statement to optimize based on the classification types selected in the Batch Optimize options page. Batch Optimize selects Problematic and Complex SQL classification types by default.
Batch Optimize executes SQL alternatives it generates based on the statement types selected in the Batch Optimize options page. Batch Optimize selects SELECT statements by default.
Select Batch List in the Batch Job List pane to view information about the jobs you created.
The Batch List pane sorts information about your jobs by batches. Additional information displays in the Jobs Improved pane.
Select a batch from the batch list node to see details for the batch in the Job List pane.
The Job List pane displays the type of job, job status, and time of improvement for each job in the batch. Additional information displays in the SQL Classification and Cost and Elapsed Time Comparison panes for a selected job.
Tip: Select a job in the Job List pane and click to generate a replacement script with the optimized SQL statement.
Select a job from the batch node to see details of the job.
The SQL List pane displays SQL classification information for the SQL statements in the job you select. The Original SQL Text and Best Alternative SQL Text panes allow you to compare your original SQL statement with the best alternative Batch Optimize generated.
Tip: Select a SQL statement in the SQL List pane and click to send the statement to Optimize SQL and view all SQL alternatives.
Scan SQL helps you identify problematic SQL statements in your database environment by automatically extracting statements embedded in database objects, stored in application source code and binary files, captured from Oracle's System Global Area, or saved in Foglight Performance Investigator repositories. Scan SQL retrieves and analyzes execution plans for the extracted statements and classifies them according to complexity. You can then send statements that Scan SQL classifies as problematic or complex to Optimize SQL.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To scan SQL
Select the Scan SQL tab in the main window.
Click to select a previously created group or click
to create a new group for your scan job.
Note: Scan SQL helps you manage scan jobs by organizing them into groups.
Click . The Add Scanner Jobs window displays.
Review the following for additional information:
Page | Description |
---|---|
Database Objects Page | |
Database objects |
Select a schema, database object type, or individual database object, and then click Tip: Click |
Source Code Page | |
Source code type |
Select Text/Binary files, Oracle SQL *Plus Script, or COBOL programming source code to indicate the source code type for the file or directory you want to scan. |
Add by file |
Click |
Add by directory |
Click Note: Select the Include Sub-directory checkbox to scan sub-directories. |
Scan using schema |
Click |
Inspect SGA Page | |
Group |
Select the Inspector group that contains the SQL statements you want to scan. |
Scan using schema |
Click |
Foglight Performance Investigator for Oracle Page | |
Select a database to search for the repository used to store captured SQL |
Click Tip: Click |
Scan using schema |
Click |
Click Finish to start scanning.
Select a scan job from the Job List pane to view additional information.
Details displayed in the Job List pane include the number of SQL statements found and the classification for each statement.
Tip: Click and select a different group to display scan jobs from a different group.
Select a SQL statement in the SQL List pane to view additional information for the selected statement in the SQL Text and Execution Plan panes.
Tip: Click in the SQL Text pane to send the selected statement to Optimize SQL.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback 利用規約 プライバシー Cookie Preference Center