| Query Optimization (merged) [message #402583] |
Mon, 11 May 2009 10:19  |
sdey
Messages: 20
Registered: May 2009
|
Junior Member |
|
|
Hi,
I am new in tuning sql statements. Can any one give methodology of tuning the sql statements and to predict the output of explain plan.
Can any one pls clarify explain plan for a particular query for me in detail.
Query is -
select customer_id from CUSTOMER
where upper(class_a_customer_id) = upper('CUSTDTH') AND customer_category_cd =1 AND is_user_manager = 2
PLAN_TABLE_OUTPUT -
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2117 | 50808 | 484 |
|* 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 2117 | 50808 | 484 |
|* 2 | INDEX RANGE SCAN | NDX_T_CUSTOMER_CLASSACUSTID | 12697 | | 41 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUSTOMER"."CUSTOMER_CATEGORY_CD"=1 AND "CUSTOMER"."IS_USER_MANAGER"=2)
2 - access(UPPER("CUSTOMER"."CLASS_A_CUSTOMER_ID")='CUSTDTH')
Note: cpu costing is off
How can I optimize the query. Please guide me.
-
Attachment: query.txt
(Size: 1.15KB, Downloaded 1282 times)
|
|
|
|
|
|
|
|
| Re: Query Optimization (merged) [message #402591 is a reply to message #402585] |
Mon, 11 May 2009 10:42  |
sdey
Messages: 20
Registered: May 2009
|
Junior Member |
|
|
Hi,
I hv created the following index -
CREATE INDEX ndx_cust_class_a_id ON CUSTOMER
(
class_a_customer_id ASC,
customer_category_cd ASC,
is_user_manager ASC
)
TABLESPACE ENABLERS_INDX
;
After creating the index the explain plan looks like this -
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2117 | 50808 | 58 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 2117 | 50808 | 58 |
|* 2 | INDEX SKIP SCAN | NDX_CUST_CLASS_A_ID | 2117 | | 55 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUSTOMER"."CUSTOMER_CATEGORY_CD"=1 AND
"CUSTOMER"."IS_USER_MANAGER"=2)
filter(UPPER("CUSTOMER"."CLASS_A_CUSTOMER_ID")='CUSTDTH' AND
"CUSTOMER"."CUSTOMER_CATEGORY_CD"=1AND "CUSTOMER"."IS_USER_MANAGER"=2)
Note: cpu costing is off
[Updated on: Mon, 11 May 2009 10:43] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query Optimization (merged) [message #402671 is a reply to message #402651] |
Tue, 12 May 2009 00:22 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| BlackSwan wrote on Tue, 12 May 2009 01:50 |
SQL> set autotrace traceonly explain
SQL> select sysdate from dual;
Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
|
Are you bragging or complaining?
Please post your DDL & DML
|
|
|
|
|
|
| Re: Query Optimization (merged) [message #402678 is a reply to message #402675] |
Tue, 12 May 2009 00:41 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
CPU costing is off because system statistics were not calculated.
| Quote: | What is/was the point of your response?
What value added did your response added to the solution?
|
Totally agree.
Now keep that in mind for future responses!
|
|
|
|
| Re: Query Optimization (merged) [message #402681 is a reply to message #402651] |
Tue, 12 May 2009 00:55 |
sdey
Messages: 20
Registered: May 2009
|
Junior Member |
|
|
Hi ,
Please find the details-
EXPLAIN PLAN FOR
SELECT customer_id FROM CUSTOMER WHERE UPPER(class_a_customer_id) = UPPER('CUSTDTH') AND customer_category_cd =2 AND is_user_manager = 1
SELECT plan_table_output FROM TABLE(dbms_xplan.display('plan_table',NULL,'serial'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2117 | 50808 | 484 |
|* 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 2117 | 50808 | 484 |
|* 2 | INDEX RANGE SCAN | NDX_T_CUSTOMER_CLASSACUSTID | 12697 | | 41 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUSTOMER"."CUSTOMER_CATEGORY_CD"=2 AND "CUSTOMER"."IS_USER_MANAGER"=1)
2 - access(UPPER("CUSTOMER"."CLASS_A_CUSTOMER_ID")='CUSTDTH')
Note: cpu costing is off
|
|
|
|
|
|
| Re: Query Optimization (merged) [message #402721 is a reply to message #402681] |
Tue, 12 May 2009 03:17 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You needed to pay slightly closer attention to @Michel's suggestion.
Instead of creating an index on Class_a_customer_id, he suggested creating one on Upper(class_a_customer_id) and customer_id.
If the performace of this one query is very important, I'd say that the best index to create would be on:
Upper(class_a_customer_id)
customer_category_cd
is_user_manager
customer_id
That should allow the optimiser to resolve the query without ever talking to anything except the index.
|
|
|
|
| Re: Query Optimization (merged) [message #402788 is a reply to message #402721] |
Tue, 12 May 2009 08:34 |
sdey
Messages: 20
Registered: May 2009
|
Junior Member |
|
|
I have created index as follows-
CREATE INDEX ndx_cust_class_a_id ON CUSTOMER
(
upper(class_a_customer_id) ASC,
customer_category_cd ASC,
is_user_manager ASC
)
;
Do I have to create index on customer_id fields?
|
|
|
|
| Re: Query Optimization (merged) [message #402798 is a reply to message #402788] |
Tue, 12 May 2009 09:09 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Currently, your index can be used to identify the rows in the table that need to be read to return the data for the query.
If you add Customer_Id to the end of that index, then the index will contain all the data neccessary to build the result set for the query. Oracle is smart enough to know that it doesn't need to access the table at all in these cases, which can substantially lower the data read from the disk, and improve the performance.
|
|
|
|
|
|
| Re: Query Optimization (merged) [message #403025 is a reply to message #402971] |
Wed, 13 May 2009 09:19 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I imply from the fact that the OP is using UPPER on the column that they have mixed case data stored in that column.
How do you come to the conclusion that all the data in that column is currently lower case?
|
|
|
|
| Query Optimization [message #403027 is a reply to message #402583] |
Wed, 13 May 2009 09:22 |
sdey
Messages: 20
Registered: May 2009
|
Junior Member |
|
|
Hi,
I need to optimize the following query .Please find the details -
SQL>
EXPLAIN PLAN FOR
SELECT order_id FROM CRMIS_SUBSCRIPTION O WHERE O.crmis_order_status = 9 AND ( O.jms_attempt_cnt IS NULL OR O.jms_attempt_cnt < 6) AND O.crm_cancel_dt IS NULL AND O.crmis_order_close_dt IS NULL
SQL>
SELECT plan_table_output FROM TABLE(dbms_xplan.display('plan_table',NULL,'serial'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3914 | 74366 | 842 |
|* 1 | INDEX FAST FULL SCAN| NDX_CRMIS_SUBSC_JMS | 3914 | 74366 | 842 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("O"."CRMIS_ORDER_STATUS"=9 AND ("O"."JMS_ATTEMPT_CNT" IS NULL
OR "O"."JMS_ATTEMPT_CNT"<6) AND "O"."CRM_CANCEL_DT" IS NULL AND
"O"."CRMIS_ORDER_CLOSE_DT" IS NULL)
Note: cpu costing is off
Following index is present -
CREATE INDEX ndx_crmis_subsc_jms ON CRMIS_SUBSCRIPTION
(
crmis_order_status ASC,
order_id ASC,
jms_attempt_cnt ASC,
crm_cancel_dt ASC,
crmis_order_close_dt ASC
)
;
|
|
|
|
|
|
|
|
|
|
| Re: Query Optimization [message #403058 is a reply to message #403047] |
Wed, 13 May 2009 10:31 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
|
Post them in the Performance Tuning forum, and please read the Sticky Post at the top of the forum to get an idea o fthe information that we like to have to enable us to do remote performance tuning
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
