| Oracle 11g r2 result_cache is Not working [message #480500] |
Mon, 25 October 2010 05:45  |
oravijay
Messages: 43
Registered: March 2010
|
Member |
|
|
Hi Oracle Experts,
I am querying a table having 6 million records,It takes arround 35 seconds in command line terminal using putty in a windows client.
Also the server is linux centos with oracle 11g installed.
I saw the method Oracle result_cache which i am suspecting to be fast
my query is
"select /*+ result_cache */ * from relationshipobject;"
SQL> set autotrace traceonly stat
SQL> set timi on
SQL> select /*+ result_cache */ * from relationshipobject;
4937158 rows selected.
Elapsed: 00:00:35.48
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
381927 consistent gets
56529 physical reads
0 redo size
176531212 bytes sent via SQL*Net to client
3621097 bytes received via SQL*Net from client
329145 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4937158 rows processed
second time the same query
SQL> /
4937158 rows selected.
Elapsed: 00:00:34.84
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
381926 consistent gets
56529 physical reads
0 redo size
176531212 bytes sent via SQL*Net to client
3621097 bytes received via SQL*Net from client
329145 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4937158 rows processed
How can i reduce the consistent gets of a query?
here are my result_cache parameters
sql>exec dbms_result_cache.memory_report;
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 4032K bytes (4032 blocks)
Maximum Result Size = 201K bytes (201 blocks)
[Memory]
Total Memory = 354064 bytes [0.066% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 343368 bytes [0.064% of the Shared Pool]
....... Overhead = 113992 bytes
....... Cache Memory = 224K bytes (224 blocks)
........... Unused Memory = 19 blocks
........... Used Memory = 205 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 203 blocks
................... SQL = 2 blocks (2 count)
................... Invalid = 201 blocks (1 count)
PL/SQL procedure successfully completed.
SQL> show parameter result_cache
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 4032K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
Please let me know i can i reduce my query execution time and consistent gets?
Thanks in Advance
Vijay
|
|
|
|
| Re: Oracle 11g r2 result_cache is Not working [message #480501 is a reply to message #480500] |
Mon, 25 October 2010 05:52  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Quote:
...
Maximum Cache Size = 4032K bytes (4032 blocks)
...
176531212 bytes sent via SQL*Net to client
...
About 172393MB of results won't fit in a 4MB cache.
And it would be a really bad idea to try to put 4937158 lines of a result with almost 2GB into the RAM of a server. How much RAM does the server have anyway?
|
|
|
|
|
|
|
|
| Re: Oracle 11g r2 result_cache is Not working [message #480594 is a reply to message #480503] |
Mon, 25 October 2010 23:11 |
oravijay
Messages: 43
Registered: March 2010
|
Member |
|
|
This is the sgainfo
SQL> select * from v$sgainfo
2 /
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 2214936 No
Redo Buffers 5189632 No
Buffer Cache Size 251658240 Yes
Shared Pool Size 536870912 Yes
Large Pool Size 16777216 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 33554432 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 2087780352 No
Startup overhead in Shared Pool 184549376 No
NAME BYTES RES
-------------------------------- ---------- ---
Free SGA Memory Available 1224736768
12 rows selected.
Please advise, what result_cache_max_size is more optimium for large table queries
Thanks in Advance
Vijay
|
|
|
|
| Re: Oracle 11g r2 result_cache is Not working [message #480595 is a reply to message #480594] |
Mon, 25 October 2010 23:15 |
oravijay
Messages: 43
Registered: March 2010
|
Member |
|
|
Is it advisable to go with result_cache for large table having 6 million records.What is the other option can i use.I have partitioned the table and indexed already
Is it possible to keep large tables in keep_pool cache
Please share your ideas
thanks
Regards
Vijay
|
|
|
|
|
|
| Re: Oracle 11g r2 result_cache is Not working [message #480642 is a reply to message #480595] |
Tue, 26 October 2010 03:24 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
A Result Cache is built based on the size of the query result set and is built for queries that are frequently executed.
If you have a query against a 6million row table that returns 20 rows each time and the 6million rows don't change frequently, you could built a Result Cache for the 20 rows.
But if your Result Set itself is large, a Cache isn't what you should be looking it.
What sort of application would be executing a query frequently such that n-million rows are to be returned to the client each time ? Even if Oracle executes the query in 10seconds, the client might spend many more minutes in just retrieving all the rows and formatting them.
Instead of asking whether you should be using a Result Cache, you should ask whether you really have such a query running frequently. Such a query running every few hours might make sense and a Result Cache is an awful waste of resources for that requirement. Such a query running every few minutes makes no sense and you must stop your design there itself.
Hemant K Chitale
[Updated on: Tue, 26 October 2010 03:50] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Oracle 11g r2 result_cache is Not working [message #480927 is a reply to message #480925] |
Thu, 28 October 2010 03:05 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi - I didn't see this thread before, but the reason your query isn't cached, even after raising the cache size, is this parameter:
result_cache_max_result integer 5
This ensures that no query bigger than 5% of the cache will be cached: so you will always have at least 20 results cached. Try returning count(*) rather than the individual rows, and you'll see the effect.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
