Skip to Main Content
  • Questions

  • Client Result Cache not supported by sqlplus ?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Matthias.

Asked: August 23, 2017 - 5:25 pm UTC

Last updated: October 19, 2017 - 3:35 am UTC

Version: 12.2.0.1.0

Viewed 1000+ times

You Asked 

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

Next call now should use client result cache, I would assume.
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

No, it didn't, I expected to see 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 >


Why doesn't client result cache work here ?
How can I see "0 SQL*Net roundtrips to/from client" ?
Or is this feature not supported by sqlplus ?

note: I have
OCI_RESULT_CACHE_MAX_SIZE=1G
OCI_RESULT_CACHE_MAX_RSET_SIZE=10M
OCI_RESULT_CACHE_MAX_RSET_ROWS=20M

in my client-sqlnet.ora (is this even needed for client result cache ?)

and Connor said...

Sorry for taking so long - I needed to do some research.

One of the prerequisites for client cache is statement caching, eg

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.
<code>

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>


And there you go !


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

still does not work for me

Matthias Rogel, September 20, 2017 - 12:25 pm UTC

Thanks Connor,

however, it still does not work for me on
D:\>sqlplus -v

SQL*Plus: Release 12.2.0.1.0 Production

and
sql > select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
sql > show parameter result

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 1G
result_cache_max_result              integer     5
result_cache_max_size                big integer 4M
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0
.

Do you have any idea why ?
sql > create table t as select * from dba_objects;

Table created.

sql > set statementcache 50
sql > set feedback only
sql > select /*+result_cache */ * from t;

78686 rows selected.

sql > set feedback on
sql > select
sql >    n.name, m.VALUE
sql > from v$mystat m join v$statname n using (statistic#)
sql > where upper(n.name) like upper('%SQL*Net%')
sql > order by m.value desc
sql > /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
bytes sent via SQL*Net to client                                    4721546
bytes received via SQL*Net from client                                66379
SQL*Net roundtrips to/from client                                      5269
bytes via SQL*Net vector to client                                        0
bytes via SQL*Net vector from client                                      0
bytes via SQL*Net vector to dblink                                        0
SQL*Net roundtrips to/from dblink                                         0
bytes via SQL*Net vector from dblink                                      0
bytes sent via SQL*Net to dblink                                          0
bytes received via SQL*Net from dblink                                    0

10 rows selected.

sql > set feedback only
sql > select /*+result_cache */ * from t;

78686 rows selected.

sql > set feedback on
sql > select
sql >    n.name, m.VALUE
sql > from v$mystat m join v$statname n using (statistic#)
sql > where upper(n.name) like upper('%SQL*Net%')
sql > order by m.value desc
sql > /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
bytes sent via SQL*Net to client                                    9435833
bytes received via SQL*Net from client                               124890
SQL*Net roundtrips to/from client                                     10520
bytes via SQL*Net vector to client                                        0
bytes via SQL*Net vector from client                                      0
bytes via SQL*Net vector to dblink                                        0
SQL*Net roundtrips to/from dblink                                         0
bytes via SQL*Net vector from dblink                                      0
bytes sent via SQL*Net to dblink                                          0
bytes received via SQL*Net from dblink                                    0

10 rows selected.

sql > set feedback only
sql > select /*+result_cache */ * from t;

78686 rows selected.

sql > set feedback on
sql > select
sql >    n.name, m.VALUE
sql > from v$mystat m join v$statname n using (statistic#)
sql > where upper(n.name) like upper('%SQL*Net%')
sql > order by m.value desc
sql > /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
bytes sent via SQL*Net to client                                   14149901
bytes received via SQL*Net from client                               183401
SQL*Net roundtrips to/from client                                     15771
bytes via SQL*Net vector to client                                        0
bytes via SQL*Net vector from client                                      0
bytes via SQL*Net vector to dblink                                        0
SQL*Net roundtrips to/from dblink                                         0
bytes via SQL*Net vector from dblink                                      0
bytes sent via SQL*Net to dblink                                          0
bytes received via SQL*Net from dblink                                    0

10 rows selected.

sql >


Thanks,
Matthias
Connor McDonald
October 19, 2017 - 3:35 am UTC

Any chance you can try the OCI program in MOS note 864736.1 to confirm whether clicent cache is working at the OCI level ?

Otherwise you're into sqlnet trace territory....and that's no-one's idea of fun :-)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database