No question at this time
DBA Top 10
1 M. Cadot 6700
2 B. Vroman 3900
3 A. Kavsek 2700
4 J. Péran 1700
5 T. Boles 600
5 D. Johnson 600
7 T. P 500
8 P. Wisse 400
9 S. Spb 300
10 V. Gupta 200
10 M. Jain 200
10 K. Pingeli 200
10 R. Pastre 200
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48649
Total active users1382
Act. users last 24h1
Act. users last hour1
Registered user hits last week81
Registered user hits last month285
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, 42 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, 42 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