Hello community,
I'd like to benefit from your experience with understanding optimizer plan to be used
In the context of moving our app on Oracle19 (currently on Oracle11), I'm doing a simple benchmark where I execute my test on both server and look at end-to-end elapsed time.
impacted tables are empty at the beginning of the test. a backup of the source schema is reloaded on both instance with a schema gather stats so it sounds to me that the test-case is executed almost on same conditions
Oracle19 is 'slower' and now I need to understand the difference.
I did an AWR to search for any guitly sql and I noticed one statementwith following info (info available also in attached document section "DATA FROM AWR")
sqlid: 9b5x2yx7js94h
elapsed time(s): 104,057.03
executions: 3,398
elapsed time per Exec (s): 30.62
%Total: 86.86
%CPU: 99.56
%IO: 0.00
Then I executed the statement below to get the plan but it returns me several ones (one per child)
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('9b5x2yx7js94h',null,'ALLSTATS LAST'));
Most of the plans are identical. What concerns me is the 'Starts', 'A-Rows' and 'Buffers' (info available also in attached document section "SELECT * FROM table...")
Looking at v$sql I can see several children for this sqlid.
we can see two of them were mostly executed and the elapsed is coming from there (info available also in attached document section "V$SQL INFO FOR THIS SQLPID")
Having those information,
I'm now wondering why do we have several child cursor?
shall I purge my cache to be sure my test is being executed correctly?
What would you do if you were to correct this?
Thanks all for your suggestions
Simon
question_community.txt (8.21 KB)