OraFAQ Forum: Performance Tuning » What influences cost?

Home » RDBMS Server » Performance Tuning » What influences cost?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

What influences cost? [message #65754]

Tue, 21 December 2004 11:08

Shane Kaszyca
Messages: 16
Registered: May 2003

Junior Member

I have a table - "folder". Two columns of importance are folder_id and folder_path. I have an index on the column "folder_path". The cost of one of the queries is significantly higher than the other, and I have no clue why. Can anyone give me an idea of how I can determine why the cost is higher? I can provide more information if you require.

Query 1:
select folder_id from folder
where folder_path='/TKB/Compliance/International U.S.'
Cost 1: 82

Query 2:
select folder_id from folder
where folder_path='/TKB/Compliance/Federal U.S.'
Cost 2: 2

The table has approximately 217000 entries in it. About 183000 of the rows have null for the folder_path and the other 34000 have a unique value. Your help is much appreciated.

Report message to a moderator

Re: What influences cost? [message #65757 is a reply to message #65754]

Tue, 21 December 2004 21:45

K. Prakash babu
Messages: 46
Registered: July 2001

Member

can u let me know the indexes(if any) on this table?

Report message to a moderator

Re: What influences cost? [message #65758 is a reply to message #65754]

Wed, 22 December 2004 00:52

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

cost is just a number derived by CBO( based on numerous flip flops like data distribution).
It can be anything. Sometimes it may have no affect also.
( i mean , the cost may be high the query would be running fast and VICE VERSA).
This number ( the relative cost of an individual step in your sql) is used by CBO when it is looking for an optimal path to fetch the data.
CBO eventually picks the plan with a lower cost.

Even in an utopian environment, comparing two costs may be inaccurate.
Generally, cost is NOT considered as a primary measure for tuning as becuase COST and response time are NOT DIRECTLY RELATED.
By tweaking a few known parameters like optimizer_index_cost_ad you can see the numbers change.

So, analyze the tables and associated indexes.
Thus update the statistics.
try again.

Report message to a moderator

Re: What influences cost? [message #65759 is a reply to message #65757]

Wed, 22 December 2004 03:28

Shane Kaszyca
Messages: 16
Registered: May 2003

Junior Member

There is an index on the folder_path column.

Report message to a moderator

Re: What influences cost? [message #65761 is a reply to message #65758]

Wed, 22 December 2004 03:34

Shane Kaszyca
Messages: 16
Registered: May 2003

Junior Member

This is exactly my problem. These two queries are part of a larger query. The larger queries are generating two different query plans. These two different query plans I believe are partly based on the cost of these queries I have posted. It seems to me that the cost of these two queries I have posted should be close, if not identical, as the column used in the where clause is in an indexed column. Am I not correct?

Report message to a moderator

Re: What influences cost? [message #65762 is a reply to message #65761]

Wed, 22 December 2004 03:59

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>. It seems to me that the cost of these two queries I have posted should be close, if not identical, as the column used in the where clause is in an indexed column.

NO.
Not a must.
All becuase a column is indexed it doesnt mean that the cost should be high or low .
It actually depends on the number of rows scanned.
If the index is used in your query, it may be a litter faster.
There are many reason why the query may not use an index.
and sometimes ( if there is too many rows to be returned)
CBO will not use an index , becuase it is much efficient to have full table scan.

So first update the statistics of your table.
then look into execution plan.

Please have a look in this thread
http://www.orafaq.com/forum/t/23478/0/
and the followup thread by Frank
http://www.orafaq.com/forum/t/23478/0/

[Updated on: Fri, 18 February 2005 23:32]

Report message to a moderator

Re: What influences cost? [message #65763 is a reply to message #65762]

Wed, 22 December 2004 04:08

Shane Kaszyca
Messages: 16
Registered: May 2003

Junior Member

I updated the stats and here are the query plans. As you can see, the query plans are the same - but the cost and Bytes differ significantly. Any insight?

/* Start : Plan and Statistics for slow query */
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=82 Card=100688 Bytes=3322704)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DM_FOLDER_R' (Cost=82 Card=100688 Bytes=3322704)
2 1 INDEX (RANGE SCAN) OF 'D_1F000C4F80000015' (NON-UNIQUE) (Cost=43 Card=15901)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
226 bytes sent via SQL*Net to client
318 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
/* End : Plan and Statistics for slow query */

/* Start : Plan and Statistics for fast query */

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=200 Bytes=6600)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DM_FOLDER_R' (Cost=2 Card=200 Bytes=6600)
2 1 INDEX (RANGE SCAN) OF 'D_1F000C4F80000015' (NON-UNIQUE) (Cost=3 Card=32)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
226 bytes sent via SQL*Net to client
318 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

/* End : Plan and Statistics for fast query */

Report message to a moderator

Re: What influences cost? [message #65764 is a reply to message #65763]

Wed, 22 December 2004 04:16

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

CBO does the job exactly what it needs to do!.
you can seee
for slow query,

Code: [Select all] [Show/ hide]

 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=82 Card=100688 Bytes=3322704)

card is 100688 or approximately 100688 rows is returned.
TO make this happen, the estimated cost is 82.

for the fast query

Code: [Select all] [Show/ hide]

 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=200 Bytes=6600)

it is returning only 200 rows so the cost is significanly less and so is the total bytes processed!!!.

Report message to a moderator

Re: What influences cost? [message #65765 is a reply to message #65762]

Wed, 22 December 2004 04:19

Shane Kaszyca
Messages: 16
Registered: May 2003

Junior Member

Hello,

I created a copy of the table by doing a create table as select, and then created an index on the newly created table. I analyzed the table and the index and these are my query plans for the copied table... By this, would you think the root problem with the original table lies in the table or in the index? This is the execution plan that is reported for BOTH queries - which is how I would expect it to work for the original table.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=6 Bytes=198)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FOLDER' (Cost=2 Card=6 Bytes=198)
2 1 INDEX (RANGE SCAN) OF 'FOLDER_PATH_INDEX' (NON-UNIQUE) (Cost=3 Card=1)

Report message to a moderator

Re: What influences cost? [message #65766 is a reply to message #65764]

Wed, 22 December 2004 04:25

Shane Kaszyca
Messages: 16
Registered: May 2003

Junior Member

Stupid question.... why are so many rows returned in the slow query?

Report message to a moderator

Re: What influences cost? [message #65767 is a reply to message #65766]

Wed, 22 December 2004 04:29

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

As per your previous posting

Code: [Select all] [Show/ hide]

Query 1:
select folder_id from folder
where folder_path='/TKB/Compliance/International U.S.'
Cost 1: 82

Query 2:
select folder_id from folder
where folder_path='/TKB/Compliance/Federal U.S.'
Cost 2: 2

Both are very different queries!!!!!.
the where condition is changed.
actually, the query is slow because it is returning MORE ROWS.

Report message to a moderator

Re: What influences cost? [message #65768 is a reply to message #65767]

Wed, 22 December 2004 04:33

Shane Kaszyca
Messages: 16
Registered: May 2003

Junior Member

Could my query benefit from reorganizing the table or resequencing the rows? As I mentioned in one of my previous postings, if I create a copy of the table and index the copy, the results return quite quickly in both cases.

Report message to a moderator

Re: What influences cost? [message #65769 is a reply to message #65768]

Wed, 22 December 2004 04:35

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

are both queries returning the same count of rows?

Report message to a moderator

Re: What influences cost? [message #65770 is a reply to message #65769]

Wed, 22 December 2004 04:42

Shane Kaszyca
Messages: 16
Registered: May 2003

Junior Member

These are the results of the fast and slow queries on both the original and copied tables. As you can see, the execution plan for both queries in the copied table are identical.

/* Original Table Execution Plan (Slow Query) */
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=82 Card=100688 Bytes=3322704)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DM_FOLDER_R' (Cost=82 Card=100688 Bytes=3322704)
2 1 INDEX (RANGE SCAN) OF 'D_1F000C4F80000015' (NON-UNIQUE) (Cost=43 Card=15901)

/* Original Table Execution Plan (Fast Query) */
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=200 Bytes=6600)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DM_FOLDER_R' (Cost=2 Card=200 Bytes=6600)
2 1 INDEX (RANGE SCAN) OF 'D_1F000C4F80000015' (NON-UNIQUE) (Cost=3 Card=32)

/* Copied Table Execution Plan (Fast Query) */
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=97 Bytes=3201)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FOLDER' (Cost=2 Card=97 Bytes=3201)
2 1 INDEX (RANGE SCAN) OF 'FOLDER_PATH_INDEX' (NON-UNIQUE) (Cost=3 Card=15)

/* Copied Table Execution Plan (Slow Query) */
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=97 Bytes=3201)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FOLDER' (Cost=2 Card=97 Bytes=3201)
2 1 INDEX (RANGE SCAN) OF 'FOLDER_PATH_INDEX' (NON-UNIQUE) (Cost=3 Card=15)

Report message to a moderator

Re: What influences cost? [message #65771 is a reply to message #65770]

Wed, 22 December 2004 04:45

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

NO they are not the same.
You can see the card on new index is different from old.

You said you analyzed the table ( old table).
did you analyze the old index?
analyze the old table and old index and try again.

Report message to a moderator

Re: What influences cost? [message #65773 is a reply to message #65771]

Wed, 22 December 2004 04:56

Shane Kaszyca
Messages: 16
Registered: May 2003

Junior Member

You know what, I analyzed the index but not the table. Now that I analyzed the table it is working. The execution plans appear to be working as expected now. I am in your debt. Thank you and happy holidays.

Shane Kaszyca

Report message to a moderator

Re: What influences cost? [message #65775 is a reply to message #65758]

Wed, 22 December 2004 06:29

K. Prakash babu
Messages: 46
Registered: July 2001

Member

Rebuild that index..and then check both the queries

Report message to a moderator

Previous Topic:	Temporary tables . Delete or Truncate?
Next Topic:	FROM and WHERE clause

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 06 23:52:28 CST 2025