On Oracle platforms, SQL statements that reside in the SGA can also be tuned. When you create a tuning job and specify an Oracle source, an additional tab appears in the Tuning Candidates section of tuning, named Active SQL in SGA.
The SGA contains all the SQL since the database has been started up, except for those that have been purged when the system runs out of memory. When analyzing the causes of a database bottleneck, it is perhaps more useful to view and tune the SQL statements most recently run, than those that have run in the last month, for example. DB Optimizer cannot tell you which statements have most recently run by looking in the SGA. However, by profiling the database using DB Optimizer Profiling and then optimizing the code by executing and running the generated cases, you will be able to see which paths are most likely causing a bottleneck and can be altered to enhance performance. Also, you can use IDERA Performance Center to continually monitor a database over a longer period of time to help you analyze and optimize database performance.
To add a statement active in the SGA
- From the Overview tab, click the SQL icon and select Scan Oracle SGA. The Scan SGA wizard appears.
- Set the filtering criteria for an SGA scan and then run the wizard. It returns all active statements on the Oracle source.
- Choose the specific statements and add them to the tuning job.