IO and CPU cost - direct path read

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Tue, 24 May 2005 01:55:36 -0700 (PDT)
Message-ID: <20050524085536.97331.qmail@web52601.mail.yahoo.com>

Hi all,

I have an interesting IO and CPU cost calculation problem.

I am missing something crucial here.

I am doing comparisons of costs for two things to better understand the costs when direct path reads are used:

select /*+ full(x) */ count(*) from x;
select /*+ full(x) parallel(x 2) */ count(*) from x;

I did the same on another table y to compare results. select /*+ full(y) */ count(*) from y;
select /*+ full(y) parallel(x 2) */ count(*) from y;

Stats for table x and y are almost identical, because the tables are the same (apart from 3 new added columns in x out of around 50 - maybe clue for somebody).

Statistics for x and y:
#NBLKS = 28534 (x) | 28201 (y)
TBS = USERS for both
Average row length = 233 (x) | 229 (y)
CHAIN_CNT = 0 for both
ANALYZED = YES I got these values for IO and CPU cost from v$sql_plan (Oracle is 9.2.0.6):

parallel SQL******************** IO cost: parallel(x) - 37 full(x) - 1619 parallel(y) - 801 full(y) - 1601

CPU cost:
parallel(x) - 2.5M (+1 to the overall cost) full(x) - 313M (+55 to the overall cost) parallel(y) - 55M (+10 to the overall cost) full(y) - 311M (+54 to the overall cost)

Overall cost:
parallel(x) - 38
full(x) - 1674
parallel(y) - 811
full(y) - 1655

System statistics are:

SREADTIM                           10.257
MREADTIM                           46.526
CPUSPEED                              563
MBRC                                   80
MAXTHR                          255572992
SLAVETHR                           425984

The number of CPU's is 24 and the block size is 8k.

How that big difference in the IO cost of direct path read in the case of parallel(x) SQL?

I just do not get it. As you can see because of parallel 2 you got IO cost down to 2 as you expect. Also direct path read CPU cost is lower as you would expect. The thing we know is CPU cost depends on the system stats values.

But how that 37 came in the game?

I just had a difficult time with direct path reads costing in the SQL's where I have subqueries that are materialized in the TEMP table then used in NESTED loops with direct path reads of course.

Thanks in advance.

Regards,
Zoran

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 24 2005 - 05:00:25 CDT