Aw: Re: How to trace memory resize ops
From: Ingrid Voigt <giantpanda_at_gmx.net>
Date: Mon, 26 Mar 2018 17:09:27 +0200
Message-ID: <trinity-5a010eae-c55d-4f29-b4e8-79810cd4155f-1522076966981_at_3c-app-gmx-bs25>
Ingrid,
Stefan's given you an option for tracing.
If you want to do a quick check of the most obvious guess before waiting for the next occurrence then run up the AWR reports for the intervals just before, during and after the last occurrence and check the instance activity stats for large numbers for "parse count (hard)"; and the SQL Ordered by Version Count for statements with large numbers of versions, and SQL Ordered by Sharable Memory for large memory consumers.
Basically the guess is that something fires in a very large number of similar SQL statements with different literals (that's the check for hard parses), or the same SQL statement with bind variable hits a bug which causes large numbers of re-optimizations (which also increase hard parses) that leave the statement with lots of versions and a total large memory requirement.
If you're lucky it will be the literal SQL problem, which is often very easy to fix; if you're unlucky it will be some obscure Oracle bug causing the second problem. Either way you might find some early clues in a quickk examination of AWR reports
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Ingrid Voigt <giantpanda_at_gmx.net>
Sent: 26 March 2018 11:43
To: oracle-l_at_freelists.org
Subject: How to trace memory resize ops
Oracle 11.2.0.4.171130 EE on Windows 64 Bit
We've been having problems with memory resize operations shrinking the buffer cache
below specified limits:
Specified: 16GB db_cache_size, 3GB shared_pool_size, 20GB sga_max_size.
Changes to: approx. 3.5GB db_cache_size, 16GB shared_pool_size
This occurs about 2-3 times per month, usually after hours, severely decreases database
performance and ends with a bounce of the database.
According to MOS 1269139.1 the resizes are expected behavior.
Ist there some way to trace / find out what exactly causes this?
(SQL, background process, other details of the root cause)
My customer is not willing to set _MEMORY_IMM_MODE_WITHOUT_AUTOSGA=false
and risk ORA-04031. If that changes, could one see something with a trace of
ORA-04031?
Thanks and best regards
Ingrid Voigt
-- http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 26 2018 - 17:09:27 CEST
Date: Mon, 26 Mar 2018 17:09:27 +0200
Message-ID: <trinity-5a010eae-c55d-4f29-b4e8-79810cd4155f-1522076966981_at_3c-app-gmx-bs25>
Hi,
thank you Stefan and Jonathan. I have found something to dig deeper. The
suspicious SQL is from the application and looks at first glance as if we could
maybe change it.
_at_Mark:
No caching, no.
If something interesting turns up I will be back with results.
Regards
Ingrid Voigt
Gesendet: Montag, 26. März 2018 um 14:44 Uhr
Von: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
An: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
Betreff: Re: How to trace memory resize ops
Von: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
An: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
Betreff: Re: How to trace memory resize ops
Ingrid,
Stefan's given you an option for tracing.
If you want to do a quick check of the most obvious guess before waiting for the next occurrence then run up the AWR reports for the intervals just before, during and after the last occurrence and check the instance activity stats for large numbers for "parse count (hard)"; and the SQL Ordered by Version Count for statements with large numbers of versions, and SQL Ordered by Sharable Memory for large memory consumers.
Basically the guess is that something fires in a very large number of similar SQL statements with different literals (that's the check for hard parses), or the same SQL statement with bind variable hits a bug which causes large numbers of re-optimizations (which also increase hard parses) that leave the statement with lots of versions and a total large memory requirement.
If you're lucky it will be the literal SQL problem, which is often very easy to fix; if you're unlucky it will be some obscure Oracle bug causing the second problem. Either way you might find some early clues in a quickk examination of AWR reports
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Ingrid Voigt <giantpanda_at_gmx.net>
Sent: 26 March 2018 11:43
To: oracle-l_at_freelists.org
Subject: How to trace memory resize ops
Oracle 11.2.0.4.171130 EE on Windows 64 Bit
We've been having problems with memory resize operations shrinking the buffer cache
below specified limits:
Specified: 16GB db_cache_size, 3GB shared_pool_size, 20GB sga_max_size.
Changes to: approx. 3.5GB db_cache_size, 16GB shared_pool_size
This occurs about 2-3 times per month, usually after hours, severely decreases database
performance and ends with a bounce of the database.
According to MOS 1269139.1 the resizes are expected behavior.
Ist there some way to trace / find out what exactly causes this?
(SQL, background process, other details of the root cause)
My customer is not willing to set _MEMORY_IMM_MODE_WITHOUT_AUTOSGA=false
and risk ORA-04031. If that changes, could one see something with a trace of
ORA-04031?
Thanks and best regards
Ingrid Voigt
-- http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l