关于11g查询结构缓存的疑问? - Oracle新技术/11g - ITPUB论坛

commanderhyk
初级会员

精华贴数 0
个人空间 0
技术积分 28 (41903)
社区积分 0 (138262)
注册日期 2004-8-17
论坛徽章:0

#1

使用道具

发表于 2007-12-28 21:17

关于11g查询结构缓存的疑问?

tableA有几百万行,一些列做了索引

如果使用11g能不能达到这个效果:
1.
select /*+ result_cache */ col1 from tbla where col1 like '%xxx%' and col2 in('xx','xx')
第一句话的sql从几百万的表中查到了几千条由于like导致了经历很长的时间得到结果
2.我想在第一步的查询结果上作一个递进查询(理想状态在刚刚查询出的几千条上再次筛选如 col3 like '%xx%')
那么目前我的sql不得不写成
select /*+ result_cache */ col1 from tbla where col1 like '%xxx%' and col2 in('xx','xx') and col3 like '%xx%'
如果使用11g不知道递进查询的时候能否在第一次查询的缓存结果集上继续筛选,而不是从新搜一遍数据库.

只看该作者

cc59
Love oracle

精华贴数 0
个人空间 326
技术积分 10885 (108)
社区积分 13746 (106)
注册日期 2004-8-13
论坛徽章:68

#2

使用道具

发表于 2007-12-28 23:51

对于你第一个问题,你是赚sql慢吗?不知道你的执行计划是怎么样的?

对于第二条.不知道我是否明白了你的意思,
至少在我这里的测试是满足不了你的需求.
是需要作逻辑读的.

PHP code:



SQL> select /*+result_cache*/ * from test where a='aaa';





A                    B                    C


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


aaa                  bbb                  ccc


aaa                  bbb                  DDD


aaa                  BBB                  DDD








执行计划


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


Plan hash value: 1357081020





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





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





| Id  | Operation          | Name                       | Rows  | Bytes | Cost (





%CPU)| Time     |





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





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





|   0 | SELECT STATEMENT   |                            |     3 |   108 |     3


  (0)| 00:00:01 |





|   1 |  RESULT CACHE      | 39610p5nk2buh00zfs4zhvz4d7 |       |       |


     |          |





|*  2 |   TABLE ACCESS FULL| TEST                       |     3 |   108 |     3


  (0)| 00:00:01 |





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





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








Predicate Information (identified by operation id):


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





   2 - filter(&quot;A&quot;='aaa')





Result Cache Information (identified by operation id):


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





   1 - column-count=3; dependencies=(LIU.TEST); parameters=(nls); name=&quot;select /





*+result_cache*/ * from test where a='aaa'&quot;








Note


-----


   - dynamic sampling used for this statement








统计信息


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


          5  recursive calls


          0  db block gets


         16  consistent gets


          0  physical reads


          0  redo size


        572  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)


          3  rows processed





SQL> select /*+result_cache*/ * from test where a='aaa';





A                    B                    C


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


aaa                  bbb                  ccc


aaa                  bbb                  DDD


aaa                  BBB                  DDD








执行计划


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


Plan hash value: 1357081020





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





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





| Id  | Operation          | Name                       | Rows  | Bytes | Cost (





%CPU)| Time     |





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





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





|   0 | SELECT STATEMENT   |                            |     3 |   108 |     3


  (0)| 00:00:01 |





|   1 |  RESULT CACHE      | 39610p5nk2buh00zfs4zhvz4d7 |       |       |


     |          |





|*  2 |   TABLE ACCESS FULL| TEST                       |     3 |   108 |     3


  (0)| 00:00:01 |





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





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








Predicate Information (identified by operation id):


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





   2 - filter(&quot;A&quot;='aaa')





Result Cache Information (identified by operation id):


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





   1 - column-count=3; dependencies=(LIU.TEST); parameters=(nls); name=&quot;select /





*+result_cache*/ * from test where a='aaa'&quot;








Note


-----


   - dynamic sampling used for this statement








统计信息


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


          0  recursive calls


          0  db block gets


          0  consistent gets


          0  physical reads


          0  redo size


        572  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)


          3  rows processed





SQL> select /*+result_cache*/ * from test where a='aaa' and b='bbb';





A                    B                    C


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


aaa                  bbb                  ccc


aaa                  bbb                  DDD








执行计划


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


Plan hash value: 1357081020





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





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





| Id  | Operation          | Name                       | Rows  | Bytes | Cost (





%CPU)| Time     |





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





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





|   0 | SELECT STATEMENT   |                            |     2 |    72 |     3


  (0)| 00:00:01 |





|   1 |  RESULT CACHE      | d9n5wu7wksfy449rhgwuq18ktf |       |       |


     |          |





|*  2 |   TABLE ACCESS FULL| TEST                       |     2 |    72 |     3


  (0)| 00:00:01 |





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





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








Predicate Information (identified by operation id):


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





   2 - filter(&quot;A&quot;='aaa' AND &quot;B&quot;='bbb')





Result Cache Information (identified by operation id):


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





   1 - column-count=3; dependencies=(LIU.TEST); parameters=(nls); name=&quot;select /





*+result_cache*/ * from test where a='aaa' and b='bbb'&quot;








Note


-----


   - dynamic sampling used for this statement








统计信息


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


          5  recursive calls


          0  db block gets


         16  consistent gets


          0  physical reads


          0  redo size


        563  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)


          2  rows processed





SQL> /





A                    B                    C


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


aaa                  bbb                  ccc


aaa                  bbb                  DDD








执行计划


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


Plan hash value: 1357081020





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





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





| Id  | Operation          | Name                       | Rows  | Bytes | Cost (





%CPU)| Time     |





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





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





|   0 | SELECT STATEMENT   |                            |     2 |    72 |     3


  (0)| 00:00:01 |





|   1 |  RESULT CACHE      | d9n5wu7wksfy449rhgwuq18ktf |       |       |


     |          |





|*  2 |   TABLE ACCESS FULL| TEST                       |     2 |    72 |     3


  (0)| 00:00:01 |





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





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








Predicate Information (identified by operation id):


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





   2 - filter(&quot;A&quot;='aaa' AND &quot;B&quot;='bbb')





Result Cache Information (identified by operation id):


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





   1 - column-count=3; dependencies=(LIU.TEST); parameters=(nls); name=&quot;select /





*+result_cache*/ * from test where a='aaa' and b='bbb'&quot;








Note


-----


   - dynamic sampling used for this statement








统计信息


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


          0  recursive calls


          0  db block gets


          0  consistent gets


          0  physical reads


          0  redo size


        563  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)


          2  rows processed





SQL> show parameter result





NAME                                 TYPE        VALUE


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


client_result_cache_lag              big integer 3000


client_result_cache_size             big integer 0


result_cache_max_result              integer     5


result_cache_max_size                big integer 1M


result_cache_mode                    string      MANUAL


result_cache_remote_expiration       integer  &nb

__________________

My blog: tuning rac and using parallel

msn:liuyi8903@hotmail.com

只看该作者

zjuzsf
初级会员

精华贴数 0
个人空间 0
技术积分 117 (14637)
社区积分 0 (102807)
注册日期 2003-12-20
论坛徽章:0

#3

使用道具

发表于 2007-12-29 10:02

result cache改写跟mview类似，sql语句写法稍微改一下就可以了
CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

SELECT COUNT(*) FROM (select/*+result_cache*/ * from t where owner = 'T' );
SELECT COUNT(*) FROM (select/*+result_cache*/ * from t where owner = 'T' );
SELECT COUNT(*) FROM (select/*+result_cache*/ * from t where owner = 'T' );
select STATUS,NAME,SCAN_COUNT from v$result_cache_objects;

SELECT COUNT(*) FROM (select/*+result_cache*/ * from t where owner = 'T' ) WHERE TIMESTAMP = 'VALID';
select STATUS,NAME,SCAN_COUNT from v$result_cache_objects;

SELECT COUNT(*) FROM (select/*+result_cache*/ * from t where owner = 'T' ) WHERE STATUS = 'VALID';
select STATUS,NAME,SCAN_COUNT from v$result_cache_objects;

把resultcache的sql放在里面，其他条件放在外面加，就可以模拟出view的写法，就可以使用result cache功能了
看执行计划的 RESULT CACHE 和  select STATUS,NAME,SCAN_COUNT from v$result_cache_objects的 scan_count列

T@orcl>CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

Table created.

Elapsed: 00:00:04.92
T@orcl>
T@orcl>SELECT COUNT(*) FROM (select/*+result_cache*/ * from t where owner = 'T' );

  COUNT(*)
----------
      28

Elapsed: 00:00:00.71

Execution Plan
----------------------------------------------------------
Plan hash value: 3715821533

---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                      | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |                         |    1 |    | 282 (1)| 00:00:04 |
| 1 |  SORT AGGREGATE    |                         |    1 |    |          |       |
| 2 | VIEW             |                         | 11 |    | 282 (1)| 00:00:04 |
| 3 | RESULT CACHE    | 79bb0zv42vf7kb11dqd251qxp3 |    |    |          |       |
|*  4 |    TABLE ACCESS FULL| T                         | 11 |  2277 | 282 (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------

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

4 - filter("OWNER"='T')

Result Cache Information (identified by operation id):
------------------------------------------------------

3 - column-count=15; dependencies=(T.T); parameters=(nls); name="select/*+result_cache*/ * from t where owner = 'T' "

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
      395  recursive calls
      0  db block gets
   1214  consistent gets
   1006  physical reads
      0  redo size
      418  bytes sent via SQL*Net to client
      416  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      5  sorts (memory)
      0  sorts (disk)
      1  rows processed

T@orcl>SELECT COUNT(*) FROM (select/*+result_cache*/ * from t where owner = 'T' );

  COUNT(*)
----------
      28

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3715821533

---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                      | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |                         |    1 |    | 282 (1)| 00:00:04 |
| 1 |  SORT AGGREGATE    |                         |    1 |    |          |       |
| 2 | VIEW             |                         | 11 |    | 282 (1)| 00:00:04 |
| 3 | RESULT CACHE    | 79bb0zv42vf7kb11dqd251qxp3 |    |    |          |       |
|*  4 |    TABLE ACCESS FULL| T                         | 11 |  2277 | 282 (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------

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

4 - filter("OWNER"='T')

Result Cache Information (identified by operation id):
------------------------------------------------------

3 - column-count=15; dependencies=(T.T); parameters=(nls); name="select/*+result_cache*/ * from t where owner = 'T' "

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
      418  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)
      1  rows processed

T@orcl>SELECT COUNT(*) FROM (select/*+result_cache*/ * from t where owner = 'T' );

  COUNT(*)
----------
      28

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3715821533

---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                      | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |                         |    1 |    | 282 (1)| 00:00:04 |
| 1 |  SORT AGGREGATE    |                         |    1 |    |          |       |
| 2 | VIEW             |                         | 11 |    | 282 (1)| 00:00:04 |
| 3 | RESULT CACHE    | 79bb0zv42vf7kb11dqd251qxp3 |    |    |          |       |
|*  4 |    TABLE ACCESS FULL| T                         | 11 |  2277 | 282 (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------

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

4 - filter("OWNER"='T')

Result Cache Information (identified by operation id):
------------------------------------------------------

3 - column-count=15; dependencies=(T.T); parameters=(nls); name="select/*+result_cache*/ * from t where owner = 'T' "

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
      418  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)
      1  rows processed

T@orcl>select STATUS,NAME,SCAN_COUNT from v$result_cache_objects;

STATUS
---------
NAME
--------------------------------------------------------------------------------------------------------------------------------
SCAN_COUNT
----------
Published
T.T
      0

Published
select/*+result_cache*/ * from t where owner = 'T'
      2

Elapsed: 00:00:00.20

Execution Plan
----------------------------------------------------------
Plan hash value: 2938610334

-------------------------------------------------------------------------------
| Id  | Operation       | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |          |    1 | 118 |    0 (0)| 00:00:01 |
|*  1 |  FIXED TABLE FULL| X$QESRCOBJ |    1 | 118 |    0 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("INST_ID"=USERENV('INSTANCE'))

Statistics
----------------------------------------------------------
      429  recursive calls
      0  db block gets
      78  consistent gets
      0  physical reads
      0  redo size
      634  bytes sent via SQL*Net to client
      416  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      9  sorts (memory)
      0  sorts (disk)
      2  rows processed

T@orcl>SELECT COUNT(*) FROM (select/*+result_cache*/ * from t where owner = 'T' ) WHERE TIMESTAMP = 'VALID';

  COUNT(*)
----------
      0

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3715821533

---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                      | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |                         |    1 | 11 | 282 (1)| 00:00:04 |
| 1 |  SORT AGGREGATE    |                         |    1 | 11 |          |       |
|*  2 | VIEW             |                         | 11 | 121 | 282 (1)| 00:00:04 |
| 3 | RESULT CACHE    | 79bb0zv42vf7kb11dqd251qxp3 |    |    |          |       |
|*  4 |    TABLE ACCESS FULL| T                         | 11 |  2277 | 282 (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------

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

2 - filter("TIMESTAMP"='VALID')
4 - filter("OWNER"='T')

Result Cache Information (identified by operation id):
------------------------------------------------------

3 - column-count=15; dependencies=(T.T); parameters=(nls); name="select/*+result_cache*/ * from t where owner = 'T' "

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
      67  recursive calls
      4  db block gets
      171  consistent gets
      0  physical reads
      0  redo size
      417  bytes sent via SQL*Net to client
      416  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      5  sorts (memory)
      0  sorts (disk)
      1  rows processed

T@orcl>select STATUS,NAME,SCAN_COUNT from v$result_cache_objects;

STATUS
---------
NAME
--------------------------------------------------------------------------------------------------------------------------------
SCAN_COUNT
----------
Published
T.T
      0

Published
select/*+result_cache*/ * from t where owner = 'T'
      3

Elapsed: 00:00:00.17

Execution Plan
----------------------------------------------------------
Plan hash value: 2938610334

-------------------------------------------------------------------------------
| Id  | Operation       | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |          |    1 | 118 |    0 (0)| 00:00:01 |
|*  1 |  FIXED TABLE FULL| X$QESRCOBJ |    1 | 118 |    0 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("INST_ID"=USERENV('INSTANCE'))

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
      634  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)
      2  rows processed

T@orcl>SELECT COUNT(*) FROM (select/*+result_cache*/ * from t where owner = 'T' ) WHERE STATUS = 'VALID';

  COUNT(*)
----------
      27

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 3715821533

---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                      | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |                         |    1 |    5 | 282 (1)| 00:00:04 |
| 1 |  SORT AGGREGATE    |                         |    1 |    5 |          |       |
|*  2 | VIEW             |                         | 11 | 55 | 282 (1)| 00:00:04 |
| 3 | RESULT CACHE    | 79bb0zv42vf7kb11dqd251qxp3 |    |    |          |       |
|*  4 |    TABLE ACCESS FULL| T                         | 11 |  2277 | 282 (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------

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

2 - filter("STATUS"='VALID')
4 - filter("OWNER"='T')

Result Cache Information (identified by operation id):
------------------------------------------------------

3 - column-count=15; dependencies=(T.T); parameters=(nls); name="select/*+result_cache*/ * from t where owner = 'T' "

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
      67  recursive calls
      4  db block gets
      171  consistent gets
      0  physical reads
      0  redo size
      418  bytes sent via SQL*Net to client
      416  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      5  sorts (memory)
      0  sorts (disk)
      1  rows processed

T@orcl>select STATUS,NAME,SCAN_COUNT from v$result_cache_objects;

STATUS
---------
NAME
--------------------------------------------------------------------------------------------------------------------------------
SCAN_COUNT
----------
Published
T.T
      0

Published
select/*+result_cache*/ * from t where owner = 'T'
      4

Elapsed: 00:00:00.15

Execution Plan
----------------------------------------------------------
Plan hash value: 2938610334

-------------------------------------------------------------------------------
| Id  | Operation       | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |          |    1 | 118 |    0 (0)| 00:00:01 |
|*  1 |  FIXED TABLE FULL| X$QESRCOBJ |    1 | 118 |    0 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("INST_ID"=USERENV('INSTANCE'))

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
      634  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)
      2  rows processed

__________________

Oracle fans
PostgreSQL backend source code

只看该作者

cc59
Love oracle

精华贴数 0
个人空间 326
技术积分 10885 (108)
社区积分 13746 (106)
注册日期 2004-8-13
论坛徽章:68

#4

使用道具

发表于 2007-12-30 01:27

确实如 zjuzsf 所言.

********************************************************************************
采用view方式:
SQL ID : 4p0bncyf8011w
select count(*)
from
(select /*+result_cache*/ * from test)

call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       2    0.01    0.08       0       0       0          0
Execute    2    0.00    0.00       0       0       0          0
Fetch       4    0.00    0.01       6       7       0          2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       8    0.01    0.10       6       7       0          2

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 89

Rows    Row Source Operation
-------  ---------------------------------------------------
   1  SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
   4 VIEW  (cr=0 pr=0 pw=0 time=6 us cost=3 size=0 card=4)
   4 RESULT CACHE  60bf29hyyjdw7a9wjc2gjng548 (cr=0 pr=0 pw=0 time=2 us)
   0    TABLE ACCESS FULL TEST (cr=0 pr=0 pw=0 time=0 us cost=3 size=48 card=4)

Elapsed times include waiting on following events:
  Event waited on                            Times Max. Wait  Total Waited
  ---------------------------------------- Waited  ----------  ------------
  SQL*Net message to client                      4       0.00       0.00
  SQL*Net message from client                   4    104.08       111.25
  db file sequential read                      1       0.01       0.01
  db file scattered read                         1       0.00       0.00
********************************************************************************

SQL ID : 0p971xjfhbjq6
select count(*)
from
(select /*+result_cache*/ * from test) where a='aaa'

call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       2    0.01    0.01       0       0       0          0
Execute    2    0.00    0.00       0       0       0          0
Fetch       4    0.00    0.00       0       0       0          2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       8    0.01    0.01       0       0       0          2

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 89

Rows    Row Source Operation
-------  ---------------------------------------------------
   1  SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
   3 VIEW  (cr=0 pr=0 pw=0 time=7 us cost=3 size=48 card=4)
   4 RESULT CACHE  60bf29hyyjdw7a9wjc2gjng548 (cr=0 pr=0 pw=0 time=3 us)
   0    TABLE ACCESS FULL TEST (cr=0 pr=0 pw=0 time=0 us cost=3 size=48 card=4)

Elapsed times include waiting on following events:
  Event waited on                            Times Max. Wait  Total Waited
  ---------------------------------------- Waited  ----------  ------------
  SQL*Net message to client                      4       0.00       0.00
  SQL*Net message from client                   4    12.13       13.28
********************************************************************************

SQL ID : 39a6vq1mpq9fz
select count(*)
from
(select /*+result_cache*/ * from test) where a='aaa' and b='bbb'

call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       2    0.00    0.01       0       0       0          0
Execute    2    0.00    0.00       0       0       0          0
Fetch       4    0.00    0.00       0       0       0          2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       8    0.00    0.01       0       0       0          2

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 89

Rows    Row Source Operation
-------  ---------------------------------------------------
   1  SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
   2 VIEW  (cr=0 pr=0 pw=0 time=6 us cost=3 size=96 card=4)
   4 RESULT CACHE  60bf29hyyjdw7a9wjc2gjng548 (cr=0 pr=0 pw=0 time=2 us)
   0    TABLE ACCESS FULL TEST (cr=0 pr=0 pw=0 time=0 us cost=3 size=48 card=4)

Elapsed times include waiting on following events:
  Event waited on                            Times Max. Wait  Total Waited
  ---------------------------------------- Waited  ----------  ------------
  SQL*Net message to client                      4       0.00       0.00
  SQL*Net message from client                   4    51.82       53.76
********************************************************************************

SQL ID : 06nvwn223659v
alter session set events '10046 trace name context off'

非view方式:

TKPROF: Release 11.1.0.6.0 - Production on 星期日 12月 30 01:19:42 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Trace file: orcl_ora_2452.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu    = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk    = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows    = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID : 3zjqbk5mpa14s
select /*+result_cache*/ count(*)
from
test

call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       2    0.00    0.00       0       0       0          0
Execute    2    0.00    0.00       0       0       0          0
Fetch       4    0.00    0.00       0       7       0          2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       8    0.00    0.00       0       7       0          2

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 89

Rows    Row Source Operation
-------  ---------------------------------------------------
   1  RESULT CACHE  afysw5qk0jrfv1fqvy8ps394mx (cr=7 pr=0 pw=0 time=0 us)
   1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us)
   4 TABLE ACCESS FULL TEST (cr=7 pr=0 pw=0 time=2 us cost=3 size=0 card=4)

Elapsed times include waiting on following events:
  Event waited on                            Times Max. Wait  Total Waited
  ---------------------------------------- Waited  ----------  ------------
  SQL*Net message to client                      5       0.00       0.00
  SQL*Net message from client                   5    21.08       34.28
********************************************************************************

SQL ID : 6ncyfa0f9urxu
select /*+result_cache*/ count(*)
from
test where a='aaa'

call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       2    0.00    0.00       0       0       0          0
Execute    2    0.00    0.00       0       0       0          0
Fetch       4    0.00    0.00       0       7       0          2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       8    0.00    0.00       0       7       0          2

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 89

Rows    Row Source Operation
-------  ---------------------------------------------------
   1  RESULT CACHE  4a53fwussbcp3ak24814b5nkjy (cr=0 pr=0 pw=0 time=0 us)
   0 SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
   0 TABLE ACCESS FULL TEST (cr=0 pr=0 pw=0 time=0 us cost=3 size=12 card=3)

Elapsed times include waiting on following events:
  Event waited on                            Times Max. Wait  Total Waited
  ---------------------------------------- Waited  ----------  ------------
  SQL*Net message to client                      4       0.00       0.00
  SQL*Net message from client                   4       5.19       6.10
********************************************************************************

SQL ID : 4z6vtnz30cvpn
select /*+result_cache*/ count(*)
from
test where a='aaa' and b='bbb'

call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       2    0.00    0.00       0       0       0          0
Execute    2    0.00    0.00       0       0       0          0
Fetch       4    0.00    0.00       0       7       0          2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       8    0.00    0.00       0       7       0          2

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 89

Rows    Row Source Operation
-------  ---------------------------------------------------
   1  RESULT CACHE  8kr6s0nwjw9s415arfyrc12fmu (cr=7 pr=0 pw=0 time=0 us)
   1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us)
   2 TABLE ACCESS FULL TEST (cr=7 pr=0 pw=0 time=2 us cost=3 size=8 card=1)

__________________

My blog: tuning rac and using parallel

msn:liuyi8903@hotmail.com

只看该作者

cainiao2
高级会员

精华贴数 0
个人空间 0
技术积分 8433 (144)
社区积分 23 (7039)
注册日期 2002-2-27
论坛徽章:21

#5

使用道具

发表于 2008-4-8 18:17

学习一下！

__________________

好好学习，天天向上!!!!!!

只看该作者