Home » SQL & PL/SQL » SQL & PL/SQL » Query Rewriting - Assistance Needed (Oracle 11.2.0.4, Solaris 10 1/13)
| Query Rewriting - Assistance Needed [message #649732] |
Tue, 05 April 2016 00:31  |
 |
suddhasatwa_oracle
Messages: 24
Registered: January 2014
|
Junior Member |
|
|
Hello All,
I have a requirement wherein from my transactions database, I would need to print some columns from 2/3 tables related to a particular package which is being sold by the organisation. To do so, however, we need to take the max or latest transaction ID or latest transaction date for calculations.
To write this, I have written the below query (table names changed intentionally), which is looking at the data only for one package for last 90 days. However, it is still posing much slowness, so I believe I have not written it well.
Kindly advise on how this can be made better in terms of execution.
Regards,
Suddhasatwa
SELECT
/*+ index(trans idx6_rpt_transaction_status)
index(trans idx3_rpt_transaction) index(sub idx3_rpt_subscription)
index(sub idx4_rpt_subscription) */
SUB.PACKAGE_ID,
TRANS.USER_GROUP,
TRANS.AMOUNT,
TRANS.PROMO_CODE,
SUB.STATUS_OBJ_ID,
COUNT(DISTINCT ACC.ACCOUNT_OBJ_ID) AS DISTINCT_USERS
FROM TRANSACTION_TABLE TRANS,
SUBSCRIPTION_TABLE SUB,
ACCOUNT_TABLE ACC,
COUNTRY_TABLE country_main
WHERE ( SUB.COUNTRY_CODE =ACC.COUNTRY_CODE
AND SUB.SYSTEM_NAME =ACC.SYSTEM_NAME
AND SUB.ACCOUNT_OBJ_ID =ACC.ACCOUNT_OBJ_ID )
AND ( TRANS.COUNTRY_CODE =SUB.COUNTRY_CODE
AND TRANS.SYSTEM_NAME =SUB.SYSTEM_NAME
AND TRANS.SUBSCRIPTION_OBJ_ID =SUB.SUBSCRIPTION_OBJ_ID )
AND ( country_main.COUNTRY_CODE =ACC.COUNTRY_CODE )
AND ( SUB.STATUS_OBJ_ID IN (1,4))
AND country_main.COUNTRY_CODE = 2
AND SUB.PACKAGE_ID = 'p523tvpre1_W'
AND TRANS.AMOUNT <> 1.49
AND SUBSTR(country_main.ISO_COUNTRY,0,2) = 'DE'
AND TRANS.TRANSACTION_TYPE_OBJ_ID IN ('2','4','5')
AND TRANS.resource_obj_id <> 6
AND TRANS.PROMO_CODE IN ('TRIAL','*')
AND TRANS.USER_GROUP = '*'
AND TRANS.AMOUNT IN (0,0.99)
AND TRANS.PAYMENT_TRANS_OBJ_ID IN
(SELECT
/*+ index(tra idx6_rpt_transaction_status) index(tra idx3_rpt_transaction)
index(subs idx3_rpt_subscription) index(subs idx4_rpt_subscription) */
MAX(TRA.PAYMENT_TRANS_OBJ_ID)
FROM TRANSACTION_TABLE TRA,
SUBSCRIPTION_TABLE SUBS,
ACCOUNT_TABLE AC
WHERE SUBS.COUNTRY_CODE =AC.COUNTRY_CODE
AND SUBS.SYSTEM_NAME =AC.SYSTEM_NAME
AND SUBS.ACCOUNT_OBJ_ID =AC.ACCOUNT_OBJ_ID
AND TRA.COUNTRY_CODE =SUBS.COUNTRY_CODE
AND TRA.SYSTEM_NAME =SUBS.SYSTEM_NAME
AND TRA.SUBSCRIPTION_OBJ_ID =SUBS.SUBSCRIPTION_OBJ_ID
--AND SUBS.PACKAGE_ID = 'p523tvpre1_W'
AND AC.ACCOUNT_OBJ_ID = ACC.ACCOUNT_OBJ_ID
--AND SUBS.STATUS_OBJ_ID IN (1,4)
--AND TRANS.AMOUNT <> 1.49
--AND TRA.COUNTRY_CODE = 2
--AND TRA.TRANSACTION_TYPE_OBJ_ID IN ('2','4','5')
--AND TRA.resource_obj_id <> 6
--AND TRA.PROMO_CODE IN ('TRIAL','*')
--AND TRA.USER_GROUP = '*'
--AND TRA.AMOUNT IN (0,0.99)
--AND TRA.CREATED_DATE < sysdate - 30
AND TRA.ER_CREATED_DATE > sysdate - 60
GROUP BY AC.ACCOUNT_OBJ_ID
)
-- AND TRANS.ER_CREATED_DATE > sysdate - 90
GROUP BY SUB.PACKAGE_ID,
TRANS.USER_GROUP,
TRANS.AMOUNT,
TRANS.PROMO_CODE,
SUB.STATUS_OBJ_ID ;
|
|
|
|
|
|
|
|
| Re: Query Rewriting - Assistance Needed [message #649744 is a reply to message #649738] |
Tue, 05 April 2016 02:38  |
|
suddhasatwa_oracle
Messages: 24
Registered: January 2014
|
Junior Member |
|
|
I have revised the query as under :
WITH max_transaction_id AS
(SELECT
MAX(TRA.PAYMENT_TRANS_OBJ_ID) AS PAYMENT_TRANS_OBJ_ID
FROM TRANSACTION_TABLE TRA,
SUBSCRIPTION_TABLE SUBS,
RS_RPT_ACCOUNT AC
WHERE SUBS.COUNTRY_CODE =AC.COUNTRY_CODE
AND SUBS.SYSTEM_NAME =AC.SYSTEM_NAME
AND SUBS.ACCOUNT_OBJ_ID =AC.ACCOUNT_OBJ_ID
AND TRA.COUNTRY_CODE =SUBS.COUNTRY_CODE
AND TRA.SYSTEM_NAME =SUBS.SYSTEM_NAME
AND TRA.SUBSCRIPTION_OBJ_ID =SUBS.SUBSCRIPTION_OBJ_ID
AND SUBS.PACKAGE_ID = 'p523tvpre1_W'
AND SUBS.STATUS_OBJ_ID IN (1,4)
AND TRA.AMOUNT <> 1.49
AND TRA.COUNTRY_CODE = 2
AND TRA.TRANSACTION_TYPE_OBJ_ID IN ('2','4','5')
AND TRA.resource_obj_id <> 6
AND TRA.PROMO_CODE IN ('TRIAL','*')
AND TRA.USER_GROUP = '*'
AND TRA.AMOUNT IN (0,0.99)
AND TRA.ER_CREATED_DATE > sysdate - 60
GROUP BY AC.ACCOUNT_OBJ_ID
)
SELECT
SUB.PACKAGE_ID,
TRANS.USER_GROUP,
TRANS.AMOUNT,
TRANS.PROMO_CODE,
SUB.STATUS_OBJ_ID,
COUNT(DISTINCT ACC.ACCOUNT_OBJ_ID) AS DISTINCT_USERS
FROM TRANSACTION_TABLE TRANS,
SUBSCRIPTION_TABLE SUB,
RS_RPT_ACCOUNT ACC,
COUNTRY_TABLE country_main
WHERE ( SUB.COUNTRY_CODE =ACC.COUNTRY_CODE
AND SUB.SYSTEM_NAME =ACC.SYSTEM_NAME
AND SUB.ACCOUNT_OBJ_ID =ACC.ACCOUNT_OBJ_ID )
AND ( TRANS.COUNTRY_CODE =SUB.COUNTRY_CODE
AND TRANS.SYSTEM_NAME =SUB.SYSTEM_NAME
AND TRANS.SUBSCRIPTION_OBJ_ID =SUB.SUBSCRIPTION_OBJ_ID )
AND ( country_main.COUNTRY_CODE =ACC.COUNTRY_CODE )
AND ( SUB.STATUS_OBJ_ID IN (1,4))
AND country_main.COUNTRY_CODE = 2
--AND SUB.PACKAGE_ID = 'p523tvpre1_W'
AND TRANS.AMOUNT <> 1.49
AND SUBSTR(country_main.ISO_COUNTRY,0,2) = 'DE'
AND TRANS.TRANSACTION_TYPE_OBJ_ID IN ('2','4','5')
AND TRANS.resource_obj_id <> 6
AND TRANS.PROMO_CODE IN ('TRIAL','*')
AND TRANS.USER_GROUP = '*'
AND TRANS.AMOUNT IN (0,0.99)
AND TRANS.PAYMENT_TRANS_OBJ_ID IN
(SELECT DISTINCT PAYMENT_TRANS_OBJ_ID
FROM max_transaction_id
)
AND TRANS.ER_CREATED_DATE > sysdate - 90
GROUP BY SUB.PACKAGE_ID,
TRANS.USER_GROUP,
TRANS.AMOUNT,
TRANS.PROMO_CODE,
SUB.STATUS_OBJ_ID ;
The explain plan is as under:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2423280418
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 13 (16)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 1 | 40 | 13 (16)| 00:00:01 | | |
| 2 | VIEW | VM_NWVW_2 | 1 | 40 | 13 (16)| 00:00:01 | | |
| 3 | HASH GROUP BY | | 1 | 121 | 13 (16)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1 | 121 | 12 (9)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 1 | 107 | 12 (9)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1 | 70 | 10 (10)| 00:00:01 | | |
| 7 | NESTED LOOPS | | 1 | 27 | 7 (15)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | IX_RS_COUNTRY_03 | 1 | 14 | 1 (0)| 00:00:01 | | |
| 9 | VIEW | VW_NSO_1 | 1 | 13 | 6 (17)| 00:00:01 | | |
| 10 | SORT UNIQUE | | 1 | 94 | 6 (17)| 00:00:01 | | |
| 11 | SORT GROUP BY | | 1 | 94 | 6 (17)| 00:00:01 | | |
| 12 | NESTED LOOPS | | 1 | 94 | 5 (0)| 00:00:01 | | |
| 13 | NESTED LOOPS | | 1 | 80 | 5 (0)| 00:00:01 | | |
| 14 | PARTITION RANGE ITERATOR | | 1 | 43 | 3 (0)| 00:00:01 | KEY | 113 |
|* 15 | TABLE ACCESS BY LOCAL INDEX ROWID| RS_RPT_TRANSACTION | 1 | 43 | 3 (0)| 00:00:01 | KEY | 113 |
|* 16 | INDEX RANGE SCAN | IX_RS_RPT_TRANSACTION_03 | 1 | | 2 (0)| 00:00:01 | KEY | 113 |
|* 17 | TABLE ACCESS BY INDEX ROWID | RS_RPT_SUBSCRIPTION | 1 | 37 | 2 (0)| 00:00:01 | | |
|* 18 | INDEX UNIQUE SCAN | PK_RPT_SUBSCRIPTION | 1 | | 1 (0)| 00:00:01 | | |
|* 19 | INDEX UNIQUE SCAN | PK_RPT_ACCOUNT | 1 | 14 | 0 (0)| 00:00:01 | | |
|* 20 | TABLE ACCESS BY GLOBAL INDEX ROWID | RS_RPT_TRANSACTION | 1 | 43 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 21 | INDEX RANGE SCAN | PK_RS_RPT_TRANSACTION | 1 | | 2 (0)| 00:00:01 | | |
|* 22 | TABLE ACCESS BY INDEX ROWID | RS_RPT_SUBSCRIPTION | 2 | 74 | 2 (0)| 00:00:01 | | |
|* 23 | INDEX UNIQUE SCAN | PK_RPT_SUBSCRIPTION | 1 | | 1 (0)| 00:00:01 | | |
|* 24 | INDEX UNIQUE SCAN | PK_RPT_ACCOUNT | 1 | 14 | 0 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("COUNTRY_MAIN"."COUNTRY_CODE"=2)
filter(SUBSTR("COUNTRY_MAIN"."ISO_COUNTRY",0,2)='DE')
15 - filter(("TRA"."AMOUNT"=0 OR "TRA"."AMOUNT"=0.99) AND "TRA"."RESOURCE_OBJ_ID"<>6 AND ("TRA"."TRANSACTION_TYPE_OBJ_ID"=2 OR
"TRA"."TRANSACTION_TYPE_OBJ_ID"=4 OR "TRA"."TRANSACTION_TYPE_OBJ_ID"=5) AND "TRA"."USER_GROUP"='*' AND ("TRA"."PROMO_CODE"='*' OR
"TRA"."PROMO_CODE"='TRIAL') AND "TRA"."AMOUNT"<>1.49)
16 - access("TRA"."ER_CREATED_DATE">SYSDATE@!-60 AND "TRA"."COUNTRY_CODE"=2 AND "TRA"."ER_CREATED_DATE" IS NOT NULL)
filter("TRA"."COUNTRY_CODE"=2)
17 - filter("SUBS"."PACKAGE_ID"='p523tvpre1_W' AND ("SUBS"."STATUS_OBJ_ID"=1 OR "SUBS"."STATUS_OBJ_ID"=4))
18 - access("SUBS"."COUNTRY_CODE"=2 AND "TRA"."SUBSCRIPTION_OBJ_ID"="SUBS"."SUBSCRIPTION_OBJ_ID" AND
"TRA"."SYSTEM_NAME"="SUBS"."SYSTEM_NAME")
19 - access("AC"."COUNTRY_CODE"=2 AND "SUBS"."ACCOUNT_OBJ_ID"="AC"."ACCOUNT_OBJ_ID" AND "SUBS"."SYSTEM_NAME"="AC"."SYSTEM_NAME")
20 - filter(("TRANS"."AMOUNT"=0 OR "TRANS"."AMOUNT"=0.99) AND "TRANS"."RESOURCE_OBJ_ID"<>6 AND
("TRANS"."TRANSACTION_TYPE_OBJ_ID"=2 OR "TRANS"."TRANSACTION_TYPE_OBJ_ID"=4 OR "TRANS"."TRANSACTION_TYPE_OBJ_ID"=5) AND
"TRANS"."ER_CREATED_DATE">SYSDATE@!-90 AND "TRANS"."USER_GROUP"='*' AND ("TRANS"."PROMO_CODE"='*' OR "TRANS"."PROMO_CODE"='TRIAL')
AND "TRANS"."AMOUNT"<>1.49)
21 - access("TRANS"."COUNTRY_CODE"=2 AND "TRANS"."PAYMENT_TRANS_OBJ_ID"="PAYMENT_TRANS_OBJ_ID")
22 - filter("SUB"."STATUS_OBJ_ID"=1 OR "SUB"."STATUS_OBJ_ID"=4)
23 - access("SUB"."COUNTRY_CODE"=2 AND "TRANS"."SUBSCRIPTION_OBJ_ID"="SUB"."SUBSCRIPTION_OBJ_ID" AND
"TRANS"."SYSTEM_NAME"="SUB"."SYSTEM_NAME")
24 - access("ACC"."COUNTRY_CODE"=2 AND "SUB"."ACCOUNT_OBJ_ID"="ACC"."ACCOUNT_OBJ_ID" AND "SUB"."SYSTEM_NAME"="ACC"."SYSTEM_NAME")
|
|
|
|
| Re: Query Rewriting - Assistance Needed [message #649745 is a reply to message #649744] |
Tue, 05 April 2016 02:58 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
More than likely your statistics are rubbish. Do a few checks. For example, how many rows does this filter at operation 15 return -
select count(*) from RS_RPT_TRANSACTION tra
where"TRA"."ER_CREATED_DATE">SYSDATE@!-60 AND "TRA"."COUNTRY_CODE"=2 AND "TRA"."ER_CREATED_DATE" IS NOT NULL;
Oracle thinks there is only one row.
--update, sorry, operation 16, not 15.
[Updated on: Tue, 05 April 2016 02:58] Report message to a moderator |
|
|
|
| Re: Query Rewriting - Assistance Needed [message #649752 is a reply to message #649745] |
Tue, 05 April 2016 04:30 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Did you generate that explain plan on the DB where the query is running slow or on a DEV box with no data?
It needs to be on the slow DB. If it was, then as John says the stats are rubbish and need to be regathered.
|
|
|
|
|
|
| Re: Query Rewriting - Assistance Needed [message #649772 is a reply to message #649771] |
Wed, 06 April 2016 01:23 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You need to discuss this further with your DBA. In your release of the database, statistics are (or should be) gathered by the autotask system, certainly not by DBMS_JOB. Perhaps he is looking in the wring place. And in the meantime, I would do this:
exec dbms_stats.gather_database_stats(estimate_percent=>100,method_opt=>'for all columns size skewonly')
You'll need to gather dictionary stats, fixed object stats, and system stats too. I'll leave you to work out the syntax for that.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 08:00:33 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
