Monitoring SQL Currently in the Shared Pool

Another task many DBAs have to perform is sampling the SQL code being executed by the database for code review and potential tuning. Why would the DBA need a screen to do this? Why couldn't they just look at the code itself? If the end-users are doing ad-hoc queries or using tools that construct the SQL code on the fly, the DBA may have no real source code to inspect. This is becoming much more common with the plethora of SQL database end- user and developer tools now available. Thus the DBA often must peruse the SGA in order to see what's being done.

TOAD offers a screen perfectly tailored for this task, the SGA Trace/Optimization screen, shown in Figure 5.25 and located on the main menu at Tools, SGA Trace/Optimization. The top half of the screen shows the SQL statements in the SGA, although the bottom half shows the complete statement, its execution statistics, and its explain plan. Note that this screen offers drop-down boxes to restrict by SQL statement type and by the user executing (in Figure 5.25 only SELECT statements for the user MOVIES_PROD are shown). Moreover, the DBA is free to add additional, optional search constraints for requiring either a minimum number of executions or only SQL statements containing certain text.

Figure 5.25. TOAD SGA Trace/Optimization screen.

After you locate SQL statements of interest, you can click the SQL toolbar icon to load that SQL into TOAD's SQL Editor. Now you can utilize TOAD's numerous coding and tuning features to optimize that code. And if you're having trouble locating the SQL code of interest due to the very large number of statements being held in the SGA, you can click the Flush the SGA button to clear the statements.