No question at this time
The DBA-Village forum
as RSS feed
Site Statistics| Ever registered users | 48741 | | Total active users | 1338 | | Act. users last 24h | 6 | | Act. users last hour | 0 | | Registered user hits last week | 145 | | Registered user hits last month | 435 |
|
Go up
SQL with more than 1300 children
| Subject: |
SQL with more than 1300 children |
| Author: |
Joël Péran, France |
| Date: |
Oct 26, 2021, 08:16, 580 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, 580 days ago |
| Score: |
  |
| 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
|