Force OR Expansion (OR bind IS NULL condition)
The scenario I encountered (10.2.0.5 Enterprise):
Application code:
This turns out to be more expensive.
Because :B1 is null does not reference columns in the index, the filter happens at the table access step rather than the index, meaning it still accesses every database row, just in a far less efficient way.
The USE_CONCAT hint will not work, because the second predicate does not reference an indexed column, the soution is the OR_EXPAND hint.
The cost is slightly higher, than the original plan (157 vs 155)
Consistent gets is down from 693 to 3.
In the unlikely event that the application does pass through a null bind variable, the cost is only slightly higher than a FTS.
Application code:
select * from cust where ( cust_id = :B1 or :B1 is null );
Changing the application code was not an option, after a solution that didn't involve changing parameters.
Under normal operation, the bind variable is never null, so the application did a FTS for no good reason.
Plan was FTS on CUST table.
Under normal operation, the bind variable is never null, so the application did a FTS for no good reason.
var B1 number ;
exec :B1:=394 ;
set autotrace traceonly;
set timing on;
Execution Plan ---------------------------------------------------------- Plan hash value: 2144666378 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1556 | 256K| 155 (2)| 00:00:02 | |* 1 | TABLE ACCESS FULL| CUST | 1556 | 256K| 155 (2)| 00:00:02 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(:B1 IS NULL OR "CUST_ID"=TO_NUMBER(:B1)) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 693 consistent gets 0 physical reads 0 redo size 2273 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Option 1: index, replace the FTS with a full index scan. (hint: this is a terrible idea)
select /*+ index(cust) */ * from cust where ( cust_id = :B1 or :B1 is null );
Because :B1 is null does not reference columns in the index, the filter happens at the table access step rather than the index, meaning it still accesses every database row, just in a far less efficient way.
Execution Plan
----------------------------------------------------------
Plan hash value: 1015757790
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1556 | 256K| 23919 (1)| 00:04:48 |
|* 1 | TABLE ACCESS BY INDEX ROWID| CUST | 1556 | 256K| 23919 (1)| 00:04:48 |
| 2 | INDEX FULL SCAN | CUST_ID_IX | 31097 | | 66 (2)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:B1 IS NULL OR "CUST_ID"=TO_NUMBER(:B1))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
27156 consistent gets
0 physical reads
0 redo size
2273 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
My solution - Force OR Expansion on the two predicates.The USE_CONCAT hint will not work, because the second predicate does not reference an indexed column, the soution is the OR_EXPAND hint.
select /*+ OR_EXPAND(@SEL$1 CUST@SEL$1 cust_id) */ * from cust where ( cust_id = :B1 or :B1 is null );In this plan - the FTS (line 5) only executes if the bind variable is null.
The cost is slightly higher, than the original plan (157 vs 155)
Consistent gets is down from 693 to 3.
In the unlikely event that the application does pass through a null bind variable, the cost is only slightly higher than a FTS.
Execution Plan ---------------------------------------------------------- Plan hash value: 2276934167 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 31097 | 5132K| 157 (2)| 00:00:02 | | 1 | CONCATENATION | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| CUST | 1 | 169 | 2 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | CUST_ID_IX | 1 | | 1 (0)| 00:00:01 | |* 4 | FILTER | | | | | | |* 5 | TABLE ACCESS FULL | CUST | 31096 | 5132K| 155 (2)| 00:00:02 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("CUST_ID"=TO_NUMBER(:B1)) 4 - filter(:B1 IS NULL) 5 - filter(LNNVL("CUST_ID"=TO_NUMBER(:B1))) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 2273 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
How to apply the hint without changing code?
Use a sql profile.
I pull the sql text from the sql area for matching.
set serveroutput on size unlimited;
declare
v_profile_name varchar2(30);
v_sql_text clob;
v_sql_id varchar2(30):= '<sql_id of problem statement>';
v_hint_1 varchar2(300):= 'OR_EXPAND(@SEL$1 CUST@SEL$1 CUST_ID)';
begin
select sql_fulltext into v_sql_text
from v$sqlarea where sql_id = v_sql_id;
v_profile_name := 'PROFILE_'||v_sql_id||'_MANUAL';
dbms_sqltune.import_sql_profile(
sql_text => v_sql_text,
profile => sqlprof_attr(v_hint_1),
name => v_profile_name,
force_match => true
);
dbms_output.put_line('Profile '||v_profile_name||' created.');
end;
/
This process can be used to inject individual hints into sql statements without using the tuning advisor.
Comments
Post a Comment