问题描述
sokrates > select distinct
sokrates > client_connection, client_oci_library, client_version, client_driver
sokrates > from v$session_connect_info
sokrates > where sid = (select sid from v$mystat where rownum=1)
sokrates > /
CLIENT_CONNEC CLIENT_OCI_LIBRARY CLIENT_VERSION CLIENT_DRIVER
------------- --------------------------- ---------------------------------------- ------------------------------
Heterogeneous Home-based 12.2.0.1.0 SQL*PLUS
sokrates > show parameter result
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 60000
client_result_cache_size big integer 1G
result_cache_max_result integer 5
result_cache_max_size big integer 629152K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
sokrates >
sokrates > create table t(i) result_cache(mode force) as select 1 from dual;
Table created.
sokrates > set autotr traceonly
sokrates > select * from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | 0t0bmg4r2a06vd6wzxfsj5f5ud | | | | |
| 2 | TABLE ACCESS FULL| T | 1 | 3 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(SOKRATES.T); name="select * from t"
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
5 consistent gets
1 physical reads
0 redo size
351 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
下一次调用现在应该使用客户端结果缓存，我会假设。
sokrates > /
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | 0t0bmg4r2a06vd6wzxfsj5f5ud | | | | |
| 2 | TABLE ACCESS FULL| T | 1 | 3 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(SOKRATES.T); name="select * from t"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
351 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
不，没有，我希望看到0 SQL*Net roundtrips to/from client
sokrates > set autotr off sokrates > select * from CLIENT_RESULT_CACHE_STATS$ sokrates > where cache_id in (select sid from v$mystat); no rows selected sokrates >
为什么客户端结果缓存在这里不起作用？
如何查看 “0 SQL * 往返客户端的净往返”？
还是sqlplus不支持此功能？
注意: 我有
OCI_RESULT_CACHE_MAX_SIZE=1G OCI_RESULT_CACHE_MAX_RSET_SIZE=10M OCI_RESULT_CACHE_MAX_RSET_ROWS=20M
在我的client-sqlnet.ora (这甚至需要客户端结果缓存吗？)
专家解答
抱歉花了这么长时间-我需要做一些研究。
客户端缓存的先决条件之一是语句缓存，例如
给你!
客户端缓存的先决条件之一是语句缓存，例如
SQL> create table t as select * from dba_objects;
Table created.
SQL> set feedback only
SQL> select * from t;
78257 rows selected.
SQL> set feedback on
SQL>
SQL> @mystat
Enter value for statname: SQL*Net
NAME VALUE
-------------------------------------------------- ----------
bytes sent via SQL*Net to client 12028716
bytes received via SQL*Net from client 114602
SQL*Net roundtrips to/from client 5248
bytes sent via SQL*Net to dblink 0
bytes received via SQL*Net from dblink 0
SQL*Net roundtrips to/from dblink 0
bytes via SQL*Net vector to client 0
bytes via SQL*Net vector from client 0
bytes via SQL*Net vector to dblink 0
bytes via SQL*Net vector from dblink 0
10 rows selected.
SQL>
SQL> set feedback only
SQL> select /*+ result_cache */ * from t;
78257 rows selected.
SQL> set feedback on
SQL> @mystat
Enter value for statname: SQL*Net
NAME VALUE
-------------------------------------------------- ----------
bytes sent via SQL*Net to client 24048365
bytes received via SQL*Net from client 219963
SQL*Net roundtrips to/from client 10469
bytes sent via SQL*Net to dblink 0
bytes received via SQL*Net from dblink 0
SQL*Net roundtrips to/from dblink 0
bytes via SQL*Net vector to client 0
bytes via SQL*Net vector from client 0
bytes via SQL*Net vector to dblink 0
bytes via SQL*Net vector from dblink 0
10 rows selected.
SQL>
SQL>
SQL> set feedback only
SQL> select /*+ result_cache */ * from t;
78257 rows selected.
SQL> set feedback on
SQL> @mystat
Enter value for statname: SQL*Net
NAME VALUE
-------------------------------------------------- ----------
bytes sent via SQL*Net to client 36068015
bytes received via SQL*Net from client 325233
SQL*Net roundtrips to/from client 15690
bytes sent via SQL*Net to dblink 0
bytes received via SQL*Net from dblink 0
SQL*Net roundtrips to/from dblink 0
bytes via SQL*Net vector to client 0
bytes via SQL*Net vector from client 0
bytes via SQL*Net vector to dblink 0
bytes via SQL*Net vector from dblink 0
10 rows selected.
So even with (server) result caching we see around 12megabytes across the network each call. Let us now turn on the statement cache
SQL>
SQL> set statementcache 50
SQL>
SQL> set feedback only
SQL> select /*+ result_cache */ * from t;
78257 rows selected.
SQL> set feedback on
SQL> @mystat
Enter value for statname: SQL*Net
NAME VALUE
-------------------------------------------------- ----------
bytes sent via SQL*Net to client 48087665
bytes received via SQL*Net from client 430571
SQL*Net roundtrips to/from client 20911
bytes sent via SQL*Net to dblink 0
bytes received via SQL*Net from dblink 0
SQL*Net roundtrips to/from dblink 0
bytes via SQL*Net vector to client 0
bytes via SQL*Net vector from client 0
bytes via SQL*Net vector to dblink 0
bytes via SQL*Net vector from dblink 0
10 rows selected.
SQL>
SQL> set feedback only
SQL> select /*+ result_cache */ * from t;
78257 rows selected.
SQL> set feedback on
SQL> @mystat
Enter value for statname: SQL*Net
NAME VALUE
-------------------------------------------------- ----------
bytes sent via SQL*Net to client 48088761
bytes received via SQL*Net from client 430619
SQL*Net roundtrips to/from client 20913
bytes sent via SQL*Net to dblink 0
bytes received via SQL*Net from dblink 0
SQL*Net roundtrips to/from dblink 0
bytes via SQL*Net vector to client 0
bytes via SQL*Net vector from client 0
bytes via SQL*Net vector to dblink 0
bytes via SQL*Net vector from dblink 0
10 rows selected.
SQL>
SQL>
给你!
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
