Home » RDBMS Server » Performance Tuning » need assistance to tune the query (solarin 5.10, oracle 10.2.0.3)
need assistance to tune the query [message #336066] |
Thu, 24 July 2008 11:56 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kumar.joy
Messages: 69 Registered: August 2007 Location: morrisville
|
Member |
|
|
Experts,
i need assistance to tune the following query. i am seeing full table scan on one table which has 40 million records. is there any way to avoid the full table scan.
please find the query and execution plan.
SELECT pc_name,
sid,
pgid,
bgid,
cgid,
resetgroups,
customer_type,
license_key.lid,
license_key.hwid,
license_key.pseudo_id,
license_key.type,
license_key.id,
license_key.platformid,
manufacturer,
map_type,
status_code,
license_key.pc_partno,
license_key.mfg_partno,
license_key.short_description,
license_key.long_description,
license_key.vendor_data,
license_key.carrierid,
license_key.adsid,
license_key.item_list_name,
license_key.price_version,
initial_download_eventid,
last_download_eventid,
last_delete_eventid,
last_ta_eventid,
to_char(last_delete_date),
to_char(last_download_date),
last_upgrade_eventid,
last_transfer_eventid,
last_deactivate_eventid,
bill_once,
method,
basis,
value,
eventid,
to_char(startdate, 'MM/DD/YYYY'),
to_char(enddate, 'MM/DD/YYYY'),
price,
dap,
price_currency,
dap_currency,
prepay_flag,
ext_count,
to_char(billeddate, 'dd-mon-yyyy hh24:mi:ss'),
to_char((sysdate + ( local_time - gmt )), 'dd-mon-yyyy hh24:mi:ss'),
to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss'),
price_type,
node_id,
node_name
FROM
cdsvzw.license license,
cdsvzw.license_key license_key,
cdsvzw.license_control license_control,
cdsvzw.subscription subscription,
cdsvzw.sidmap sidmap
where sidmap.pc_name = :1
and sidmap.pseudo_id = license_key.pseudo_id
and sidmap.code >= 0 and license.method = 3
and license_key.status_code >= 0
and license_key.type = :2
and license.lid = license_key.lid
and license.lid = license_control.lid
and license.lid = subscription.lid
and nvl(license_control.bill_once, 'N') = 'N'
and to_char(subscription.billeddate, 'YYYYM M') < to_char(sysdate, 'YYYYMM')
and to_char(subscription.startdate, 'YYYYMM') < to_char(sysdate, 'YYYYMM')
and trunc(nvl(subscription.enddate, sysdate + 2)) > trunc(sysdate)
and nvl(subscription.eventid, -1) > 0
and to_number(to_char(subscription.startdate, 'DD')) = 24
and mod(subscription.lid, :3) = :4
and prepay_flag = :5
[B]execution plan [/B]
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 562 | 189K (3)| 00:37:53 | | |
| 1 | NESTED LOOPS | | 1 | 562 | 189K (3)| 00:37:53 | | |
| 2 | NESTED LOOPS | | 1 | 456 | 189K (3)| 00:37:53 | | |
| 3 | NESTED LOOPS | | 1 | 248 | 189K (3)| 00:37:53 | | |
| 4 | NESTED LOOPS | | 1 | 183 | 189K (3)| 00:37:53 | | |
| 5 | PARTITION HASH ALL | | 1 | 163 | 189K (3)| 00:37:53 | 1 | 32 |
|* 6 | TABLE ACCESS FULL | SUBSCRIPTION | 1 | 163 | 189K (3)| 00:37:53 | 1 | 32 |
|* 7 | TABLE ACCESS BY GLOBAL INDEX ROWID| LICENSE | 1 | 20 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 8 | INDEX UNIQUE SCAN | LICENSE_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY GLOBAL INDEX ROWID | LICENSE_CONTROL | 1 | 65 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 10 | INDEX UNIQUE SCAN | LICENSE_CONTROL_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 11 | TABLE ACCESS BY GLOBAL INDEX ROWID | LICENSE_KEY | 1 | 208 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 12 | INDEX UNIQUE SCAN | LICENSE_KEY_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 13 | TABLE ACCESS BY GLOBAL INDEX ROWID | SIDMAP | 1 | 106 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 14 | INDEX UNIQUE SCAN | SIDMAP_PK | 1 | | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------
Regards,
Jyothy...
[Updated on: Thu, 24 July 2008 11:57] Report message to a moderator
|
|
|
|
|
Re: need assistance to tune the query [message #336072 is a reply to message #336066] |
Thu, 24 July 2008 12:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
These functions preclude index use
and to_char(subscription.billeddate, 'YYYYM M') < to_char(sysdate, 'YYYYMM')
and to_char(subscription.startdate, 'YYYYMM') < to_char(sysdate, 'YYYYMM')
and trunc(nvl(subscription.enddate, sysdate + 2)) > trunc(sysdate)
and nvl(subscription.eventid, -1) > 0
and to_number(to_char(subscription.startdate, 'DD')) = 24
and mod(subscription.lid, :3) = :4
IMO, without a re-design of table, a FTS of subscription table is required.
|
|
|
|
Re: need assistance to tune the query [message #336076 is a reply to message #336073] |
Thu, 24 July 2008 13:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kumar.joy
Messages: 69 Registered: August 2007 Location: morrisville
|
Member |
|
|
please find the full plan.
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(NVL("SUBSCRIPTION"."EVENTID",(-1))>0 AND MOD("SUBSCRIPTION"."LID",TO_NUMBER(:3))=TO_NUMBER(:4) AND
TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("SUBSCRIPTION"."STARTDATE"),'DD'))=24 AND
TO_CHAR(INTERNAL_FUNCTION("SUBSCRIPTION"."STARTDATE"),'YYYYMM')<TO_CHAR(SYSDATE@!,'YYYYMM') AND
TO_CHAR(INTERNAL_FUNCTION("SUBSCRIPTION"."BILLEDDATE"),'YYYYM M')<TO_CHAR(SYSDATE@!,'YYYYMM') AND
TRUNC(NVL("SUBSCRIPTION"."ENDDATE",SYSDATE@!+2))>TRUNC(SYSDATE@!) AND "PREPAY_FLAG"=:5)
7 - filter(TO_NUMBER("LICENSE"."METHOD")=3)
8 - access("LICENSE"."LID"="SUBSCRIPTION"."LID")
9 - filter(NVL("LICENSE_CONTROL"."BILL_ONCE",'N')='N')
10 - access("LICENSE"."LID"="LICENSE_CONTROL"."LID")
11 - filter("LICENSE_KEY"."STATUS_CODE">=0 AND "LICENSE_KEY"."TYPE"=TO_NUMBER(:2))
12 - access("LICENSE"."LID"="LICENSE_KEY"."LID")
13 - filter("SIDMAP"."CODE">=0 AND "SIDMAP"."PC_NAME"=:1)
14 - access("SIDMAP"."PSEUDO_ID"="LICENSE_KEY"."PSEUDO_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "SUBSCRIPTION"."EVENTID"[NUMBER,22], "SUBSCRIPTION"."STARTDATE"[TIMESTAMP,11],
"SUBSCRIPTION"."ENDDATE"[TIMESTAMP,11], "SUBSCRIPTION"."BILLEDDATE"[TIMESTAMP,11], "PRICE"[NUMBER,22],
"DAP"[NUMBER,22], "PRICE_CURRENCY"[VARCHAR2,9], "DAP_CURRENCY"[VARCHAR2,9], "PREPAY_FLAG"[VARCHAR2,3],
"EXT_COUNT"[NUMBER,22], "LOCAL_TIME"[TIMESTAMP,11], "GMT"[TIMESTAMP,11], "PRICE_TYPE"[NUMBER,22],
"NODE_ID"[NUMBER,22], "NODE_NAME"[NVARCHAR2,2000], "LICENSE"."METHOD"[VARCHAR2,30], "BASIS"[VARCHAR2,3],
"VALUE"[VARCHAR2,720], "INITIAL_DOWNLOAD_EVENTID"[NUMBER,22], "LAST_UPGRADE_EVENTID"[NUMBER,22],
"LAST_DOWNLOAD_EVENTID"[NUMBER,22], "LAST_DELETE_EVENTID"[NUMBER,22], "LAST_TA_EVENTID"[NUMBER,22],
"LAST_TRANSFER_EVENTID"[NUMBER,22], "LAST_DEACTIVATE_EVENTID"[NUMBER,22], "LICENSE_CONTROL"."BILL_ONCE"[VARCHAR2,
3],
"LAST_DELETE_DATE"[TIMESTAMP,11], "LAST_DOWNLOAD_DATE"[TIMESTAMP,11], "LICENSE_KEY"."LID"[NUMBER,22],
"LICENSE_KEY"."HWID"[VARCHAR2,720], "LICENSE_KEY"."PSEUDO_ID"[NUMBER,22], "LICENSE_KEY"."ID"[VARCHAR2,720],
"MANUFACTURER"[VARCHAR2,720], "MAP_TYPE"[VARCHAR2,30], "LICENSE_KEY"."STATUS_CODE"[NUMBER,22],
"LICENSE_KEY"."TYPE"[NUMBER,22], "LICENSE_KEY"."PLATFORMID"[NUMBER,22], "LICENSE_KEY"."PC_PARTNO"[VARCHAR2,720],
"LICENSE_KEY"."MFG_PARTNO"[VARCHAR2,720], "LICENSE_KEY"."SHORT_DESCRIPTION"[VARCHAR2,720],
"LICENSE_KEY"."LONG_DESCRIPTION"[VARCHAR2,4000], "LICENSE_KEY"."VENDOR_DATA"[VARCHAR2,4000],
"LICENSE_KEY"."ITEM_LIST_NAME"[NVARCHAR2,4000], "LICENSE_KEY"."CARRIERID"[NUMBER,22],
"LICENSE_KEY"."ADSID"[NUMBER,22], "LICENSE_KEY"."PRICE_VERSION"[NUMBER,22], "SIDMAP"."PC_NAME"[VARCHAR2,90],
"SID"[VARCHAR2,720], "PGID"[NVARCHAR2,480], "BGID"[NVARCHAR2,480], "CGID"[NVARCHAR2,480], "RESETGROUPS"[VARCHAR2,
3],
"CUSTOMER_TYPE"[VARCHAR2,30]
2 - (#keys=0) "SUBSCRIPTION"."EVENTID"[NUMBER,22], "SUBSCRIPTION"."STARTDATE"[TIMESTAMP,11],
"SUBSCRIPTION"."ENDDATE"[TIMESTAMP,11], "SUBSCRIPTION"."BILLEDDATE"[TIMESTAMP,11], "PRICE"[NUMBER,22],
"DAP"[NUMBER,22], "PRICE_CURRENCY"[VARCHAR2,9], "DAP_CURRENCY"[VARCHAR2,9], "PREPAY_FLAG"[VARCHAR2,3],
"EXT_COUNT"[NUMBER,22], "LOCAL_TIME"[TIMESTAMP,11], "GMT"[TIMESTAMP,11], "PRICE_TYPE"[NUMBER,22],
"NODE_ID"[NUMBER,22], "NODE_NAME"[NVARCHAR2,2000], "LICENSE"."METHOD"[VARCHAR2,30], "BASIS"[VARCHAR2,3],
"VALUE"[VARCHAR2,720], "INITIAL_DOWNLOAD_EVENTID"[NUMBER,22], "LAST_UPGRADE_EVENTID"[NUMBER,22],
"LAST_DOWNLOAD_EVENTID"[NUMBER,22], "LAST_DELETE_EVENTID"[NUMBER,22], "LAST_TA_EVENTID"[NUMBER,22],
"LAST_TRANSFER_EVENTID"[NUMBER,22], "LAST_DEACTIVATE_EVENTID"[NUMBER,22], "LICENSE_CONTROL"."BILL_ONCE"[VARCHAR2,
3],
"LAST_DELETE_DATE"[TIMESTAMP,11], "LAST_DOWNLOAD_DATE"[TIMESTAMP,11], "LICENSE_KEY"."LID"[NUMBER,22],
"LICENSE_KEY"."HWID"[VARCHAR2,720], "LICENSE_KEY"."PSEUDO_ID"[NUMBER,22], "LICENSE_KEY"."ID"[VARCHAR2,720],
"MANUFACTURER"[VARCHAR2,720], "MAP_TYPE"[VARCHAR2,30], "LICENSE_KEY"."STATUS_CODE"[NUMBER,22],
"LICENSE_KEY"."TYPE"[NUMBER,22], "LICENSE_KEY"."PLATFORMID"[NUMBER,22], "LICENSE_KEY"."PC_PARTNO"[VARCHAR2,720],
"LICENSE_KEY"."MFG_PARTNO"[VARCHAR2,720], "LICENSE_KEY"."SHORT_DESCRIPTION"[VARCHAR2,720],
"LICENSE_KEY"."LONG_DESCRIPTION"[VARCHAR2,4000], "LICENSE_KEY"."VENDOR_DATA"[VARCHAR2,4000],
"LICENSE_KEY"."ITEM_LIST_NAME"[NVARCHAR2,4000], "LICENSE_KEY"."CARRIERID"[NUMBER,22],
"LICENSE_KEY"."ADSID"[NUMBER,22], "LICENSE_KEY"."PRICE_VERSION"[NUMBER,22]
3 - (#keys=0) "SUBSCRIPTION"."EVENTID"[NUMBER,22], "SUBSCRIPTION"."STARTDATE"[TIMESTAMP,11],
"SUBSCRIPTION"."ENDDATE"[TIMESTAMP,11], "SUBSCRIPTION"."BILLEDDATE"[TIMESTAMP,11], "PRICE"[NUMBER,22],
"DAP"[NUMBER,22], "PRICE_CURRENCY"[VARCHAR2,9], "DAP_CURRENCY"[VARCHAR2,9], "PREPAY_FLAG"[VARCHAR2,3],
"EXT_COUNT"[NUMBER,22], "LOCAL_TIME"[TIMESTAMP,11], "GMT"[TIMESTAMP,11], "PRICE_TYPE"[NUMBER,22],
"NODE_ID"[NUMBER,22], "NODE_NAME"[NVARCHAR2,2000], "LICENSE"."LID"[NUMBER,22], "LICENSE"."METHOD"[VARCHAR2,30],
"BASIS"[VARCHAR2,3], "VALUE"[VARCHAR2,720], "INITIAL_DOWNLOAD_EVENTID"[NUMBER,22],
"LAST_UPGRADE_EVENTID"[NUMBER,22], "LAST_DOWNLOAD_EVENTID"[NUMBER,22], "LAST_DELETE_EVENTID"[NUMBER,22],
"LAST_TA_EVENTID"[NUMBER,22], "LAST_TRANSFER_EVENTID"[NUMBER,22], "LAST_DEACTIVATE_EVENTID"[NUMBER,22],
"LICENSE_CONTROL"."BILL_ONCE"[VARCHAR2,3], "LAST_DELETE_DATE"[TIMESTAMP,11], "LAST_DOWNLOAD_DATE"[TIMESTAMP,11]
4 - (#keys=0) "SUBSCRIPTION"."EVENTID"[NUMBER,22], "SUBSCRIPTION"."STARTDATE"[TIMESTAMP,11],
"SUBSCRIPTION"."ENDDATE"[TIMESTAMP,11], "SUBSCRIPTION"."BILLEDDATE"[TIMESTAMP,11], "PRICE"[NUMBER,22],
"DAP"[NUMBER,22], "PRICE_CURRENCY"[VARCHAR2,9], "DAP_CURRENCY"[VARCHAR2,9], "PREPAY_FLAG"[VARCHAR2,3],
"EXT_COUNT"[NUMBER,22], "LOCAL_TIME"[TIMESTAMP,11], "GMT"[TIMESTAMP,11], "PRICE_TYPE"[NUMBER,22],
"NODE_ID"[NUMBER,22], "NODE_NAME"[NVARCHAR2,2000], "LICENSE"."LID"[NUMBER,22], "LICENSE"."METHOD"[VARCHAR2,30],
"BASIS"[VARCHAR2,3], "VALUE"[VARCHAR2,720]
5 - "SUBSCRIPTION"."LID"[NUMBER,22], "SUBSCRIPTION"."EVENTID"[NUMBER,22],
"SUBSCRIPTION"."STARTDATE"[TIMESTAMP,11], "SUBSCRIPTION"."ENDDATE"[TIMESTAMP,11],
"SUBSCRIPTION"."BILLEDDATE"[TIMESTAMP,11], "PRICE"[NUMBER,22], "DAP"[NUMBER,22], "PRICE_CURRENCY"[VARCHAR2,9],
"DAP_CURRENCY"[VARCHAR2,9], "PREPAY_FLAG"[VARCHAR2,3], "EXT_COUNT"[NUMBER,22], "LOCAL_TIME"[TIMESTAMP,11],
"GMT"[TIMESTAMP,11], "PRICE_TYPE"[NUMBER,22], "NODE_ID"[NUMBER,22], "NODE_NAME"[NVARCHAR2,2000]
6 - "SUBSCRIPTION"."LID"[NUMBER,22], "SUBSCRIPTION"."EVENTID"[NUMBER,22],
"SUBSCRIPTION"."STARTDATE"[TIMESTAMP,11], "SUBSCRIPTION"."ENDDATE"[TIMESTAMP,11],
"SUBSCRIPTION"."BILLEDDATE"[TIMESTAMP,11], "PRICE"[NUMBER,22], "DAP"[NUMBER,22], "PRICE_CURRENCY"[VARCHAR2,9],
"DAP_CURRENCY"[VARCHAR2,9], "PREPAY_FLAG"[VARCHAR2,3], "EXT_COUNT"[NUMBER,22], "LOCAL_TIME"[TIMESTAMP,11],
"GMT"[TIMESTAMP,11], "PRICE_TYPE"[NUMBER,22], "NODE_ID"[NUMBER,22], "NODE_NAME"[NVARCHAR2,2000]
7 - "LICENSE"."LID"[NUMBER,22], "LICENSE"."METHOD"[VARCHAR2,30], "BASIS"[VARCHAR2,3], "VALUE"[VARCHAR2,720]
8 - "LICENSE".ROWID[ROWID,10], "LICENSE"."LID"[NUMBER,22]
9 - "INITIAL_DOWNLOAD_EVENTID"[NUMBER,22], "LAST_UPGRADE_EVENTID"[NUMBER,22], "LAST_DOWNLOAD_EVENTID"[NUMBER,22],
"LAST_DELETE_EVENTID"[NUMBER,22], "LAST_TA_EVENTID"[NUMBER,22], "LAST_TRANSFER_EVENTID"[NUMBER,22],
"LAST_DEACTIVATE_EVENTID"[NUMBER,22], "LICENSE_CONTROL"."BILL_ONCE"[VARCHAR2,3], "LAST_DELETE_DATE"[TIMESTAMP,11]
,
"LAST_DOWNLOAD_DATE"[TIMESTAMP,11]
10 - "LICENSE_CONTROL".ROWID[ROWID,10]
11 - "LICENSE_KEY"."LID"[NUMBER,22], "LICENSE_KEY"."HWID"[VARCHAR2,720], "LICENSE_KEY"."PSEUDO_ID"[NUMBER,22],
"LICENSE_KEY"."ID"[VARCHAR2,720], "MANUFACTURER"[VARCHAR2,720], "MAP_TYPE"[VARCHAR2,30],
"LICENSE_KEY"."STATUS_CODE"[NUMBER,22], "LICENSE_KEY"."TYPE"[NUMBER,22], "LICENSE_KEY"."PLATFORMID"[NUMBER,22],
"LICENSE_KEY"."PC_PARTNO"[VARCHAR2,720], "LICENSE_KEY"."MFG_PARTNO"[VARCHAR2,720],
"LICENSE_KEY"."SHORT_DESCRIPTION"[VARCHAR2,720], "LICENSE_KEY"."LONG_DESCRIPTION"[VARCHAR2,4000],
"LICENSE_KEY"."VENDOR_DATA"[VARCHAR2,4000], "LICENSE_KEY"."ITEM_LIST_NAME"[NVARCHAR2,4000],
"LICENSE_KEY"."CARRIERID"[NUMBER,22], "LICENSE_KEY"."ADSID"[NUMBER,22], "LICENSE_KEY"."PRICE_VERSION"[NUMBER,22]
12 - "LICENSE_KEY".ROWID[ROWID,10], "LICENSE_KEY"."LID"[NUMBER,22]
13 - "SIDMAP"."PC_NAME"[VARCHAR2,90], "SID"[VARCHAR2,720], "PGID"[NVARCHAR2,480], "BGID"[NVARCHAR2,480],
"CGID"[NVARCHAR2,480], "RESETGROUPS"[VARCHAR2,3], "CUSTOMER_TYPE"[VARCHAR2,30]
14 - "SIDMAP".ROWID[ROWID,10]
|
|
|
|
|
Re: need assistance to tune the query [message #336120 is a reply to message #336094] |
Thu, 24 July 2008 22:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
anacedent wrote on Fri, 25 July 2008 05:48 | So unless & until you can get the optimizer to use 5 indexes against table subscription, a FTS will be required.
|
I think I understand what this means, but it is terribly misleading.
Unless you can get the optimiser to scan indexes on all of the predicates, then redundant table reads are unavoidable. Those reads will not be a FTS, they will be TABLE ACCESS BY INDEX ROWID (which is often worse!).
But this may not be a bad thing. If scanning on just ONE of those predicates is highly selective, then including others in the index(es) may not add much value.
This one looks non-selective. I guess most rows will predate SYSDATE. I would not bother changing it.and to_char(subscription.billeddate, 'YYYYM M') < to_char(sysdate, 'YYYYMM')
Same hereand to_char(subscription.startdate, 'YYYYMM') < to_char(sysdate, 'YYYYMM')
There may be some value in indexing this oneand trunc(nvl(subscription.enddate, sysdate + 2)) > trunc(sysdate) This is functionally equivalent to "endate after today - no NULLs". Try rewriting it as
and subscription.enddate >= trunc(sysdate) + 1 and index subscription.enddate.
Rewrite the followingand nvl(subscription.eventid, -1) > 0 to but it doesn't sound very selective to me, so I wouldn't index it.
This is a strange oneand to_number(to_char(subscription.startdate, 'DD')) = 24 Why would you only be interested in subscriptions starting on the 24th of ANY month? If this is legitimate, you could look into a function-based index.
and mod(subscription.lid, :3) = :4 Don't know how selective this would be, but you cannot index it because of the variable modulus in the MOD().
In summary, you could try indexing:
subscription( to_number(to_char(subscription.startdate, 'DD')), enddate)
Ross Leishman
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:08:48 CDT 2024
|