No question at this time
DBA Top 10
1 A. Kavsek 12200
2 M. Cadot 7000
3 P. Wisse 5600
4 B. Vroman 5400
5 J. PĂ©ran 1900
6 . Lauri 1500
7 T. P 500
7 R. Pattyn 500
9 T. Boles 400
10 A. Chavan 300
10 J. Schnackenberg 300
10 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48454
Total active users1499
Act. users last 24h5
Act. users last hour0
Registered user hits last week216
Registered user hits last month950
Go up

EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT
Next thread: Question about licensing
Prev thread: RefCursor vs Views

Message Score Author Date
Dear Guru's Please let me know if executing be...... dattatraya walgude Sep 24, 2019, 08:38
Hi Dattatraya, The Shared Pool consist of many ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Philip Wisse Sep 24, 2019, 09:16
Thanks Philip We are using memory target, one m...... dattatraya walgude Sep 24, 2019, 09:25
Dattatraya, If the result cache is manual then ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Philip Wisse Sep 24, 2019, 10:08
Thanks Philip Will check .... dattatraya walgude Sep 24, 2019, 10:36

Follow up by mail Click here


Subject: EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT
Author: dattatraya walgude, India
Date: Sep 24, 2019, 08:38, 56 days ago
Os info: Linux
Oracle info: 12.1.0.2.0
Message: Dear Guru's

Please let me know if executing below statement helps in increase the performance of the system

EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT;

In our last regeneration execution data is continuously fetching from the database and every statement fired from pl/sql block results in to new set of data which is not earlier stored in memory.

Hence memory in SGA occupied a lot to store multiple results.

Free up this result area can help us to improve the performance.

Your opinion on this please.

Dattatraya
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT
Author: Philip Wisse, Netherlands
Date: Sep 24, 2019, 09:16, 56 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Dattatraya,

The Shared Pool consist of many sub pools.
If the 'DBMS Result Cache' is large compared to the 'Buffer Cache' then it may be beneficial to your performance to clean up the Result Cache.

But why is your Buffer Cache not functional?
Do you size the components automatically?
Then info is in V$SGA_DYNAMIC_COMPONENTS.

Ref https://docs.oracle.com/database/121/TGDBA/tune_sga.htm#TGDBA320

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT
Author: dattatraya walgude, India
Date: Sep 24, 2019, 09:25, 56 days ago
Message: Thanks Philip

We are using memory target, one more thing result_cache_mode is MANUAL.

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 24672K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
hi_shared_memory_address integer 0
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer 1
percent
memory_max_target big integer 9632M
memory_target big integer 9632M
optimizer_inmemory_aware boolean TRUE
shared_memory_address integer 0
SQL>
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT
Author: Philip Wisse, Netherlands
Date: Sep 24, 2019, 10:08, 56 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Dattatraya,

If the result cache is manual then you are responsible for the settings -- otherwise it's Oracle.

A normal size for the result cache is 1% of the shared pool:
https://docs.oracle.com/database/121/TGDBA/tune_result_cache.htm#TGDBA616

This size should have no influence on the performance of the buffer cache. So you might concentrate on tuning the buffer cache:
https://docs.oracle.com/database/121/TGDBA/tune_buffer_cache.htm#TGDBA294

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT
Author: dattatraya walgude, India
Date: Sep 24, 2019, 10:36, 56 days ago
Message: Thanks Philip
Will check .
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here