Home » RDBMS Server » Performance Tuning » Please guide how to tune the given query (merged)
Please guide how to tune the given query (merged) [message #242602] Mon, 04 June 2007 05:20 Go to next message
mr_dba01
Messages: 5
Registered: May 2007
Location: mumbai
Junior Member
Dear all,
Please guide how to tune the following queries:

Case (1)
SELECT SECURITY_NAME,
SECURITY_CCY,S.SECURITY_NOMINAL_VALUE,S.SECURITY_CODE,
SUM(HOLDINGS_BOOKCOSTPRICE * HOLDINGS_BOOKPOSITION) FACE_VALUE_SECURITY,
(SUM(HOLDINGS_BOOKPOSITION)*(WMS_FUN_SECURITY_PRICE(S.SECURITY_CODE, to_date({?pTO_DATE},WMS_DATE_FORMAT)))) MARKET_VALUE_OF_SECURITY,
(SUM(HOLDINGS_PLEDGED) * (WMS_FUN_SECURITY_PRICE(S.SECURITY_CODE,
to_date({?pTO_DATE},WMS_DATE_FORMAT)))) PLEDGE_VALUE_OF_SECURITY
FROM WMS_SECURITY S ,WMS_HOLDINGS H
WHERE S.SECURITY_CODE=H.SECURITY_CODE
H.HOLDINGS_ASON_DATE >=TO_DATE('01/01/2005','DD/MM/YYYY')
AND
AND HOLDINGS_PLEDGED>0
GROUP BY SECURITY_NAME,SECURITY_CCY,S.SECURITY_NOMINAL_VALUE,S.SECURITY_CODE

****************************************************************
Tables referenced in above query are
WMS_HOLDINGS : PRIMARY KEY ON (holding_ason_date,invest_wms_ac_number, security_code)
WMS_SECURITY: PRIMARY KEY ON (security code)

WMS_FUN_SECURITY_PRICE and WMS_DATE_FORMAT are the functions.

Number of records present:
WMS_SECURITY: 809
WMS_HODLDINGS: 55967
******************************************************************
Thanks & Regards
Prasad

Please guide how to tune the given query [message #242603 is a reply to message #242602] Mon, 04 June 2007 05:22 Go to previous messageGo to next message
mr_dba01
Messages: 5
Registered: May 2007
Location: mumbai
Junior Member
Dear all,
Please guide how to tune the following queries:

Case (1)
SELECT SECURITY_NAME,
SECURITY_CCY,S.SECURITY_NOMINAL_VALUE,S.SECURITY_CODE,
SUM(HOLDINGS_BOOKCOSTPRICE * HOLDINGS_BOOKPOSITION) FACE_VALUE_SECURITY,
(SUM(HOLDINGS_BOOKPOSITION)*(WMS_FUN_SECURITY_PRICE(S.SECURITY_CODE, to_date({?pTO_DATE},WMS_DATE_FORMAT)))) MARKET_VALUE_OF_SECURITY,
(SUM(HOLDINGS_PLEDGED) * (WMS_FUN_SECURITY_PRICE(S.SECURITY_CODE,
to_date({?pTO_DATE},WMS_DATE_FORMAT)))) PLEDGE_VALUE_OF_SECURITY
FROM WMS_SECURITY S ,WMS_HOLDINGS H
WHERE S.SECURITY_CODE=H.SECURITY_CODE
H.HOLDINGS_ASON_DATE >=TO_DATE('01/01/2005','DD/MM/YYYY')
AND
AND HOLDINGS_PLEDGED>0
GROUP BY SECURITY_NAME,SECURITY_CCY,S.SECURITY_NOMINAL_VALUE,S.SECURITY_CODE

****************************************************************
Tables referenced in above query are
WMS_HOLDINGS : PRIMARY KEY ON (holding_ason_date,invest_wms_ac_number, security_code)
WMS_SECURITY: PRIMARY KEY ON (security code)

WMS_FUN_SECURITY_PRICE and WMS_DATE_FORMAT are the functions.

Number of records present:
WMS_SECURITY: 809
WMS_HODLDINGS: 55967
******************************************************************
Thanks & Regards
Prasad
Re: Please guide how to tune the gvien queries [message #242613 is a reply to message #242602] Mon, 04 June 2007 06:09 Go to previous messageGo to next message
parthokonar
Messages: 17
Registered: September 2006
Location: india
Junior Member
See the num of ROWS FROM DBA_INDEXES to see join, What you are getting through explain plan with plan only, with no of reads. see the statspack report with migration of blocks. pin in with alter table table_name with storage clause. Then definetely the statement will be some what tuned.
Re: Please guide how to tune the gvien queries [message #242619 is a reply to message #242602] Mon, 04 June 2007 06:22 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

./Gather statistics of objects used
./Post your explain plan

Brayan

Re: Please guide how to tune the gvien queries [message #242624 is a reply to message #242602] Mon, 04 June 2007 06:30 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
1. Post the actual query, this one would result in a syntax error.
2. format your code.
3. post version
4. have you read and followed the sticky?
Re: Please guide how to tune the gvien queries [message #242634 is a reply to message #242602] Mon, 04 June 2007 08:25 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
The performance problems may be caused by called functions as well, so I recommend to generate SQL trace (or event 10046 trace) and posting TKPROF.

Michael
Re: Please guide how to tune the gvien queries [message #242651 is a reply to message #242602] Mon, 04 June 2007 09:40 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/82946/74940/
Do not cross/multi-post
Previous Topic: WORK_AREA_SIZE_POLICY = manual
Next Topic: about patch
Goto Forum:
  


Current Time: Sat Dec 10 14:28:13 CST 2016

Total time taken to generate the page: 0.09480 seconds