The issue happened a bit before so no details in v$ views. But I have
captured the ASH(gv$active_session_history) dump of that sql. And it's
coming around ~1.2MB , i tried sending it twice but somehow i think it's
not going through because of the attachment or something i am not sure. Is
there any other way to pass it on?
And the big threat is this query can submit to the database at any time
which we are not sure of , and we are not able to get the exact culprit
(i.e. probably the exact top_level_sql_id which submit this recursive
query), so is there any other way to put a track on this so that we would
be alerted before hand.
I do see a job as below was showing up in the OEM top activity section
during that time, not sure if the recursive sql can be part of this parent?
DECLARE
job BINARY_INTEGER := :job;
next_date TIMESTAMP WITH TIME ZONE := :mydate;
broken BOOLEAN := FALSE;
job_name VARCHAR2 (30) := :job_name;
job_subname VARCHAR2 (30) := :job_subname;
job_owner VARCHAR2 (30) := :job_owner;
job_start TIMESTAMP WITH TIME ZONE := :job_start;
job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start;
window_start TIMESTAMP WITH TIME ZONE := :window_start;
window_end TIMESTAMP WITH TIME ZONE := :window_end;
chain_id VARCHAR2 (14) := :chainid;
credential_owner VARCHAR2 (30) := :credown;
credential_name VARCHAR2 (30) := :crednam;
destination_owner VARCHAR2 (30) := :destown;
destination_name VARCHAR2 (30) := :destnam;
job_dest_id VARCHAR2 (14) := :jdestid;
log_id NUMBER := :log_id;
BEGIN
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (15, TRUE);
END;
:mydate := next_date;
IF broken
THEN
:b := 1;
ELSE
:b := 0;
END IF;
END;
On Wed, Dec 23, 2020 at 6:02 PM Sayan Malakshinov <xt.and.r@xxxxxxxxx>
wrote:
First of all, I wonder why in case of PGA issue you didn't analyze it.
I would start from top sessions by PGA consumption:
https://github.com/xtender/xt_scripts/blob/master/pga_top.sql
Then v$process_memory and v$process_memory_detail:
https://github.com/xtender/xt_scripts/blob/master/pga/pga_usage_by_sid.sql
https://github.com/xtender/xt_scripts/blob/master/pga/details_simple.sql
Then I would use session snapper by Tanel Poder:
https://github.com/tanelpoder/tpt-oracle/blob/master/snapper.sql
to check what exactly is going on in that sessions. Considering the P2
value of "Library cache: mutex X" I would kill only the session holding
that cursor (p1 value).
Then I would analyze that cursor with its bind variables including
v$sql_shared_cursor:
https://github.com/xtender/xt_scripts/blob/master/sql_shared.sql
Now since we have no exact details, can you share ashdump for that period?
or export dba_hist_active_sess_history
On Wed, Dec 23, 2020 at 3:14 PM Pap <oracle.developer35@xxxxxxxxx> wrote:
I see the top_level_sql_id is getting populated with same sql_id and the
sql_exec_id column is all null along with sql_exec_start for all of those
~30K samples. Now i also checked gv$sql and see the last_load_time showing
as ~2 days back and the EXECUTIONs column showing figure 15, so which means
even i just consider today's ~30 minutes execution happen for that same sql
, it still looks lot more for those total ~15 executions.
I tried executing the query manually by passing some sample bind
values(which again may not be the actual binds which caused the issue). i
see the query finishing in seconds even with and without rows. So is it
possible that some specific bind is causing the issue?
On Wed, Dec 23, 2020 at 5:32 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
A couple of thoughts.
First - the AWR output looks a little odd - the Total Executions is
lower than the Total Parse Calls. Any idea why ? (It may be completely
unrelated to your problem, of course).
Secondly - this looks like a sys-recursive statement that would be
executed while a statement is being parsed, but normally such a statement
would only be called a few times because it would be populating the
dictionary cache (v$rowcache). There are some bit of the dictionary, that
don't get cached, though (parts of views, parts of histograms, maybe) so
it's possible that what you're seeing is a strange parsing issue relating
to a parent statement. When you see this SQL (sql_id) in the sample there
are two other columns you can check: top_level_sql_id, and sql_exec_id.
THe former MAY tell you about a statement that is being parsed, the latter
may give you an idea of how many times this statement is actually called.
My first guess (on a bare minimum of information) would be that you have
a statement with a monster IN-list and Oracle is trying to optimize a
massive UNION ALL where every branch is accessing a view.
Regards
Jonathan Lewis
ASH / v$active_session_history from your 25 minute run with 25,000
samples.
One of the columns of
On Tue, 22 Dec 2020 at 18:12, Pap <oracle.developer35@xxxxxxxxx> wrote:
Hello Friends, this is version 11.2.0.4 database. I am struggling to
understand the reason behind sudden memory saturation in one of our hosts
causing multiple databases to be brought to its knee. The host memory
utilization went up from ~30% to 100%(~500GB) within ~3-4hrs. After digging
into this we found it's one of the databases whose pga was bumped to almost
~500+ GB and the oem was filled with concurrency wait (Library cache: mutex
X) with the below sql being highlighted was singled out as the one.We end
up killing all the processes related top this query and few others after
which the other databases in that host then came up to speed. And also this
query was showing the program as "Jdbc Thin client".
However in the AWR i don't see any oddity i.e. this below query was not
showing in the top in those parse or version count sections in the AWR. And
in the dba_hist_active_sess_history the flags "in_parse"
,"in_sql_execution","IN_HARD_PARSE" were all showing as "N" throughout the
issue period.
So wanted to check if anybody else has encountered such an issue
because this sql seems to be a normal one (or say some recursive sql). So
if there are any chances of memory leak or any related stuff? Also i saw
there were not many sessions running this query , so wondering how come
this query can cause such drastic memory consumption?
SELECT obj#, type#, ctime, mtime, stime, status,dataobj#,
flags,oid$,spare1,spare2
FROM obj$
WHERE owner# = :1 AND name = :2
AND namespace = :3 AND (remoteowner = :4 OR remoteowner IS NULL AND
:4 IS NULL)
AND (linkname = :5 OR linkname IS NULL AND :5 IS NULL)
AND (subname = :6 OR subname IS NULL AND :6 IS NULL)
*Section of AWR:-*
[image: image.png]
<https://us.v-cdn.net/6032257/uploads/35FPPSKFLLVS/image.png>
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org
