Hi. My name is Matthew Phan, and I'm a systems consultant In this video, I'll demonstrate how Spotlight can be used to identify problematic or poor performing SQL statements in need of tuning. I want to be productive in keeping my database running smoothly, so I'll look for the worst running SQL queries in my system and see if they need to be optimized. There are some easy ways of accomplishing that with Spotlight on Oracle.
One method is to use a SQL and Application Workload window, represented by the SQL icon on the toolbar. This window can display what SQL is running my system at the moment. I don't want to pull back every SQL statement in my system, so I'm going to filter it down by using the Set SQL criteria link at the top right of my screen. This filter will allow me to determine what criteria must be met in order for the SQL to appear in my results set. For example, I just want to take a look at SQL queries that have been executed more than 10 times, so I'll select executions greater than 10. In this case, I also want to see the executions just by the schema Matthew. Finally, let's sort the results by the elapsed time of the SQL statement. My results are presented, and here are all the SQL statements that fit my criteria. I can go through these SQL statements starting from the ones that have the longest elapsed time at the top. I can analyze them and see whether they need to be tuned. I can see the SQL statement itself formatted nicely, and I can get a breakdown of the SQL statement statistics, its details, and a breakdown of what resources it's using, as well as the SQL plan itself.
Perhaps the elapsed time of the statement is unacceptable. If I determine this candidate is good for optimization, I could take the SQL statement and send it directly over to another solution called the SQL Optimizer for Oracle by clicking on this icon here. The SQL Optimizer can automatically rewrite your SQL query for you, suggesting new indexes or Oracle plans to deploy to improve the SQL statement's performance. Here are some alternative ways to rewrite the SQL statement which may perform better.
Back in Spotlight, another way to hunt for SQL statements is by looking at the Oracle trace files. And Spotlight makes that very easy to parse trace files and present the data visually. I can go to this Analyze Trace tab and it'll show me the trace files that have been collected by the Oracle instance. I could select one of these trace files and have Spotlight analyze it with a right click. This will break it down and parse the SQL within the trace file into these tabs here. I could take the SQL statements found in the trace file, sort them by whatever criteria I like, and identify if these SQL statements are ones that I want to optimize.
Now, perhaps you want to browse through the sessions on your system and see if any of them are particularly slow. In this case, you might want to check out the Top Sessions window in Spotlight. This will allow you to see the active sessions on your database, and you could sort them by different criteria like database time or CPU. Selecting the session will show you the session's properties, including the most recently executed SQL statement and the resources they're using. This could give you some insight if they're running poorly written SQL statements that need to be optimized. Last but not least, I can use Spotlight's predictive diagnostics to look at my SQL statements which may be running fine today, but predict out into the future which ones may cause me issues down the line.
Predictive diagnostics can be accessed by clicking on this eyeball icon on the toolbar. I'll select Identify Degrading SQL Statements from the menu. In this case, I'm looking ahead three months from now, predicting the elapsed time of the statement. And again, I can filter my statements by using these Set SQL Criteria at the top right. Predictive diagnostics in Spotlight collects information about my SQL statement over time and maps them out. Using analytics based on these trends, Spotlight gives me insight in how performance of these SQL statements will degrade over time. For this statement, it's currently using less than 1% of my total elapsed time, but three months from now it'll grow to 1.1 percent, an exponential increase of 368%. All the statements highlight in red have exponential growth and elapsed time, so these are ones I want to be sure to review. Once again, the SQL statement is displayed for me at the bottom with options to review the explained plan or send it over to the SQL Optimizer.
So I showed you a few different ways to find poor performing SQL queries. Once you find them, you could send them back to the developer to be rewritten, rewrite it yourself, or you could send them over to the SQL Optimizer for further analysis and suggestions. All this information is found quickly at your fingertips with Spotlight on Oracle, helping you to maintain the best performance for your database.
04:54