The Challenge of Dynamic SQL with Literals
The following example is taken from a PeopleSoft General Ledger system. The SQL was generated by the nVision reporting tool (some literal values have been obfuscated).
SELECT L4.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT)
FROM PS_XX_SUM_XXXXX_VW A, PSTREESELECT10 L4, PSTREESELECT10 L2
WHERE A.LEDGER='X_UKMGT'
AND A.FISCAL_YEAR=2022 AND A.ACCOUNTING_PERIOD=1
AND L4.SELECTOR_NUM=415 AND A.CHARTFIELD3=L4.RANGE_FROM_10
AND L2.SELECTOR_NUM=416 AND A.ACCOUNT=L2.RANGE_FROM_10
AND (A.DEPTID BETWEEN '10000' AND '18999' OR
A.DEPTID BETWEEN '20000' AND '29149' OR A.DEPTID='29156' OR
A.DEPTID='29158' OR A.DEPTID BETWEEN '29165' AND '29999' OR A.DEPTID
BETWEEN '30000' AND '39022' OR A.DEPTID BETWEEN '39023' AND '39999' OR
A.DEPTID BETWEEN '40000' AND '49999' OR A.DEPTID BETWEEN '50000' AND
'59999' OR A.DEPTID BETWEEN '60000' AND '69999' OR A.DEPTID BETWEEN
'70000' AND '79999' OR A.DEPTID BETWEEN '80000' AND '89999' OR
A.DEPTID='29150' OR A.DEPTID=' ')
AND A.CHARTFIELD1='0120413'
AND A.CURRENCY_CD='GBP'
GROUP BY L4.TREE_NODE_NUM
Plan hash value: 1653134809
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 27 (100)| | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10006 | 1 | 29 | 27 (63)| 00:00:01 | | | Q1,06 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 1 | 29 | 27 (63)| 00:00:01 | | | Q1,06 | PCWP | |
| 4 | PX RECEIVE | | 1 | 29 | 27 (63)| 00:00:01 | | | Q1,06 | PCWP | |
| 5 | PX SEND HASH | :TQ10005 | 1 | 29 | 27 (63)| 00:00:01 | | | Q1,05 | P->P | HASH |
| 6 | HASH GROUP BY | | 1 | 29 | 27 (63)| 00:00:01 | | | Q1,05 | PCWP | |
| 7 | HASH JOIN | | 1 | 29 | 27 (63)| 00:00:01 | | | Q1,05 | PCWP | |
| 8 | JOIN FILTER CREATE | :BF0000 | 1 | 16 | 25 (68)| 00:00:01 | | | Q1,05 | PCWP | |
| 9 | PX RECEIVE | | 1 | 16 | 25 (68)| 00:00:01 | | | Q1,05 | PCWP | |
| 10 | PX SEND HYBRID HASH | :TQ10003 | 1 | 16 | 25 (68)| 00:00:01 | | | Q1,03 | P->P | HYBRID HASH|
| 11 | STATISTICS COLLECTOR | | | | | | | | Q1,03 | PCWC | |
| 12 | VIEW | VW_GBC_10 | 1 | 16 | 25 (68)| 00:00:01 | | | Q1,03 | PCWP | |
| 13 | HASH GROUP BY | | 1 | 67 | 25 (68)| 00:00:01 | | | Q1,03 | PCWP | |
| 14 | PX RECEIVE | | 1 | 67 | 25 (68)| 00:00:01 | | | Q1,03 | PCWP | |
| 15 | PX SEND HASH | :TQ10002 | 1 | 67 | 25 (68)| 00:00:01 | | | Q1,02 | P->P | HASH |
| 16 | HASH GROUP BY | | 1 | 67 | 25 (68)| 00:00:01 | | | Q1,02 | PCWP | |
| 17 | HASH JOIN | | 60 | 4020 | 24 (67)| 00:00:01 | | | Q1,02 | PCWP | |
| 18 | JOIN FILTER CREATE | :BF0001 | 60 | 3120 | 22 (73)| 00:00:01 | | | Q1,02 | PCWP | |
| 19 | PX RECEIVE | | 60 | 3120 | 22 (73)| 00:00:01 | | | Q1,02 | PCWP | |
| 20 | PX SEND HYBRID HASH | :TQ10000 | 60 | 3120 | 22 (73)| 00:00:01 | | | Q1,00 | P->P | HYBRID HASH|
| 21 | STATISTICS COLLECTOR | | | | | | | | Q1,00 | PCWC | |
| 22 | PX BLOCK ITERATOR | | 60 | 3120 | 22 (73)| 00:00:01 | 29 | 29 | Q1,00 | PCWC | |
| 23 | MAT_VIEW REWRITE ACCESS INMEMORY FULL| PS_XX_SUM_XXXXX_MV | 60 | 3120 | 22 (73)| 00:00:01 | 29 | 29 | Q1,00 | PCWP | |
| 24 | PX RECEIVE | | 306 | 4590 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 25 | PX SEND HYBRID HASH | :TQ10001 | 306 | 4590 | 2 (0)| 00:00:01 | | | Q1,01 | P->P | HYBRID HASH|
| 26 | JOIN FILTER USE | :BF0001 | 306 | 4590 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 27 | PX BLOCK ITERATOR | | 306 | 4590 | 2 (0)| 00:00:01 | 416 | 416 | Q1,01 | PCWC | |
| 28 | TABLE ACCESS STORAGE FULL | PSTREESELECT10 | 306 | 4590 | 2 (0)| 00:00:01 | 416 | 416 | Q1,01 | PCWP | |
| 29 | PX RECEIVE | | 202 | 2626 | 2 (0)| 00:00:01 | | | Q1,05 | PCWP | |
| 30 | PX SEND HYBRID HASH | :TQ10004 | 202 | 2626 | 2 (0)| 00:00:01 | | | Q1,04 | P->P | HYBRID HASH|
| 31 | JOIN FILTER USE | :BF0000 | 202 | 2626 | 2 (0)| 00:00:01 | | | Q1,04 | PCWP | |
| 32 | PX BLOCK ITERATOR | | 202 | 2626 | 2 (0)| 00:00:01 | 415 | 415 | Q1,04 | PCWC | |
| 33 | TABLE ACCESS STORAGE FULL | PSTREESELECT10 | 202 | 2626 | 2 (0)| 00:00:01 | 415 | 415 | Q1,04 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$6240F0FF
12 - SEL$B80655F7 / VW_GBC_10@SEL$9C8D6CC0
13 - SEL$B80655F7
23 - SEL$B80655F7 / PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6
28 - SEL$B80655F7 / L2@SEL$1
33 - SEL$6240F0FF / L4@SEL$1
… Plan
SQL Plan Force Matching SQL Plan Parse
# OPRID RUNCNTLID ACTION SQL_ID Hash Value Signature IDs Execs Secs Table Name
-- ------------ ---------------------- -------------------------------- ------------- ------------ --------------------- ------ ------ ------- ------------------
1 NVISION NVS_RPTBOOK_99 PI=9984520:UKGL999I:12345 01g5hvs91k4hn 1653134809 1995330195085985689 276 276 2843 PS_XX_SUM_XXXXX_MV
…
SQL Parse involves checking the statement is syntactically correct, and that the user has permission to access the objects, then during the SQL optimization stage the optimizer decides how to execute the statement before it moves to row source generation.
If the statement has been parsed previously and is still in the shared pool, Oracle can skip the optimization and row source generation stages. This is often called soft parse.
SQL Optimization
During the optimization stage, the optimizer calculates the 'cost' of different possible execution plans. Depending upon the SQL, the optimizer considers different table join orders, different table join methods, and different SQL transformations. The optimizer cost is an estimation of the time that it will take to execute a particular plan. The unit of cost is roughly equivalent to the duration of a single block read. More expensive plans are abandoned as they become more expensive than the cheapest known plan so far. Thus the 'cost-based' optimizer produces the cheapest plan. However, the process of optimization consumes time and CPU.
If I write SQL that is executed many times with bind variables rather than literals, then I should avoid some hard parses and the associated CPU consumption. Oracle has always recommended using bind variables rather than literals to improve performance as well as protect against SQL injection. However, there are many applications that still use literals, particularly in dynamically generated SQL. Every statement has to be hard parsed, and the cumulative CPU consumption can start to become significant. PeopleSoft is one such application that does this in some areas of the product, but it is by no means an isolated example.
Oracle produced a feature called Cursor Sharing. Literals in statements are automatically converted to bind variables. It can be very effective. It does reduce SQL parse, but can sometimes also produce undesirable side effects where the execution plan may not change as the bind variable values change.
Hints
Hints are directives to the optimizer. They tell it to do something or more generally not to do something else. If I were to add some optimizer hints to a statement that will produce the same, or a similar, execution plan, then the optimizer should do less work, consume less CPU, and less time coming to the same or similar conclusion.
For example, if I add a LEADING hint to force the optimizer to start with a particular object, that will reduce the number of join orders to be considered.
- A two-table query has 2 possible join orders; a LEADING hint will reduce it to 1.
- A three-table query has 6 possible join orders; a LEADING hint on a single table will reduce it to 2.
N.B. SQL Profiles require the SQL Tuning pack licence.
Example SQL Profile
I don't have to use the full outline of hints from the execution plan, I have chosen to apply just a few.
- LEADING(L2): I want the query to start with the dimension table PSTREESELECT10. This will result in a change to the execution plan
- REWRITE: PS_XX_SUM_XXXXX_MV is a materialized view built on the view PS_XX_SUM_XXXXX_VW of an underlying summary ledger. Rewriting the SQL to use the materialized view is a cost-based decision. Oracle usually decides to rewrite it to use the materialized view, but I want to ensure that this always happens with this hint.
- NO_PARALLEL: This query selects only a single accounting period, so it is only scanning a single partition, therefore I don't want to invoke a parallel query.
- PX_JOIN_FILTER(PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6): The dimension table is equijoined to the fact table. Therefore, it is a good candidate for using a Bloom filter on the look-up fact table. This doesn't always happen naturally on this statement. I have had to use the query block name taken from the execution plan of the rewritten statement. The query block name is stable, it is a hash value based on the object name and the operation.
set serveroutput on
DECLARE
l_sql_text CLOB;
l_signature NUMBER;
h SYS.SQLPROF_ATTR;
…
BEGIN
…
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[NO_PARALLEL]',
q'[LEADING(L2)]',
q'[PX_JOIN_FILTER(PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6)]',
q'[REWRITE]',
q'[END_OUTLINE_DATA]');
l_signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(l_sql_text);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => l_sql_text,
profile => h,
name => 'NVS_UKGL999I_FUNC_ACEXP1',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => TRUE);
…
END;
/Note that the SELECTOR_NUM and CHARTFIELD1 predicates have changed.
SELECT L4.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT)
FROM PS_XX_SUM_XXXXX_VW A, PSTREESELECT10 L4, PSTREESELECT10 L2
WHERE A.LEDGER='X_UKMGT'
AND A.FISCAL_YEAR=2023 AND A.ACCOUNTING_PERIOD=1
AND L4.SELECTOR_NUM=433 AND A.CHARTFIELD3=L4.RANGE_FROM_10
AND L2.SELECTOR_NUM=434 AND A.ACCOUNT=L2.RANGE_FROM_10
AND (A.DEPTID BETWEEN '10000' AND '18999' OR
A.DEPTID BETWEEN '20000' AND '29149' OR A.DEPTID='29156' OR
A.DEPTID='29158' OR A.DEPTID BETWEEN '29165' AND '29999' OR A.DEPTID
BETWEEN '30000' AND '39022' OR A.DEPTID BETWEEN '39023' AND '39999' OR
A.DEPTID BETWEEN '40000' AND '49999' OR A.DEPTID BETWEEN '50000' AND
'59999' OR A.DEPTID BETWEEN '60000' AND '69999' OR A.DEPTID BETWEEN
'70000' AND '79999' OR A.DEPTID BETWEEN '80000' AND '89999' OR
A.DEPTID='29150' OR A.DEPTID=' ')
AND A.CHARTFIELD1='0051001'
AND A.CURRENCY_CD='GBP'
GROUP BY L4.TREE_NODE_NUM
Plan hash value: 3033847137
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 214 (100)| | | |
| 1 | SORT GROUP BY | | 5 | 400 | 214 (62)| 00:00:01 | | |
| 2 | HASH JOIN | | 2347 | 183K| 213 (62)| 00:00:01 | | |
| 3 | HASH JOIN | | 2347 | 153K| 210 (63)| 00:00:01 | | |
| 4 | JOIN FILTER CREATE | :BF0000 | 306 | 4590 | 3 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE SINGLE | | 306 | 4590 | 3 (0)| 00:00:01 | 434 | 434 |
| 6 | TABLE ACCESS STORAGE FULL | PSTREESELECT10 | 306 | 4590 | 3 (0)| 00:00:01 | 434 | 434 |
| 7 | JOIN FILTER USE | :BF0000 | 26468 | 1344K| 206 (64)| 00:00:01 | | |
| 8 | PARTITION RANGE SINGLE | | 26468 | 1344K| 206 (64)| 00:00:01 | 42 | 42 |
| 9 | MAT_VIEW REWRITE ACCESS INMEMORY FULL| PS_XX_SUM_XXXXX_MV | 26468 | 1344K| 206 (64)| 00:00:01 | 42 | 42 |
| 10 | PARTITION RANGE SINGLE | | 202 | 2626 | 3 (0)| 00:00:01 | 433 | 433 |
| 11 | TABLE ACCESS STORAGE FULL | PSTREESELECT10 | 202 | 2626 | 3 (0)| 00:00:01 | 433 | 433 |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$38F8C49D
6 - SEL$38F8C49D / L2@SEL$1
9 - SEL$38F8C49D / PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6
11 - SEL$38F8C49D / L4@SEL$1
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
U - NO_PARALLEL
…
1 - SEL$38F8C49D
- LEADING(L2)
- REWRITE
9 - SEL$38F8C49D / PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6
- PX_JOIN_FILTER(PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6)
Note
-----
…
- SQL profile "NVS_UKGL999I_FUNC_ACEXP1" used for this statement
- The new execution plan does indeed start with the dimension table
- The query was rewritten to use the materialized view
- A Bloom filter was used on the materialized view that is now the fact table
- The NO_PARALLEL hint wasn't used because Oracle chose not to parallelise this statement anyway.
Plan
SQL Plan Force Matching SQL Plan Parse
# OPRID RUNCNTLID ACTION SQL_ID Hash Value Signature IDs Execs Secs Table Name
-- ------------ ---------------------- -------------------------------- ------------- ------------ --------------------- ------ ------ ------- ------------------
…
1 NVISION NVS_RPTBOOK_99 PI=9984933:UKGL278I:12345 03nwc4yy1r1r7 3033847137 1995330195085985689 138 138 1428 PS_XX_SUM_XXXXX_MV
In this case, adding these hints with a SQL Profile has halved the time spent parsing this set of SQL statements.
Posts
No comments :
Post a Comment