By Connor McDonald
In my previous article on SQL tuning, I discussed the importance of stepping back from focusing on a purely technical approach to tuning SQL. Before tuning SQL statements, the practitioners charged with the task must first understand the business requirement of the application components that execute that SQL, so that they can validate the correctness of the SQL statements to meet that requirement.
Even when the SQL’s correctness is validated, it is still important to have the right focus when tuning an application. As I mentioned in the previous article, no business has a “Let’s make sure SQL runs fast” model; they have core functions that drive revenue. It just so happens that these core functions are often backed by IT applications that execute SQL statements.
Hence the processing cost of a SQL statement is not linked to business success unless that cost has an impact on the delivery of those core business functions, and the real task of a database developer is to tune the delivery of the core functions. For example, if a daily report takes two hours to execute and a consequence is that critical information is not available to management for making strategic decisions about business operations, then a “tuning” exercise might be simply to automate the running of the report before standard working hours, so that the report output is available when managers come into the office. Although it is seductive to focus solely on tuning code—after all, most developers chose their IT careers to become coders—the delivery of business functionality must be the driving force for tuning efforts. It can also be much more rewarding for you as a developer to think this way, because when you focus on ensuring that users of the application are successful in performing their business functions, the same users will sing your praises when you improve their experience with the applications you build.
The best way to identify the pain points for users of your application is also the simplest. Communicate directly with the users of your application. Nothing impresses business users more than taking the time to sit alongside them to get a firm understanding of where tuning efforts are best focused, and often this can even reduce the amount of effort required. For example, I had a client who detected huge response times on a key screen of its application, and I fully expected venting of anger about this performance when I visited. However, it turned out that the organization’s business workflow meant that the slow performance didn’t have any impact at all—the staff just launched the application each morning at the beginning of the departmental standup meeting. In fact, the most important enhancement request was simply to change the text on one of the buttons to make its function less ambiguous for the junior staff. A five-minute fix, and they were wowed by the quick turnaround time and the “personal touch” of having an IT person liaise with them directly. The other benefit of direct contact with your users is that even if a performance problem cannot be solved immediately, or perhaps ever, they normally appreciate that you are at least reaching out to them and are aware of the things that matter to them.
If it is possible to have direct interaction with application users and have them demonstrate a performance issue on a particular business function, the task becomes identifying whether the issue is being caused by slow SQL execution and, if so, what those poorly performing SQL statements are. The definitive tool for performance capture and analysis is Oracle’s SQL trace capability. Tracing is enabled just before execution of the business function and disabled immediately afterward, thus capturing only the time frame and the SQL executions of interest. A detailed look at tracing is beyond the scope of this article, but Arup Nanda covers an introduction to tracing in this Oracle Magazine article.
As powerful as it is, there are some challenges with tracing:
If tracing cannot be successfully utilized, there are still other options available for homing in on the SQL statements that are directly impacting business functions.
Automatic Workload Repository. The Automatic Workload Repository feature of Oracle Database collects and analyzes performance statistics in the database to assist with problematic SQL detection. The gathered data is stored both in memory and in the database, and the data includes SQL execution and performance statistics. An Automatic Workload Repository snapshot is a capture of the state of a database at a point in time, so when you’re analyzing a performance issue encountered by a user, a delta of the snapshots before and after the time when the performance issue occurred can be used to discover problematic SQL statements that most probably were related to the issue. Much as with the trace methodology, the ideal scenario is to take snapshots immediately before and after replicating a performance problem.
Active Session History. The Active Session History feature of Oracle Database samples active database sessions each second, writing the data to memory and persistent storage. An active session is a session that is using CPU and is not sitting idle waiting for a request from an application and/or user. Slow-running SQL statements can also be detected by Active Session History, because a session running a SQL statement for, say, 10 seconds will have 10 consecutive entries in the V$ACTIVE_SESSION_HISTORY view for the same SQL_ID/SQL_EXEC_ID columns pairing.
Automatic Workload Repository and Active Session History are powerful tools, because they allow for after-the-fact problem analysis. A business user may report a performance problem that occurred a few hours beforehand. Even if that problem cannot be replicated, the Automatic Workload Repository and Active Session History data can often be used to detect the problematic SQL statements for that user.
However, both Active Session History and Automatic Workload Repository are additional license options of Oracle Database, Enterprise Edition, so they may not be available for your performance tuning analysis. If that’s the case, you can use the dynamic performance views to find poorly performing SQL statements in a database. Marrying this information with a business user’s performance issue is more difficult, but with a little luck, the most resource-intensive SQL statements will have some identifying characteristics that allow a basic level of confidence in mapping them to business functions. The V$SQL performance view lists SQL statements in the shared SQL area and presents numerous performance measurements alongside each, as shown in Listing 1.
Here are the most commonly used V$SQL columns for providing performance measurement on SQL statements:
Those columns provide the performance characteristics of each SQL statement. They can be combined with other V$SQL columns to tie SQL statements back to their root business functions:
Although V$SQL is a very powerful view for discovering information about SQL statements currently in the shared SQL area, there is a risk to using it. Querying V$SQL occasionally for a particular performance issue that occurs rarely is fine, but if you are diagnosing a system that is under severe duress, then hammering away at V$SQL can create problems of its own. A system under duress is quite possibly experiencing extreme contention in its shared memory structures, and by aggressively querying V$SQL, you may be adding to that workload and/or contention. This appears to be a catch-22 situation: How do you diagnose a struggling system if diagnosing it via queries to V$SQL will make it struggle more? For such cases, there is an alternative performance view, V$SQLSTATS, that contains almost as much information as V$SQL. (Below is a summary, but see the documentation for a more detailed description of V$SQLSTATS.)
V$SQLSTATS displays basic performance statistics for SQL cursors and contains one row per SQL statement (that is, one row per unique value of SQL_ID). The column definitions for columns in V$SQLSTATS are identical to those in the V$SQL and V$SQLAREA views. However, the V$SQLSTATS view differs from V$SQL and V$SQLAREA in that it is faster, more scalable, and has a greater data retention (the statistics may still appear in this view, even after the cursor has been aged out of the shared pool). Note that V$SQLSTATS contains a subset of columns that appear in V$SQL and V$SQLAREA.My general approach is to find poorly performing SQL statements initially by querying V$SQLSTATS and then if I need to determine more information about individual SQL statements such as the MODULE or ACTION column values, I will access V$SQL, using the SQL_ID of just those poorly performing SQL statements. Using V$SQLSTATS isn’t an invitation to smash away continuously at the database, but by avoiding V$SQL, you are steering clear of a critical shared-memory structure that many database sessions are typically competing for. V$SQLSTATS has been available since Oracle Database 10g.
To recap, the developer should always focus on the specific business functions for the user and then use the trace methodology and tools such as Automatic Workload Repository, Active Session History, and the performance views to identify the SQL statements that are the cause of poor response times for those business functions.
Now that the SQL statements have been identified, I recommend performing one extra step before diving into tuning techniques, and that is to expand the SQL text. Expanding a SQL statement reveals what the database will truly be running when it is presented with the text of that SQL statement. For example, if I have a simple query
SQL> select * from EMP_LIST;
it is only through my prior knowledge that I created a synonym via
SQL> create synonym EMP_LIST as SCOTT.EMP;
that I know that I am actually querying the SCOTT.EMP table. Rather than taking each referenced object in the SQL statement and digging into the data dictionary to see if that object is a synonym or a view and recursively continuing that process until all objects are resolved, I can use the supplied DBMS_UTILITY database package to expand a SQL statement to reveal what the database will ultimately “see.” Listing 2 uses the above query as an example.
Listing 2: Expanding the SQL text of a statement
SQL> variable c clob SQL> begin 2 dbms_utility.expand_sql_text 3 ( 'select * from EMP_LIST',:c); 4 end; 5 / PL/SQL procedure successfully completed. SQL> print c C ————————————————————————————————————————————————————————————————————— SELECT "A1"."EMPNO" "EMPNO","A1"."ENAME" "ENAME","A1"."JOB" "JOB", "A1"."MGR" "MGR","A1"."HIREDATE" "HIREDATE","A1"."SAL" "SAL", "A1"."COMM" "COMM","A1"."DEPTNO" "DEPTNO" FROM "SCOTT"."EMP" "A1"
Expanding the SQL text may seem like a waste of time and effort, but here is another example of why looks can be deceiving when you’re faced with a SQL statement for tuning. Listing 3 shows a view that joins two database dictionary objects, appropriately named LOOKS_SO_INNOCENT.
Listing 3: View definition for LOOKS_SO_INNOCENT
SQL> create or replace 2 view LOOKS_SO_INNOCENT as 3 select 4 o.owner, 5 o.created, 6 s.bytes, 7 s.tablespace_name 8 from 9 dba_segments s, 10 all_objects o 11 where o.owner = s.owner 12 and o.object_name = s.segment_name; View created.
When I expanded the SELECT * FROM LOOKS_SO_INNOCENT query via the DBMS_UTILITY package, the resulting formatted SQL statement was more than 1,000 lines, or approximately 25 pages of standard text on a printed page (and available in Listing 4). It can be puzzling to see a single-line query performing poorly—but not so much when that query is expanded to 1,000 lines.
In the previous article in this series, I explained the importance of validating that a SQL statement is correct in terms of its construction and is solving the intended business requirement before diving into the various tuning techniques at your disposal.
Now continuing in the vein of taking a holistic approach, I’ve explained the importance of focusing on tuning from the perspective of the business user rather than the SQL statements, because the worst-performing SQL in the database is not necessarily related to the business function that is causing the most frustration for a user. Remember that the goal is to tune the user experience, and that is what drives identification of the SQL statements to be tuned.
In the next article, I’ll explore how I proceed after a set of poorly performing SQL statements has been identified.
LEARN more about SQL tuning.
TRY more about Oracle Autonomous Transaction Processing.
DOWNLOAD Oracle Database 18c.
Illustration by Wes Rowell
Connor McDonald is a former Oracle ACE Director who has joined the Oracle Developer Advocates Team. Over the past 25 years, he has worked with systems in Australia, the United Kingdom, Southeast Asia, Western Europe, and the United States. He has co-authored three books and has been a popular speaker at Oracle conferences around the world, specializing in topics regarding the database engine and PL/SQL.