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 -~----------~----~----~----~------~----~------~--~---