Hi David,
Thanks a lot. I got it what i am expecting from your reply
Thanks
Daiesh
On May 7, 6:26 pm, ddf <orat...@msn.com> wrote:
> On May 6, 11:40 pm, Daiesh <mcavenkad...@gmail.com> wrote:
>
> > Hi David,
>
> > When creating the table we have used table option CACHE. But how do we
> > know which is used or not when executing queries for fine tuning ?
>
> > Thanks,
> > Daiesh
>
> > On May 6, 8:03 pm, ddf <orat...@msn.com> wrote:
>
> > > On May 6, 6:57 am, Daiesh <mcavenkad...@gmail.com> wrote:
>
> > > > Dear All,
>
> > > > Please let me know, how can we know if the oracle table CACHE is used
> > > > when we execute the query? .
>
> > > > Thanks
> > > > Daiesh.
>
> > > What, exactly, are you asking?
>
> > > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> Now that I know what you're asking the answer is fairly simple:
>
> You can set autotrace on and see the per-query statistics, including
> consistent gets, once the results have been returned. Setting CACHE
> for a table will ensure the blocks read from disk will remain in the
> cache as long as possible (depending upon your configuration and the
> level of activity). Let's look at an example:
>
> SQL> --
> SQL> -- Create table and cache query results
> SQL> --
> SQL> create table emp_c
> 2 cache
> 3 tablespace users
> 4 as select * from emp;
>
> Table created.
>
> SQL>
> SQL> --
> SQL> -- Let's see if it works
> SQL> --
> SQL> set autotrace on
> SQL>
> SQL> --
> SQL> -- Query table, load cache, show stats
> SQL> --
> SQL> select *
> 2 from emp_c;
>
> EMPNO ENAME JOB MGR HIREDATE SAL
> COMM DEPTNO
> ---------- ---------- --------- ---------- --------- ----------
> ---------- ----------
> 7369 SMITH CLERK 7902 17-DEC-80
> 800 20
> 7499 ALLEN SALESMAN 7698 20-FEB-81 1600
> 300 30
> 7521 WARD SALESMAN 7698 22-FEB-81 1250
> 500 30
> 7566 JONES MANAGER 7839 02-APR-81
> 2975 20
> 7654 MARTIN SALESMAN 7698 28-SEP-81 1250
> 1400 30
> 7698 BLAKE MANAGER 7839 01-MAY-81
> 2850 30
> 7782 CLARK MANAGER 7839 09-JUN-81
> 2450 10
> 7788 SCOTT ANALYST 7566 09-DEC-82
> 3000 20
> 7839 KING PRESIDENT 17-NOV-81
> 5000 10
> 7844 TURNER SALESMAN 7698 08-SEP-81
> 1500 0 30
> 7876 ADAMS CLERK 7788 12-JAN-83
> 1100 20
> 7900 JAMES CLERK 7698 03-DEC-81
> 950 30
> 7902 FORD ANALYST 7566 03-DEC-81
> 3000 20
> 7934 MILLER CLERK 7782 23-JAN-82
> 1300 10
>
> 14 rows selected.
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 3230908102
>
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time |
> ---------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)|
> 00:00:01 |
> | 1 | TABLE ACCESS FULL| EMP_C | 14 | 1218 | 3 (0)|
> 00:00:01 |
> ---------------------------------------------------------------------------
>
> Note
> -----
> - dynamic sampling used for this statement
>
> Statistics
> ----------------------------------------------------------
> 168 recursive calls
> 0 db block gets
> 32 consistent gets
> 5 physical reads <--- Cache loaded
> from physical reads
> 0 redo size
> 1422 bytes sent via SQL*Net to client
> 416 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 14 rows processed
>
> SQL>
> SQL> --
> SQL> -- Query table again, should be reading cache
> SQL> --
> SQL> -- We find it does, as only consistent gets
> SQL> -- are reported
> SQL> --
> SQL> select *
> 2 from emp_c;
>
> EMPNO ENAME JOB MGR HIREDATE SAL
> COMM DEPTNO
> ---------- ---------- --------- ---------- --------- ----------
> ---------- ----------
> 7369 SMITH CLERK 7902 17-DEC-80
> 800 20
> 7499 ALLEN SALESMAN 7698 20-FEB-81 1600
> 300 30
> 7521 WARD SALESMAN 7698 22-FEB-81 1250
> 500 30
> 7566 JONES MANAGER 7839 02-APR-81
> 2975 20
> 7654 MARTIN SALESMAN 7698 28-SEP-81 1250
> 1400 30
> 7698 BLAKE MANAGER 7839 01-MAY-81
> 2850 30
> 7782 CLARK MANAGER 7839 09-JUN-81
> 2450 10
> 7788 SCOTT ANALYST 7566 09-DEC-82
> 3000 20
> 7839 KING PRESIDENT 17-NOV-81
> 5000 10
> 7844 TURNER SALESMAN 7698 08-SEP-81
> 1500 0 30
> 7876 ADAMS CLERK 7788 12-JAN-83
> 1100 20
> 7900 JAMES CLERK 7698 03-DEC-81
> 950 30
> 7902 FORD ANALYST 7566 03-DEC-81
> 3000 20
> 7934 MILLER CLERK 7782 23-JAN-82
> 1300 10
>
> 14 rows selected.
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 3230908102
>
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time |
> ---------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)|
> 00:00:01 |
> | 1 | TABLE ACCESS FULL| EMP_C | 14 | 1218 | 3 (0)|
> 00:00:01 |
> ---------------------------------------------------------------------------
>
> Note
> -----
> - dynamic sampling used for this statement
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 4 consistent gets <---- Only cache reads here
> 0 physical reads
> 0 redo size
> 1422 bytes sent via SQL*Net to client
> 416 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 14 rows processed
>
> SQL>
> SQL> --
> SQL> -- Let's query another table
> SQL> --
> SQL> select *
> 2 from all_objects;
>
> [lots of data here]
>
> 68454 rows selected.
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 1406736206
>
> ------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes
> | Cost (%CPU)| Time |
> ------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 30607 |
> 3616K| 250 (4)| 00:00:03 |
> |* 1 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 9
> | 1 (0)| 00:00:01 |
> |* 2 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 |
> | 0 (0)| 00:00:01 |
> | 3 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 30
> | 3 (0)| 00:00:01 |
> |* 4 | INDEX RANGE SCAN | I_OBJ1 | 1 |
> | 2 (0)| 00:00:01 |
> |* 5 | FILTER | | |
> | | |
> |* 6 | HASH JOIN | | 70169 |
> 8291K| 250 (4)| 00:00:03 |
> | 7 | TABLE ACCESS FULL | USER$ | 98 | 1568
> | 3 (0)| 00:00:01 |
> |* 8 | HASH JOIN | | 70169 |
> 7195K| 246 (4)| 00:00:03 |
> | 9 | INDEX FULL SCAN | I_USER2 | 98 | 2254
> | 1 (0)| 00:00:01 |
> |* 10 | TABLE ACCESS FULL | OBJ$ | 70169 |
> 5619K| 244 (3)| 00:00:03 |
> |* 11 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8
> | 2 (0)| 00:00:01 |
> |* 12 | INDEX UNIQUE SCAN | I_IND1 | 1 |
> | 1 (0)| 00:00:01 |
> |* 13 | HASH JOIN | | 1 | 14
> | 3 (34)| 00:00:01 |
> |* 14 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | 11
> | 2 (0)| 00:00:01 |
> | 15 | FIXED TABLE FULL | X$KZSRO | 22 | 66
> | 0 (0)| 00:00:01 |
> |* 16 | FIXED TABLE FULL | X$KZSPR | 2 | 14
> | 0 (0)| 00:00:01 |
> |* 17 | HASH JOIN | | 1 | 14
> | 3 (34)| 00:00:01 |
> |* 18 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | 11
> | 2 (0)| 00:00:01 |
> | 19 | FIXED TABLE FULL | X$KZSRO | 22 | 66
> | 0 (0)| 00:00:01 |
> |* 20 | HASH JOIN | | 1 | 14
> | 3 (34)| 00:00:01 |
> |* 21 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | 11
> | 2 (0)| 00:00:01 |
> | 22 | FIXED TABLE FULL | X$KZSRO | 22 | 66
> | 0 (0)| 00:00:01 |
> |* 23 | FIXED TABLE FULL | X$KZSPR | 2 | 14
> | 0 (0)| 00:00:01 |
> |* 24 | FIXED TABLE FULL | X$KZSPR | 1 | 7
> | 0 (0)| 00:00:01 |
> | 25 | NESTED LOOPS | | |
> | | |
> | 26 | NESTED LOOPS | | 1 | 67
> | 8 (0)| 00:00:01 |
> | 27 | NESTED LOOPS | | 1 | 57
> | 5 (0)| 00:00:01 |
> | 28 | NESTED LOOPS | | 1 | 46
> | 4 (0)| 00:00:01 |
> | 29 | MERGE JOIN CARTESIAN | | 1 | 42
> | 3 (0)| 00:00:01 |
> |* 30 | INDEX RANGE SCAN | I_OBJ5 | 1 | 39
> | 3 (0)| 00:00:01 |
> | 31 | BUFFER SORT | | 22 | 66
> | 0 (0)| 00:00:01 |
> | 32 | FIXED TABLE FULL | X$KZSRO | 22 | 66
> | 0 (0)| 00:00:01 |
> |* 33 | INDEX RANGE SCAN | I_USER2 | 1 | 4
> | 1 (0)| 00:00:01 |
> |* 34 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | 11
> | 1 (0)| 00:00:01 |
> |* 35 | INDEX RANGE SCAN | I_DEPENDENCY1 | 3 |
> | 2 (0)| 00:00:01 |
> |* 36 | TABLE ACCESS BY INDEX ROWID | DEPENDENCY$ | 1 | 10
> | 3 (0)| 00:00:01 |
> |* 37 | FIXED TABLE FULL | X$KZSPR | 2 | 14
> | 0 (0)| 00:00:01 |
> |* 38 | HASH JOIN | | 1 | 28
> | 3 (34)| 00:00:01 |
> | 39 | NESTED LOOPS | | 1 | 25
> | 2 (0)| 00:00:01 |
> |* 40 | TABLE ACCESS BY INDEX ROWID| TRIGGER$ | 1 | 14
> | 1 (0)| 00:00:01 |
> |* 41 | INDEX UNIQUE SCAN | I_TRIGGER2 | 1 |
> | 0 (0)| 00:00:01 |
> |* 42 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | 11
> | 1 (0)| 00:00:01 |
> | 43 | FIXED TABLE FULL | X$KZSRO | 22 | 66
> | 0 (0)| 00:00:01 |
> |* 44 | FIXED TABLE FULL | X$KZSPR | 2 | 14
> | 0 (0)| 00:00:01 |
> | 45 | NESTED LOOPS | | |
> | | |
> | 46 | NESTED LOOPS | | 1 | 67
> | 8 (0)| 00:00:01 |
> | 47 | NESTED LOOPS | | 1 | 57
> | 5 (0)| 00:00:01 |
> | 48 | NESTED LOOPS | | 1 | 46
> | 4 (0)| 00:00:01 |
> | 49 | MERGE JOIN CARTESIAN | | 1 | 42
> | 3 (0)| 00:00:01 |
> |* 50 | INDEX RANGE SCAN | I_OBJ5 | 1 | 39
> | 3 (0)| 00:00:01 |
> | 51 | BUFFER SORT | | 22 | 66
> | 0 (0)| 00:00:01 |
> | 52 | FIXED TABLE FULL | X$KZSRO | 22 | 66
> | 0 (0)| 00:00:01 |
> |* 53 | INDEX RANGE SCAN | I_USER2 | 1 | 4
> | 1 (0)| 00:00:01 |
> |* 54 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | 11
> | 1 (0)| 00:00:01 |
> |* 55 | INDEX RANGE SCAN | I_DEPENDENCY1 | 3 |
> | 2 (0)| 00:00:01 |
> |* 56 | TABLE ACCESS BY INDEX ROWID | DEPENDENCY$ | 1 | 10
> | 3 (0)| 00:00:01 |
> |* 57 | FIXED TABLE FULL | X$KZSPR | 2 | 14
> | 0 (0)| 00:00:01 |
> |* 58 | FIXED TABLE FULL | X$KZSPR | 2 | 14
> | 0 (0)| 00:00:01 |
> |* 59 | FIXED TABLE FULL | X$KZSPR | 1 | 7
> | 0 (0)| 00:00:01 |
> |* 60 | FIXED TABLE FULL | X$KZSPR | 1 | 7
> | 0 (0)| 00:00:01 |
> |* 61 | FIXED TABLE FULL | X$KZSPR | 1 | 7
> | 0 (0)| 00:00:01 |
> |* 62 | FIXED TABLE FULL | X$KZSPR | 2 | 14
> | 0 (0)| 00:00:01 |
> | 63 | VIEW | | 1 | 13
> | 2 (0)| 00:00:01 |
> | 64 | FAST DUAL | | 1 |
> | 2 (0)| 00:00:01 |
> |* 65 | FIXED TABLE FULL | X$KZSPR | 2 | 14
> | 0 (0)| 00:00:01 |
> |* 66 | FIXED TABLE FULL | X$KZSPR | 1 | 7
> | 0 (0)| 00:00:01 |
> |* 67 | FIXED TABLE FULL | X$KZSPR | 2 | 14
> | 0 (0)| 00:00:01 |
> |* 68 | FIXED TABLE FULL | X$KZSPR | 1 | 7
> | 0 (0)| 00:00:01 |
> |* 69 | FIXED TABLE FULL | X$KZSPR | 1 | 7
> | 0 (0)| 00:00:01 |
> |* 70 | FIXED TABLE FULL | X$KZSPR | 2 | 14
> | 0 (0)| 00:00:01 |
> |* 71 | FIXED TABLE FULL | X$KZSPR | 1 | 7
> | 0 (0)| 00:00:01 |
> |* 72 | FIXED TABLE FULL | X$KZSPR | 2 | 14
> | 0 (0)| 00:00:01 |
> |* 73 | FIXED TABLE FULL | X$KZSPR | 2 | 14
> | 0 (0)| 00:00:01 |
> |* 74 | FIXED TABLE FULL | X$KZSPR | 2 | 14
> | 0 (0)| 00:00:01 |
> |* 75 | FIXED TABLE FULL | X$KZSPR | 2 | 14
> | 0 (0)| 00:00:01 |
> |* 76 | FIXED TABLE FULL | X$KZSPR | 1 | 7
> | 0 (0)| 00:00:01 |
> | 77 | NESTED LOOPS | | 1 | 28
> | 2 (0)| 00:00:01 |
> |* 78 | INDEX FULL SCAN | I_USER2 | 1 | 20
> | 1 (0)| 00:00:01 |
> |* 79 | INDEX RANGE SCAN | I_OBJ4 | 1 | 8
> | 1 (0)| 00:00:01 |
> ------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
> 2 - access("S"."OBJ#"=:B1)
> 4 - access("EO"."OBJ#"=:B1)
> 5 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1
> AND (SELECT 1 FROM
> "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1
> OR "I"."TYPE#"=2 OR
> "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR
> "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
> AND (("O"."SPARE3"=USERENV('SCHEMAID') OR
> "O"."SPARE3"=1) OR ( EXISTS (SELECT 0 FROM
> "SYS"."OBJAUTH$" "OA",SYS."X$KZSRO" "X$KZSRO" WHERE
> "OA"."GRANTEE#"="KZSROROL" AND
> "OA"."OBJ#"=:B2 AND ("OA"."PRIVILEGE#"=12 OR
> "OA"."PRIVILEGE#"=26)) OR EXISTS (SELECT
> 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE ((-"KZSPRPRV")=
> (-144) OR (-"KZSPRPRV")=(-141) OR
> (-"KZSPRPRV")=(-241)) AND "INST_ID"=USERENV
> ('INSTANCE'))) AND ("O"."TYPE#"=7 OR
> "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=28 OR
> "O"."TYPE#"=29 OR "O"."TYPE#"=30 OR
> "O"."TYPE#"=56) OR "O"."TYPE#"<>29 AND "O"."TYPE#"<>13
> AND "O"."TYPE#"<>11 AND
> "O"."TYPE#"<>9 AND "O"."TYPE#"<>30 AND "O"."TYPE#"<>12
> AND "O"."TYPE#"<>56 AND
> "O"."TYPE#"<>8 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>7
> AND "O"."TYPE#"<>28 AND EXISTS
> (SELECT 0 FROM "SYS"."OBJAUTH$" "OBJAUTH$",SYS."X$KZSRO"
> "X$KZSRO" WHERE
> "GRANTEE#"="KZSROROL" AND "OBJ#"=:B3 AND ("PRIVILEGE#"=3
> OR "PRIVILEGE#"=6 OR
> "PRIVILEGE#"=7 OR "PRIVILEGE#"=9 OR "PRIVILEGE#"=10 OR
> "PRIVILEGE#"=11 OR
> "PRIVILEGE#"=12 OR "PRIVILEGE#"=16 OR "PRIVILEGE#"=17 OR
> "PRIVILEGE#"=18)) OR ( EXISTS
> (SELECT 0 FROM "SYS"."OBJAUTH$" "OA",SYS."X$KZSRO" "X
> $KZSRO" WHERE
> "OA"."GRANTEE#"="KZSROROL" AND "OA"."OBJ#"=:B4 AND
> ("OA"."PRIVILEGE#"=12 OR
> "OA"."PRIVILEGE#"=26)) OR EXISTS (SELECT 0 FROM SYS."X
> $KZSPR" "X$KZSPR" WHERE
> ((-"KZSPRPRV")=(-184) OR (-"KZSPRPRV")=(-181) OR
> (-"KZSPRPRV")=(-241)) AND
> "INST_ID"=USERENV('INSTANCE'))) AND "O"."TYPE#"=13 OR
> ("O"."TYPE#"=1 OR "O"."TYPE#"=2
> OR "O"."TYPE#"=3 OR "O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
> "O"."TYPE#"=19 OR "O"."TYPE#"=20
> OR "O"."TYPE#"=34 OR "O"."TYPE#"=35) AND EXISTS (SELECT
> 0 FROM SYS."X$KZSPR" "X$KZSPR"
> WHERE ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR
> (-"KZSPRPRV")=(-48) OR
> (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND
> "INST_ID"=USERENV('INSTANCE')) OR (
> EXISTS (SELECT 0 FROM "SYS"."OBJAUTH$"
> "OA","SYS"."DEPENDENCY$" "DEP",SYS."USER$"
> "U",SYS."OBJ$" "O",SYS."X$KZSRO" "X$KZSRO" WHERE
> "O"."NAME"=:B5 AND "O"."SPARE3"=:B6
> AND "O"."TYPE#"=9 AND "O"."TYPE#"<>88 AND
> "O"."OWNER#"="U"."USER#" AND
> "DEP"."D_OBJ#"=:B7 AND "DEP"."P_OBJ#"="O"."OBJ#" AND
> "OA"."OBJ#"="O"."OBJ#" AND
> "OA"."PRIVILEGE#"=26 AND "OA"."GRANTEE#"="KZSROROL") OR
> EXISTS (SELECT 0 FROM
> SYS."X$KZSPR" "X$KZSPR" WHERE ((-"KZSPRPRV")=(-141) OR
> (-"KZSPRPRV")=(-241)) AND
> "INST_ID"=USERENV('INSTANCE'))) AND "O"."TYPE#"=11 OR
> ( EXISTS (SELECT 0 FROM
> "SYS"."OBJAUTH$" "OA","SYS"."TRIGGER$" "T",SYS."X$KZSRO"
> "X$KZSRO" WHERE
> "OA"."GRANTEE#"="KZSROROL" AND "T"."OBJ#"=:B8 AND BITAND
> ("T"."PROPERTY",24)=0 AND
> "OA"."OBJ#"="T"."BASEOBJECT" AND "OA"."PRIVILEGE#"=26)
> OR EXISTS (SELECT 0 FROM
> SYS."X$KZSPR" "X$KZSPR" WHERE ((-"KZSPRPRV")=(-152) OR
> (-"KZSPRPRV")=(-241)) AND
> "INST_ID"=USERENV('INSTANCE'))) AND "O"."TYPE#"=12 OR
> ( EXISTS (SELECT 0 FROM
> "SYS"."OBJAUTH$" "OA","SYS"."DEPENDENCY$" "DEP",SYS."USER
> $" "U",SYS."OBJ$"
> "O",SYS."X$KZSRO" "X$KZSRO" WHERE "O"."NAME"=:B9 AND
> "O"."SPARE3"=:B10 AND
> "O"."TYPE#"=13 AND "O"."TYPE#"<>88 AND
> "O"."OWNER#"="U"."USER#" AND "DEP"."D_OBJ#"=:B11
> AND "DEP"."P_OBJ#"="O"."OBJ#" AND "OA"."OBJ#"="O"."OBJ#"
> AND "OA"."PRIVILEGE#"=26 AND
> "OA"."GRANTEE#"="KZSROROL") OR EXISTS (SELECT 0 FROM
> SYS."X$KZSPR" "X$KZSPR" WHERE
> ((-"KZSPRPRV")=(-181) OR (-"KZSPRPRV")=(-241)) AND
> "INST_ID"=USERENV('INSTANCE'))) AND
> "O"."TYPE#"=14 OR "O"."TYPE#"=6 AND EXISTS (SELECT 0
> FROM SYS."X$KZSPR" "X$KZSPR"
> WHERE (-"KZSPRPRV")=(-109) AND "INST_ID"=USERENV
> ('INSTANCE')) OR "O"."TYPE#"=22 AND
> EXISTS (SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE
> ((-"KZSPRPRV")=(-189) OR
> (-"KZSPRPRV")=(-190) OR (-"KZSPRPRV")=(-191) OR
> (-"KZSPRPRV")=(-192)) AND
> "INST_ID"=USERENV('INSTANCE')) OR ("O"."TYPE#"=57 OR
> "O"."TYPE#"=69 OR "O"."TYPE#"=72
> OR "O"."TYPE#"=74) OR "O"."TYPE#"=33 AND EXISTS (SELECT
> 0 FROM SYS."X$KZSPR" "X$KZSPR"
> WHERE ((-"KZSPRPRV")=(-200) OR (-"KZSPRP)
> 6 - access("O"."SPARE3"="U"."USER#")
> 8 - access("O"."OWNER#"="U"."USER#")
> 10 - filter(BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT'
> AND
> "O"."NAME"<>'_default_auditing_options_' AND
> "O"."LINKNAME" IS NULL)
> 11 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR
> "I"."TYPE#"=4 OR
> "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
> 12 - access("I"."OBJ#"=:B1)
> 13 - access("OA"."GRANTEE#"="KZSROROL")
> 14 - access("OA"."OBJ#"=:B1)
> filter("OA"."PRIVILEGE#"=12 OR "OA"."PRIVILEGE#"=26)
> 16 - filter(((-"KZSPRPRV")=(-144) OR (-"KZSPRPRV")=(-141) OR
> (-"KZSPRPRV")=(-241))
> AND "INST_ID"=USERENV('INSTANCE'))
> 17 - access("GRANTEE#"="KZSROROL")
> 18 - access("OBJ#"=:B1)
> filter("PRIVILEGE#"=3 OR "PRIVILEGE#"=6 OR "PRIVILEGE#"=7 OR
> "PRIVILEGE#"=9 OR
> "PRIVILEGE#"=10 OR "PRIVILEGE#"=11 OR "PRIVILEGE#"=12 OR
> "PRIVILEGE#"=16 OR
> "PRIVILEGE#"=17 OR "PRIVILEGE#"=18)
> 20 - access("OA"."GRANTEE#"="KZSROROL")
> 21 - access("OA"."OBJ#"=:B1)
> filter("OA"."PRIVILEGE#"=12 OR "OA"."PRIVILEGE#"=26)
> 23 - filter(((-"KZSPRPRV")=(-184) OR (-"KZSPRPRV")=(-181) OR
> (-"KZSPRPRV")=(-241))
> AND "INST_ID"=USERENV('INSTANCE'))
> 24 - filter(((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR
> (-"KZSPRPRV")=(-48) OR
> (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND
> "INST_ID"=USERENV('INSTANCE'))
> 30 - access("O"."SPARE3"=:B1 AND "O"."NAME"=:B2 AND "O"."TYPE#"=9)
> filter("O"."TYPE#"=9 AND "O"."TYPE#"<>88)
> 33 - access("O"."OWNER#"="U"."USER#")
> 34 - access("OA"."OBJ#"="O"."OBJ#" AND "OA"."GRANTEE#"="KZSROROL"
> AND
> "OA"."PRIVILEGE#"=26)
> filter("OA"."PRIVILEGE#"=26 AND "OA"."GRANTEE#"="KZSROROL")
> 35 - access("DEP"."D_OBJ#"=:B1)
> 36 - filter("DEP"."P_OBJ#"="O"."OBJ#")
> 37 - filter(((-"KZSPRPRV")=(-141) OR (-"KZSPRPRV")=(-241)) AND
> "INST_ID"=USERENV('INSTANCE'))
> 38 - access("OA"."GRANTEE#"="KZSROROL")
> 40 - filter(BITAND("T"."PROPERTY",24)=0)
> 41 - access("T"."OBJ#"=:B1)
> 42 - access("OA"."OBJ#"="T"."BASEOBJECT" AND "OA"."PRIVILEGE#"=26)
> filter("OA"."PRIVILEGE#"=26)
> 44 - filter(((-"KZSPRPRV")=(-152) OR (-"KZSPRPRV")=(-241)) AND
> "INST_ID"=USERENV('INSTANCE'))
> 50 - access("O"."SPARE3"=:B1 AND "O"."NAME"=:B2 AND "O"."TYPE#"=13)
> filter("O"."TYPE#"=13 AND "O"."TYPE#"<>88)
> 53 - access("O"."OWNER#"="U"."USER#")
> 54 - access("OA"."OBJ#"="O"."OBJ#" AND "OA"."GRANTEE#"="KZSROROL"
> AND
> "OA"."PRIVILEGE#"=26)
> filter("OA"."PRIVILEGE#"=26 AND "OA"."GRANTEE#"="KZSROROL")
> 55 - access("DEP"."D_OBJ#"=:B1)
> 56 - filter("DEP"."P_OBJ#"="O"."OBJ#")
> 57 - filter(((-"KZSPRPRV")=(-181) OR (-"KZSPRPRV")=(-241)) AND
> "INST_ID"=USERENV('INSTANCE'))
> 58 - filter((-"KZSPRPRV")=(-109) AND "INST_ID"=USERENV('INSTANCE'))
> 59 - filter(((-"KZSPRPRV")=(-189) OR (-"KZSPRPRV")=(-190) OR
> (-"KZSPRPRV")=(-191) OR
> (-"KZSPRPRV")=(-192)) AND "INST_ID"=USERENV('INSTANCE'))
> 60 - filter(((-"KZSPRPRV")=(-200) OR (-"KZSPRPRV")=(-201) OR
> (-"KZSPRPRV")=(-202) OR
> (-"KZSPRPRV")=(-203) OR (-"KZSPRPRV")=(-204)) AND
> "INST_ID"=USERENV('INSTANCE'))
> 61 - filter(((-"KZSPRPRV")=(-251) OR (-"KZSPRPRV")=(-252) OR
> (-"KZSPRPRV")=(-253) OR
> (-"KZSPRPRV")=(-254)) AND "INST_ID"=USERENV('INSTANCE'))
> 62 - filter(((-"KZSPRPRV")=(-268) OR (-"KZSPRPRV")=(-267)) AND
> "INST_ID"=USERENV('INSTANCE'))
> 65 - filter(((-"KZSPRPRV")=(-177) OR (-"KZSPRPRV")=(-178)) AND
> "INST_ID"=USERENV('INSTANCE'))
> 66 - filter(((-"KZSPRPRV")=(-205) OR (-"KZSPRPRV")=(-206) OR
> (-"KZSPRPRV")=(-207) OR
> (-"KZSPRPRV")=(-208)) AND "INST_ID"=USERENV('INSTANCE'))
> 67 - filter(((-"KZSPRPRV")=(-222) OR (-"KZSPRPRV")=(-223)) AND
> "INST_ID"=USERENV('INSTANCE'))
> 68 - filter(((-"KZSPRPRV")=(-258) OR (-"KZSPRPRV")=(-259) OR
> (-"KZSPRPRV")=(-260) OR
> (-"KZSPRPRV")=(-261)) AND "INST_ID"=USERENV('INSTANCE'))
> 69 - filter(((-"KZSPRPRV")=(-246) OR (-"KZSPRPRV")=(-247) OR
> (-"KZSPRPRV")=(-248) OR
> (-"KZSPRPRV")=(-249)) AND "INST_ID"=USERENV('INSTANCE'))
> 70 - filter((-"KZSPRPRV")=(-265) AND "INST_ID"=USERENV('INSTANCE'))
> 71 - filter(((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR
> (-"KZSPRPRV")=(-48) OR
> (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND
> "INST_ID"=USERENV('INSTANCE'))
> 72 - filter((-"KZSPRPRV")=12 AND "INST_ID"=USERENV('INSTANCE'))
> 73 - filter(((-"KZSPRPRV")=(-265) OR (-"KZSPRPRV")=(-266)) AND
> "INST_ID"=USERENV('INSTANCE'))
> 74 - filter(((-"KZSPRPRV")=(-277) OR (-"KZSPRPRV")=(-278)) AND
> "INST_ID"=USERENV('INSTANCE'))
> 75 - filter(((-"KZSPRPRV")=(-292) OR (-"KZSPRPRV")=(-293) OR
> (-"KZSPRPRV")=(-294))
> AND "INST_ID"=USERENV('INSTANCE'))
> 76 - filter(((-"KZSPRPRV")=(-282) OR (-"KZSPRPRV")=(-283) OR
> (-"KZSPRPRV")=(-284) OR
> (-"KZSPRPRV")=(-285)) AND "INST_ID"=USERENV('INSTANCE'))
> 78 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT
> ('userenv','current_
> edition_id')))
> filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT
> ('userenv','current_
> edition_id')))
> 79 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND
> "O2"."OWNER#"="U2"."USER#")
>
> Statistics
> ----------------------------------------------------------
> 110769 recursive calls
> 3 db block gets
> 288857 consistent gets
> 1432 physical reads <---- Put more data blocks into
> cache
> 732 redo size
> 3512883 bytes sent via SQL*Net to client
> 50609 bytes received via SQL*Net from client
> 4565 SQL*Net roundtrips to/from client
> 1842 sorts (memory)
> 0 sorts (disk)
> 68454 rows processed
>
> SQL>
> SQL> --
> SQL> -- Query table again, should still be reading cache
> SQL> --
> SQL> -- We find it does, as only consistent gets
> SQL> -- are reported
> SQL> --
> SQL> select *
> 2 from emp_c;
>
> EMPNO ENAME JOB MGR HIREDATE SAL
> COMM DEPTNO
> ---------- ---------- --------- ---------- --------- ----------
> ---------- ----------
> 7369 SMITH CLERK 7902 17-DEC-80
> 800 20
> 7499 ALLEN SALESMAN 7698 20-FEB-81 1600
> 300 30
> 7521 WARD SALESMAN 7698 22-FEB-81 1250
> 500 30
> 7566 JONES MANAGER 7839 02-APR-81
> 2975 20
> 7654 MARTIN SALESMAN 7698 28-SEP-81 1250
> 1400 30
> 7698 BLAKE MANAGER 7839 01-MAY-81
> 2850 30
> 7782 CLARK MANAGER 7839 09-JUN-81
> 2450 10
> 7788 SCOTT ANALYST 7566 09-DEC-82
> 3000 20
> 7839 KING PRESIDENT 17-NOV-81
> 5000 10
> 7844 TURNER SALESMAN 7698 08-SEP-81
> 1500 0 30
> 7876 ADAMS CLERK 7788 12-JAN-83
> 1100 20
> 7900 JAMES CLERK 7698 03-DEC-81
> 950 30
> 7902 FORD ANALYST 7566 03-DEC-81
> 3000 20
> 7934 MILLER CLERK 7782 23-JAN-82
> 1300 10
>
> 14 rows selected.
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 3230908102
>
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time |
> ---------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)|
> 00:00:01 |
> | 1 | TABLE ACCESS FULL| EMP_C | 14 | 1218 | 3 (0)|
> 00:00:01 |
> ---------------------------------------------------------------------------
>
> Note
> -----
> - dynamic sampling used for this statement
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 4 consistent gets <----- Still only cache reads, no
> physical reads, cache option works
> 0 physical reads
> 0 redo size
> 1422 bytes sent via SQL*Net to client
> 416 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 14 rows processed
>
> SQL>
>
> This is not necessarily a good option for large, heavily updated
> tables as the data blocks will be changing rapidly and the cache may
> not be able to keep up with the pace of the inserts/updates/deletes.
>
> David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
