Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

issue with to_number

Vili DialisJan 5 2010 — edited Jan 5 2010
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.
This post has been answered by Ali Sakar on Jan 5 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 2 2010
Added on Jan 5 2010
23 comments
2,289 views