OraFAQ Forum: Performance Tuning » Tune Query or Modify Index or Partition

Home » RDBMS Server » Performance Tuning » Tune Query or Modify Index or Partition

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

Tune Query or Modify Index or Partition [message #541022]

Fri, 27 January 2012 02:10

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 1794 times)

Report message to a moderator

Re: Tune Query or Modify Index or Partition [message #541026 is a reply to message #541022]

Fri, 27 January 2012 02:29

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 25 10:35:01 CST 2025