No question at this time
DBA Top 10
1 M. Cadot 5800
2 B. Vroman 3800
3 J. Péran 2200
4 A. Kavsek 1400
5 P. Tsongayinwe 800
6 D. Johnson 700
7 T. Boles 600
8 D. Walgude 400
9 J. Alcroft 200
9 S. Spb 200
9 P. Wisse 200
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48667
Total active users1333
Act. users last 24h2
Act. users last hour0
Registered user hits last week22
Registered user hits last month182
Go up

SQL with more than 1300 children
Next thread: Golden Gate issue
Prev thread: How to implement authentication basic in Oracle Restful data service with Weblogic 12c

Message Score Author Date
Hello everyone, I have an application in ...... Joël Péran Oct 26, 2021, 08:16
Hi again, In a MOS Note (2658406.1), I found ou...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Joël Péran Oct 26, 2021, 09:00

Follow up by mail Click here


Subject: SQL with more than 1300 children
Author: Joël Péran, France
Date: Oct 26, 2021, 08:16, 211 days ago
Os info: Windows 2K16 RE
Oracle info: 19.9 patch 201020
Message: Hello everyone,

I have an application in production which is both developped in C#, PHP and Pl/SQL.
One C# program is calling a stored procedure that just open a cursor with some parameters given at the beginning, and finally returns the cursor to the caller.
We saw that this particular SQL statement was having more than 3000 children (select sql_id,sql_text,count(1) from v$sql group by sql_id, sql_text having count(1) > 10;) and the database was completely stuck, with a lot of concurrency wait time events ...
In fact, after a call to the support, and as we are still in single instance mode, we had to add a hidden parameter to the database (_cursor_obsolete_threshold) with the value of 1024 instead of the default of 8192.
After doing that, everything went normal again. And no more contention, no more concurrency, no more nothing. Great performance and so on.
But today, I ran the query again to check the child numbers for the database ... and noticed that for this particular query, the number of child cursors was above 1300 ... Which is more than the limit fixed by the hidden parameter. I don't really think this is a normal case but I'm quite stuck. Do you have some ideas for me ?
Best regards,
Joel
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: SQL with more than 1300 children
Author: Joël Péran, France
Date: Oct 26, 2021, 09:00, 211 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi again,

In a MOS Note (2658406.1), I found out that :
<quote>When the child count of a SQL exceeding the threshold setting of "_cursor_obsolete_threshold", all the child cursor of this SQL will be marked as OBSOLETE, but the cursors' memory structures are still remaining in the lirary cache utill reclaimed in neccessary. Therefore, the actual valid and shareable child count is below the threhold setting of "_cursor_obsolete_threshold", please check the IS_OBSOLETE of V$SQL.
</quote>
So it seems to be a "normal" way of working ... But I'm still worried about that ... So many child numbers ...
Regards,
Joel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here