Oracle Explain Plan What does Mode Analyzed mean?

Hello,

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?).

Any help would be appreciated.

Thanks in advance.

-------------
--FIRST RUN
-------------
********************************************************************************

SELECT COUNT(*)
FROM
TEMP_TABLE_ONE TTO WHERE TTO.FIELD1 = :B5 AND TTO.FIELD2 = :B4
AND TTO.FIELD3 = :B3 AND TTO.FIELD4 = :B2 AND TTO.FIELD5 = :B1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 527160 195.13 207.95 0 0 0 0
Fetch 527160 21558.40 21871.63 0 918727201 0 527160
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1054321 21753.53 22079.58 0 918727201 0 527160

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 293 (USER_A) (recursive depth: 1)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 SORT (AGGREGATE)
0 TABLE ACCESS (BY INDEX ROWID) OF 'TEMP_TABLE_ONE' (TABLE
(TEMP))
0 INDEX (RANGE SCAN) OF 'IDX_TEMP_TABLE_ONE_01' (INDEX)

********************************************************************************


-------------
--SECOND RUN
-------------
********************************************************************************

SELECT COUNT(*)
FROM
TEMP_TABLE_ONE TTO WHERE TTO.FIELD1 = :B5 AND TTO.FIELD2 = :B4
AND TTO.FIELD3 = :B3 AND TTO.FIELD4 = :B2 AND TTO.FIELD5 = :B1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 527160 188.01 196.03 0 0 0 0
Fetch 527160 12.99 12.74 0 1932723 0 527160
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1054321 201.00 208.78 0 1932723 0 527160

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 293 (USER_A) (recursive depth: 1)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 SORT (AGGREGATE)
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'TEMP_TABLE_ONE' (TABLE (TEMP))
0 INDEX (RANGE SCAN) OF 'IDX_TEMP_TABLE_ONE_01' (INDEX)

********************************************************************************


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LVL 5
jtriftsMI and AutomationAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Franck PachotOracle DBACommented:
Hi,

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.

Regards,
Franck.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Franck PachotOracle DBACommented:
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.
0
jtriftsMI and AutomationAuthor Commented:
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
0
Franck PachotOracle DBACommented:
Hi,

I think I got it !

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

Regards,
Franck.
0
jtriftsMI and AutomationAuthor Commented:
Hi Franck,
Thanks for your reply.
I will take a look at the detail and come back.
Moderators: do not be surprised if this doesn't happen until Friday!
Thanks,
JT
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.