Once you have created a tuning job and named it, using File > Save As, you need to add SQL statements to the job that are to be tuned. All standard DML statements can be tuned.
Statements are added to tuning via the Overview pane.
There are several different methods for adding SQL statements to a job, as reflected by the option in the New SQL text menu.
- New SQL Text enables tuning via manual entry, or cutting and pasting into the tuning window.
- Extract from Database Objects enables you to select stored SQL from the data source to which you are connected. You can either drag and drop objects from the Data Source Explorer or you can add database objects matching specified filers. For example, entering t in the filter area of the Data Source Objects Selection dialog, can match functions, materialized views, procedures, and views, whose name begins with t. You can then drag and drop the matches from the Data Source Explorer to the Tuning Statements grid.
- The Import from File (Workspace) and Import from File (System) options enables you to choose an SQL file saved in your workspace or elsewhere on your computer or network.
- The Scan Oracle SGA option enables you to scan for and select active SQL in the System Global Area (SGA). For more information, see Tuning SQL statements in the System Global Area.
To add an ad hoc SQL statement:
Select the New SQL Text option and manually type an SQL statement in the window, or copy/paste the statement from another source.
To add a database object:
- Select the Extract from Database Objects option.
The Data Source Object Selection dialog appears where you can search for and then select the object you want to tune. Type an object name prefix or pattern in the field provided. The Matching objects window automatically populates with all statements residing on the specified data source that match your criteria. Database objects include functions, materialized views, packages, package bodies, procedures, stored outlines, triggers, and views.
In order to find matching objects, data source indexing must be enabled. To enable data source indexing, click Configure data source indexing, select Enable indexing, and then click OK.
Click the object you want to add. Ctrl-click to add more than one object to the job.
Click OK.Alternatively, after clicking the Database Objects tab, you can drag and drop objects from Data Source Explorer into the Database Objects window. As long as the dragged object is a valid object type, it will be added to the Database Objects tab.
To add an SQL *file*
- From the New SQL Text menu, select either Import from File (Workspace) or Import from File (System), depending on where the file you want to add is stored:
- Workspace files are files that reside in the application, meaning project files or other objects generated or stored in the system.
- File System files are files that reside on your machine or the network.
- Select a file from the dialog that appears. It is automatically added to the job.
To add SQL from the Oracle SGA
- From the New SQL Text menu, select Scan Oracle SGA.
- The system scans for SQL text which you can filter on the Scan SGA dialog that appears.
- Choose the statement to be tuned and then click Finish.