Home » RDBMS Server » Performance Tuning » Takes long time on sorting.
Takes long time on sorting. [message #272546] Fri, 05 October 2007 08:37 Go to next message
lm_suresh
Messages: 24
Registered: September 2007
Location: Chennai,India
Junior Member
This is my Query and the Execution plan and it is taking more time on sort. why so .. and is there anyway to avoid.version is
10g.

INSERT INTO Names_stg
(Old_First_Name,
Old_First_Name_srch,
Old_Last_Name,
Old_Last_Name_pref_nld,
Old_Last_Name_srch,
Old_Middle_Name,
Old_Name,
Old_Name_ac,
Old_Name_Display,
Old_Name_Formal,
Old_Name_Initials,
Old_Name_Prefix,
Old_Name_Royal_Prefix,
Old_Name_Royal_Suffix,
Old_Name_Suffix,
Old_Name_Title,
Old_Partner_Last_Name,
Old_Partner_roy_Prefix,
Old_pref_First_Name,
Old_Second_Last_Name,
Old_Second_Last_srch,
Old_Country_nm_Format)
(SELECT DISTINCT First_Name,
First_Name_srch,
Last_Name,
Last_Name_pref_nld,
Last_Name_srch,
Middle_Name,
NAME,
Name_ac,
Name_Display,
Name_Formal,
Name_Initials,
Name_Prefix,
Name_Royal_Prefix,
Name_Royal_Suffix,
Name_Suffix,
Name_Title,
Partner_Last_Name,
Partner_roy_Prefix,
pref_First_Name,
Second_Last_Name,
Second_Last_srch,
Country_nm_Format
FROM Names
MINUS
SELECT DISTINCT Old_First_Name,
Old_First_Name_srch,
Old_Last_Name,
Old_Last_Name_pref_nld,
Old_Last_Name_srch,
Old_Middle_Name,
Old_Name,
Old_Name_ac,
Old_Name_Display,
Old_Name_Formal,
Old_Name_Initials,
Old_Name_Prefix,
Old_Name_Royal_Prefix,
Old_Name_Royal_Suffix,
Old_Name_Suffix,
Old_Name_Title,
Old_Partner_Last_Name,
Old_Partner_roy_Prefix,
Old_pref_First_Name,
Old_Second_Last_Name,
Old_Second_Last_srch,
Old_Country_nm_Format
FROM Names_stg)



Execution Plan
----------------------------------------------------------
Plan hash value: 2356657012
---------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)
| Time |

------------------------------------------------------------

| 0 | INSERT STATEMENT | | 100K| 22M| | 6661 (53)
| 00:01:20 |

| 1 | MINUS | | | | |
| |

| 2 | SORT UNIQUE | | 100K| 11M| 32M| 3194 (2)
| 00:00:39 |

| 3 | TABLE ACCESS FULL| NAMES | 100K| 11M| | 510 (3)
| 00:00:07 |

| 4 | SORT UNIQUE | | 100K| 11M| 32M| 3467 (2)
| 00:00:42 |

| 5 | TABLE ACCESS FULL| NAMES_STG | 100K| 11M| | 782 (2)
| 00:00:10 |

-----------------------------------------------------------------
Re: Takes long time on sorting. [message #272548 is a reply to message #272546] Fri, 05 October 2007 08:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can remove the two DISTINCT statements in the Selects.
The MINUS will perform an implicit DISTICT, so there's no point performing two extra ones.
Re: Takes long time on sorting. [message #272551 is a reply to message #272546] Fri, 05 October 2007 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Takes long time on sorting. [message #272552 is a reply to message #272546] Fri, 05 October 2007 08:46 Go to previous messageGo to next message
lm_suresh
Messages: 24
Registered: September 2007
Location: Chennai,India
Junior Member
Thanks for the reply. I have already tried without distinct and i dont see any improvement in response time. we are using this query to insert miilions of records and it takes hours to complete. Any ideas ?
Re: Takes long time on sorting. [message #272553 is a reply to message #272552] Fri, 05 October 2007 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read How to Identify Performance Problem and Bottleneck and provide the requested information?

Regards
Michel
Re: Takes long time on sorting. [message #272560 is a reply to message #272552] Fri, 05 October 2007 09:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, if you're trying to do a DISTINCT on all the columns of a table for millions of rows, then it's going to use a lot of memory and take quite a long time.

I'd recommend massively increasing the SORT_AREA_SIZE for this session.
Re: Takes long time on sorting. [message #272668 is a reply to message #272560] Fri, 05 October 2007 23:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There's two problems with MINUS:

- It performs the equivalent of a SORT-MERGE join. This was great pre-7.3 when Sort-Merge was the fastest (stable) join type for large datasets, but is totally overshadowed these days by HASH join.

- It compares the contents of EVERY column. Sometimes that's what you want, but sometimes you really just want the key. Since it performs a SORT, each row in the sort takes up a lot of temp space. When TEMP runs out (as it will do rather quickly with every column in there) it starts swapping to disk. Now that's REALLY slow.

So how do you get one of these newfangled HASH joins? Use a NOT EXISTS or NOT IN sub-query.

"But I read somewhere that NOT EXISTS and NOT IN are bad. How about them apples?", you say. That was true - once again - pre 7.3 - when NOT EXISTS and NOT IN sub-queries always resolved with the equivalent of a NESTED LOOPS join. The introduction of Hash-Anti-Joins has made them really efficient. The myth of MINUS as an efficient operation still persists, and will probably continue to do so until all of the over-35 DBAs are pensioned off.

So rewrite your SQL as a NOT EXISTS or NOT IN, run it through Explain Plan and make sure you see a HASH ANTI step (watch out for NESTED LOOPS ANTI and FILTER; you don't want them!), and then run it. Over large row sets, you should find it orders of magnitude faster.

Oh yeah, and never, ever, ever write a MINUS, INTERSECT, or UNION query again.

Ross Leishman
Re: Takes long time on sorting. [message #272958 is a reply to message #272546] Mon, 08 October 2007 06:30 Go to previous messageGo to next message
lm_suresh
Messages: 24
Registered: September 2007
Location: Chennai,India
Junior Member
I have modified the query with not exists.

INSERT INTO NAMES_STG(OLD_COUNTRY_NM_FORMAT,OLD_FIRST_NAME,
OLD_FIRST_NAME_SRCH,OLD_LAST_NAME,OLD_LAST_NAME_PREF_NLD,OLD_LAST_NAME_SRCH,
OLD_MIDDLE_NAME,OLD_NAME,OLD_NAME_AC,OLD_NAME_DISPLAY,OLD_NAME_FORMAL,
OLD_NAME_INITIALS,OLD_NAME_PREFIX,OLD_NAME_ROYAL_PREFIX,
OLD_NAME_ROYAL_SUFFIX,OLD_NAME_SUFFIX,OLD_NAME_TITLE,OLD_PARTNER_LAST_NAME,
OLD_PARTNER_ROY_PREFIX,OLD_PREF_FIRST_NAME,OLD_SECOND_LAST_NAME,
OLD_SECOND_LAST_SRCH)
SELECT COUNTRY_NM_FORMAT,FIRST_NAME,FIRST_NAME_SRCH,
LAST_NAME,LAST_NAME_PREF_NLD,LAST_NAME_SRCH,MIDDLE_NAME,NAME,NAME_AC,
NAME_DISPLAY,NAME_FORMAL,NAME_INITIALS,NAME_PREFIX,NAME_ROYAL_PREFIX,
NAME_ROYAL_SUFFIX,NAME_SUFFIX,NAME_TITLE,PARTNER_LAST_NAME,
PARTNER_ROY_PREFIX,PREF_FIRST_NAME,SECOND_LAST_NAME,SECOND_LAST_SRCH FROM
NAMES
WHERE NOT EXISTS
(SELECT OLD_COUNTRY_NM_FORMAT,OLD_FIRST_NAME,
OLD_FIRST_NAME_SRCH,OLD_LAST_NAME,OLD_LAST_NAME_PREF_NLD,OLD_LAST_NAME_SRCH,
OLD_MIDDLE_NAME,OLD_NAME,OLD_NAME_AC,OLD_NAME_DISPLAY,OLD_NAME_FORMAL,
OLD_NAME_INITIALS,OLD_NAME_PREFIX,OLD_NAME_ROYAL_PREFIX,
OLD_NAME_ROYAL_SUFFIX,OLD_NAME_SUFFIX,OLD_NAME_TITLE,OLD_PARTNER_LAST_NAME,
OLD_PARTNER_ROY_PREFIX,OLD_PREF_FIRST_NAME,OLD_SECOND_LAST_NAME,
OLD_SECOND_LAST_SRCH FROM NAMES_STG HXN,NAMES PSN WHERE
PSN.COUNTRY_NM_FORMAT = HXN.OLD_COUNTRY_NM_FORMAT AND
PSN.FIRST_NAME = HXN.OLD_FIRST_NAME AND
PSN.FIRST_NAME_SRCH = HXN.OLD_FIRST_NAME_SRCH AND
PSN.LAST_NAME = HXN.OLD_LAST_NAME AND
PSN.LAST_NAME_PREF_NLD = HXN.OLD_LAST_NAME_PREF_NLD AND
PSN.LAST_NAME_SRCH = HXN.OLD_LAST_NAME_SRCH AND
PSN.MIDDLE_NAME = HXN.OLD_MIDDLE_NAME AND
PSN.NAME =HXN.OLD_NAME AND
PSN.NAME_AC = HXN.OLD_NAME_AC AND
PSN.NAME_DISPLAY = HXN.OLD_NAME_DISPLAY AND
PSN.NAME_FORMAL = HXN.OLD_NAME_FORMAL AND
PSN.NAME_INITIALS = HXN.OLD_NAME_INITIALS AND
PSN.NAME_PREFIX = HXN.OLD_NAME_PREFIX AND
PSN.NAME_ROYAL_PREFIX = HXN.OLD_NAME_ROYAL_PREFIX AND
PSN.NAME_ROYAL_SUFFIX = HXN.OLD_NAME_ROYAL_SUFFIX AND
PSN.NAME_SUFFIX = HXN.OLD_NAME_SUFFIX AND
PSN.NAME_TITLE = HXN.OLD_NAME_TITLE AND
PSN.PARTNER_LAST_NAME = HXN.OLD_PARTNER_LAST_NAME AND
PSN.PARTNER_ROY_PREFIX = HXN.OLD_PARTNER_ROY_PREFIX AND
PSN.PREF_FIRST_NAME = HXN.OLD_PREF_FIRST_NAME AND
PSN.SECOND_LAST_NAME = HXN.OLD_SECOND_LAST_NAME AND
PSN.SECOND_LAST_SRCH = HXN.OLD_SECOND_LAST_SRCH )

Execution Plan
----------------------------------------------------------
Plan hash value: 3050209076
-----------------------------------------------------------


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time


| 0 | INSERT STATEMENT | | 1000K| 98M| 3013K (1)| 10:02:42 |

|* 1 | FILTER | | | |
| |

| 2 | TABLE ACCESS FULL | NAMES | 1000K| 98M| 5139 (3)| 00:01:02 |

| 3 | NESTED LOOPS | | 1000K| 196M| 3008K (1)| 10:01:40 |

| 4 | TABLE ACCESS FULL | NAMES_STG | 1000K| 98M| 4182 (3)| 00:00:51 |

|* 5 | TABLE ACCESS BY INDEX ROWID| NAMES | 1 | 103 | 3 (0)| 00:00:01 |

|* 6 | INDEX RANGE SCAN | PS0NAMES | 1 | | 2 (0)| 00:00:01 |

How to enable hash join. my version is 10.2.0.1.0. I tried with hash_aj hint
but still same execution plan and no improvement in response time even with not exists.
No PK in both tables and both allows null values for all the columns.
Re: Takes long time on sorting. [message #272967 is a reply to message #272958] Mon, 08 October 2007 07:07 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 05 October 2007 15:43

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel


Previous Topic: delete statement taking long time
Next Topic: Non Standard Block size
Goto Forum:
  


Current Time: Sat Dec 10 05:18:50 CST 2016

Total time taken to generate the page: 0.07923 seconds