| improve performance of query over dblink. [message #179109] |
Sun, 25 June 2006 13:44  |
rajd
Messages: 6
Registered: June 2006
|
Junior Member |
|
|
Hi,
I have a situation like this, I have 2 databases, one in 10g and other in 9i. Both the databases are on seperate servers.
In 9i Database i have schema A where i have following tables
Table Name (No of Rows) (Statistics Gathered)
===============================================================
CAMPAIGN (124) (Yes)
PRODUCT (867) (Yes)
PRODUCT_SUBSCRIPTION (18530648) (Yes)
PS_BLOCK_CAUSE (17027915) (Yes)
CALLING_CARD (33902155) (Yes)
ACCOUNT_STATE (18369624) (Yes)
In 10g Database I have schema B where i have following tables
Table Name (No of Rows) (Statistics Gathered)
===============================================================
CALLING_CARD_LOT (911194) (Yes)
CALLING_CARD_LOT_PRODUCT (799895) (Yes)
In 10g Database I have another schema C where i have following tables and synonyms
Table Name (No of Rows) (Statistics Gathered)
===============================================================
PARAMETER_MASTER (7) (Yes)
Synonym Name (Base Table) (dblink used)
================================================================
I_CALLING_CARD_LOT (B.CALLING_CARD_LOT) (No)
I_CALLING_CARD_LOT_PRODUCT (B.CALLING_CARD_LOT_PRODUCT) (No)
I_ACCOUNT_STATE (A.ACCOUNT_STATE) (dblink_AB)
I_CALLING_CARD (A.CALLING_CARD) (dblink_AB)
I_CAMPAIGN (A.CAMPAIGN) (dblink_AB)
I_PRODUCT (A.PRODUCT) (dblink_AB)
I_PRODUCT_SUBSCRIPTION (A.PRODUCT_SUBSCRIPTION) (dblink_AB)
I_PS_BLOCK_CAUSE (A.PS_BLOCK_CAUSE) (dblink_AB)
Now I am executing a select query form C schema which has 1 table and 8 synonyms as listed above.
SELECT ccl.owner_account_id AS owner_account_id,
cclp.product_id AS product_id, cc.ID AS card_id,
ccl.ID AS lot_id,
CASE
WHEN cc.account_id IS NULL
THEN 'I'
WHEN psb.block_cause_id IS NULL
THEN 'A'
WHEN (psb.block_cause_id) = 7
THEN 'E'
WHEN (psb.block_cause_id) <> 7
THEN 'B'
END AS status,
ccl.number_of_cards AS number_of_cards,
ABS (ccl.balance) AS face_value,
NVL (ABS (ast.balance), 0) AS current_balance,
CASE
WHEN cc.account_id IS NULL
THEN 'N'
WHEN psb.block_cause_id IS NULL
THEN 'N'
WHEN (psb.block_cause_id) = 7
THEN 'N'
WHEN (psb.block_cause_id) <> 7
THEN CASE
WHEN ccl.balance <> NVL (ast.balance, 0)
THEN 'Y'
WHEN ccl.balance = NVL (ast.balance, 0)
THEN 'N'
END
END AS full_face_value
FROM i_product,
i_campaign,
i_calling_card cc,
i_calling_card_lot ccl,
i_calling_card_lot_product cclp,
i_product_subscription ps,
i_ps_block_cause psb,
i_account_state ast
WHERE cc.calling_card_lot_id = ccl.ID
AND cclp.calling_card_lot_id = ccl.ID
AND cclp.campaign_id = i_campaign.ID
AND cclp.product_id = i_product.ID
AND cc.account_id = ps.account_id(+)
AND (ps.product_id = cclp.product_id OR ps.product_id IS NULL)
AND ps.ID = psb.product_subscription_id(+)
AND ast.account_id(+) = cc.account_id
AND ccl.ispvn_id IN (SELECT parameter_value
FROM parameter_master
WHERE parameter_type = 'ISPVN_ID')
ORDER BY cc.ID ASC;
When I fire this query from schema C, this runs for several hours without giving any output. Ultimately I need to kill this. At the execution time i saw all network related wait events ie SQL*message to dblink and SQL*Message from dblink etc. I traced this session during executuon phase of the query and generated report using tkprof utility with sort=fchela option. I am attaching am tkprof output with is posting. In tkprof report also i see that SQL*message to dblink,SQL*message from dblink and SQL*message from client wait events are taking most of the time.
Can anybody tell me what is the performance bottleneck of my sql query ? any suggestions on how to improve performance for sql queries which are based on dblinks ?
Thanks in advance.
Regards
Raj - DBA
-
Attachment: tune.prf
(Size: 11.46KB, Downloaded 1587 times)
|
|
|
|
|
|
| Re: improve performance of query over dblink. [message #179111 is a reply to message #179110] |
Sun, 25 June 2006 15:00  |
rajd
Messages: 6
Registered: June 2006
|
Junior Member |
|
|
Hi,
1) I am extremely sorry for the text formatting. As i am new to this group, i am not aware of how to do the text formatting. i will surely search & read the doc regarding text formatting on this site.
2)As you said, to eliminate i_product,i_campaign and i_product_subscription tables and put them in the where clause, if i understood correctly to achive this,i need to use subquery in the where clause. but i guess it is always better to use joins than a subquery .. correct me if i am wrong. Also i am not very good in sql query writing. Can you please tell me, how do i achive eliminate said tables from FROM clause and incorporate it in where clause ? and how it will affect the performance ?
Also what is the cause of getting high SQL*net to message idle event.
Thanks,
Raj
|
|
|
|
|
|
|
|
| Re: improve performance of query over dblink. [message #179117 is a reply to message #179116] |
Sun, 25 June 2006 17:19 |
rajd
Messages: 6
Registered: June 2006
|
Junior Member |
|
|
Thanks for sharing your expirence. I need to check with the client if materialized setup will be ok for them, but i kept that as a last resort. I need to see first the option suggested by anacedent for tunning the query.
Thanks,
Raj
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
