Home » RDBMS Server » Performance Tuning » Query Optimization (merged)
Query Optimization (merged) [message #402583] Mon, 11 May 2009 10:19 Go to next message
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 129 times)
Re: Query Optimization (merged) [message #402585 is a reply to message #402583] Mon, 11 May 2009 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can any one give methodology of tuning the sql statements and to predict the output of explain plan.

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Tuning High-Volume SQL Wiki page

Then provide the required information.

You can also have a look at Database Performance Tuning Guide.

Quote:
How can I optimize the query.

Quote:
upper(class_a_customer_id) = upper('CUSTDTH')

Add this in your index as well as customer_id.

Regards
Michel

[Updated on: Mon, 11 May 2009 10:30]

Report message to a moderator

Re: Query Optimization (merged) [message #402586 is a reply to message #402583] Mon, 11 May 2009 10:31 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/toc.htm


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Query Optimization (merged) [message #402591 is a reply to message #402585] Mon, 11 May 2009 10:42 Go to previous messageGo to next message
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 #402592 is a reply to message #402583] Mon, 11 May 2009 10:50 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Reposting same message is a waste of electrons.


>Note: cpu costing is off
What does this mean?
Re: Query Optimization (merged) [message #402594 is a reply to message #402591] Mon, 11 May 2009 10:51 Go to previous messageGo to next message
sdey
Messages: 20
Registered: May 2009
Junior Member
Hi,
I am also not getting Cost(% CPU) and Time in Explain plan.
Regards,
sdey.
Re: Query Optimization (merged) [message #402595 is a reply to message #402592] Mon, 11 May 2009 10:53 Go to previous messageGo to next message
sdey
Messages: 20
Registered: May 2009
Junior Member
HI,
I am getting this message.
Re: Query Optimization (merged) [message #402596 is a reply to message #402583] Mon, 11 May 2009 10:55 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I am getting this message.
What message?


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Query Optimization (merged) [message #402651 is a reply to message #402583] Mon, 11 May 2009 18:50 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
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 |
-----------------------------------------------------------------
Re: Query Optimization (merged) [message #402671 is a reply to message #402651] Tue, 12 May 2009 00:22 Go to previous messageGo to next message
Frank
Messages: 7880
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 #402675 is a reply to message #402583] Tue, 12 May 2009 00:28 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Frank,

OP was stated he did not have CPU % or Time in his EXPLAIN PLAN.
I provided a concrete example how to do so.

What is/was the point of your response?

What value added did your response added to the solution?

Lead, follow or stay out of the way!
Re: Query Optimization (merged) [message #402678 is a reply to message #402675] Tue, 12 May 2009 00:41 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 #402682 is a reply to message #402583] Tue, 12 May 2009 00:59 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
OK,
Frank,
I have failed.
Good Luck!
Re: Query Optimization (merged) [message #402721 is a reply to message #402681] Tue, 12 May 2009 03:17 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #402971 is a reply to message #402798] Wed, 13 May 2009 05:30 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
 upper(class_a_customer_id) = upper('CUSTDTH')


Instead of using funtioned based index would not it fair to create index on class_a_customer_id and use lower() in RHS?
Thanks
Re: Query Optimization (merged) [message #403025 is a reply to message #402971] Wed, 13 May 2009 09:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #403031 is a reply to message #403027] Wed, 13 May 2009 09:27 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
We have a reply button over here.
You can use it. No need to create a new topic.

By
Vamsi
Re: Query Optimization [message #403047 is a reply to message #403031] Wed, 13 May 2009 10:04 Go to previous messageGo to next message
sdey
Messages: 20
Registered: May 2009
Junior Member
Hi,
I have some more queries to optimize. Can I post them in the same title or create them as new topic?
Regards,
sdey.
Re: Query Optimization [message #403053 is a reply to message #403047] Wed, 13 May 2009 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
sdey wrote on Wed, 13 May 2009 08:04
Hi,
I have some more queries to optimize. Can I post them in the same title or create them as new topic?
Regards,
sdey.



You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Please NOTE a whole/complete sub-forum is dedicated to Performance issues available via URL below.

http://www.orafaq.com/forum/f/6/136107/


Please NOTE a lengthy post which enumerates what you need to do BEFORE posting any questions is at URL below.

http://www.orafaq.com/forum/t/84315/136107/

Re: Query Optimization [message #403058 is a reply to message #403047] Wed, 13 May 2009 10:31 Go to previous message
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
Previous Topic: No performance boost after upgrading database server RAM from 3GB to 16G
Next Topic: query
Goto Forum:
  


Current Time: Sun Dec 04 08:47:03 CST 2016

Total time taken to generate the page: 0.13812 seconds