Hello all.
Please explain me the following phenomena, i cant figure this out.
Why is 'to_number' function forced by the optimizer in this query @ the operation number 11 ?
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL>
SQL>
SQL>
SQL> explain plan for
2 with q as (
3 select v$parameter.value bs_value
4 from v$parameter
5 where name='db_block_size'
6 )
7 select a.username,
8 a.sid, a.serial#,
9 round(((b.blocks*bs_value)/1024/1024),2) size_mb,
10 a.status,
11 b.segfile#,
12 b.segblk#
13 from v$session a,
14 v$sort_usage b,
15 v$process c,
16 q
17 where a.saddr = b.session_addr
18 and a.paddr = c.addr
19 and ((nvl(b.blocks,0)*nvl(bs_value,0))/1024/1024) > 0
20 -- group by b.segfile#, b.segblk#,
21 order by size_mb;
Explained.
SQL>
SQL>
SQL> set linesize 250
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3927520946
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 668 | 3 (100)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 668 | 3 (100)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 668 | 2 (100)| 00:00:01 |
|* 3 | HASH JOIN | | 1 | 655 | 2 (100)| 00:00:01 |
|* 4 | HASH JOIN | | 1 | 625 | 2 (100)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 509 | 1 (100)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 6 | HASH JOIN | | 1 | 496 | 1 (100)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 428 | 1 (100)| 00:00:01 |
|* 8 | HASH JOIN | | 1 | 138 | 1 (100)| 00:00:01 |
|* 9 | FIXED TABLE FULL | X$KSUSE | 1 | 69 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FULL | X$KTSSO | 1 | 69 | 0 (0)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KSPPCV | 5 | 1450 | 0 (0)| 00:00:01 |
|* 12 | FIXED TABLE FULL | X$KSPPI | 1 | 68 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KSUSE | 1 | 116 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KSUPR | 1 | 30 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S"."KSUSEPRO"="ADDR")
4 - access("S"."ADDR"="KTSSOSES")
6 - access("X"."INDX"="Y"."INDX")
filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR "KSPPSTDF"='FALSE' OR
BITAND("KSPPSTVF",5)>0)
8 - access("KTSSOSES"="S"."ADDR" AND "KTSSOSNO"="S"."KSUSESER")
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
BITAND("S"."KSUSEFLG",1)<>0)
10 - filter("X$KTSSO"."INST_ID"=USERENV('INSTANCE'))
11 - filter(NVL("KTSSOBLKS",0)*TO_NUMBER(NVL("KSPPSTVL",'0'))/1024/1024>0)
12 - filter("KSPPINM"='db_block_size' AND "X"."INST_ID"=USERENV('INSTANCE') AND
TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
13 - filter("S"."KSUSEOPC"="E"."INDX")
14 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
BITAND("S"."KSUSEFLG",1)<>0)
15 - filter("INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0)
16 - filter("S"."KSUSEOPC"="E"."INDX")
44 rows selected.