Home » RDBMS Server » Performance Tuning » What influences cost?
What influences cost? [message #65754] Tue, 21 December 2004 11:08 Go to next message
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.
Re: What influences cost? [message #65757 is a reply to message #65754] Tue, 21 December 2004 21:45 Go to previous messageGo to next message
K. Prakash babu
Messages: 46
Registered: July 2001
Member
can u let me know the indexes(if any) on this table?
Re: What influences cost? [message #65758 is a reply to message #65754] Wed, 22 December 2004 00:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
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.
Re: What influences cost? [message #65759 is a reply to message #65757] Wed, 22 December 2004 03:28 Go to previous messageGo to next message
Shane Kaszyca
Messages: 16
Registered: May 2003
Junior Member
There is an index on the folder_path column.
Re: What influences cost? [message #65761 is a reply to message #65758] Wed, 22 December 2004 03:34 Go to previous messageGo to next message
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?
Re: What influences cost? [message #65762 is a reply to message #65761] Wed, 22 December 2004 03:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
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 Go to previous messageGo to next message
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 */
Re: What influences cost? [message #65764 is a reply to message #65763] Wed, 22 December 2004 04:16 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
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,
 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
 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!!!.
Re: What influences cost? [message #65765 is a reply to message #65762] Wed, 22 December 2004 04:19 Go to previous messageGo to next message
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)
Re: What influences cost? [message #65766 is a reply to message #65764] Wed, 22 December 2004 04:25 Go to previous messageGo to next message
Shane Kaszyca
Messages: 16
Registered: May 2003
Junior Member
Stupid question.... why are so many rows returned in the slow query?
Re: What influences cost? [message #65767 is a reply to message #65766] Wed, 22 December 2004 04:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As per your previous posting
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.
Re: What influences cost? [message #65768 is a reply to message #65767] Wed, 22 December 2004 04:33 Go to previous messageGo to next message
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.
Re: What influences cost? [message #65769 is a reply to message #65768] Wed, 22 December 2004 04:35 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
are both queries returning the same count of rows?
Re: What influences cost? [message #65770 is a reply to message #65769] Wed, 22 December 2004 04:42 Go to previous messageGo to next message
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)
Re: What influences cost? [message #65771 is a reply to message #65770] Wed, 22 December 2004 04:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
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.
Re: What influences cost? [message #65773 is a reply to message #65771] Wed, 22 December 2004 04:56 Go to previous messageGo to next message
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
Re: What influences cost? [message #65775 is a reply to message #65758] Wed, 22 December 2004 06:29 Go to previous message
K. Prakash babu
Messages: 46
Registered: July 2001
Member
Rebuild that index..and then check both the queries
Previous Topic: Temporary tables . Delete or Truncate?
Next Topic: FROM and WHERE clause
Goto Forum:
  


Current Time: Tue Sep 29 11:44:17 CDT 2020