Performance in sql

Question

So i got one select from view and two tables, long story short,

first execution is ok, less than 1s, after first execution i lost performance, execution last about 30s

trick - after i change string add '(space)' or take off(somewhere in select) performance first execution is again 1s and second or more is 30s

exec plan i guess is the same first and second+ time execution...

Question - whats happening?

cache?

Yes, tried to rewrite in 5 another ways same, 30 seconds if more then one execution,if not changing select adding ' ' or take it off.

hope someone can help me :)

Best regards

Answer 1

This sounds like a problem caused by cardinality feedback. Cardinality feedback compares the estimated number of rows from an execution plan with the actual number of rows. If the difference is large the second execution will use the more accurate numbers. The optimizer generally works better when it has more accurate statistics. As with any complex model, there are weird cases where more accurate input causes problems.

To determine if this is really the case, find the SQL_ID with a statement like this:

select sql_id from v$sql where lower(sql_fulltext) like '%some unique string%';

Then find the current execution plan for that statement:

select * from table(dbms_xplan.display_cursor(sql_id => '<sql_id from above>'));

If cardinality feedback was used there will be something like this in the Note section:

Note
-----
   - cardinality feedback used for this statement

There are many ways that execution plans can change over time. To know for sure, please add the explain plan to the original question. Also the exact version of Oracle is important, there have been been significant changes to the new automatic re-optimization features in 12c.