Oracle Explain Plan What does Mode Analyzed mean?

I'm trying to figure out what exactly the text "MODE: ANALYZED" means when identified in an oracle explain plan and why the presence of this text seems to result in drastically different performance.

I'm using Oracle 10.2.0.4.0 and looking at the TKPROF output (below) from re-running the same process twice, against the exact same data (we used Oracle Flashback between the 2 runs to ensure the exact same data), I'm seeing drastically different run times and the only difference in the explain plan of both runs appears to be the presence of the text "MODE: ANALYZED" (on the third line of the explain plan).

Note: TEMP_TABLE_ONE is a Global Temporary Table and we did manually plug in statistics for this table using DBMS_STATS.SET_TABLE_STATS on the second run only (This was the only change to the environment between the 2 runs): I would assume that the "MODE: ANALYZED" showing up in the second Run has something to do with us setting statistics on the global temporary table, however in both cases the same index appears to be used so I am not understanding why such a performance difference when "MODE: ANALYZED" shows up as part of the explain plan, nor am I sure exactly what "MODE: ANALYZED" is supposed to be telling me (is it simply saying that statistics exist for the table?).

You are right, the 'ANALYZED' here comes from the OPTIMIZER column of PLAN_TABLE, and it shows that the table was analyzed.
But this is not the explanation of your performance problem.
The first run had to read 918727201 blocks, but the second run needed only 1932723.

Can you explain exactly what you did before the first run, how you loaded data.

I tried to think for a possible reason why you have that difference in consistent reads, but there is not enough information.
You said it is a Global Temporary Table, but you said you used Flashback. you can't Flashback a temporary table, so there is something more complex.
And because you had to flashback, that mean you have made updated to data.

Hi Franck,
Thanks for your reply.
Will dig a bit deeper. At this point, I believe that with flashback the data prior to running the GTT population was reset to the before picture, then stats *artificially* generated, and the process, which also loads the GTT, was re-run.
Will come back with more info -- but if you are able to provide any recommendations what we should be looking for in this example, I'm "all ears".
Regards,
JT

The tkprof do not show the real execution plan (that would be titled 'Row Source Operation ') but an explain plan that was done at the time you have run the tkprof.
Table was empty at that time and this is why you see 0 rows in the execution plan.

When the query has been executed at the first time, oracle has probably done a full table scan. I suppose the table had 1742 blocks under high water mark, and that why you have 1472*527160=18727 201 consistent reads.

At the second time, with statistics on the index, oracle has seen that the where clause was very selective, and has choosen an index access.

By the way, if you have some thing to change, it is probably not a good idea to run that query 527160 times. You probably can get the whole faster with:

SELECT FIELD1,FIELD2,FIELD3,FIELD4,FIELD5, COUNT(*)
FROM TEMP_TABLE_ONE TTO group by FIELD1,FIELD2,FIELD3,FIELD4,FIELD5

Truncate is a DDL Command where as Delete is a DML Command.
Both will delete data from table, but what is the difference between these below statements
truncate table <table_name> ??
delete from <table_name> ??
The first command cannot be …

Working with Network Access Control Lists in Oracle 11g (part 2)
Part 1: http://www.e-e.com/A_8429.html
Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access. For many…