Monday, October 25, 2010

SQL Execution in 10g and 11g (1)


I executed “select * from cat” on both and below is the result:
In next post will try to explain the reasons and work around.


Elapsed: 00:00:00.28 - 10g (10.1.0.4.0 )
Elapsed: 00:00:01.31 - 11g (11.1.0.7.0)

Execution plan  for 10g:
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=81 Card=803 Bytes=
          72270)

   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'OBJ$' (TABLE) (Cost=81 Card=7013
           Bytes=631170)

   3    1     TABLE ACCESS (CLUSTER) OF 'TAB$' (CLUSTER) (Cost=2 Card=
          1 Bytes=12)

   4    3       INDEX (UNIQUE SCAN) OF 'I_OBJ#' (INDEX) (Cost=1 Card=1
          )

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2781  consistent gets
        769  physical reads
          0  redo size
     113954  bytes sent via SQL*Net to client
       3062  bytes received via SQL*Net from client
        220  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3274  rows processed
=========================
For 11g
=========================
 Execution Plan
----------------------------------------------------------
Plan hash value: 473646690

--------------------------------------------------------------------------------
-

| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
-

|   0 | SELECT STATEMENT      |         |  1096 |   112K|    30   (4)| 00:00:01
|

|*  1 |  FILTER               |         |       |       |            |
|

|*  2 |   HASH JOIN           |         |  2458 |   252K|    30   (4)| 00:00:01
|

|   3 |    INDEX FULL SCAN    | I_USER2 |    91 |  2093 |     1   (0)| 00:00:01
|

|*  4 |    INDEX RANGE SCAN   | I_OBJ5  |  2458 |   196K|    28   (0)| 00:00:01
|

|*  5 |   TABLE ACCESS CLUSTER| TAB$    |     1 |    12 |     2   (0)| 00:00:01
|

|*  6 |    INDEX UNIQUE SCAN  | I_OBJ#  |     1 |       |     1   (0)| 00:00:01
|

|   7 |   NESTED LOOPS        |         |     1 |    28 |     2   (0)| 00:00:01
|

|*  8 |    INDEX FULL SCAN    | I_USER2 |     1 |    20 |     1   (0)| 00:00:01
|

|*  9 |    INDEX RANGE SCAN   | I_OBJ4  |     1 |     8 |     1   (0)| 00:00:01
|

--------------------------------------------------------------------------------
-


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=6) OR
              "O"."TYPE#"=2 AND  NOT EXISTS (SELECT 0 FROM "SYS"."TAB$" "T" WHER
E

              "T"."OBJ#"=:B1 AND (BITAND("T"."PROPERTY",512)=512 OR
              BITAND("T"."PROPERTY",8192)=8192))) AND ("O"."TYPE#"<>4 AND
              "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE
#"<>9

              AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
              "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND
              "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 O
R

              ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8
OR

              "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=1
2 OR

              "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=
87)

              AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND
              "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CON
TEXT('u

              serenv','current_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."USER
$"

              "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'
))))))

   2 - access("O"."OWNER#"="U"."USER#")
   4 - access("O"."SPARE3"=USERENV('SCHEMAID') AND "O"."LINKNAME" IS
              NULL)
       filter("O"."LINKNAME" IS NULL)
   5 - filter(BITAND("T"."PROPERTY",512)=512 OR
              BITAND("T"."PROPERTY",8192)=8192)
   6 - access("T"."OBJ#"=:B1)
   8 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('use
              renv','current_edition_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('use
              renv','current_edition_id')))
   9 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND
              "O2"."OWNER#"="U2"."USER#")


Statistics
----------------------------------------------------------
        578  recursive calls
          0  db block gets
       3150  consistent gets
        305  physical reads
          0  redo size
     161145  bytes sent via SQL*Net to client
       3802  bytes received via SQL*Net from client
        300  SQL*Net roundtrips to/from client
         22  sorts (memory)
          0  sorts (disk)
       4471  rows processed

No comments:

Post a Comment