Home » RDBMS Server » Performance Tuning » Insert from Select - Performance Issue (Oracle 11 g)
Insert from Select - Performance Issue [message #641711] Thu, 20 August 2015 09:22 Go to next message
ram50958
Messages: 7
Registered: September 2010
Junior Member
How can I rewrite the below query to make it faster? I have indexes on all the columns being used in where clause. Both the tables being used have millions of records.
INSERT INTO au_comparison_goc(src_au_src_name, src_seq_id, trgt_au_src_name, trgt_seq_id, active_goc)
                  SELECT  DISTINCT src.au_source, src.seq_id, trgt.au_source, trgt.seq_id, 'A'
                  FROM  dsmt_assmt_au_mv src,
                        dsmt_assmt_au_mv trgt,
                        au_distinct_goc src_goc ,
                        au_distinct_goc trgt_goc
                  WHERE src.seq_id = src_goc.seq_id
                  AND trgt.seq_id = trgt_goc.seq_id
                  AND trgt.seq_id != src.seq_id
                  AND src.au_source = au_src.au_source
                  AND trgt.au_source = trgt_src.au_source
                  AND src_goc.goc = trgt_goc.goc
                  AND src_goc.src_eff_status = 'A'
                  AND trgt_goc.src_eff_status = 'A';

*BlackSwan added {code} tags. Please do so yourself in the future.
How to use {code} tags and make your code easier to read

[Updated on: Thu, 20 August 2015 09:26] by Moderator

Report message to a moderator

Re: Insert from Select - Performance Issue [message #641712 is a reply to message #641711] Thu, 20 August 2015 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member

ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#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
Re: Insert from Select - Performance Issue [message #641713 is a reply to message #641711] Thu, 20 August 2015 09:38 Go to previous messageGo to next message
John Watson
Messages: 7669
Registered: January 2010
Location: Global Village
Senior Member
THis looks like a variation on your previous post,
http://www.orafaq.com/forum/m/641630
is tht correct?
You know what to do: provide execution plans, enclosed in code [tags].

Though if you do not close your previous topic, you may find people less willing to assist.
Re: Insert from Select - Performance Issue [message #641714 is a reply to message #641712] Thu, 20 August 2015 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
are statistics current for all tables & indexes involved?

post full results from SQL below

SELECT * FROM V$VERSION;
Re: Insert from Select - Performance Issue [message #641715 is a reply to message #641714] Thu, 20 August 2015 09:47 Go to previous messageGo to next message
cookiemonster
Messages: 13339
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually before you do any of that (though you do still need to post the requested info) you need to post the SQL you're actually running. What you've posted has multiple invalid table aliases and won't compile.
Re: Insert from Select - Performance Issue [message #641716 is a reply to message #641715] Thu, 20 August 2015 09:48 Go to previous messageGo to next message
cookiemonster
Messages: 13339
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or are trgt_src.au_source and au_src.au_source variables?
Re: Insert from Select - Performance Issue [message #641811 is a reply to message #641716] Mon, 24 August 2015 21:22 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
Please post a valid piece of sql, preferrably the sql that actually has the problem (OH wait, I see CookieMonster has already asked for that, good job).

Once you post the real thing we will give you more help.

In the mean time, consider the following:

There are only two kinds of queries, WAREHOUSE STYLE and PRECISION STYLE.  A warehouse style query wants more than 2% of the rows in your tables, a precision style query wants less than 2% of the rows in your tables.


Given the (admittedly a little controversial yet overall very accurate) statement above, which is your query? Do you want lots of data (>2% of rows) or do you want a small amount of data (< 2% of rows (ideally lots less))? If your query is a warehouse style query then your indexes will be mostly useless and you will be using HASH JOIN to put tables together. If your query is a precision style query then your indexes may be crucial to success along with using NESTED LOOPS JOIN to put tables together.

OK so there is always a little wiggle room and I have seen my fair share of queries that ignore the information above. Yet in the grand scheme of things this basic rule is golden. So you have to tell us (in addition to providing the real query) what is the data load in terms of percentages of rows from each table. Once you know this, you will have taken the first major step towards tuning your query. For example, you will be able to tell where you are using indexes and should not be or vice versa. The same with joins. You will be able to tell if you are using NESTED LOOPS JOIN and instead should be using HASH JOIN. Once you know this you can then decide if your problem is the need to change basic access methods and join strategies, or if you need to tweak the ones you already have to make them more efficient.

So get us these things:

1. the real query
2. number of rows in each table
3. number of rows after applying filter criteria for each table in the WHERE clause
4. an actual query plan that contains at a minimum, PLAN_TABLE_OUTPUT and PREDICATE_INFORMATION sections

** If you have the luxury of being able to run the query then use GATHER_PLAN_STATISTICS hint and generate the plan from that. It will contain the above info.

Kevin
Re: Insert from Select - Performance Issue [message #641845 is a reply to message #641811] Tue, 25 August 2015 10:28 Go to previous messageGo to next message
skmsamy
Messages: 2
Registered: August 2015
Location: Chicago
Junior Member
One thing you can do to speed up the process is by adding the /*+ APPEND */ hint (nologging) if possible. Before you do that disable all the indexes, enable nologging (make sure DB doesn't have force logging enabled) at tablespace/table level, insert append and then rebuild all the indexes. Not sure if this is possible in your environment.
Re: Insert from Select - Performance Issue [message #646628 is a reply to message #641845] Tue, 05 January 2016 23:36 Go to previous message
garan
Messages: 27
Registered: January 2016
Junior Member
Yes you can use APPEND HINT

Ex inserting 2 million rows using the above hint is fast and generates less redo when compared to INSERT without the append hint

CREATE TABLE EMP(ID NUMBER);
SET AUTOTRACE ON

INSERT /*+ append */INTO EMP(ID)
SELECT LEVEL
FROM DUAL WHERE LEVEL <=2000000
;

Statistics
----------------------------------------------------------
0 recursive calls
3189 db block gets
0 consistent gets
0 physical reads
7184 redo size
657 bytes sent via SQL*Net to client
612 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2000000 rows processed

garani
Previous Topic: Materialized View
Next Topic: Parallel Server for Dummies Checklist
Goto Forum:
  


Current Time: Mon Nov 19 00:56:59 CST 2018