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 Go to next message
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 #342199 is a reply to message #342183] Thu, 21 August 2008 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> it is taking long time.
> Time = 00:00:01
you must be living in a time warp.
Is 00:00:01 not fast enough for you?

[Updated on: Thu, 21 August 2008 12:08] by Moderator

Report message to a moderator

Re: need suggestions to optimize the query [message #342218 is a reply to message #342199] Thu, 21 August 2008 13:11 Go to previous messageGo to next message
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
Member
Thanks for reply,

i guess it is for bind values. i observed form EM console. it spend 72%of the db time


i am not getting actual values from my application team.
Re: need suggestions to optimize the query [message #342752 is a reply to message #342183] Sun, 24 August 2008 21:29 Go to previous message
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
Previous Topic: Are system procedures using Rule hint in Oracle 10g?
Next Topic: SQL Tuning Urgent
Goto Forum:
  


Current Time: Tue Dec 03 15:27:47 CST 2024