CBO ... So much words has been said for Oracle most curious technology today and still most of us know very little about it. Until now, I was pretty sure that any SQL that was parsed without errors cannot be wrong at run time... To be more clear I was sure that SQL result may not depend on physical data in tables.
But I was wrong! Following example came from real live problem on 10g database.
The problem
I wanted to write a script which they can query TEMP usage through SQLPlus as ordinary sql query. After some time I managed to get satisfactory query.I run it as an test in my environment:
SQL> select 2 s.username, 3 s.sid, s.serial#, 4 round(((su.blocks*pa.value)/1024/1024),2) size_mb, 5 s.status, 6 su.segfile#, 7 su.segblk# 8 from v$session s, 9 v$sort_usage su, 10 v$process pr, 11 v$parameter pa 12 where 13 s.saddr = su.session_addr 14 and s.paddr = pr.addr 15 and pa.name='db_block_size' 16 and ((nvl(su.blocks,0)*pa.value)/1024/1024) > 0 17 order by size_mb; no rows selected SQL>Result "no rows selected" force me to occupy some TEMP space to test properly. So I run in other SQLPlus session script which do exactly that-occupy TEMP space:
SQL> select * from dba_source order by text desc;After some time (when previous script start to output data) I run again mine TEMP script and get an error:
SQL> select 2 s.username, 3 s.sid, s.serial#, 4 round(((su.blocks*pa.value)/1024/1024),2) size_mb, 5 s.status, 6 su.segfile#, 7 su.segblk# 8 from v$session s, 9 v$sort_usage su, 10 v$process pr, 11 v$parameter pa 12 where 13 s.saddr = su.session_addr 14 and s.paddr = pr.addr 15 and pa.name='db_block_size' 16 and ((nvl(su.blocks,0)*pa.value)/1024/1024) > 0 17 order by size_mb; and ((nvl(su.blocks,0)*pa.value)/1024/1024) > 0 * ERROR at line 16: ORA-01722: invalid number SQL>I was surprised with an error.
Firstly I have rechecked return value from v$parameter query:
SQL> select value 2 from v$parameter pa 3 where pa.name='db_block_size'; VALUE -------------------------------- 8192 SQL>
Then I retest that returned value is correct (it is a number):
SQL> select value * 10 2 from v$parameter pa 3 where pa.name='db_block_size'; VALUE*10 ---------- 81920 SQL>
Then I tried to place to_number() to explicitly cast problematic part-no success!
Then I thought that Oracle database statistic (data dictionary) was too old so I run it manually (dbms_stats.gather_database_stats)-no success!
After that I run explain plan to see what is going on with the query:
SQL> explain plan for
2 select
3 s.username,
4 s.sid, s.serial#,
5 round(((su.blocks*pa.value)/1024/1024),2) size_mb,
6 s.status,
7 su.segfile#,
8 su.segblk#
9 from v$session s,
10 v$sort_usage su,
11 v$process pr,
12 v$parameter pa
13 where
14 s.saddr = su.session_addr
15 and s.paddr = pr.addr
16 and pa.name='db_block_size'
17 and ((nvl(su.blocks,0)*pa.value)/1024/1024) > 0
18 order by size_mb;
Explained.
SQL> set lines 2000
SQL> set head off
SQL> select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'advanced'));
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2461 | 3 (100)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 2461 | 3 (100)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 2461 | 2 (100)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 2448 | 2 (100)| 00:00:01 |
|* 4 | HASH JOIN | | 1 | 2422 | 2 (100)| 00:00:01 |
|* 5 | HASH JOIN | | 1 | 2390 | 2 (100)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 2283 | 1 (100)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 2270 | 1 (100)| 00:00:01 |
|* 8 | HASH JOIN | | 1 | 2244 | 1 (100)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 2176 | 1 (100)| 00:00:01 |
|* 10 | HASH JOIN | | 1 | 142 | 1 (100)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KSUSE | 1 | 71 | 0 (0)| 00:00:01 |
|* 12 | FIXED TABLE FULL | X$KTSSO | 1 | 71 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KSPPCV | 5 | 10170 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KSPPI | 1 | 68 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 26 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 |
|* 17 | FIXED TABLE FULL | X$KSUSE | 1 | 107 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KSUPR | 1 | 32 | 0 (0)| 00:00:01 |
|* 19 | FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) | 1 | 26 | 0 (0)| 00:00:01 |
|* 20 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 |
As it is easy to see, the problem rely in view v$parameter and column value which has mixed values (numbers as well as varchars). So when Oracle's CBO perform Full Table Scan (FTS) (regardless value I was retrieving was pure number) it breaks.The whole explain plan output can be downloaded from one file
.Solution
Solution was relatively easy when we know the cause. So I'll show one most acceptable way. Placing hint will always prevent Oracle's CBO to go wrong-to make FTS!SQL> select /*+ rule */ 2 s.username, 3 s.sid, s.serial#, 4 round(((su.blocks*pa.value)/1024/1024),2) size_mb, 5 s.status, 6 su.segfile#, 7 su.segblk# 8 from v$session s, 9 v$sort_usage su, 10 v$process pr, 11 v$parameter pa 12 where 13 s.saddr = su.session_addr 14 and s.paddr = pr.addr 15 and pa.name='db_block_size' 16 and ((nvl(su.blocks,0)*pa.value)/1024/1024) > 0 17 order by size_mb; USERNAME SID SERIAL# SIZE_MB STATUS SEGFILE# SEGBLK# -------------- --------- ---------- ---------- -------- ---------- ---------- TAB 307 905 226 INACTIVE 81 60681 SQL>The same could be done with rule FIRST_ROWS.
To see that realy CBO plan changed, let us look into it:
SQL> explain plan for
2 select /*+ rule */
3 s.username,
4 s.sid, s.serial#,
5 round(((su.blocks*pa.value)/1024/1024),2) size_mb,
6 s.status,
7 su.segfile#,
8 su.segblk#
9 from v$session s,
10 v$sort_usage su,
11 v$process pr,
12 v$parameter pa
13 where
14 s.saddr = su.session_addr
15 and s.paddr = pr.addr
16 and pa.name='db_block_size'
17 and ((nvl(su.blocks,0)*pa.value)/1024/1024) > 0
18 order by size_mb;
Explained.
SQL> set lines 2000
SQL> set head off
SQL> select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'advanced'));
--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | MERGE JOIN | |
| 3 | SORT JOIN | |
| 4 | MERGE JOIN | |
| 5 | SORT JOIN | |
| 6 | MERGE JOIN | |
| 7 | SORT JOIN | |
| 8 | MERGE JOIN | |
| 9 | SORT JOIN | |
| 10 | MERGE JOIN | |
| 11 | SORT JOIN | |
| 12 | MERGE JOIN | |
| 13 | SORT JOIN | |
| 14 | MERGE JOIN | |
| 15 | SORT JOIN | |
| 16 | NESTED LOOPS | |
| 17 | MERGE JOIN | |
| 18 | FIXED TABLE FULL | X$KSPPCV |
|* 19 | FILTER | |
|* 20 | SORT JOIN | |
|* 21 | FIXED TABLE FULL| X$KSPPI |
|* 22 | FIXED TABLE FULL | X$KTSSO |
|* 23 | SORT JOIN | |
|* 24 | FIXED TABLE FULL | X$KSUSE |
|* 25 | SORT JOIN | |
| 26 | FIXED TABLE FULL | X$KSLWT |
|* 27 | SORT JOIN | |
| 28 | FIXED TABLE FULL | X$KSLED |
|* 29 | SORT JOIN | |
|* 30 | FIXED TABLE FULL | X$KSUSE |
|* 31 | SORT JOIN | |
|* 32 | FIXED TABLE FULL | X$KSUPR |
|* 33 | SORT JOIN | |
| 34 | FIXED TABLE FULL | X$KSLWT |
|* 35 | SORT JOIN | |
| 36 | FIXED TABLE FULL | X$KSLED |
--------------------------------------------------------
The whole explain plan output can be downloaded from one file The conclusion
This problem I have reproduced on 10g database as well as 11g (32/64 bit Win/Lin versions) with default init ora parameter optimizer_mode defined as ALL_ROWS.Because "bug" was too obvious finally I search on Metalink and found exactly the same case: "Bug 6263716: QUERY WORKS FOR ORACLE 9I BUT IT DOESN'T WORK FOR ORACLE 10G-ORA-1722" but it is closed as not a bug but expected behaviour.
So, be careful on tables with mixed values in one column-they might reproduce an unexpected error at run time ... and this is expected behaviour...
;-)
Cheers!
I onece encountered the following:
ReplyDeletesystem@DB10G> select index_name, degree from dba_indexes where degree > 2;
select index_name, degree from dba_indexes where degree > 2
*ERROR at line 1:
ORA-01722: invalid number
Until then I realized the degree is VARCHAR2 type.
Yu,
ReplyDeleteFor certain arithmetic operations Oracle do automatic casting. Look in post for exactly this demo.
Your case was not that-you are comparing values and here oracle makes no implicit casting.
Damir Vadas
http://damir-vadas.blogspot.com/
I think in the col > nubmer case oracle makes implicit casting too:
ReplyDeleteTESTDB>create table t(deg varchar2(30));
Table created.
TESTDB>insert into t values('1');
1 row created.
TESTDB>insert into t values('2');
1 row created.
TESTDB>insert into t values('3');
1 row created.
TESTDB>commit;
Commit complete.
TESTDB>set autotrace traceonly
TESTDB>select * from t where deg > 2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 17 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("DEG")>2)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
218 bytes sent via SQL*Net to client
238 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
TESTDB>set autotrace off
TESTDB>insert into t values('default');
1 row created.
TESTDB>select * from t where deg > 2;
ERROR:
ORA-01722: invalid number
no rows selected
Yun,
ReplyDeleteSeems you are right...it makes implicit cast.
;-)
ThX for nice explanation and time to make that alive here...
Damir