Home » RDBMS Server » Performance Tuning » Need help to tune the query
Need help to tune the query [message #417042] Wed, 05 August 2009 15:58 Go to next message
oraappsfaq
Messages: 9
Registered: August 2009
Location: US
Junior Member
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/
hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id,
hcas.cust_acct_site_id address_id, hl.state primary_state,
hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id+0
AND hcsu.site_use_code = 'SHIP_TO'
AND hcsu.primary_flag = 'Y'
AND hcsu.status = 'A'

UNION ALL --WHERE NO PARTY_SITE_ID
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/
hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account, -1 location_id,
-1 address_id, '' primary_state, '' primary_country
FROM hz_parties hp, hz_cust_accounts hca
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND NOT EXISTS (SELECT 'z'
FROM hz_party_sites hps
WHERE hp.party_id = hps.party_id)
UNION ALL --THERE IS SHIP_TO BUT NO ACTIVE PRIMARY SHIP TO
SELECT hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id, hcas.cust_acct_site_id address_id,
hl.state primary_state, hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.site_use_code = 'SHIP_TO'
AND hcsu.primary_flag = 'N'
AND hcsu.cust_acct_site_id =
(SELECT MIN (hcsu2.cust_acct_site_id)
FROM hz_parties hp2,
hz_party_sites hps2,
hz_cust_acct_sites_all hcas2,
hz_cust_site_uses_all hcsu2
WHERE hp2.party_id = hps2.party_id
AND hps2.party_site_id = hcas2.party_site_id
AND hcas2.cust_acct_site_id = hcsu2.cust_acct_site_id
AND hcsu2.site_use_code = 'SHIP_TO'
AND hcsu2.primary_flag = 'N'
AND hp2.party_id = hp.party_id)
AND NOT EXISTS (
SELECT 'z'
FROM hz_parties hp1,
hz_party_sites hps1,
hz_cust_acct_sites_all hcas1,
hz_cust_site_uses_all hcsu1
WHERE hp1.party_id = hps1.party_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hcsu1.site_use_code = 'SHIP_TO'
AND hcsu1.primary_flag = 'Y'
AND hcsu1.status = 'A'
AND hp1.party_id = hp.party_id)
UNION ALL -- NO SHIP TO , but BILL_TO
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/
hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id, hcas.cust_acct_site_id address_id,
hl.state primary_state, hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.site_use_id =
(SELECT MIN (hcsu2.site_use_id)
FROM hz_parties hp2,
hz_party_sites hps2,
hz_cust_acct_sites_all hcas2,
hz_cust_site_uses_all hcsu2
WHERE hp2.party_id = hps2.party_id
AND hps2.party_site_id = hcas2.party_site_id
AND hcas2.cust_acct_site_id = hcsu2.cust_acct_site_id
AND hcsu2.site_use_code IN
('BILL_TO', 'INDIVIDUAL RESPONSIBLE SUB')
AND hcas2.cust_account_id = hca.cust_account_id)
AND NOT EXISTS (
SELECT 'z'
FROM hz_parties hp1,
hz_party_sites hps1,
hz_cust_acct_sites_all hcas1,
hz_cust_site_uses_all hcsu1
WHERE hp1.party_id = hps1.party_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hcsu1.site_use_code = 'SHIP_TO'
AND hcas1.cust_account_id = hca.cust_account_id)
UNION ALL
--No Site Use (Most of these customers are type PERSON which have some location but no address..confusing..??)Smile)
SELECT hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id, -1 address_id,
hl.state primary_state, hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND NOT EXISTS (
SELECT 'z'
FROM hz_parties hp1,
hz_party_sites hps1,
hz_cust_acct_sites_all hcas1,
hz_cust_site_uses_all hcsu1
WHERE hp1.party_id = hps1.party_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hp1.party_id = hp.party_id);
Re: Need help to tune the query [message #417049 is a reply to message #417042] Wed, 05 August 2009 17:10 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
I suggest you do not hold your breath while waiting for a solution; which I doubt will ever arrive.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Need help to tune the query [message #417050 is a reply to message #417049] Wed, 05 August 2009 17:14 Go to previous messageGo to next message
oraappsfaq
Messages: 9
Registered: August 2009
Location: US
Junior Member
Yes, I understand. I am going through the links provided simultaneously.
Re: Need help to tune the query [message #417051 is a reply to message #417042] Wed, 05 August 2009 17:25 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Post Operating System (OS) name & version for DB server system.
Post results of SELECT * from v$version.

Are statistics current on all tables & indexes?
Re: Need help to tune the query [message #417057 is a reply to message #417051] Wed, 05 August 2009 18:39 Go to previous messageGo to next message
oraappsfaq
Messages: 9
Registered: August 2009
Location: US
Junior Member
The issue with this query is its erratic behavior. The program runs every 2 hours and only sometimes (once in the begging of the month) it gives error "snapshot too old rollback segment is too small."

We have been suspecting that something else goes on in application when this happens but DBA's are insisting that we tune the query.

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

yes all indexes and other stats are current.
Re: Need help to tune the query [message #417059 is a reply to message #417042] Wed, 05 August 2009 19:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
> it gives error "snapshot too old rollback segment is too small."
The SQL is the victim & has no problem; per se.

http://asktom.oracle.com
do keyword search on ORA-01555

Problem is some SQL is doing DML against same table(s) in posted SELECT above.
Re: Need help to tune the query [message #417229 is a reply to message #417059] Thu, 06 August 2009 11:14 Go to previous messageGo to next message
oraappsfaq
Messages: 9
Registered: August 2009
Location: US
Junior Member
This is helpful. We will keep an eye on the program and updates to database, to nail this down. Thanks a ton.
Re: Need help to tune the query [message #417230 is a reply to message #417042] Thu, 06 August 2009 11:21 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Problem is some SQL is doing DML against same table(s) in posted SELECT above.
To clarify the above statement is necessary, but not sufficient to generate ORA-01555. The DML must be followed with COMMIT;

Please post EXPLAIN PLAN for original SELECT & use <code tags> as described in Posting Guidelines

SQL> SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
SQL> SELECT ....

use CUT & PASTE results back here
Re: Need help to tune the query [message #417235 is a reply to message #417230] Thu, 06 August 2009 11:33 Go to previous messageGo to next message
oraappsfaq
Messages: 9
Registered: August 2009
Location: US
Junior Member
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=819 Card=5 Bytes=6
42)

1 0 UNION-ALL
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_SITE_USES_ALL'
(TABLE) (Cost=3 Card=1 Bytes=21)

3 2 NESTED LOOPS (Cost=72 Card=1 Bytes=123)
4 3 NESTED LOOPS (Cost=69 Card=1 Bytes=102)
5 4 NESTED LOOPS (Cost=67 Card=1 Bytes=92)
6 5 NESTED LOOPS (Cost=66 Card=1 Bytes=80)
7 6 NESTED LOOPS (Cost=63 Card=1 Bytes=65)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_AC
COUNTS' (TABLE) (Cost=62 Card=1 Bytes=29)

9 8 INDEX (FULL SCAN) OF 'HZ_CUST_ACCOUNTS_U1'
(INDEX (UNIQUE)) (Cost=61 Card=1)

10 7 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTIES
' (TABLE) (Cost=1 Card=1 Bytes=36)

11 10 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (IN
DEX (UNIQUE)) (Cost=0 Card=1)

12 6 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTY_SIT
ES' (TABLE) (Cost=3 Card=1 Bytes=15)

13 12 INDEX (RANGE SCAN) OF 'HZ_PARTY_SITES_N1' (I
NDEX) (Cost=1 Card=2)

14 5 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_LOCATIONS'
(TABLE) (Cost=1 Card=1 Bytes=12)

15 14 INDEX (UNIQUE SCAN) OF 'HZ_LOCATIONS_U1' (INDE
X (UNIQUE)) (Cost=0 Card=1)

16 4 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_ACCT_SIT
ES_ALL' (TABLE) (Cost=2 Card=1 Bytes=10)

17 16 INDEX (RANGE SCAN) OF 'HZ_CUST_ACCT_SITES_N1' (I
NDEX) (Cost=1 Card=1)

18 3 INDEX (RANGE SCAN) OF 'HZ_CUST_SITE_USES_N1' (INDEX)
(Cost=2 Card=1)

19 1 NESTED LOOPS (ANTI) (Cost=64 Card=1 Bytes=70)
20 19 NESTED LOOPS (Cost=63 Card=1 Bytes=65)
21 20 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_ACCOUNTS'
(TABLE) (Cost=62 Card=1 Bytes=29)

22 21 INDEX (FULL SCAN) OF 'HZ_CUST_ACCOUNTS_U1' (INDEX
(UNIQUE)) (Cost=61 Card=1)

23 20 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTIES' (TABLE
) (Cost=1 Card=1 Bytes=36)

24 23 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (INDEX (UNI
QUE)) (Cost=0 Card=1)

25 19 INDEX (RANGE SCAN) OF 'HZ_PARTY_SITES_N1' (INDEX) (Cos
t=1 Card=16524 Bytes=82620)

26 1 NESTED LOOPS (Cost=286 Card=1 Bytes=121)
27 26 NESTED LOOPS (Cost=284 Card=1 Bytes=102)
28 27 NESTED LOOPS (Cost=282 Card=1 Bytes=92)
29 28 NESTED LOOPS (Cost=281 Card=1 Bytes=80)
30 29 NESTED LOOPS (Cost=278 Card=1 Bytes=65)
31 30 TABLE ACCESS (FULL) OF 'HZ_CUST_ACCOUNTS' (TAB
LE) (Cost=277 Card=1 Bytes=29)

32 30 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTIES'
(TABLE) (Cost=1 Card=1 Bytes=36)

33 32 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (INDE
X (UNIQUE)) (Cost=0 Card=1)

34 33 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_
SITE_USES_ALL' (TABLE) (Cost=3 Card=1 Bytes=21)

35 34 NESTED LOOPS (Cost=8 Card=1 Bytes=46)
36 35 NESTED LOOPS (Cost=5 Card=1 Bytes=25)
37 36 NESTED LOOPS (Cost=3 Card=1 Bytes=15
)

38 37 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES
_U1' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=5)

39 37 TABLE ACCESS (BY INDEX ROWID) OF '
HZ_PARTY_SITES' (TABLE) (Cost=2 Card=1 Bytes=10)

40 39 INDEX (RANGE SCAN) OF 'HZ_PARTY_
SITES_N1' (INDEX) (Cost=1 Card=1)

41 36 TABLE ACCESS (BY INDEX ROWID) OF 'HZ
_CUST_ACCT_SITES_ALL' (TABLE) (Cost=2 Card=1 Bytes=10)

42 41 INDEX (RANGE SCAN) OF 'HZ_CUST_ACC
T_SITES_N1' (INDEX) (Cost=1 Card=1)

43 35 INDEX (RANGE SCAN) OF 'HZ_CUST_SITE_US
ES_N1' (INDEX) (Cost=2 Card=1)

44 29 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTY_SITES
' (TABLE) (Cost=3 Card=1 Bytes=15)

45 44 INDEX (RANGE SCAN) OF 'HZ_PARTY_SITES_N1' (IND
EX) (Cost=1 Card=2)

46 28 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_LOCATIONS' (T
ABLE) (Cost=1 Card=1 Bytes=12)

47 46 INDEX (UNIQUE SCAN) OF 'HZ_LOCATIONS_U1' (INDEX
(UNIQUE)) (Cost=0 Card=1)

48 27 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_ACCT_SITES
_ALL' (TABLE) (Cost=2 Card=1 Bytes=10)

49 48 INDEX (RANGE SCAN) OF 'HZ_CUST_ACCT_SITES_N1' (IND
EX) (Cost=1 Card=1)

50 26 INDEX (RANGE SCAN) OF 'HZ_CUST_SITE_USES_N1' (INDEX) (
Cost=2 Card=1 Bytes=19)

51 50 SORT (AGGREGATE)
52 51 NESTED LOOPS (Cost=7 Card=1 Bytes=44)
53 52 NESTED LOOPS (Cost=5 Card=1 Bytes=25)
54 53 NESTED LOOPS (Cost=3 Card=1 Bytes=15)
55 54 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (INDE
X (UNIQUE)) (Cost=1 Card=1 Bytes=5)

56 54 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTY_S
ITES' (TABLE) (Cost=2 Card=1 Bytes=10)

57 56 INDEX (RANGE SCAN) OF 'HZ_PARTY_SITES_N1'
(INDEX) (Cost=1 Card=1)

58 53 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_ACCT
_SITES_ALL' (TABLE) (Cost=2 Card=1 Bytes=10)

59 58 INDEX (RANGE SCAN) OF 'HZ_CUST_ACCT_SITES_N1
' (INDEX) (Cost=1 Card=1)

60 52 INDEX (RANGE SCAN) OF 'HZ_CUST_SITE_USES_N1' (IN
DEX) (Cost=2 Card=1 Bytes=19)

61 1 FILTER
62 61 HASH (GROUP BY) (Cost=91 Card=1 Bytes=241)
63 62 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_SITE_USES_
ALL' (TABLE) (Cost=4 Card=1 Bytes=22)

64 63 NESTED LOOPS (Cost=80 Card=1 Bytes=241)
65 64 NESTED LOOPS (Cost=76 Card=1 Bytes=219)
66 65 NESTED LOOPS (Cost=76 Card=1 Bytes=214)
67 66 NESTED LOOPS (Cost=75 Card=1 Bytes=204)
68 67 NESTED LOOPS (Cost=72 Card=1 Bytes=184)
69 68 NESTED LOOPS (Cost=69 Card=1 Bytes=162)
70 69 NESTED LOOPS (Cost=67 Card=1 Bytes=140
)

71 70 NESTED LOOPS (Cost=66 Card=1 Bytes=1
16)

72 71 NESTED LOOPS (Cost=63 Card=1 Bytes
=89)

73 72 TABLE ACCESS (BY INDEX ROWID) OF
'HZ_CUST_ACCOUNTS' (TABLE) (Cost=62 Card=1 Bytes=41)

74 73 INDEX (FULL SCAN) OF 'HZ_CUST_
ACCOUNTS_U1' (INDEX (UNIQUE)) (Cost=61 Card=1)

75 74 NESTED LOOPS (Cost=10 Card=2
Bytes=94)

76 75 NESTED LOOPS (Cost=6 Card=
2 Bytes=60)

77 76 NESTED LOOPS (Cost=6 Car
d=2 Bytes=50)

78 77 TABLE ACCESS (BY INDEX
ROWID) OF 'HZ_CUST_ACCT_SITES_ALL' (TABLE) (Cost=4 Card=2 B
ytes=30)

79 78 INDEX (RANGE SCAN) O
F 'HZ_CUST_ACCT_SITES_N2' (INDEX) (Cost=2 Card=2)

80 77 TABLE ACCESS (BY INDEX
ROWID) OF 'HZ_PARTY_SITES' (TABLE) (Cost=1 Card=1 Bytes=10)

81 80 INDEX (UNIQUE SCAN)
OF 'HZ_PARTY_SITES_U1' (INDEX (UNIQUE)) (Cost=0 Card=1)

82 76 INDEX (UNIQUE SCAN) OF '
HZ_PARTIES_U1' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=5)

83 75 INDEX (RANGE SCAN) OF 'HZ_
CUST_SITE_USES_N1' (INDEX) (Cost=2 Card=1 Bytes=17)

84 72 TABLE ACCESS (BY INDEX ROWID) OF
'HZ_PARTIES' (TABLE) (Cost=1 Card=1 Bytes=48)

85 84 INDEX (UNIQUE SCAN) OF 'HZ_PAR
TIES_U1' (INDEX (UNIQUE)) (Cost=0 Card=1)

86 71 TABLE ACCESS (BY INDEX ROWID) OF '
HZ_PARTY_SITES' (TABLE) (Cost=3 Card=1 Bytes=27)

87 86 INDEX (RANGE SCAN) OF 'HZ_PARTY_
SITES_N1' (INDEX) (Cost=1 Card=2)

88 70 TABLE ACCESS (BY INDEX ROWID) OF 'HZ
_LOCATIONS' (TABLE) (Cost=1 Card=1 Bytes=24)

89 88 INDEX (UNIQUE SCAN) OF 'HZ_LOCATIO
NS_U1' (INDEX (UNIQUE)) (Cost=0 Card=1)

90 69 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_C
UST_ACCT_SITES_ALL' (TABLE) (Cost=2 Card=1 Bytes=22)

91 90 INDEX (RANGE SCAN) OF 'HZ_CUST_ACCT_
SITES_N1' (INDEX) (Cost=1 Card=1)

92 68 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUS
T_SITE_USES_ALL' (TABLE) (Cost=3 Card=2 Bytes=44)

93 92 INDEX (RANGE SCAN) OF 'HZ_CUST_SITE_US
ES_N1' (INDEX) (Cost=2 Card=2)

94 67 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_
ACCT_SITES_ALL' (TABLE) (Cost=3 Card=2 Bytes=40)

95 94 INDEX (RANGE SCAN) OF 'HZ_CUST_ACCT_SITE
S_N2' (INDEX) (Cost=1 Card=2)

96 66 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTY_S
ITES' (TABLE) (Cost=1 Card=1 Bytes=10)

97 96 INDEX (UNIQUE SCAN) OF 'HZ_PARTY_SITES_U1'
(INDEX (UNIQUE)) (Cost=0 Card=1)

98 65 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (INDEX
(UNIQUE)) (Cost=0 Card=1 Bytes=5)

99 64 INLIST ITERATOR
100 99 INDEX (RANGE SCAN) OF 'HZ_CUST_SITE_USES_N1' (
INDEX) (Cost=3 Card=1)

101 1 NESTED LOOPS (Cost=282 Card=1 Bytes=87)
102 101 NESTED LOOPS (Cost=281 Card=1 Bytes=75)
103 102 NESTED LOOPS (Cost=278 Card=1 Bytes=65)
104 103 TABLE ACCESS (FULL) OF 'HZ_CUST_ACCOUNTS' (TABLE)
(Cost=277 Card=1 Bytes=29)

105 103 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTIES' (TAB
LE) (Cost=1 Card=1 Bytes=36)

106 105 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (INDEX (U
NIQUE)) (Cost=0 Card=1)

107 106 NESTED LOOPS (Cost=7 Card=2 Bytes=60)
108 107 NESTED LOOPS (Cost=5 Card=1 Bytes=25)
109 108 NESTED LOOPS (Cost=3 Card=1 Bytes=15)
110 109 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (
INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=5)

111 109 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PAR
TY_SITES' (TABLE) (Cost=2 Card=1 Bytes=10)

112 111 INDEX (RANGE SCAN) OF 'HZ_PARTY_SITES_
N1' (INDEX) (Cost=1 Card=1)

113 108 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_
ACCT_SITES_ALL' (TABLE) (Cost=2 Card=1 Bytes=10)

114 113 INDEX (RANGE SCAN) OF 'HZ_CUST_ACCT_SITE
S_N1' (INDEX) (Cost=1 Card=1)

115 107 INDEX (RANGE SCAN) OF 'HZ_CUST_SITE_USES_N1'
(INDEX) (Cost=2 Card=2 Bytes=10)

116 102 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTY_SITES' (T
ABLE) (Cost=3 Card=1 Bytes=10)

117 116 INDEX (RANGE SCAN) OF 'HZ_PARTY_SITES_N1' (INDEX)
(Cost=1 Card=2)

118 101 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_LOCATIONS' (TABLE
) (Cost=1 Card=1 Bytes=12)

119 118 INDEX (UNIQUE SCAN) OF 'HZ_LOCATIONS_U1' (INDEX (UNI
QUE)) (Cost=0 Card=1)





Statistics
----------------------------------------------------------
141 recursive calls
0 db block gets
485998 consistent gets
233 physical reads
0 redo size
78208 bytes sent via SQL*Net to client
7149 bytes received via SQL*Net from client
62 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
906 rows processed

SQL>
Re: Need help to tune the query [message #417242 is a reply to message #417235] Thu, 06 August 2009 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you really think we can read what you posted?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), 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.

Regards
Michel
Re: Need help to tune the query [message #417253 is a reply to message #417242] Thu, 06 August 2009 14:30 Go to previous messageGo to next message
oraappsfaq
Messages: 9
Registered: August 2009
Location: US
Junior Member
Formatted query. Preview does not show any indentation so hoping the actual post will.

SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/
hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id,
hcas.cust_acct_site_id address_id,
hl.state primary_state, hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id + 0
AND hcsu.site_use_code = 'SHIP_TO'
AND hcsu.primary_flag = 'Y'
AND hcsu.status = 'A'
UNION ALL --WHERE NO PARTY_SITE_ID
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/
hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
-1 location_id,
-1 address_id, '' primary_state, '' primary_country
FROM hz_parties hp, hz_cust_accounts hca
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND NOT EXISTS (SELECT 'z'
FROM hz_party_sites hps
WHERE hp.party_id = hps.party_id)
UNION ALL --THERE IS SHIP_TO BUT NO ACTIVE PRIMARY SHIP TO
SELECT hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id,
hcas.cust_acct_site_id address_id,
hl.state primary_state, hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.site_use_code = 'SHIP_TO'
AND hcsu.primary_flag = 'N'
AND hcsu.cust_acct_site_id =
(SELECT MIN (hcsu2.cust_acct_site_id)
FROM hz_parties hp2,
hz_party_sites hps2,
hz_cust_acct_sites_all hcas2,
hz_cust_site_uses_all hcsu2
WHERE hp2.party_id = hps2.party_id
AND hps2.party_site_id = hcas2.party_site_id
AND hcas2.cust_acct_site_id = hcsu2.cust_acct_site_id
AND hcsu2.site_use_code = 'SHIP_TO'
AND hcsu2.primary_flag = 'N'
AND hp2.party_id = hp.party_id)
AND NOT EXISTS (
SELECT 'z'
FROM hz_parties hp1,
hz_party_sites hps1,
hz_cust_acct_sites_all hcas1,
hz_cust_site_uses_all hcsu1
WHERE hp1.party_id = hps1.party_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hcsu1.site_use_code = 'SHIP_TO'
AND hcsu1.primary_flag = 'Y'
AND hcsu1.status = 'A'
AND hp1.party_id = hp.party_id)
UNION ALL -- NO SHIP TO , but BILL_TO
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/
hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id,
hcas.cust_acct_site_id address_id,
hl.state primary_state,
hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.site_use_id =
(SELECT MIN (hcsu2.site_use_id)
FROM hz_parties hp2,
hz_party_sites hps2,
hz_cust_acct_sites_all hcas2,
hz_cust_site_uses_all hcsu2
WHERE hp2.party_id = hps2.party_id
AND hps2.party_site_id = hcas2.party_site_id
AND hcas2.cust_acct_site_id = hcsu2.cust_acct_site_id
AND hcsu2.site_use_code IN ('BILL_TO', 'INDIVIDUAL RESPONSIBLE SUB')
AND hcas2.cust_account_id = hca.cust_account_id)
AND NOT EXISTS (
SELECT 'z'
FROM hz_parties hp1,
hz_party_sites hps1,
hz_cust_acct_sites_all hcas1,
hz_cust_site_uses_all hcsu1
WHERE hp1.party_id = hps1.party_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hcsu1.site_use_code = 'SHIP_TO'
AND hcas1.cust_account_id = hca.cust_account_id)
UNION ALL
--No Site Use (Most of these customers are type PERSON which have some location but no address..confusing..??)Smile)
SELECT hp.party_name customer_name,
hca.account_number customer_number,
hca.cust_account_id customer_id,
hp.category_code category_code,
TO_NUMBER (hca.attribute12) system_code,
NVL (hca.attribute10, 'N') vip_list,
TO_NUMBER (hca.attribute13) manual_code,
NVL (hca.attribute14, 'N') strategic_account,
hl.location_id location_id,
-1 address_id,
hl.state primary_state,
hl.country primary_country
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_locations hl
WHERE hp.party_id = hca.party_id
AND hca.attribute12 IN ('1', '2', '3', '4')
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND NOT EXISTS (
SELECT 'z'
FROM hz_parties hp1,
hz_party_sites hps1,
hz_cust_acct_sites_all hcas1,
hz_cust_site_uses_all hcsu1
WHERE hp1.party_id = hps1.party_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hp1.party_id = hp.party_id);
Re: Need help to tune the query [message #417256 is a reply to message #417042] Thu, 06 August 2009 14:37 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/ hp.party_name              customer_name,
                                           hca.account_number         customer_number,
                                           hca.cust_account_id        customer_id,
                                           hp.category_code           category_code,
                                           To_number(hca.attribute12) system_code,
                                           Nvl(hca.attribute10,'N')   vip_list,
                                           To_number(hca.attribute13) manual_code,
                                           Nvl(hca.attribute14,'N')   strategic_account,
                                           hl.location_id             location_id,
                                           hcas.cust_acct_site_id     address_id,
                                           hl.state                   primary_state,
                                           hl.country                 primary_country
FROM   hz_parties hp,
       hz_cust_accounts hca,
       hz_party_sites hps,
       hz_locations hl,
       hz_cust_acct_sites_all hcas,
       hz_cust_site_uses_all hcsu
WHERE  hp.party_id = hca.party_id
       AND hca.attribute12 IN ('1','2','3','4')
       AND hca.cust_account_id = Nvl(:p_customer_id,hca.cust_account_id)
       AND hp.party_id = hps.party_id
       AND hps.location_id = hl.location_id
       AND hps.party_site_id = hcas.party_site_id
       AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id + 0
       AND hcsu.site_use_code = 'SHIP_TO'
       AND hcsu.primary_flag = 'Y'
       AND hcsu.status = 'A'
UNION ALL --WHERE NO PARTY_SITE_ID
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/ hp.party_name              customer_name,
                                           hca.account_number         customer_number,
                                           hca.cust_account_id        customer_id,
                                           hp.category_code           category_code,
                                           To_number(hca.attribute12) system_code,
                                           Nvl(hca.attribute10,'N')   vip_list,
                                           To_number(hca.attribute13) manual_code,
                                           Nvl(hca.attribute14,'N')   strategic_account,
                                           -1                         location_id,
                                           -1                         address_id,
                                           ''                         primary_state,
                                           ''                         primary_country
FROM   hz_parties hp,
       hz_cust_accounts hca
WHERE  hp.party_id = hca.party_id
       AND hca.attribute12 IN ('1','2','3','4')
       AND hca.cust_account_id = Nvl(:p_customer_id,hca.cust_account_id)
       AND NOT EXISTS (SELECT 'z'
                       FROM   hz_party_sites hps
                       WHERE  hp.party_id = hps.party_id)
UNION ALL --THERE IS SHIP_TO BUT NO ACTIVE PRIMARY SHIP TO
SELECT hp.party_name              customer_name,
       hca.account_number         customer_number,
       hca.cust_account_id        customer_id,
       hp.category_code           category_code,
       To_number(hca.attribute12) system_code,
       Nvl(hca.attribute10,'N')   vip_list,
       To_number(hca.attribute13) manual_code,
       Nvl(hca.attribute14,'N')   strategic_account,
       hl.location_id             location_id,
       hcas.cust_acct_site_id     address_id,
       hl.state                   primary_state,
       hl.country                 primary_country
FROM   hz_parties hp,
       hz_cust_accounts hca,
       hz_party_sites hps,
       hz_locations hl,
       hz_cust_acct_sites_all hcas,
       hz_cust_site_uses_all hcsu
WHERE  hp.party_id = hca.party_id
       AND hca.attribute12 IN ('1','2','3','4')
       AND hca.cust_account_id = Nvl(:p_customer_id,hca.cust_account_id)
       AND hp.party_id = hps.party_id
       AND hps.location_id = hl.location_id
       AND hps.party_site_id = hcas.party_site_id
       AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
       AND hcsu.site_use_code = 'SHIP_TO'
       AND hcsu.primary_flag = 'N'
       AND hcsu.cust_acct_site_id = (SELECT Min(hcsu2.cust_acct_site_id)
                                     FROM   hz_parties hp2,
                                            hz_party_sites hps2,
                                            hz_cust_acct_sites_all hcas2,
                                            hz_cust_site_uses_all hcsu2
                                     WHERE  hp2.party_id = hps2.party_id
                                            AND hps2.party_site_id = hcas2.party_site_id
                                            AND hcas2.cust_acct_site_id = hcsu2.cust_acct_site_id
                                            AND hcsu2.site_use_code = 'SHIP_TO'
                                            AND hcsu2.primary_flag = 'N'
                                            AND hp2.party_id = hp.party_id)
       AND NOT EXISTS (SELECT 'z'
                       FROM   hz_parties hp1,
                              hz_party_sites hps1,
                              hz_cust_acct_sites_all hcas1,
                              hz_cust_site_uses_all hcsu1
                       WHERE  hp1.party_id = hps1.party_id
                              AND hps1.party_site_id = hcas1.party_site_id
                              AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
                              AND hcsu1.site_use_code = 'SHIP_TO'
                              AND hcsu1.primary_flag = 'Y'
                              AND hcsu1.status = 'A'
                              AND hp1.party_id = hp.party_id)
UNION ALL -- NO SHIP TO , but BILL_TO
SELECT /*+index(hca,HZ_CUST_ACCOUNTS_U1)*/ hp.party_name              customer_name,
                                           hca.account_number         customer_number,
                                           hca.cust_account_id        customer_id,
                                           hp.category_code           category_code,
                                           To_number(hca.attribute12) system_code,
                                           Nvl(hca.attribute10,'N')   vip_list,
                                           To_number(hca.attribute13) manual_code,
                                           Nvl(hca.attribute14,'N')   strategic_account,
                                           hl.location_id             location_id,
                                           hcas.cust_acct_site_id     address_id,
                                           hl.state                   primary_state,
                                           hl.country                 primary_country
FROM   hz_parties hp,
       hz_cust_accounts hca,
       hz_party_sites hps,
       hz_locations hl,
       hz_cust_acct_sites_all hcas,
       hz_cust_site_uses_all hcsu
WHERE  hp.party_id = hca.party_id
       AND hca.attribute12 IN ('1','2','3','4')
       AND hca.cust_account_id = Nvl(:p_customer_id,hca.cust_account_id)
       AND hp.party_id = hps.party_id
       AND hps.location_id = hl.location_id
       AND hps.party_site_id = hcas.party_site_id
       AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
       AND hcsu.site_use_id = (SELECT Min(hcsu2.site_use_id)
                               FROM   hz_parties hp2,
                                      hz_party_sites hps2,
                                      hz_cust_acct_sites_all hcas2,
                                      hz_cust_site_uses_all hcsu2
                               WHERE  hp2.party_id = hps2.party_id
                                      AND hps2.party_site_id = hcas2.party_site_id
                                      AND hcas2.cust_acct_site_id = hcsu2.cust_acct_site_id
                                      AND hcsu2.site_use_code IN ('BILL_TO','INDIVIDUAL RESPONSIBLE SUB')
                                      AND hcas2.cust_account_id = hca.cust_account_id)
       AND NOT EXISTS (SELECT 'z'
                       FROM   hz_parties hp1,
                              hz_party_sites hps1,
                              hz_cust_acct_sites_all hcas1,
                              hz_cust_site_uses_all hcsu1
                       WHERE  hp1.party_id = hps1.party_id
                              AND hps1.party_site_id = hcas1.party_site_id
                              AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
                              AND hcsu1.site_use_code = 'SHIP_TO'
                              AND hcas1.cust_account_id = hca.cust_account_id)
UNION ALL
--No Site Use (Most of these customers are type PERSON which have some location but no address..confusing..??)Smile)
SELECT hp.party_name              customer_name,
       hca.account_number         customer_number,
       hca.cust_account_id        customer_id,
       hp.category_code           category_code,
       To_number(hca.attribute12) system_code,
       Nvl(hca.attribute10,'N')   vip_list,
       To_number(hca.attribute13) manual_code,
       Nvl(hca.attribute14,'N')   strategic_account,
       hl.location_id             location_id,
       -1                         address_id,
       hl.state                   primary_state,
       hl.country                 primary_country
FROM   hz_parties hp,
       hz_cust_accounts hca,
       hz_party_sites hps,
       hz_locations hl
WHERE  hp.party_id = hca.party_id
       AND hca.attribute12 IN ('1','2','3','4')
       AND hca.cust_account_id = Nvl(:p_customer_id,hca.cust_account_id)
       AND hp.party_id = hps.party_id
       AND hps.location_id = hl.location_id
       AND NOT EXISTS (SELECT 'z'
                       FROM   hz_parties hp1,
                              hz_party_sites hps1,
                              hz_cust_acct_sites_all hcas1,
                              hz_cust_site_uses_all hcsu1
                       WHERE  hp1.party_id = hps1.party_id
                              AND hps1.party_site_id = hcas1.party_site_id
                              AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
                              AND hp1.party_id = hp.party_id); 


which is easier to read & understand?
Re: Need help to tune the query [message #417257 is a reply to message #417256] Thu, 06 August 2009 14:46 Go to previous messageGo to next message
oraappsfaq
Messages: 9
Registered: August 2009
Location: US
Junior Member
Great! How did you do it? Sorry, I am a newbie on this forum.
Re: Need help to tune the query [message #417258 is a reply to message #417042] Thu, 06 August 2009 14:50 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Need help to tune the query [message #417259 is a reply to message #417257] Thu, 06 August 2009 15:08 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
oraappsfaq wrote on Thu, 06 August 2009 21:46
Great! How did you do it? Sorry, I am a newbie on this forum.

Michel Cadot wrote on Thu, 06 August 2009 19:45
Do you really think we can read what you posted?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), 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.

Regards
Michel

Are you able to read?

[Updated on: Thu, 06 August 2009 15:09]

Report message to a moderator

Previous Topic: Block Corruption
Next Topic: Hard parse in RAC
Goto Forum:
  


Current Time: Fri Dec 02 16:39:19 CST 2016

Total time taken to generate the page: 0.10539 seconds