Home » RDBMS Server » Performance Tuning » SQL Tuning and optimization- Help Needed (10g)
SQL Tuning and optimization- Help Needed [message #438995] Wed, 13 January 2010 09:26 Go to next message
benjamin.tl
Messages: 12
Registered: January 2010
Junior Member
Hi Guys I need a help from you....

I need to reduce the time of executing an insert statement which is in the below format.

******************************************************
insert into temp_customer NOLOGGING ( A,B, C, D, E, F )
select a.cust_code
, customer_hierarchy.cust_code
, customer_hierarchy.cust_market
, customer_hierarchy.cust_zone
, customer_hierarchy.cust_region
, customer_hierarchy.cust_area
from customer_os a,
(select mo1.cust_code
, mo1.LEVEL_1_CODE
, mo1.LEVEL_2_CODE
, mo1.LEVEL_3_CODE
, mo1.LEVEL_4_CODE
from marketing_os mo1
where mo1.LEVEL_1_CODE<>' '
and mo1.LEVEL_2_CODE<>' '
and mo1.LEVEL_3_CODE<>' '
and mo1.LEVEL_4_CODE<>' '
) dealer_hierarchy


******************************************************
Note:

*temp_customer is a table.

*customer_os is a synonym

*customer_hierarchy is a result of a select query which gets data from another synonym(marketing_os).

Please help me guys...I am new to this SQL Tuning and optimization.

******************************************************
Re: SQL Tuning and optimization- Help Needed [message #438997 is a reply to message #438995] Wed, 13 January 2010 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Please realize that at some point any given SQL can no longer be improved.
Re: SQL Tuning and optimization- Help Needed [message #439007 is a reply to message #438995] Wed, 13 January 2010 10:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are you sure you've got that query right?

1) There is no join condition between the two tables in the SELECT
2) It selects values from Marketing_OS, but never uses them.

The effect of this is to use MARKETING_OS as a row generator - ie you will get n identical records for each row in CUSTOMER_OS where n is the number of rows returned by the DEALER_HIERARCHY sub select.

To broaden the question - why are you inserting into a temporary table? It's not generally neccessary in Oracle, and in the cases where it is, you'd probably be much better off using a proper Temporary Table

Re: SQL Tuning and optimization- Help Needed [message #439099 is a reply to message #439007] Thu, 14 January 2010 04:27 Go to previous messageGo to next message
benjamin.tl
Messages: 12
Registered: January 2010
Junior Member
@JRowbottom

Sorry there has been small mistake in the query I gave...it is customer_hierarchy not dealer_hierarchy(in the last line)

the logic is to insert the data selected by the "select statement" into the table temp_customer.

The "select statement" fetches data from two different synonyms "customer_os" and "marketing_os" which has an alias name as "a" and "customer_hierarchy(not dealer_hierarchy)" respectively....since I am new to this forum I dont know how to correct the error in the query which I gave.

This is the correct one:

insert into temp_customer NOLOGGING ( A,B, C, D, E, F )
select a.cust_code
, customer_hierarchy.cust_code
, customer_hierarchy.cust_market
, customer_hierarchy.cust_zone
, customer_hierarchy.cust_region
, customer_hierarchy.cust_area
from customer_os a,
(select mo1.cust_code
, mo1.LEVEL_1_CODE
, mo1.LEVEL_2_CODE
, mo1.LEVEL_3_CODE
, mo1.LEVEL_4_CODE
from marketing_os mo1
where mo1.LEVEL_1_CODE<>' '
and mo1.LEVEL_2_CODE<>' '
and mo1.LEVEL_3_CODE<>' '
and mo1.LEVEL_4_CODE<>' '
) customer_hierarchy


I am new to this forum and I don't have much knowledge in tuning and optimization. But I am in a big trouble in finding a solution since the time frame allocated to me is less.
Re: SQL Tuning and optimization- Help Needed [message #439103 is a reply to message #438995] Thu, 14 January 2010 04:49 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Nope that's still not correct. There's no column called cust_market in the inline view for starters.
It would a lot easier if you just copied and pasted the original query.
Once you've posted the correct insert statement, read the post Blackswan linked to and post the requested information - the explain plan and a list of indexes on the tables would be a good start.
Re: SQL Tuning and optimization- Help Needed [message #439128 is a reply to message #439103] Thu, 14 January 2010 09:02 Go to previous message
benjamin.tl
Messages: 12
Registered: January 2010
Junior Member
Ok I will provide the details by referring the link above..
Previous Topic: 11g tuning
Next Topic: Wait event problem
Goto Forum:
  


Current Time: Sat Dec 03 12:28:05 CST 2016

Total time taken to generate the page: 0.14644 seconds