We have an unusual issue. We are doing UAT and the users complained that a screen took very long to query and get the result.
Analyzing the issue I discovered the following.
(1.) Users invokes the screen, specifies the parameters and press the Query button.
(1.) Our Java front-end dynamically creates the SQL by adding the parameters and sends it to the Oracle DB to run and fetch the result rows.
(2.) The fairly large SQL has 2 PL/SQL functions that is used in the SELECT and WHERE clauses. This will definitely cause context switching.
(3.) Both these functions use the same SQL (one uses additional coding).
(4.) it is one of the WHERE clause conditions that is causing the performance issue.
(5.) I.e. If a remove this condition in both SQLs performance improves to acceptable levels.
However the thing is if I compare the PLANS for this SQL (inside the function) with and without that condition,
the one without the condition has a much higher overall cost (75) that the one with the condition (which
is causing the performance issue) (21).
But if I runt The SQL (with and without the WHERE clause) for a single-case, the one with the condition
runs in 0.229 seconds, while the one without the condition runs only n 0.05 seconds (4.58 times faster).
Why is this?
Mind you the condition concerned which goes like p.chargetype = 'NA' and the product table do
have an index for this column chargetype, but it still gives a performance degradation.
Why is this???