sql tuning

[Roshan]

Hi Erman,

I have a few questions regarding sql tuning.

Before executing
select * from table(dbms_xplan.display_cursor('<SQL ID>',NULL,'allstats typical last'));

I set

exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.gather_dictionary_stats;

or alter session set statistics=all;

For testing purpose, I ran xplain plan on the sql in the screenshot below.
pastedImage_0.png
The E-rows shows is it looping 4 rows from dba_data_files.

The second time I ran the xplain plan, the value is different for E-rows and FIXED TABLE INDEX
test2.PNG

Why is it so?

[ErmanArslansOracleBlog]

you didn't copy and paste the whole output , I guess?
Ther should be some note in the bottom -> cardinality feedback used for this statement

E-Rows is the number of the expected rows.
The optimizer calcuates this E-rows  using the statistics in the data dictionary.

Probably, your second execution plan used the information from the first execution plan.
So , i think it is because the "cardinality/statistics feedback

Take a look -> https://blogs.oracle.com/optimizer/cardinality-feedback