OraFAQ Forum: Performance Tuning » Distinct query taking long time to run

Home » RDBMS Server » Performance Tuning » Distinct query taking long time to run (Oracle 11.2,Linux 6)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Distinct query taking long time to run [message #669760]

Thu, 10 May 2018 14:07

senmng
Messages: 22
Registered: April 2018

Junior Member

I have this insert query in one of my proc to load the data into a target table where the distinct clause is making huge impact and taking too much time(since each table is having records more than 2 and 3 million records). Is their any way to optimize this select query with distinct?

Code: [Select all] [Show/ hide]

Insert into target table
SELECT DISTINCT
tab1.col1 col1,
tab1.col2 col2,
tab2.col3 col3,
tab2.col4 col4,
tab3.col5 col5,
tab3.col6 col6,
tab3.col7 col7,
tab3.col8 col8,
tab3.col9 col9,
to_char(tab3.col7, 'YYYY-MM') col10,
to_char(tab3.col7, 'YYYY') col11,
to_char(tab3.col7, 'MM') col12,
CURRENT_DATE col13
FROM tab3@dblink t2
JOIN tab4@dblink ON tab3.col5 = tab4.col13
JOIN tab5@dblink ON tab4.col14 = tab5.col15
JOIN tab2@dblink ON tab5.col16 = tab2.col17
JOIN tab1 ON tab1.col18 = tab2.col3;

[EDITED by LF: removed superfluous info from Product, version, OS]

[Updated on: Fri, 11 May 2018 02:11] by Moderator

Report message to a moderator

Re: Distinct query taking long time to run [message #669761 is a reply to message #669760]

Thu, 10 May 2018 14:16

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

You'll need to show the execution plan to determine if the DISTINCT could be the problem.

Report message to a moderator

Re: Distinct query taking long time to run [message #669762 is a reply to message #669760]

Thu, 10 May 2018 15:39

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Besides what the execution plan tells you, joining tables across databases, escecially if they are on differet servers on a network can be a cause of major slowdown regardless of distinct.

Report message to a moderator

Re: Distinct query taking long time to run [message #669763 is a reply to message #669761]

Thu, 10 May 2018 15:50

senmng
Messages: 22
Registered: April 2018

Junior Member

Hi John - When i tried to restrict the rows to 1000 by using (where ronwnum<=1000),am able to retrieve the results but not able to retrieve only i don't use any condition.Also attached the explain plan report.

Attachment: EP1.jpg
(Size: 127.19KB, Downloaded 2531 times)

Report message to a moderator

Re: Distinct query taking long time to run [message #669768 is a reply to message #669763]

Fri, 11 May 2018 00:19

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

THat execution plan is useless. You need to generate one the correct way.

Use EXPLAIN PLAN, and then format the results with DBMS_XPLAN.DISPLAY.

After that, set STATISTICS_LEVEL=ALL, run the statement, and get the plan with execution statistics with DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST').

Report message to a moderator

Re: Distinct query taking long time to run [message #669788 is a reply to message #669768]

Fri, 11 May 2018 09:36

msol25
Messages: 396
Registered: June 2011

Senior Member

Hi,

The DISTINCT operator causes Oracle to fetch all rows satisfying the table join and then sort and filter out duplicate values.I hope you can use to resolve this using

Code: [Select all] [Show/ hide]

  
   SELECT <column_list>
   FROM
     (    SELECT  <column_list>
                 ,ROW_NUMBER() OVER (PARTITION BY <column_list> ORDER BY <column_list>) As RNO          
          FROM   <Table_Name>
    )
WHERE RNO = 1

[Updated on: Fri, 11 May 2018 09:37]

Report message to a moderator

Re: Distinct query taking long time to run [message #669789 is a reply to message #669788]

Fri, 11 May 2018 14:52

Alien
Messages: 292
Registered: June 1999

Senior Member

Hi,

Your analytic query would have to do the same amount of work. The only way to create the partitions is to get the whole resultset, sort it, assign a row_number and discard the rest.

The only difference is that the row_number() will likely use a 'window sort pushed rank' operation. While the distinct is likely to use a 'hash unique'. Depending on the data and available memory there might be a marginal difference between the 2. In my experience the 'window sort pushed rank' is more likely to spill to disk and start swapping, therefore substantially incrementing the runtime.

However, the amount of work being done is the same. The only difference is the algorithm used to perform the sort.

Regards,

Arian

Report message to a moderator

Re: Distinct query taking long time to run [message #669887 is a reply to message #669789]

Tue, 22 May 2018 14:17

senmng
Messages: 22
Registered: April 2018

Junior Member

Hi - i have created a view in the remote database to get the query results of join and distinct and refer the view name in the local database. It reduced the considerable amount of time in inserting the record into the target table.

Thank you for all your suggestions.

Report message to a moderator

Previous Topic:	ORA-07445: exception encountered: core dump [audplsfailure()+54]
Next Topic:	Wrong plan chosen by optimizer

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 17:45:36 CDT 2025