Home » RDBMS Server » Performance Tuning » Takes long time on sorting.
| Takes long time on sorting. [message #272546] |
Fri, 05 October 2007 08:37  |
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  |
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 #272552 is a reply to message #272546] |
Fri, 05 October 2007 08:46 |
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 #272560 is a reply to message #272552] |
Fri, 05 October 2007 09:39 |
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 |
rleishman
Messages: 3728
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 |
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 |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
|
Goto Forum:
Current Time: Wed Feb 19 20:24:51 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
