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