Home » RDBMS Server » Performance Tuning » Problem sorting and inserting huge data (merged)
Problem sorting and inserting huge data (merged) [message #331290] Wed, 02 July 2008 23:04 Go to next message
ravashingravi
Messages: 7
Registered: March 2008
Location: India
Junior Member
hi i have this query

INSERT /*+ append */ INTO UF_TAX
SELECT /*+ parallel(ta,2) */ ta.*,
DENSE_RANK () OVER (partition by ta.tlg_ban,
ta.tlg_orig_ent_seq_no,
ta.tlg_actv_code
order by ta.tlg_ban,
ta.tlg_orig_ent_seq_no,
ta.tlg_actv_code,
ta.tlg_tax_ent_seq_no )
as comp_portion_id
FROM TMP_UF_TAX ta;

its running for last 7 hours even after i removed
ORder by ta.ban,ta.seq_no

The table TMP_UF_TAX is huge containg 80 million records.
Please help me
Re: Problem sorting huge data [message #331291 is a reply to message #331290] Wed, 02 July 2008 23:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow Posting Guidelines below
http://www.orafaq.com/forum/t/88153/0/



http://www.orafaq.com/forum/t/84315/74940/
which have you tried & what were the results

[Updated on: Wed, 02 July 2008 23:06] by Moderator

Report message to a moderator

Problem sorting and inserting huge data [message #331297 is a reply to message #331290] Wed, 02 July 2008 23:27 Go to previous messageGo to next message
ravashingravi
Messages: 7
Registered: March 2008
Location: India
Junior Member
hi i have this query

INSERT /*+ append */ INTO UF_TAX
SELECT /*+ parallel(ta,2) */ ta.*,
DENSE_RANK () OVER (partition by ta.tlg_ban,
ta.tlg_orig_ent_seq_no,
ta.tlg_actv_code
order by ta.tlg_ban,
ta.tlg_orig_ent_seq_no,
ta.tlg_actv_code,
ta.tlg_tax_ent_seq_no )
as comp_portion_id
FROM TMP_UF_TAX ta;

I have created a index on
TMP_UF_TAX(TLG_BAN, TLG_TAX_ENT_SEQ_N0)


The table TMP_UF_TAX is huge containg 150 million records.

I want to insert the sorted data but it run out of TEMP space when i added ORDER BY.

its running for last 7 hours even after i removed
order by TLG_BAN,TLG_TAX_ENT_SEQ_N0
Re: Problem sorting and inserting huge data [message #331306 is a reply to message #331290] Wed, 02 July 2008 23:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wow! in 8 minutes you have 70 million rows more.
Is this the normal rate?

Regards
Michel
Re: Problem sorting huge data [message #331308 is a reply to message #331290] Wed, 02 July 2008 23:44 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> its running for last 7 hours even after i removed
order by TLG_BAN,TLG_TAX_ENT_SEQ_N0
I do not see any ORDER BY clause in the posted statement.
As DENSE_RANK needs ORDER BY clause, I just doubt this statement does not end with error.

You select huge amount of data, so index is useles; it will every time end in full table scan and sort of all its rows for analytic result.
Re: Problem sorting and inserting huge data [message #331330 is a reply to message #331297] Thu, 03 July 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT multpipost.

Regards
Michel
Re: Problem sorting and inserting huge data [message #331374 is a reply to message #331330] Thu, 03 July 2008 03:41 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The DENSE_RANK does a sort no matter what.

So you are trying to sort AND insert 150 million rows. How long do you expect that to take?

Try JUST inserting 10 million rows WITHOUT the DENSE_RANK. That will give you a baseline. Say it is 30 minutes, so now you know that it will take 7.5 hours JUST to insert the data. But that doesn't count the sort.

Now you have to work out how long the sort will take. Try running:
SELECT *
FROM (
  SELECT *
  FROM TMP_UF_TAX ta
  WHERE ROWNUM <= 10000000
  ORDER BY ta.tlg_orig_ent_seq_no,
  ta.tlg_actv_code
  order by ta.tlg_ban,
  ta.tlg_orig_ent_seq_no,
  ta.tlg_actv_code,
  ta.tlg_tax_ent_seq_no )
WHERE ROWNUM = 1;


The time this takes will be the time taken to sort 10M rows.

To get the A VERY ROUGH ESTIMATE of the probable time required for 150M rows, multiply by 15.

Add the two estimates together to get the total expected time. I think 7 hours is probably unreasonable.

If one of these estimates blows out to a very large number, then you can discuss PGA sizing with your DBA (for sorting performance) or disk I/O rates with your SysAdmin. But there is very little else you can do to tune this query. Sorting takes time, and IO takes time.

Ross Leishman
Re: Problem sorting and inserting huge data (merged) [message #331531 is a reply to message #331290] Thu, 03 July 2008 17:45 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
add lots of parallel (more than 2 if you have the resource) and kick everyone else off your box, and as rleishman said, increase your pga_aggregate_target to something big (over a gig at least if your box has capacity)
Previous Topic: Generate SQL Statements from 10046 trace file
Next Topic: How heavy an AWR snapshot is
Goto Forum:
  


Current Time: Sat Dec 10 22:19:29 CST 2016

Total time taken to generate the page: 0.08537 seconds