9.6 Oracle Database Performance Views for Tuning SQL

---

Most performance views are applicable to physical and configuration tuning and will be covered in Part III. Additionally the Oracle Database Wait Event Interface will be covered in Part III, which utilizes the SQL code views described here.

There are four views we will examine in this section: V$SQLAREA, V$SQL, V$SQLTEXT, and V$SQL_PLAN. These performance views can be used to find poorly performing queries. Once again most of the problems that can be highlighted with these performance views should not exist if SQL code is properly built in the first place, as described in previous chapters and reiterated in this one. The first three of these performance views access SQL statements parsed and resident in the SGA library cache. V$SQL_PLAN contains currently cached query execution plans or actual execution plans.

9.6.1 Finding Cached SQL Code

• V$SQLAREA.   Contains part of the text of each SQL query up to 1,000 characters plus a lot of statistics. Interesting columns in this performance view are:

  • SQL_TEXT

  • SORTS

  • FETCHES

  • EXECUTIONS

  • LOADS

  • PARSE_CALLS

  • DISK_READS

  • BUFFER_GETS

  • ROWS_PROCESSED

• V$SQL.   This view is similar to V$SQLAREA except with more statistics and without aggregation on rows as in V$SQLAREA. Interesting columns in this performance view in addition to those in V$SQLAREA are:

  • OPTIMIZER_COST

  • CPU_TIME

  • ELAPSED_TIME

• V$SQLTEXT.   Contains the entire SQL query text divided up into separate rows of 64 characters each.

  Tip 

  Much of the statistical information in these previous three performance views is part of physical and configuration tuning and will be covered in Part III if appropriate. Additionally a lot of information that can be gathered from these performance views can be retrieved using SQL Trace and TKPROF, amongst various other tools.

• V$SQL_PLAN.   Cached execution plans of recently executed SQL statements can be viewed using the V$SQL_PLAN performance view. The EXPLAIN PLAN command generates potential query plans into the PLAN_TABLE. On the contrary, the V$SQL_PLAN view contains actual query execution plans for recently executed SQL statements. Three other related performance views are V$SQL_PLAN_STATISTICS, V$SQL_PLAN_ STATISTICS_ALL, and the V$SQL_WORKAREA queries.

Examining SQL Code

Let's start with V$SQLAREA. I could hook from V$SQLAREA into currently active Accounts schema sessions but I am not doing that because the queries simply get horrendously complicated. This information is reserved for an examination of the Oracle Database Wait Event Interface in Part III. At this stage we will focus on SQL code issues exclusively. Find SQL text for a particular user as follows:

SELECT * FROM v$sqlarea WHERE parsing_user_id =       (SELECT user_id FROM dba_users WHERE username =          'ACCOUNTS');

Now we could retrieve specific columns and sort in descending order to get the hardest-hitting SQL code statements listed first. Quite often the number of executions is the most significant factor.

SELECT executions, disk_reads, buffer_gets, rows_processed,    sorts, sql_text FROM v$sqlarea WHERE parsing_user_id =          (SELECT user_id FROM dba_users WHERE username =             'ACCOUNTS') ORDER BY executions DESC;

The V$SQL performance view gives us some slightly different information. The following query is sorted in decreasing Optimizer cost order.

SELECT sa.rows_processed, s.optimizer_cost, s.cpu_time,    s.elapsed_time, sa.sql_text FROM v$sqlarea sa, v$sql s WHERE sa.parsing_user_id =       (SELECT user_id FROM dba_users WHERE username =          'ACCOUNTS')      AND s.sql_text = sa.sql_text ORDER BY s.optimizer_cost DESC;

Now let's join the V$SQLAREA and V$SQLTEXT views to retrieve the entire SQL code text string for each SQL code statement in V$SQLAREA. We will also include some data: the first five rows.

SELECT * FROM( SELECT st.sql_text FROM v$sqlarea sa, v$sqltext st WHERE sa.hash_value = st.hash_value AND sa.address =     st.address AND sa.parsing_user_id =       (SELECT user_id FROM dba_users WHERE username =          'ACCOUNTS') ORDER BY st.hash_value, st.address, st.piece) WHERE ROWNUM <= 5;     SQL_TEXT ----------------------------------------------------------- INSERT into stockmovement values(stockmovement_seq.nextval,    :b4,(:b3*-1),:b2,:b1) returning stockmovement_id INTO :b0 INSERT into generalledger values (generalledger_seq.nextval,    :b1, ABS(:b2 -:b3 ),0,:b4 ) SELECT transaction_id from transactions where     order_id =:b1

I can also use the V$SQLTEXT_WITH_NEWLINES view to make the output a little more readable. Again the first five rows have been included.

SELECT * FROM( SELECT st.sql_text FROM v$sqlarea sa, v$sqltext_with_newlines st WHERE sa.hash_value = st.hash_value AND sa.address =     st.address AND sa.parsing_user_id =       (SELECT user_id FROM dba_users WHERE username =          'ACCOUNTS') ORDER BY st.hash_value, st.address, st.piece) WHERE ROWNUM <= 5;     SQL_TEXT ----------------------------------------------------------- INSERT into stockmovement values(stockmovement_seq.nextval,    :b4,(:b3*-1),:b2,:b1) SELECT * FROM( ith_newlines st ss = st.address 

Hard-Hitting SQL Code

Significant factors for finding poorly performing SQL code measurements are rows processed, disk and buffer reads per row, sorts per row, parses per execution, amongst others. So let's find some simple ratios using the various performance views to find the worst-performing SQL code. Let's start with V$SQLAREA.

Using V$SQLAREA

V$SQLAREA gives us a window into parsing and execution statistics.

Executions

The following query finds the 10 queries executed the most often.

SELECT * FROM ( SELECT executions "Execs", rows_processed "Rows", sql_text FROM v$sqlarea WHERE parsing_user_id =       (SELECT user_id FROM dba_users WHERE username =          'ACCOUNTS') ORDER BY executions DESC ) WHERE ROWNUM <= 10;      Execs    Rows  SQL_TEXT ------  ------  ----------------------------------------------- 107785  107785  INSERT into cashbookline values(:b3,:b2,:b1) 107785  107785  INSERT into ordersline values(:b4,:b3,                   :b2,:b1) 107785  107782  INSERT into transactionsline values(:b4,                   :b3,:b2,:b 107785  107785  INSERT into stockmovement values                   (stockmovement_seq  81823   81823  SELECT to_number(to_char                   (SYSTIMESTAMP,'FF3'))+1 fr  53909   53909  SELECT systimestamp FROM sys.dual  34208       0  COMMIT  32391   32391  INSERT into generalledger values                   (generalledger_se  32391   32391  INSERT into generalledger values                   (generalledger_se  22438   22438  INSERT into cashbookline values(:b3,:b2,                   (:b1*-1)) 

Disk + Buffer Reads per Row

This query looks at highest data access queries.

SELECT * FROM( SELECT ROUND((disk_reads + buffer_gets)/rows_processed)    "Reads/Row"       ,disk_reads + buffer_gets "Reads"       ,rows_processed "Rows", sql_text FROM v$sqlarea WHERE parsing_user_id =       (SELECT user_id FROM dba_users WHERE username =          'ACCOUNTS') AND rows_processed > 0 AND (disk_reads + buffer_gets) > 0 ORDER BY ROUND((disk_reads + buffer_gets)/rows_processed)    DESC ) WHERE ROWNUM <= 10;     Reads/Row     Reads   Rows  SQL_TEXT ---------  --------   ----  ----------------------------------      9971     29912      3  select count(*) from generalledger      5562      5562      1  select min(id),min(parent_id)                               from v$sq      5265      5265      1  select count(*) from v$sql_plan      1330      3991      3  SELECT TRIM(LEVEL)||'. '||LPAD                               (' ', LE      1325      3974      3  SELECT TRIM(LEVEL)||'. '||LPAD                               (' ', LE      1310      3929      3  select p.operation, p.options,                               p.objec      1308      3925      3  SELECT TRIM(LEVEL)||'. '||LPAD                               (' ', LE      1303      3909      3  select s.sql_text, p.operation,                               p.opti       897   3719117   4148  SELECT min(order_id),max(order_id)                               from       841  14626647  17386  DECLARE job BINARY_INTEGER:= :job;                               nex

Rows per Sort

Fewer rows per sort may indicate a lack of indexing, lack of use of indexes or superfluously ordered SQL code statements. Too much sorting!

SELECT * FROM( SELECT ROUND(rows_processed/sorts) "Rows/Sort"       ,rows_processed "Rows"       ,Sorts "Sorts", sql_text FROM v$sqlarea WHERE parsing_user_id =       (SELECT user_id FROM dba_users WHERE username =          'ACCOUNTS') AND rows_processed > 0 AND sorts > 0 ORDER BY ROUND(rows_processed/sorts) ASC ) WHERE ROWNUM <= 10;     Reads/Row     Reads   Rows  SQL_TEXT ---------  --------   ----  ----------------------------------         1     15616  31232  SELECT a.supplier_id, a.price from (         1         3      3  SELECT (disk_reads + buffer_gets)/                               row         2         3      2  select p.operation, p.options,                               p.objec         2         3      2  select s.sql_text, p.operation,                               p.opti         2        27     12  SELECT * FROM ( SELECT executions,                               rows         3        10      4  SELECT * FROM( SELECT ROUND((sorts)/r         3        10      4  SELECT * FROM ( SELECT ROUND                               (parse_cal         3        10      4  SELECT * FROM ( SELECT ROUND                               (fetches/r         3        20      8  SELECT * FROM ( SELECT ROUND                               (buffer_ge         3        10      4  SELECT * FROM ( SELECT ROUND                               ((buffer_g

Rows per Fetch

The fewer rows fetched for each fetch, the worse the performance. We are looking for problems not successes.

SELECT * FROM ( SELECT ROUND(rows_processed/fetches) "Rows/Fetch"       ,rows_processed "Rows", fetches "Fetches",          sql_text FROM v$sqlarea WHERE parsing_user_id =       (SELECT user_id FROM dba_users WHERE username =          'ACCOUNTS') AND rows_processed > 0 AND fetches > 0 ORDER BY ROUND(rows_processed/fetches) ASC ) WHERE ROWNUM <= 10;     Rows/Fetch   Rows  Fetches  SQL_TEXT ----------  -----  -------  ------------------------------          1      2        2  select user from dual          1      4        4  SELECT USER FROM DUAL          1  14532    14532  SELECT max(stock_id) from stock          1      1        1  select count(*) from v$sql_plan          1     10       16  SELECT job from user_jobs          1  19060    19060  select SYSDATE+1/86400 from dual          1      4        4  SELECT DECODE('A','A','1','2')                               FROM DUAL          1 14532 14532 SELECT max(customer_id) from                               customer          1 3 3 select count(*) from                               generalledger          1 59213 59213 SELECT systimestamp FROM sys.dual

Parses per Execution

More parsing means that previously parsed SQL code in the shared pool is not being reused efficiently. This can indicate lack of SQL code bind variables. The CURSOR_SHARING parameter SIMILAR or FORCE settings can help.

SELECT * FROM ( SELECT ROUND(parse_calls/executions) "Parses/Exec"       ,parse_calls "Parses", executions "Execs", sql_text FROM v$sqlarea WHERE parsing_user_id =       (SELECT user_id FROM dba_users WHERE username =          'ACCOUNTS') AND parse_calls > 0 AND executions > 0 ORDER BY ROUND(parse_calls/executions) ASC ) WHERE ROWNUM <= 10;     Parses/Exec  Parses   Execs  SQL_TEXT -----------  ------  ------  ---------------------------------           0   17490   54306  SELECT systimestamp FROM sys.dual           0   13315  108638  INSERT into cashbookline                                values(:b3,           0   13315  108638  INSERT into ordersline                                values(:b4,:b           0   13315  108638  INSERT into transactionsline                                values(           0    2364   22646  INSERT into ordersline                                values(:b4,:b           0    2364   22646  INSERT into cashbookline                                values(:b3,           0    2364   22646  INSERT into stockmovement                                values(sto           0     685    3757  INSERT into stockmovement values           0   17490   82425  SELECT to_number(to_char                                (SYSTIMESTAM           0    2364   22646  INSERT into transactionsline                                values(

Disk versus Logical Reads

Traditionally disk I/O is slower than reading data from memory. However, logical memory reads involve CPU and potential cache latch contention overhead to maintain consistency between multiple sessions accessing the same buffer blocks. Logical versus physical disk reads is known as the Buffer Cache Hit Ratio. Changing this ratio is quite often not a performance solution in itself but more likely a symptom or indication of other potential problems. Try not to take this ratio as seriously as most Oracle Database and Oracle-oriented documentation has described in the past. In the extreme a 95%+ Buffer Cache Hit Ratio is an unreliable measure of database performance health. There will be more on this in Part III. In general use any type of ratio as a symptom or an indicator of a possible problem. Do not attempt to tune the ratio, rather find the problem.

Tip 

Current research indicates that logical reads are perhaps as little as only 30 times faster than physical reads, not thousands or hundreds of times faster as previously thought.

COLUMN bchr HEADING "Buffer Cache Hit Ratio" FORMAT a24; SELECT * FROM ( SELECT ROUND((buffer_gets/(buffer_gets+disk_reads))*100)||'%'    AS bchr       ,sql_text FROM v$sqlarea WHERE parsing_user_id =       (SELECT user_id FROM dba_users WHERE username =          'ACCOUNTS') AND buffer_gets > 0 AND disk_reads > 0 ORDER BY ROUND((buffer_gets / (buffer_gets +     disk_reads))*100) ASC ) WHERE ROWNUM <= 10;     Buffer Cache Hit Ratio  SQL_TEXT ----------------------  ------------------------------------- 29%                     SELECT * FROM ( SELECT ROUND                           (buffer_gets / ( 30%                     SELECT * FROM ( SELECT ROUND                           ((buffer_gets / 31%                     SELECT * FROM ( SELECT ROUND                           (fetches/rows_pr 31%                     SELECT * FROM ( SELECT ROUND                           (parse_calls/exe 32%                     SELECT sa.rows_processed,                           s.optimizer_cost, s 32%                     SELECT * FROM ( SELECT executions                           "Execs", ro 33%                     SELECT * FROM ( SELECT ROUND                           ((buffer_gets / 33%                     SELECT * FROM ( SELECT executions,                           rows_proce 33%                     SELECT * FROM ( SELECT executions,                           rows_proce 33%                     SELECT * FROM ( SELECT ROUND                           ((disk_reads + b

Using V$SQL

Now let's use V$SQL to look at Optimizer cost, CPU, and elapsed time.

Optimizer Cost

SELECT * FROM( SELECT s.optimizer_cost "Cost", sa.rows_processed "Rows",     sa.sql_text FROM v$sqlarea sa, v$sql s WHERE sa.parsing_user_id =       (SELECT user_id FROM dba_users WHERE username =          'ACCOUNTS') AND s.sql_text = sa.sql_text ORDER BY s.optimizer_cost DESC ) WHERE ROWNUM <= 10;     Cost    Rows  SQL_TEXT ----  ------  ----------------------------------------------- 2019    4160  INSERT into stockmovement values (:b5,:b4,:b3,: 2019  122208  INSERT into stockmovement values                 (stockmovement_seq. 2019   25474  INSERT into stockmovement values                 (stockmovement_seq. 1677   36755  INSERT into generalledger values                 (generalledger_seq 1677    7394  INSERT into generalledger values                 (generalledger_seq 1677   36755  INSERT into generalledger values                 (generalledger_seq 1677    7394  INSERT into generalledger values                 (generalledger_seq 1540   25474  INSERT into transactionsline values(:b4,:b3,                 (:b2*-1 1540  122205  INSERT into transactionsline values(:b4,:b3,                 :b2,:b1 1518   25474  INSERT into ordersline values(:b4,:b3,                 (:b2*-1),:b1) 

CPU Time

SELECT * FROM( SELECT s.cpu_time "CPU", sa.rows_processed "Rows",    sa.sql_text FROM v$sqlarea sa, v$sql s WHERE sa.parsing_user_id =       (SELECT user_id FROM dba_users WHERE username =          'ACCOUNTS') AND s.sql_text = sa.sql_text ORDER BY s.cpu_time DESC ) WHERE ROWNUM <= 10;     CPU           Rows  SQL_TEXT ----------  ------  ----------------------------------------- 5216000189   19747  DECLARE job BINARY_INTEGER := :job;                       next_date DATE 2578878295    4689  SELECT min(order_id),max(order_id) from                       orders 310826983   122566  INSERT into transactionsline values(:b4,                       :b3,:b2,:b 302695184   122573  INSERT into ordersline values(:b4,:b3,                       :b2,:b1) 297647959    17735  SELECT a.supplier_id, a.price from                       ( select supp 260374370   122588  INSERT into stockmovement values                       (stockmovement_seq 244912201   122569  INSERT into cashbookline values(:b3,                       :b2,:b1) 155974301    15062  INSERT into transactions values                       (transactions_seq.n 147301740    15062  INSERT into cashbook values                       (cheque_seq.nextval,:b3 100884998    36889  INSERT into generalledger values                       (generalledger_se

Elapsed Time

SELECT * FROM( SELECT s.elapsed_time "Time", sa.rows_processed "Rows",    sa.sql_text FROM v$sqlarea sa, v$sql s WHERE sa.parsing_user_id =       (SELECT user_id FROM dba_users WHERE username =          'ACCOUNTS') AND s.sql_text = sa.sql_text ORDER BY s.elapsed_time DESC ) WHERE ROWNUM <= 10;     Time          Rows  SQL_TEXT ----------  ------  --------------------------------------------- 4.4771E+10   19792  DECLARE job BINARY_INTEGER := :job;                       next_date DATE 1.7155E+10    4707  SELECT min(order_id),max(order_id) from                       orders 2809286653   15091  INSERT into transactions values                       (transactions_seq.n 2739120692  122853  INSERT into transactionsline values(:b4,                       :b3,:b2,:b 2649237617  122857  INSERT into ordersline values(:b4,:b3,                       :b2,:b1) 2061587320  122854  INSERT into cashbookline values(:b3,                       :b2,:b1) 1974169102   15091  INSERT into orders values                       (orders_seq.nextval,:b3,n 1885794542   17769  SELECT a.supplier_id, a.price from                       ( select supp 1731725989   36965  INSERT into generalledger values                       (generalledger_se 1601419096  122865  INSERT into stockmovement values                       (stockmovement_seq 

So what was the point in showing all these different sorted versions of the same query? If you look closely you might see some patterns. A number of the sections have a lot of INSERT statements. My high concurrency DBMS_JOBS executed OLTP activity does a lot of INSERT plus a fair amount UPDATE and DELETE activity. Also notice some versions of this query have a lot of SELECT activity. DML and particularly INSERT DML activity is obvious in queries dealing with a high number of executions and CPU time. Most of all the other measurements involve amounts of data found and thus include SELECT statements.

The high INSERT statement activity begs the following question. Why is INSERT activity using up so many resources? Probably because I am inserting multiple rows into the same blocks in the buffer cache and the database files, probably involving both index and data spaces. I am probably getting locking problems with contention for buffer and disk blocks; hot blocks. I could potentially resolve these issues by perhaps changing some primary key integer identifier indexes to reverse key indexes. However, this would probably result in my database growing so fast that I eventually run out of disk space. So for the purposes of maintaining my simulation I will leave my current configuration the way it is.

Examining Cached Query Plans

Now let's examine cached query execution plans in V$SQL_PLAN. This performance view is best queried by matching SQL statements from V$SQLAREA or V$SQL views using the common ADDRESS and HASH_VALUE columns. Using V$SQL_PLAN can be similar to this:

SELECT TRIM(depth)||'. '||LPAD (' ', depth - 1)||operation    ||' '||options||' on '||object_name "Query"       ,cost "Cost"       ,cardinality "Rows"       ,bytes "Bytes" FROM v$sql_plan ORDER BY address, hash_value, parent_id, id;     Query ---------------------------------------- 0. SELECT STATEMENT on 1.  COUNT STOPKEY on 2.   VIEW on 3.    SORT ORDER BY STOPKEY on 4.     NESTED LOOPS OUTER on 5.      NESTED LOOPS OUTER on 5.      TABLE ACCESS CLUSTER on USER$ 6.       FIXED TABLE FULL on X$KQLFXPL 6.       TABLE ACCESS BY INDEX ROWID on OBJ$ 7.        INDEX UNIQUE SCAN on I_OBJ1 6.       INDEX UNIQUE SCAN on I_USER# 0. SELECT STATEMENT on 1.  NESTED LOOPS OUTER on 2.   NESTED LOOPS OUTER on 2.   TABLE ACCESS CLUSTER on USER$ 3.    FIXED TABLE FULL on X$KQLFXPL 3.    TABLE ACCESS BY INDEX ROWID on OBJ$ 4.     INDEX UNIQUE SCAN on I_OBJ1 3.    INDEX UNIQUE SCAN on I_USER# 

That concludes this discussion of how to find problem queries and the tools used to accomplish this. In the next chapter we will look at how to tune SQL code in Oracle Database using Oracle Enterprise Manager tuning tools.

---