Excellent and clear answer.

If I could, what actually is the CARD term next to the COST?

Thanks very much Tom. Your answer helped clear many doubts.

Great Tom!!!,

Ur Answers clear a lot of doubts.
One thing i wanted to ask was that in a Paramter file , when specifying optimizer_mode. What is the diffference between all_rows and last_row.

Regards,
Ashwani Singh

Tom,

This is related to explain plans - if this is an inappropriate spot then please say so and I'll wait in the queue.

In Tkprof, you get a rows processed count. What is this representing? I used to think it was the actual number of rows for that step, however just yesterday I experienced the number of rows returned from the query (one I was tuning) to be higher than this count. Here's visual info since that is always helpful:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.33 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2153 320.83 1196.45 2341431 2343495 60 32275
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2155 320.90 1196.78 2341431 2343495 60 32275

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 119 (TESTTUNE)

Rows Row Source Operation
------- ---------------------------------------------------
32275 HASH JOIN
418 TABLE ACCESS FULL REF_ORGANIZATION
414816 TABLE ACCESS FULL EXT_XPAYD_PAY_DETAILS

Adding a bitmap index, I saw different row counts in tkprof, which scared me into thinking that somehow the query was altered, but I spooled the query without the index and with the index, retrieving 32595 rows each time.

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2174 30.42 260.61 129576 138357 15 32595
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2176 30.43 260.62 129576 138357 15 32595

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 119 (TESTTUNE)

Rows Row Source Operation
------- ---------------------------------------------------
32595 HASH JOIN
418 TABLE ACCESS FULL REF_ORGANIZATION
421154 INLIST ITERATOR
421154 TABLE ACCESS BY INDEX ROWID EXT_XPAYD_PAY_DETAILS
421728 BITMAP CONVERSION TO ROWIDS
158 BITMAP INDEX SINGLE VALUE

The number of rows returned by the query is 32595:
--- original ---
32595 rows selected.

Elapsed: 00:17:1028.59
15:31:22 TESTTUNE@HIP2:sca01> spool off
---
--- added bitmap
32595 rows selected.

Elapsed: 00:03:203.53
15:07:13 TESTTUNE@HIP2:sca01> spool off
---

So...why did I get a different number of rows on the original explain plan? Any ideas??


Thanks,
Dennis

Referring to your answer to the original question:

After the delete, the autotrace shows the same Cost and a lower Cardinality. But why is the bytes lower? If it is doing a FTS then I would have expected the bytes remained same? So what is this bytes thing actually?

Well the second trace file is larger. Nobody should've modified the init parameters...I'm pretty sure I was the only one in there, and it normally takes a couple of meetings and a ripped off arm to get things like that changed (I still do not understand why they think EVERY database needs to have the same init parameters...), so I don't imagine anyone would've changed it, especially since I usually make those suggestions and I don't recall losing an arm. ;)

I have 10240 for the max_dump_file_size. That is in KB, right? The first trace file (the weird one) is 3.68 MB, the other is 5.00 MB. It does say at the bottom of the first file that it's 2xxx lines in the trace file, but the tkprof output shows only 100 or so (I didn't weed out anything either, unless it does so by default (used to have to weed out the sys, that change in 9iR2? Guess I need to look that up don't I...)).

At any rate, it doesn't really matter now that you've confirmed that I wasn't hallucinating or anything. Rows processed. Anything else means that something screwy happened to the file. Works for me.

Thanks Tom, you're the greatest! Sorry I didn't respond right away, I was in a meeting...but I did check first thing when I got back (well...after I checked out my tar). You give us such prompt service, it's only right that we do the same!
Dennis

OK. So bytes is the number of bytes returned by the query and not the number of bytes read in order to service the query. That was my confusion. Thanks.

Tom

I've converted our development db from RBO to CBO and
performed dbms_stats on key tables and have plans to
convert Production db after testing(so far, so good)

One of the tables is fairly large 8+ mil records with
inserts and deletes taking place on a daily basis.
(older data being archived while new data is loaded).

Since CBO uses the HWM for Full Table Scans
then it is safe to assume the area where older records
were deleted and is now empty is still being scanned
by the CBO in computing the cost of execution.

To avoid this additional cost to the plan I'm surmising
I should export/import the table after large deletes
to bring the HWM to the correct level so that the CBO can
calculate a more accurate execution plan.

Is there another way to get the HWM corrected w/o having
to do exp/imp?

thanks

perhaps not on a daily basis, more of a weekly basis.

We can have 20,000+ inserts on any given day with 10,000
deletes on another day(which would raise the HWM another 10,000)...however there can be days when we "remove"
a substantial amount of data(10,000-20,000) for archiving.
I suppose in the grand scheme of things this amount of
data loading and removing is immaterial to the cost of
execution plans. We are not talking about millions of records.
Was wondering if the amount of fragmentation with the inserts/deletes poses any detrimental effects.
Just trying to ensure the optimization of the database and it's performance.

thanks!