| Please guide how to tune the given query (merged) [message #242602] |
Mon, 04 June 2007 05:20  |
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  |
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 |
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.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
