| SQL Tuning [message #65588] |
Wed, 03 November 2004 17:13  |
Sankar B. Mandalika
Messages: 20
Registered: November 2002
|
Junior Member |
|
|
Hi All,
I have a question regarding the evaluation of
an explain plan.
Here is the first query that I ran for the
explain plan:
===============================================
EXPLAIN PLAN
SET STATEMENT_ID = 'SEL001'
FOR
SELECT
id,
name,
company,
contract,
server,
revenue,
discount,
margin,
status
FROM corp
WHERE id != '';
===============================================
Here is the second query I ran to get the
output from the plan:
SELECT
lpad(' ',level-1)||operation||' '||options||' '||object_name "Query Plan",
cardinality "Rows",
cost "Cost"
FROM PLAN_TABLE
CONNECT BY prior id = parent_id
AND prior statement_id = 'SEL001'
START WITH id = 0;
===============================================
Following results are obtained when there are 10000
rows (ids) in the table.
***************10000 Begin********************
Query Plan
--------------------------------------------------------------------------------
Rows Cost
---------- ----------
SELECT STATEMENT
9999 11
TABLE ACCESS FULL CORP
9999 11
***************10000 End**********************
Following are the results obtained when there
30000 rows (ids) in the table.
***************30000 Begin********************
Query Plan
--------------------------------------------------------------------------------
Rows Cost
---------- ----------
SELECT STATEMENT
29999 30
TABLE ACCESS FULL CORP
29999 30
***************30000 End**********************
Here are my specific questions:
1. What are the COST figures of 30 and 11
mean?
2. How can I determine if the queries are
running without deteriorating the performance?
3. The COST was up by about 3 times when the
data was bumped up from 10K to 30K. On the
surface, this looks correct. What are the
other things that I should be looking at?
4. I did'nt see any major (or minor) difference
in the execution times between 10K and 30K.
5. If I need to bring down the COST figures,
what are my options? Is it advisable to
bring it down to single digits or does it
depend upon the data it is fetching?
This is Oracle 9.2.0.2.0 running on Solaris 9.
There is an index created on the id field. If I
o a select with a where condition that specifies
a specific id, then I do see the index usage.
Here is a desc on the table:
desc corp
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL VARCHAR2(256)
NAME VARCHAR2(256)
COMPANY VARCHAR2(250)
CONTRACT VARCHAR2(4)
REVENUE NUMBER(25,5)
DISCOUNT NUMBER(25,5)
MARGIN NUMBER(25,5)
STATUS VARCHAR2(64)
SERVER VARCHAR2(255)
Please let me know if you need additional details. Your help would be greatly appreciated.
Thanks,
Sankar.
|
|
|
|
| Re: SQL Tuning [message #65589 is a reply to message #65588] |
Wed, 03 November 2004 20:28  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
1) What are the COST figures of 30 and 11
mean?
A) COST is an internally used number, the Cost-based Optimizer uses to compare two plans when evaluating possible plans for a particular statement. It means practically NOTHING when comparing two different version of a statement.
2)How can I determine if the queries are
running without deteriorating the performance?
A) If it takes longer to execute than you think is acceptable.
3) The COST was up by about 3 times when the
data was bumped up from 10K to 30K. On the
surface, this looks correct. What are the
other things that I should be looking at
A) Look at IO. (search docs/Google for tkprof)
4) I did'nt see any major (or minor) difference
in the execution times between 10K and 30K.
A) First rows shown (as when using tools like TOAD) only fetches the first rows. FTS just started at first row found (no order-by).
Anyway, 10K or 30K rows is just peanuts.
5)If I need to bring down the COST figures,
what are my options? Is it advisable to
bring it down to single digits or does it
depend upon the data it is fetching?
A) See answers 1 & 2. Cost in itself is not a goal.
Look at your statement. You select all rows where id is not null. ID is a NOT NULL column, hence you select all rows. No other way for the optimizer than to go Full Table Scan.
Be happy it goes FTS, because an index-scan only involves a lookup in the index, followed by a lookup in the table, meaning a Full Table Scan PLUS an index-scan!
hth
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
