Dominic Brooks published a note recently about some very nasty SQL – originally thinking that it was displaying a run-time problem due to the extreme number of copies of the lnnvl() function the optimizer had produced. In fact it turned out to be a parse-time problem rather than a run-time problem, but when I first read Dominic’s note I was sufficiently surprised that I decided to try modelling the query.
Unfortunately the query had more than 1,000 predicates, (OR’ed together) and some of them included in-lists. Clearly, writing this up by hand wasn’t going to be a good idea, so I wrote a script to generate both the data, and the query, as follows – first a table to query:
create table t1
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level <= 1e4
)
select
rownum id1,
rownum id2,
rownum id,
lpad(rownum,10) v1,
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 1e5
;
create index t1_i1 on t1(id1, id2);
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname =>'T1',
method_opt => 'for all columns size 1'
);
end;
/
Then a piece of code to write a nasty query:
set pagesize 0
set feedback off
set termout off
spool temp1.sql
prompt select * from t1 where 1 = 2
select
'or (id1 = ' || rownum || ' and id2 = ' || (rownum + 1) || ')'
from
t1
where
rownum <= 750
union all
select
'or ( id1 = ' || (rownum + 1000) || ' and id2 in (' || rownum || ',' || (rownum+1) || '))'
from
t1
where
rownum <= 250
;
prompt /
spool off
Here’s an example of the text generated by the code – with the parameters set to 5 and 3 respectively (and notice how I’ve rigged the query so that it doesn’t return any data, whatever the optimizer thinks):
select * from t1 where 1 = 2 or (id1 = 1 and id2 = 2) or (id1 = 2 and id2 = 3) or (id1 = 3 and id2 = 4) or (id1 = 4 and id2 = 5) or (id1 = 5 and id2 = 6) or ( id1 = 1001 and id2 in (1,2)) or ( id1 = 1002 and id2 in (2,3)) or ( id1 = 1003 and id2 in (3,4)) /
So here’s the plan from the above query:
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1008 | 16 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 126 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_I1 | 1 | | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 126 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_I1 | 1 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 126 | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | T1_I1 | 1 | | 2 (0)| 00:00:01 |
| 8 | INLIST ITERATOR | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| T1 | 5 | 630 | 7 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | T1_I1 | 5 | | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID1"=1003)
filter("ID2"=3 OR "ID2"=4)
5 - access("ID1"=1002)
filter((LNNVL("ID1"=1003) OR LNNVL("ID2"=3) AND LNNVL("ID2"=4)) AND
("ID2"=2 OR "ID2"=3))
7 - access("ID1"=1001)
filter((LNNVL("ID1"=1002) OR LNNVL("ID2"=2) AND LNNVL("ID2"=3)) AND
(LNNVL("ID1"=1003) OR LNNVL("ID2"=3) AND LNNVL("ID2"=4)) AND ("ID2"=1 OR
"ID2"=2))
10 - access(("ID1"=1 AND "ID2"=2 OR "ID1"=2 AND "ID2"=3 OR "ID1"=3 AND
"ID2"=4 OR "ID1"=4 AND "ID2"=5 OR "ID1"=5 AND "ID2"=6))
filter((LNNVL("ID1"=1001) OR LNNVL("ID2"=1) AND LNNVL("ID2"=2)) AND
(LNNVL("ID1"=1002) OR LNNVL("ID2"=2) AND LNNVL("ID2"=3)) AND
(LNNVL("ID1"=1003) OR LNNVL("ID2"=3) AND LNNVL("ID2"=4)))
As you can see, the first five predicates end up in line 10 of the plan with 10 repetitions (5 * 2) of the lnnvl() function. The last three predicates show up in lines 3, 5, and 7 – and the on each line we see two more lnnvl() calls than on the previous – just imagine, then, how many lnnvl() calls the optimizer will have added to the query plan by the time we have 750 occurrences in the inlist iterator (line 8) and 250 occurrences of the slightly complex predicate. Here are the relevant CPU stats (from v$session_stats) from running the generated script on 11.1.0.7, on Windows 32-bit, 2.8GHz CPU:
Name Value ---- ----- recursive cpu usage 1,848 CPU used when call started 1,854 CPU used by this session 1,854 DB time 1,870 parse time cpu 1,847 parse time elapsed 1,862
Clearly the parse time is extreme – though not as dramatic as in Dominic’s example; but having set up the first draft of the sample code it’s easy enough to change the number of occurrences of each type of predicate, and it’s pretty easy to make longer in-lists in the more complex of the two types of predicate. It’s not too difficult to get an execution plan that mimics Dominic’s in length and time to parse.
It’s not just the parse times that are interesting when you start doing this, by the way – it’s worth playing around to see what happens. It’s probably best to run the query to pull the plans from memory if you want to see the plans, though – if you try using “explain plan” then you start using memory in the SGA for some of the work: in one of my examples I had to abort the instance after a few minutes.
