Home » RDBMS Server » Performance Tuning » query tunning
query tunning [message #207910] Thu, 07 December 2006 05:50 Go to next message
baskarb_2006
Messages: 5
Registered: September 2006
Location: chennai
Junior Member

Pl. anyone help me in tuning the below attached query.

This query takes longer time to run in our database. Explain plan output attached with this mail.

Pl. help me in this regard.

Pl. find query and explain plan attached


Regards

Baskar

Re: query tunning [message #207912 is a reply to message #207910] Thu, 07 December 2006 05:51 Go to previous messageGo to next message
baskarb_2006
Messages: 5
Registered: September 2006
Location: chennai
Junior Member

forget to attach explain plan output. now i am attaching
Re: query tunning [message #208008 is a reply to message #207912] Thu, 07 December 2006 16:19 Go to previous message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

baskarb_2006,

Please provide the query also in the forum. It may help others to look at and give you suggestion.

second, did you check the query whether it has proper join or not?

Did you or DBA ran gather statistics?

Look out there are so many places it does full table scan which has more rows.

300438 TABLE ACCESS FULL SUM_SUPER_AML 1 10 ANALYZED 97
458131 TABLE ACCESS FULL SUM_OPEN_POS 1 10 ANALYZED 165
5320325 TABLE ACCESS FULL DM_GLOBAL_PRICING_EXP 1 1 ANALYZED 161
170493 TABLE ACCESS FULL DM_GLOBAL_PRICING_EXP 1 1 ANALYZED 161
300438 TABLE ACCESS FULL SUM_SUPER_AML 1 10 ANALYZED 97
300438 TABLE ACCESS FULL SUM_SUPER_AML 1 10 ANALYZED 97
458131 TABLE ACCESS FULL SUM_OPEN_POS 1 10 ANALYZED 165
5349050 TABLE ACCESS FULL DM_GLOBAL_PRICING_EXP 1 1 ANALYZED 161
171414 TABLE ACCESS FULL DM_GLOBAL_PRICING_EXP 1 1 ANALYZED 161
300438 TABLE ACCESS FULL SUM_SUPER_AML 1 10 ANALYZED 97
12472 TABLE ACCESS FULL DM_CUST_GLOBAL_PRICING_EXP 1 1 ANALYZED 39
300438 TABLE ACCESS FULL SUM_SUPER_AML 1 10 ANALYZED 97
12472 TABLE ACCESS FULL DM_CUST_GLOBAL_PRICING_EXP 1 1 ANALYZED 39
300438 TABLE ACCESS FULL SUM_SUPER_AML 1 10 ANALYZED 97
12595 TABLE ACCESS FULL DM_CUST_GLOBAL_PRICING_EXP 1 1 ANALYZED 39
300438 TABLE ACCESS FULL SUM_SUPER_AML 1 10 ANALYZED 97
12595 TABLE ACCESS FULL DM_CUST_GLOBAL_PRICING_EXP 1 1 ANALYZED 39
300438 TABLE ACCESS FULL SUM_SUPER_AML 1 10 ANALYZED 97

You need to look at the query, joins and if need be create indexes.

Jay
Fun Programming with Oracle & Visual C++, Author of http://www.lightsql.com.
Previous Topic: DB tuning
Next Topic: nothing in trace-file
Goto Forum:
  


Current Time: Sat Dec 03 16:10:44 CST 2016

Total time taken to generate the page: 0.04263 seconds