Home » RDBMS Server » Performance Tuning » need suggestions to optimize the query (oracle 10.2.0.3 solaris 10)
need suggestions to optimize the query [message #342183] |
Thu, 21 August 2008 11:29 |
kumar.joy
Messages: 69 Registered: August 2007 Location: morrisville
|
Member |
|
|
Experts,
i need help to optimize this query. can any one help me. every thing is going on index scan still it is taking long time.
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_is95(last_delete_date),
to_is95(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 license,license_key,license_control,subscription,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 nvl(license_key.hwid, '-1') = :3
and sidmap.sid = :4
--- execution plan
Plan hash value: 3150375794
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 19 (100)| | | |
| 1 | NESTED LOOPS | | 1 | 510 | 19 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 351 | 18 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 286 | 17 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1 | 266 | 16 (0)| 00:00:01 | | |
| 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| SIDMAP | 1 | 61 | 1 (0)| 00:00:01 | ROW L | ROW L |
| 6 | INDEX RANGE SCAN | ACTIVESID | 1 | | 1 (0)| 00:00:01 | | |
| 7 | PARTITION HASH ALL | | 1 | 205 | 15 (0)| 00:00:01 | 1 | 32 |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| LICENSE_KEY | 1 | 205 | 15 (0)| 00:00:01 | 1 | 32 |
| 9 | INDEX RANGE SCAN | LICENSE_KEY_IDX1 | 1 | | 15 (0)| 00:00:01 | 1 | 32 |
| 10 | TABLE ACCESS BY GLOBAL INDEX ROWID | LICENSE | 1 | 20 | 1 (0)| 00:00:01 | ROW L | ROW L |
| 11 | INDEX UNIQUE SCAN | LICENSE_PK | 1 | | 1 (0)| 00:00:01 | | |
| 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | LICENSE_CONTROL | 1 | 65 | 1 (0)| 00:00:01 | ROW L | ROW L |
| 13 | INDEX UNIQUE SCAN | LICENSE_CONTROL_PK | 1 | | 1 (0)| 00:00:01 | | |
| 14 | TABLE ACCESS BY GLOBAL INDEX ROWID | SUBSCRIPTION | 1 | 159 | 1 (0)| 00:00:01 | ROW L | ROW L |
| 15 | INDEX UNIQUE SCAN | SUBSCRIPTION_PK | 1 | | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------
|
|
|
|
|
Re: need suggestions to optimize the query [message #342752 is a reply to message #342183] |
Sun, 24 August 2008 21:29 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
well, please go back and get some actual timings and expected timings for us.
Also, please format your code before you post it. There is a code formatter on the home page of ORAFAQ. Down the left hand column. Please use it.
Kevin
|
|
|
Goto Forum:
Current Time: Tue Dec 03 15:27:47 CST 2024
|