Home » RDBMS Server » Performance Tuning » Tune Query or Modify Index or Partition
Tune Query or Modify Index or Partition [message #541022] Fri, 27 January 2012 02:10 Go to next message
p2004
Messages: 1
Registered: January 2012
Location: California
Junior Member
Hello we have this search look up which is costing us some response time.

We have a large customer table so first thought was to partition.

Also we see two union alls in the plan - can we introduce parallelism ? How ?


Below is the plan - have attached a text file if difficult to read


SELECT V_IDENTIFIER_LOOKUP.UID_V_IDENTIFIER_LOOKUP AS "UID",
V_IDENTIFIER_LOOKUP.ABA, V_IDENTIFIER_LOOKUP.ADDRESS1,
V_IDENTIFIER_LOOKUP.ADDRESS2, V_IDENTIFIER_LOOKUP.ADDRESS3,
V_IDENTIFIER_LOOKUP.ADDRESS4, V_IDENTIFIER_LOOKUP.ALIAS,
V_IDENTIFIER_LOOKUP.CITY, V_IDENTIFIER_LOOKUP.COUNTRYCODE,
V_IDENTIFIER_LOOKUP.CUST_CODE, V_IDENTIFIER_LOOKUP.CUST_NAME,
V_IDENTIFIER_LOOKUP.HEAD_OFFICE_IN,
V_IDENTIFIER_LOOKUP.IDENTIFIER,
V_IDENTIFIER_LOOKUP.IDENTIFIER_TYPE,

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------
V_IDENTIFIER_LOOKUP.ID_TYPE_SWIFT, V_IDENTIFIER_LOOKUP.OFFICE,
V_IDENTIFIER_LOOKUP.ORIGIN_PROFILE_ID,
V_IDENTIFIER_LOOKUP.ORIGIN_PROFILE_UID_COL_NAME,
V_IDENTIFIER_LOOKUP.PROPRIETARY, V_IDENTIFIER_LOOKUP.STATE,
V_IDENTIFIER_LOOKUP.SWIFT_ID,
V_IDENTIFIER_LOOKUP.UID_ORIGIN_PROFILE, V_IDENTIFIER_LOOKUP.ZIP,
V_IDENTIFIER_LOOKUP.LL_CUST_NAME,
V_IDENTIFIER_LOOKUP.LL_ADDRESS1,
V_IDENTIFIER_LOOKUP.LL_ADDRESS2,
V_IDENTIFIER_LOOKUP.LL_ADDRESS3,
V_IDENTIFIER_LOOKUP.LL_ADDRESS4, V_IDENTIFIER_LOOKUP.LL_CITY,

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------
V_IDENTIFIER_LOOKUP.LL_ZIPCODE,
V_IDENTIFIER_LOOKUP.LL_STATE, V_IDENTIFIER_LOOKUP.LL_BRANCH,
V_IDENTIFIER_LOOKUP.LL_POBOX FROM V_IDENTIFIER_LOOKUP
V_IDENTIFIER_LOOKUP, ( SELECT MAX(uid_v_identifier_lookup)
KEEP (DENSE_RANK FIRST ORDER BY
V_IDENTIFIER_LOOKUP.HEAD_OFFICE_IN DESC)
uid_v_identifier_lookup FROM v_identifier_lookup
WHERE ( (UPPER (V_IDENTIFIER_LOOKUP.CITY) LIKE
'CHARLOTTE%' ESCAPE '\' OR UPPER
(V_IDENTIFIER_LOOKUP.LL_CITY) LIKE
'CHARLOTTE%' ESCAPE '\') AND

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------
(V_IDENTIFIER_LOOKUP.STATE LIKE 'NC%' ESCAPE '\'
OR V_IDENTIFIER_LOOKUP.LL_STATE LIKE 'NC%' ESCAPE '\')
AND V_IDENTIFIER_LOOKUP.COUNTRYCODE LIKE 'US%' ESCAPE '\')
GROUP BY identifier_type, identifier) b WHERE
V_IDENTIFIER_LOOKUP.uid_v_identifier_lookup =
b.uid_v_identifier_lookup AND ( (UPPER
(V_IDENTIFIER_LOOKUP.CITY) LIKE 'CHARLOTTE%' ESCAPE '\'
OR UPPER (V_IDENTIFIER_LOOKUP.LL_CITY) LIKE
'CHARLOTTE%' ESCAPE '\') AND (V_IDENTIFIER_LOOKUP.STATE
LIKE 'NC%' ESCAPE '\' OR V_IDENTIFIER_LOOKUP.LL_STATE
LIKE 'NC%' ESCAPE '\') AND V_IDENTIFIER_LOOKUP.COUNTRYCODE

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------
LIKE 'US%' ESCAPE '\') AND ROWNUM <= 1500

Plan hash value: 41870561

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5977 (100)| |
| 1 | COUNT STOPKEY | | | | | |
| 2 | HASH JOIN | | 1 | 2043 | 5977 (1)| 00:01:12 |
| 3 | VIEW | | 10 | 640 | 2985 (1)| 00:00:36 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------
| 4 | SORT GROUP BY | | 10 | 900 | 2985 (1)| 00:00:36 |
| 5 | VIEW | | 10 | 900 | 2984 (1)| 00:00:36 |
| 6 | UNION-ALL | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | CUSTOMRS | 1 | 450 | 38 (29)| 00:00:01 |
| 8 | INDEX FULL SCAN | IX_SWIFTID | 231 | | 37 (30)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | CUSTOMRS | 1 | 453 | 827 (1)| 00:00:10 |
| 10 | INDEX FULL SCAN | IX_CUSTOMRS_ABA | 1155K| | 26 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | CUSTOMRS | 1 | 450 | 67 (2)| 00:00:01 |
| 12 | INDEX FULL SCAN | IX_CUSTOMRS_ALIAS | 57789 | | 27 (4)| 00:00:01 |
| 13 | NESTED LOOPS | | 7 | 3423 | 2052 (1)| 00:00:25 |
| 14 | NESTED LOOPS | | 7 | 3381 | 2052 (1)| 00:00:25 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------
| 15 | TABLE ACCESS BY INDEX ROWID| CUSTOMRS | 5 | 2225 | 2040 (1)| 00:00:25 |
| 16 | INDEX RANGE SCAN | IX_CUSTOMRS | 331K| | 1810 (1)| 00:00:22 |
| 17 | TABLE ACCESS BY INDEX ROWID| NCC | 1 | 38 | 3 (0)| 00:00:01 |
| 18 | INDEX RANGE SCAN | IX_NCC_2 | 1 | | 2 (0)| 00:00:01 |
| 19 | INDEX UNIQUE SCAN | IX_COUNTRY_NCC_2 | 1 | 6 | 0 (0)| |
| 20 | VIEW | | 10 | 19790 | 2991 (1)| 00:00:36 |
| 21 | UNION-ALL | | | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | CUSTOMRS | 1 | 1873 | 38 (29)| 00:00:01 |
| 23 | INDEX FULL SCAN | IX_SWIFTID | 231 | | 37 (30)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | CUSTOMRS | 1 | 1873 | 827 (1)| 00:00:10 |
| 25 | INDEX FULL SCAN | IX_CUSTOMRS_ABA | 1155K| | 26 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------
| 26 | TABLE ACCESS BY INDEX ROWID | CUSTOMRS | 1 | 1873 | 67 (2)| 00:00:01 |
| 27 | INDEX FULL SCAN | IX_CUSTOMRS_ALIAS | 57789 | | 27 (4)| 00:00:01 |
| 28 | NESTED LOOPS | | | | | |
| 29 | NESTED LOOPS | | 7 | 13545 | 2059 (1)| 00:00:25 |
| 30 | NESTED LOOPS | | 7 | 13482 | 2052 (1)| 00:00:25 |
| 31 | TABLE ACCESS BY INDEX ROWID | CUSTOMRS | 5 | 9320 | 2040 (1)| 00:00:25 |
| 32 | INDEX RANGE SCAN | IX_CUSTOMRS | 331K| | 1810 (1)| 00:00:22 |
| 33 | TABLE ACCESS BY INDEX ROWID | NCC | 1 | 62 | 3 (0)| 00:00:01 |
| 34 | INDEX RANGE SCAN | IX_NCC_2 | 1 | | 2 (0)| 00:00:01 |
| 35 | INDEX UNIQUE SCAN | IX_COUNTRY_NCC_2 | 1 | | 0 (0)| |
| 36 | TABLE ACCESS BY INDEX ROWID | COUNTRY_NCC | 1 | 9 | 1 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
  • Attachment: Plan.txt
    (Size: 8.74KB, Downloaded 44 times)
Re: Tune Query or Modify Index or Partition [message #541026 is a reply to message #541022] Fri, 27 January 2012 02:29 Go to previous message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unreadable.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

For any peformances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

And REMOVE the useless and repeated 'PLAN_TABLE_OUTPUT' header.

Regards
Michel
Previous Topic: PLSQL and related cursors generated in v$sql
Next Topic: Is it possible to display alternate execution plans ?
Goto Forum:
  


Current Time: Fri Apr 18 19:06:05 CDT 2014

Total time taken to generate the page: 0.09397 seconds