Home » RDBMS Server » Performance Tuning » performance hit from 9i to 10g
| performance hit from 9i to 10g [message #171977] |
Fri, 12 May 2006 13:39  |
ericdp
Messages: 4
Registered: May 2006
Location: Bellevue, WA
|
Junior Member |
|
|
This query ran in 110 minutes under 9i but moving to 10.2.0.2 it now runs in 4 hours. Looking at the plan below the amount of Temp Space concerns me.
SELECT
USAGE.SUB_KEY,
USAGE.BILL_ACCT_KEY,
MAX_RECORD.MAX_BILL_DATE AS RECENT_BILL_DATE, SUM (DECODE (TO_CHAR (USAGE.BILL_DATE, 'MM'), TO_CHAR (ADD_MONTHS (MAX_RECORD.MAX_BILL_DATE, 0),'MM'), DECODE (TO_CHAR (USAGE.BILL_DATE, 'YYYY'), TO_CHAR (ADD_MONTHS (MAX_RECORD.MAX_BILL_DATE, 0), 'YYYY'), USAGE.TOTAL_VOICE_CALL_CT, 0), 0)) AS TOTAL_VOICE_CALL_CT_MONTH_0,
...
SUM (DECODE (TO_CHAR (USAGE.BILL_DATE, 'MM'), TO_CHAR (ADD_MONTHS (MAX_RECORD.MAX_BILL_DATE, -3),'MM'), DECODE (TO_CHAR (USAGE.BILL_DATE, 'YYYY'), TO_CHAR (ADD_MONTHS (MAX_RECORD.MAX_BILL_DATE, -3), 'YYYY'), USAGE.VOICEMAIL_CALL_CT, 0), 0)) AS VOICEMAIL_CALL_CT_MONTH_3
FROM
NIW_SUB_CYC_USG_SUM_HIST_RP USAGE,
NIW_SUB_STATUS_HIST_ODS STATUS,
NIW_SUB_STATUS_ODS LKP,
(SELECT SUB_KEY, MAX (BILL_DATE) MAX_BILL_DATE
FROM NIW_SUB_CYC_USG_SUM_HIST_RP
WHERE SUB_KEY > 0 GROUP BY SUB_KEY
) MAX_RECORD
WHERE
LKP.SUB_STATUS_GROUP_CODE = 'A' AND
LKP.SUB_STATUS_KEY = STATUS.SUB_STATUS_KEY AND
STATUS.EFF_DTS <= MAX_RECORD.MAX_BILL_DATE AND
STATUS.END_DTS >= MAX_RECORD.MAX_BILL_DATE AND
STATUS.SUB_KEY = USAGE.SUB_KEY AND USAGE.SUB_KEY > 0 AND
USAGE.SUB_KEY = MAX_RECORD.SUB_KEY AND
ADD_MONTHS (MAX_RECORD.MAX_BILL_DATE, -4) <= USAGE.BILL_DATE
GROUP BY
USAGE.SUB_KEY,
USAGE.BILL_ACCT_KEY,
MAX_RECORD.MAX_BILL_DATE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34267 | 3681K| | 1359K (5)| 05:17:17 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10005 | 34267 | 3681K| | 1359K (5)| 05:17:17 | | | Q1,05 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 34267 | 3681K| 8080K| 1359K (5)| 05:17:17 | | | Q1,05 | PCWP | |
| 4 | PX RECEIVE | | 34267 | 3681K| | 1359K (5)| 05:17:17 | | | Q1,05 | PCWP | |
| 5 | PX SEND HASH | :TQ10004 | 34267 | 3681K| | 1359K (5)| 05:17:17 | | | Q1,04 | P->P | HASH |
| 6 | HASH GROUP BY | | 34267 | 3681K| 8080K| 1359K (5)| 05:17:17 | | | Q1,04 | PCWP | |
|* 7 | HASH JOIN | | 34267 | 3681K| | 1359K (5)| 05:17:17 | | | Q1,04 | PCWP | |
| 8 | BUFFER SORT | | | | | | | | | Q1,04 | PCWC | |
| 9 | PX RECEIVE | | 8005K| 122M| | 584K (6)| 02:16:30 | | | Q1,04 | PCWP | |
| 10 | PX SEND HASH | :TQ10000 | 8005K| 122M| | 584K (6)| 02:16:30 | | | | S->P | HASH |
| 11 | VIEW | | 8005K| 122M| | 584K (6)| 02:16:30 | | | | | |
| 12 | HASH GROUP BY | | 8005K| 167M| 6280M| 584K (6)| 02:16:30 | | | | | |
| 13 | PARTITION RANGE ALL | | 197M| 4141M| | 145K (4)| 00:33:57 | 1 | 835 | | | |
|* 14 | INDEX FAST FULL SCAN| PK_NIW_SUB_CYC_USG_SUM_HIST_RP | 197M| 4141M| | 145K (4)| 00:33:57 | 1 | 835 | | | |
|* 15 | HASH JOIN | | 409M| 35G| 289M| 773K (4)| 03:00:30 | | | Q1,04 | PCWP | |
| 16 | PX RECEIVE | | 27M| 841M| | 29892 (3)| 00:06:59 | | | Q1,04 | PCWP | |
| 17 | PX SEND HASH | :TQ10003 | 27M| 841M| | 29892 (3)| 00:06:59 | | | Q1,03 | P->P | HASH |
|* 18 | HASH JOIN | | 27M| 841M| | 29892 (3)| 00:06:59 | | | Q1,03 | PCWP | |
| 19 | PX RECEIVE | | 6 | 30 | | 2 (0)| 00:00:01 | | | Q1,03 | PCWP | |
| 20 | PX SEND BROADCAST | :TQ10002 | 6 | 30 | | 2 (0)| 00:00:01 | | | Q1,02 | P->P | BROADCAST |
| 21 | PX BLOCK ITERATOR | | 6 | 30 | | 2 (0)| 00:00:01 | | | Q1,02 | PCWC | |
|* 22 | TABLE ACCESS FULL | NIW_SUB_STATUS_ODS | 6 | 30 | | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 23 | PX BLOCK ITERATOR | | 55M| 1420M| | 29727 (2)| 00:06:57 | | | Q1,03 | PCWC | |
|* 24 | TABLE ACCESS FULL | NIW_SUB_STATUS_HIST_ODS | 55M| 1420M| | 29727 (2)| 00:06:57 | | | Q1,03 | PCWP | |
| 25 | BUFFER SORT | | | | | | | | | Q1,04 | PCWC | |
| 26 | PX RECEIVE | | 197M| 11G| | 580K (5)| 02:15:27 | | | Q1,04 | PCWP | |
| 27 | PX SEND HASH | :TQ10001 | 197M| 11G| | 580K (5)| 02:15:27 | | | | S->P | HASH |
| 28 | PARTITION RANGE ALL | | 197M| 11G| | 580K (5)| 02:15:27 | 1 | 835 | | | |
|* 29 | TABLE ACCESS FULL | NIW_SUB_CYC_USG_SUM_HIST_RP | 197M| 11G| | 580K (5)| 02:15:27 | 1 | 835 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
If I re-write the FROM clause to use SQL99 and add a parallel hint to the sub-query, I get an improvement (according to the plan anaylyzer):
FROM
niw_sub_cyc_usg_sum_hist_rp USAGE
JOIN niw_sub_status_hist_ods STATUS
ON ( USAGE.sub_key = STATUS.sub_key )
JOIN niw_sub_status_ods LKP
ON ( LKP.sub_status_key = STATUS.sub_status_key AND
LKP.sub_status_group_code = 'A' )
JOIN (
SELECT /*+ PARALLEL(niw_sub_cyc_usg_sum_hist_rp) */
sub_key,
MAX ( bill_date ) AS max_bill_date
FROM
niw_sub_cyc_usg_sum_hist_rp
WHERE
sub_key > 0
GROUP BY
sub_key
) MAX_RECORD
ON ( MAX_RECORD.sub_key = USAGE.sub_key AND
ADD_MONTHS ( MAX_RECORD.max_bill_date, -4) <= USAGE.bill_date AND
MAX_RECORD.max_bill_date BETWEEN STATUS.end_dts AND
STATUS.eff_dts ) WHERE
USAGE.sub_key > 0
GROUP BY
USAGE.sub_key,
USAGE.bill_acct_key,
MAX_RECORD.max_bill_date;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34267 | 3681K| | 686K (4)| 02:40:14 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10006 | 34267 | 3681K| | 686K (4)| 02:40:14 | | | Q1,06 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 34267 | 3681K| 8080K| 686K (4)| 02:40:14 | | | Q1,06 | PCWP | |
| 4 | PX RECEIVE | | 34267 | 3681K| | 686K (4)| 02:40:14 | | | Q1,06 | PCWP | |
| 5 | PX SEND HASH | :TQ10005 | 34267 | 3681K| | 686K (4)| 02:40:14 | | | Q1,05 | P->P | HASH |
| 6 | HASH GROUP BY | | 34267 | 3681K| 8080K| 686K (4)| 02:40:14 | | | Q1,05 | PCWP | |
|* 7 | HASH JOIN | | 34267 | 3681K| | 686K (4)| 02:40:14 | | | Q1,05 | PCWP | |
| 8 | PX RECEIVE | | 6 | 30 | | 2 (0)| 00:00:01 | | | Q1,05 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10002 | 6 | 30 | | 2 (0)| 00:00:01 | | | Q1,02 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 6 | 30 | | 2 (0)| 00:00:01 | | | Q1,02 | PCWC | |
|* 11 | TABLE ACCESS FULL | NIW_SUB_STATUS_ODS | 6 | 30 | | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
|* 12 | HASH JOIN | | 68535 | 7027K| 35M| 686K (4)| 02:40:14 | | | Q1,05 | PCWP | |
|* 13 | HASH JOIN | | 6613K| 491M| | 649K (4)| 02:31:39 | | | Q1,05 | PCWP | |
| 14 | PX RECEIVE | | 8005K| 122M| | 69262 (4)| 00:16:10 | | | Q1,05 | PCWP | |
| 15 | PX SEND HASH | :TQ10003 | 8005K| 122M| | 69262 (4)| 00:16:10 | | | Q1,03 | P->P | HASH |
| 16 | VIEW | | 8005K| 122M| | 69262 (4)| 00:16:10 | | | Q1,03 | PCWP | |
| 17 | HASH GROUP BY | | 8005K| 167M| 6280M| 69262 (4)| 00:16:10 | | | Q1,03 | PCWP | |
| 18 | PX RECEIVE | | 197M| 4141M| | 39026 (1)| 00:09:07 | | | Q1,03 | PCWP | |
| 19 | PX SEND HASH | :TQ10001 | 197M| 4141M| | 39026 (1)| 00:09:07 | | | Q1,01 | P->P | HASH |
| 20 | PX BLOCK ITERATOR | | 197M| 4141M| | 39026 (1)| 00:09:07 | 1 | 835 | Q1,01 | PCWC | |
|* 21 | TABLE ACCESS FULL| NIW_SUB_CYC_USG_SUM_HIST_RP | 197M| 4141M| | 39026 (1)| 00:09:07 | 1 | 835 | Q1,01 | PCWP | |
| 22 | BUFFER SORT | | | | | | | | | Q1,05 | PCWC | |
| 23 | PX RECEIVE | | 197M| 11G| | 580K (5)| 02:15:27 | | | Q1,05 | PCWP | |
| 24 | PX SEND HASH | :TQ10000 | 197M| 11G| | 580K (5)| 02:15:27 | | | | S->P | HASH |
| 25 | PARTITION RANGE ALL | | 197M| 11G| | 580K (5)| 02:15:27 | 1 | 835 | | | |
|* 26 | TABLE ACCESS FULL | NIW_SUB_CYC_USG_SUM_HIST_RP | 197M| 11G| | 580K (5)| 02:15:27 | 1 | 835 | | | |
| 27 | PX RECEIVE | | 55M| 1420M| | 29727 (2)| 00:06:57 | | | Q1,05 | PCWP | |
| 28 | PX SEND HASH | :TQ10004 | 55M| 1420M| | 29727 (2)| 00:06:57 | | | Q1,04 | P->P | HASH |
| 29 | PX BLOCK ITERATOR | | 55M| 1420M| | 29727 (2)| 00:06:57 | | | Q1,04 | PCWC | |
|* 30 | TABLE ACCESS FULL | NIW_SUB_STATUS_HIST_ODS | 55M| 1420M| | 29727 (2)| 00:06:57 | | | Q1,04 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
My biggest concern is the use of 6 GB of temp space for the HASH GROUP BY. To me this seem way too high, granted there are 197 million rows in this table. All the tables have recently had stats gathered. This one table (niw_sub_cyc_usg_sum_hist_rp) has many partitions so that is where I suspect the temp space is being used up at since it is basically doing a full scan across all partitions.
Thanks for any insight ya'll can provide.
Eric
|
|
|
|
| Re: performance hit from 9i to 10g [message #171986 is a reply to message #171977] |
Fri, 12 May 2006 15:43  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Does this query produce the same results? How does it perform?SELECT usage.sub_key
, usage.bill_acct_key
, usage.max_bill_date AS recent_bill_date
, SUM(DECODE(TO_CHAR(usage.bill_date,'YYYYMM')
, TO_CHAR(ADD_MONTHS(usage.max_bill_date, 0),'YYYYMM'), usage.total_voice_call_ct, 0)) AS total_voice_call_ct_month_0
...
, SUM(DECODE(TO_CHAR(usage.bill_date,'YYYYMM')
, TO_CHAR(ADD_MONTHS(usage.max_bill_date,-3),'YYYYMM'), usage.voicemail_call_ct , 0)) AS voicemail_call_ct_month_3
FROM niw_sub_status_hist_ods status
, niw_sub_status_ods lkp
, (SELECT usg2.sub_key
, usg2.bill_acct_key
, usg2.total_voice_call_ct
, usg2.voicemail_call_ct
, usg2.bill_date
, usg2.max_bill_date
FROM (SELECT usg.sub_key
, usg.bill_acct_key
, usg.total_voice_call_ct
, usg.voicemail_call_ct
, usg.bill_date
, MAX(usg.bill_date)
OVER (PARTITION BY usg.sub_key) max_bill_date
FROM niw_sub_cyc_usg_sum_hist_rp usg
WHERE usg.sub_key > 0) usg2
WHERE ADD_MONTHS(usg2.max_bill_date.-4) <= usg2.bill_date) usage
WHERE lkp.sub_status_group_code = 'A'
AND lkp.sub_status_key = status.sub_status_key
AND max_record.max_bill_date BETWEEN status.eff_dts
AND status.end_dts
AND status.sub_key = usage.sub_key
GROUP BY usage.sub_key
, usage.bill_acct_key
, max_record.max_bill_date
/
|
|
|
|
|
|
| Re: performance hit from 9i to 10g [message #171993 is a reply to message #171986] |
Fri, 12 May 2006 16:46 |
ericdp
Messages: 4
Registered: May 2006
Location: Bellevue, WA
|
Junior Member |
|
|
well, the explain plan still shows gobs of TempSpc being used. But I do like hitting that one large table only once.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1022K| 223M| | 318K (2)| 01:14:25 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10005 | 1022K| 223M| | 318K (2)| 01:14:25 | | | Q1,05 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 1022K| 223M| 524M| 318K (2)| 01:14:25 | | | Q1,05 | PCWP | |
| 4 | PX RECEIVE | | 1022K| 223M| | 318K (2)| 01:14:25 | | | Q1,05 | PCWP | |
| 5 | PX SEND HASH | :TQ10004 | 1022K| 223M| | 318K (2)| 01:14:25 | | | Q1,04 | P->P | HASH |
| 6 | HASH GROUP BY | | 1022K| 223M| 524M| 318K (2)| 01:14:25 | | | Q1,04 | PCWP | |
|* 7 | HASH JOIN | | 1022K| 223M| 83M| 316K (2)| 01:13:55 | | | Q1,04 | PCWP | |
| 8 | PX JOIN FILTER CREATE | :BF0000 | 27M| 1026M| | 29802 (3)| 00:06:58 | | | Q1,04 | PCWP | |
| 9 | PX RECEIVE | | 27M| 1026M| | 29802 (3)| 00:06:58 | | | Q1,04 | PCWP | |
| 10 | PX SEND HASH | :TQ10002 | 27M| 1026M| | 29802 (3)| 00:06:58 | | | Q1,02 | P->P | HASH |
|* 11 | HASH JOIN | | 27M| 1026M| | 29802 (3)| 00:06:58 | | | Q1,02 | PCWP | |
| 12 | PX RECEIVE | | 6 | 30 | | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 13 | PX SEND BROADCAST | :TQ10000 | 6 | 30 | | 2 (0)| 00:00:01 | | | Q1,00 | P->P | BROADCAST |
| 14 | PX BLOCK ITERATOR | | 6 | 30 | | 2 (0)| 00:00:01 | | | Q1,00 | PCWC | |
|* 15 | TABLE ACCESS FULL | NIW_SUB_STATUS_ODS | 6 | 30 | | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 16 | PX BLOCK ITERATOR | | 55M| 1789M| | 29636 (2)| 00:06:55 | | | Q1,02 | PCWC | |
| 17 | TABLE ACCESS FULL | NIW_SUB_STATUS_HIST_ODS | 55M| 1789M| | 29636 (2)| 00:06:55 | | | Q1,02 | PCWP | |
| 18 | PX RECEIVE | | 197M| 34G| | 181K (3)| 00:42:18 | | | Q1,04 | PCWP | |
| 19 | PX SEND HASH | :TQ10003 | 197M| 34G| | 181K (3)| 00:42:18 | | | Q1,03 | P->P | HASH |
| 20 | PX JOIN FILTER USE | :BF0000 | 197M| 34G| | 181K (3)| 00:42:18 | | | Q1,03 | PCWP | |
|* 21 | VIEW | | 197M| 34G| | 181K (3)| 00:42:18 | | | Q1,03 | PCWP | |
| 22 | WINDOW SORT | | 197M| 12G| 39G| 181K (3)| 00:42:18 | | | Q1,03 | PCWP | |
| 23 | PX RECEIVE | | 197M| 12G| | 40161 (4)| 00:09:23 | | | Q1,03 | PCWP | |
| 24 | PX SEND HASH | :TQ10001 | 197M| 12G| | 40161 (4)| 00:09:23 | | | Q1,01 | P->P | HASH |
| 25 | PX BLOCK ITERATOR | | 197M| 12G| | 40161 (4)| 00:09:23 | 1 | 835 | Q1,01 | PCWC | |
|* 26 | TABLE ACCESS FULL| NIW_SUB_CYC_USG_SUM_HIST_RP | 197M| 12G| | 40161 (4)| 00:09:23 | 1 | 835 | Q1,01 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
|
|
|
Goto Forum:
Current Time: Sat May 04 15:52:52 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
