Home » RDBMS Server » Performance Tuning » Performance issue on RAC (Linux Redhat 4 AS/ES)
| Performance issue on RAC [message #340479] |
Wed, 13 August 2008 01:34  |
altafhk
Messages: 45
Registered: June 2007
Location: Islamabad
|
Member |
|
|
Can anyone tell me what is the reason behind. When I run a report, it takes 2.5 hours to generate from Production after peak hours and when I run the same report on Test server, it takes 7 minutes.
I am using Oracle 10g Rel 10.2.0.1 RAC of 2 instances and SAN without ASM on production. Whereas Test server is a standalone machine.
I took statistics through ADDM that shows SQL query optimization in recommendations. ASH shows 100% CPU utilization. It is unrealistic that I use same data imported on Test server and getting better results. The machine is also no more hifi than RAC machines.
Is the problem from SAN side? How to check its configurations? How to check where are bottlenecks exist?
I am sending statistics reports.
Regards
|
|
|
|
|
|
| Re: Performance issue on RAC [message #343802 is a reply to message #340479] |
Thu, 28 August 2008 03:48  |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
1. About ASH report - post EXPALIN of the query.
2. SPreport - as I see it - you have a number of very bad performing SQL statements without any connection to RAC.
For example:
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
54,119,581 9 6,013,286.8 94.2 0.00 1086.50 1098236163
SELECT NVL(SUM(CVT),0), NVL(SUM(WHT),0),NVL(SUM(WHT_COT),0), NVL
(SUM(WHT_MEMBER),0) FROM EQUITY_TRADE_INFO ETI WHERE EXISTS ( SE
LECT * FROM EQUITY_TRADE ET,EQUITY_ORDER EO WHERE ET.CLIENT_CODE
= :B5 AND ET.TRADE_NUMBER = ETI.TRADE_NUMBER AND ET.ISIN = :B4
AND ET.CLEARING_NO = TO_NUMBER(:B3 ) AND ET.BILL_NUMBER IS NULL
The statement performs over 6M accesses to DB per execution.
You didn't supply the complete source so I can not advice anything.
Post the complete statement source and EXPLAIN.
|
|
|
|
|
|
| Re: Performance issue on RAC [message #344004 is a reply to message #343993] |
Thu, 28 August 2008 10:10 |
altafhk
Messages: 45
Registered: June 2007
Location: Islamabad
|
Member |
|
|
I do not have source, as the application is made by a vendor. So, I cannot optimize SQL. All data files, redologs, archivelogs are on SAN without ASM.
I have 4GB RAM, 2GB SGA 1GB PGA
Please, tell me if I change some parameters like
db_file_multiblock_read_count (Presently) 15 (Advice required) 128
fast_start_mttr_target (Presently) 0 (Advice req) 300
optimizer_features_enable (Presently) 10.2.0.1 (Advice req) 8.1.7
open_cursors (Presently) 300 (Advice req) 600
optimizer_index_cost_adj (Presently) 1 (Advice req) 100
query_rewrite_integrity (Presently)enforced (Advice req) trusted
query_rewrite_enabled (Presently) false (Advice req) true
Regards,
Altaf
[Updated on: Thu, 28 August 2008 22:59] Report message to a moderator |
|
|
|
|
|
| Re: Performance issue on RAC [message #344482 is a reply to message #344332] |
Sat, 30 August 2008 00:27 |
altafhk
Messages: 45
Registered: June 2007
Location: Islamabad
|
Member |
|
|
As vendor is working on the application, I noticed many indexes. to_char from date is there but no function based index.
Please check what parameters are required to change on tables and indexes.
There are some dependents like Functions, procedures, packages and database level triggers.
Both tables have more than 1700000 rows.
Tablespace is Locally Managed
Template parameters of both the Tables and their Indexes:
Standard
Explicit
Extents initial size 19456K
next size 0K, increase size by 0%, minimum value 1, maximum value 2147483645, percentage free space reserved for update 10, transactions initial 2, maximum 255, free lists null, group null, buffer pool default, no parallel, logging, cache option place least frequently accessed data (nocache), monitoring collects modification statistics on a table.
Number of dependents found is :161
On table Equity_Trade:
ACMKHI.FK_EQTYTRADE_MEMBER_FK INDEX
ACMKHI.FK_EQUITYTRADES_7__15_FK INDEX
ACMKHI.IDX$$_00560001 INDEX
ACMKHI.IDX$$_00560002 INDEX
ACMKHI.IDX_EQUITY_TRADE_CLNO_CL_ISIN INDEX
ACMKHI.IDX_EQUITY_TRADE_CLNO_ISIN_CL INDEX
ACMKHI.IDX_EQUITY_TRADE_CL_ISIN_DT INDEX
ACMKHI.IDX_EQUITY_TRADE_DT_TKNO_BS INDEX
ACMKHI.IDX_EQUITY_TRADE_ISIN_CL_DT INDEX
ACMKHI.IDX_EQUITY_TRADE_TD_CL_ISIN INDEX
ACMKHI.IDX_EQUITY_TRADE_TD_ISIN_CL INDEX
ACMKHI.IDX_EQUITY_TRADE_TICKET_NO INDEX
ACMKHI.IDX_EQUITY_TR_CLCD_DT INDEX
ACMKHI.IDX_EQUITY_TR_ISIN_CLNO_BS_ON INDEX
ACMKHI.IDX_EQUITY_TR_ISIN_DT INDEX
ACMKHI.PK_EQUITYTRADES_1__16 INDEX
ACMKHI.REF_122783_FK INDEX
ACMKHI.REF_22636_FK INDEX
ACMKHI.REF_22643_FK INDEX
ACMKHI.REF_22650_FK INDEX
ACMKHI.REF_22658_FK INDEX
ACMKHI.REF_23791_FK INDEX
ACMKHI.REF_34549_FK INDEX
ACMKHI.REF_481489_FK INDEX
ACMKHI.REF_62640_FK INDEX
ACMKHI.REF_689318_FK INDEX
ACMKHI.PK_EQUITY_TRADE_SLAB_RATE INDEX
ACMKHI.PK_EQUITY_TRADE_TIMESTAMP INDEX
ACMKHI.PK_GROUP_TRADE INDEX
ACMKHI.REF_74552_FK INDEX
ACMKHI.REF_74560_FK INDEX
ACMKHI.PK_GROUP_TRADE_MASTER INDEX
On table Equity_Trade_Info
ACMKHI.PK_EQUITY_TRADE_INFO INDEX
|
|
|
|
|
|
|
|
| Re: Performance issue on RAC [message #344595 is a reply to message #340479] |
Sat, 30 August 2008 22:26 |
sonumalhi
Messages: 62
Registered: April 2008
|
Member |
|
|
Is the Explain Plan on PRoduction and Test is same or it is using differnet plan on both servers?
Did you check the statistics of the tables if they are stale or not?
thanks
Mehtab
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Performance issue on RAC [message #347501 is a reply to message #340479] |
Thu, 11 September 2008 22:17 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
altafhk.
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Which is easier to read & understand, your post or below?
INSERT INTO temp_Equity_Margin
(Client_Code,
IsIn,
Cash_Balance,
cUst_Balance,
Market_Date,
Market_Rate,
Outstnd_Amount,
Cash_Margin,
cUst_Margin,
Cash_Buying_Power,
cUst_Buying_Power,
Short_Sale_Value,
Future_Period_desc,
Equity_Current_Position,
Prov_Trade)
SELECT dq.Client_Code,
dq.IsIn,
0,
SUM(dq.Quantity) Custody_Balance,
NULL,
0,
0,
0,
0,
0,
0,
0,
fpd,
0,
Prov
FROM (SELECT *
FROM (SELECT cm.Client_Code,
cm.IsIn,
NULL fpd,
NULL Prov,
SUM(DECODE(cm.In_Or_Out,'I',(Nvl(cm.un_reg_Quantity,0) + Nvl(cm.reg_Quantity,0)),
- (Nvl(cm.un_reg_Quantity,0) + Nvl(cm.reg_Quantity,0)))) Quantity
FROM Custody_Master cm,
Equity_temp_Capital_g_l_Sum etc
WHERE cm.Client_Code = etc.Client_Code
AND EXISTS (SELECT 1
FROM Custody_Master cm2
WHERE cm.Transaction_Id = cm2.Transaction_Id
AND cm2.Transaction_Date <= '06-SEP-08')
AND cm.Post = 1
GROUP BY cm.Client_Code,
cm.IsIn)
WHERE Quantity <> 0
UNION ALL
SELECT scm.Client_Code,
scm.IsIn,
scm.fpd,
NULL Prov,
SUM(scm.Quantity)
FROM (SELECT cm.Clearing_No,
cm.Client_Code,
cm.IsIn,
NULL fpd,
SUM(DECODE(cm.In_Or_Out,'I',(Nvl(cm.un_reg_Quantity,0) + Nvl(cm.reg_Quantity,0)),
- (Nvl(cm.un_reg_Quantity,0) + Nvl(cm.reg_Quantity,0)))) Quantity
FROM Custody_Master cm,
Equity_temp_Capital_g_l_Sum etc
WHERE cm.Client_Code = etc.Client_Code
AND cm.Transaction_Date > '06-SEP-08'
AND cm.Clearing_No IS NOT NULL
AND cm.Post = 1
GROUP BY cm.Clearing_No,
cm.Client_Code,
cm.IsIn,
NULL
HAVING (SUM(DECODE(cm.In_Or_Out,'I',(Nvl(cm.un_reg_Quantity,0) + Nvl(cm.reg_Quantity,0)),
- (Nvl(cm.un_reg_Quantity,0) + Nvl(cm.reg_Quantity,0)))) <> 0)) scm,
Clearing_Calendar cc
WHERE scm.Clearing_No = cc.Clearing_No
AND cc.Clearing_End_Date <= '06-SEP-08'
GROUP BY scm.Client_Code,
scm.IsIn,
scm.fpd
UNION ALL
SELECT Cob.Client_Code,
Cob.IsIn,
NULL fpd,
NULL Prov,
DECODE(ca.In_Or_Out,'I',(Nvl(Cob.un_reg_Quantity,0) + Nvl(Cob.reg_Quantity,0)),
- (Nvl(Cob.un_reg_Quantity,0) + Nvl(Cob.reg_Quantity,0))) Quantity
FROM Custody_Opening_Balances Cob,
Custody_Activity ca,
Equity_temp_Capital_g_l_Sum etc
WHERE Cob.Client_Code = etc.Client_Code
AND Cob.Activity_Code = ca.Activity_Code
AND Cob.Post = 1
UNION ALL
SELECT et.Client_Code,
et.IsIn,
cc.Future_Period_desc fpd,
DECODE(Instr(sc.Symbol,'-PRO'),0,NULL,
'-PRO') Prov,
SUM(DECODE(et.Buy_Or_Sell,'B',et.Volume,
- et.Volume)) Quantity
FROM Equity_Trade et,
Clearing_Calendar cc,
Security sc,
Equity_temp_Capital_g_l_Sum etc
WHERE et.Client_Code = etc.Client_Code
AND et.Trade_Date <= '06-SEP-08'
AND Nvl(et.Bill_Number,0) = 0
AND et.Clearing_No = cc.Clearing_No
AND et.IsIn = sc.IsIn
AND et.Post = 1
GROUP BY et.Client_Code,
et.IsIn,
cc.Future_Period_desc,
sc.Symbol
UNION ALL
SELECT et.Client_Code,
et.IsIn,
NULL fpd,
'-PROW' Prov,
SUM(DECODE(et.Buy_Or_Sell,'B',et.Volume,
- et.Volume)) Quantity
FROM Equity_Trade et,
Clearing_Calendar cc,
Security sc,
Equity_temp_Capital_g_l_Sum etc
WHERE et.Client_Code = etc.Client_Code
AND et.Bill_Number IS NULL
AND et.Trade_Date > '06-SEP-08'
AND et.Trade_Type = (SELECT es.Release_Cot_Trade
FROM Equity_System es)
AND sc.Symbol LIKE '%-PRO'
AND et.Bill_Number IS NULL
AND et.Clearing_No = cc.Clearing_No
AND et.IsIn = sc.IsIn
AND et.Post = 1
GROUP BY et.Client_Code,
et.IsIn,
cc.Future_Period_desc,
sc.Symbol
UNION ALL
SELECT et.Client_Code,
et.IsIn,
cc.Future_Period_desc fpd,
'-FUTW' Prov,
SUM(DECODE(et.Buy_Or_Sell,'B',et.Volume,
- et.Volume)) Quantity
FROM Equity_Trade et,
Clearing_Calendar cc,
Security sc,
Equity_temp_Capital_g_l_Sum etc
WHERE et.Client_Code = etc.Client_Code
AND et.Bill_Number IS NULL
AND et.Trade_Date > '06-SEP-08'
AND et.Trade_Type = (SELECT es.Release_Cot_Trade
FROM Equity_System es)
AND sc.Symbol NOT LIKE '%-PRO'
AND cc.Future_Period_desc IS NOT NULL
AND et.Clearing_No = cc.Clearing_No
AND et.IsIn = sc.IsIn
AND et.Post = 1
GROUP BY et.Client_Code,
et.IsIn,
cc.Future_Period_desc,
sc.Symbol) dq
GROUP BY dq.Client_Code,
dq.IsIn,
fpd,
Prov
HAVING (SUM(dq.Quantity) <> 0)
One possible reason why the difference in behavior is the two systems have different NLS_DATE_FORMATs.
[Updated on: Thu, 11 September 2008 22:21] by Moderator Report message to a moderator |
|
|
|
| Re: Performance issue on RAC [message #347564 is a reply to message #347501] |
Fri, 12 September 2008 02:53 |
altafhk
Messages: 45
Registered: June 2007
Location: Islamabad
|
Member |
|
|
I forgot to send message in given format. Both systems have same nls_date_formats.
The query that takes too much time in calculating figures and populating GTT is given below with explain plan. How can I optimize it.
alter system flush shared_pool;
System altered.
INSERT INTO TEMP_TBL (CLIENT_CODE, CL_AMT)
SELECT CLIENT_CODE, SUM(AMT)
FROM
(
SELECT C.GL_SL_CODE CLIENT_CODE, C.AMOUNT AMT
FROM GL_VOUCHERS_DETAILS C, GL_VOUCHERS A,
EQUITY_TEMP_CAPITAL_G_L_SUM ETC
WHERE A.GL_VOUCHER_NO = C.GL_VOUCHER_NO
AND C.GL_SL_TYPE = 'ALL'--:B2
AND C.GL_SL_CODE = ETC.CLIENT_CODE
AND A.GL_FORM_DATE <= '01-Jan-2007'--:B1
AND NVL(A.GL_FORM_TYPE_CODE, 'X') <> 'ETB' AND ROWNUM > 0
UNION ALL
SELECT ET.CLIENT_CODE, DECODE(ET.BUY_OR_SELL, 'B',
(ET.VOLUME * ET.RATE) + (ET.BRK_AMOUNT + NVL(ETI.CVT, 0) +
NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0)), ((ET.VOLUME *
ET.RATE) - (ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT,
0) + NVL(ETI.WHT_COT, 0))) * -1) AMT
FROM EQUITY_TRADE ET, CLEARING_CALENDAR CC,
EQUITY_TRADE_INFO ETI, SECURITY, EQUITY_SYSTEM ES,
EQUITY_TEMP_CAPITAL_G_L_SUM ETC
WHERE ET.TRADE_NUMBER = ETI.TRADE_NUMBER(+) AND ET.ISIN =
SECURITY.ISIN AND ET.CLEARING_NO = CC.CLEARING_NO
AND ET.CLIENT_CODE = ETC.CLIENT_CODE
AND ET.TRADE_DATE <= '6-SEP-2008' --:B1
AND DECODE('F' || NVL(ET.BILL_NUMBER, -1), 'F-1',
CC.CLEARING_TYPE, 0) <> DECODE('F' ||
NVL(ET.BILL_NUMBER, -1), 'F-1', ES.FORWARD_CLR_TYPE, 1)
AND DECODE(SUBSTR(UPPER(SECURITY.SYMBOL),
LENGTH(UPPER(SECURITY.SYMBOL)) - 2), 'PRO',
DECODE(ET.BILL_NUMBER, NULL, 0, 1), 1) = 1)
GROUP BY CLIENT_CODE
/
0 rows created.
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 13 | 221 | 10274 |
| 1 | SORT GROUP BY | | 13 | 221 | 10274 |
| 2 | VIEW | | 13 | 221 | 10271 |
| 3 | UNION-ALL | | | | |
| 4 | COUNT | | | | |
| 5 | FILTER | | | | |
| 6 | NESTED LOOPS | | 8 | 296 | 177 |
| 7 | NESTED LOOPS | | 8 | 160 | 169 |
| 8 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 |
| 9 | TABLE ACCESS BY INDEX ROWID| GL_VOUCHERS_DETAILS | 8 | 128 | 168 |
| 10 | INDEX RANGE SCAN | REF_150932_VOUCHERS_DETAILS | 3 | | 2 |
| 11 | TABLE ACCESS BY INDEX ROWID | GL_VOUCHERS | 1 | 17 | 1 |
| 12 | INDEX UNIQUE SCAN | PK_GL_VOUCHERS | 1 | | |
| 13 | NESTED LOOPS OUTER | | 5 | 460 | 10094 |
| 14 | NESTED LOOPS | | 5 | 385 | 10084 |
| 15 | NESTED LOOPS | | 95 | 6650 | 9989 |
| 16 | NESTED LOOPS | | 9540 | 475K| 449 |
| 17 | MERGE JOIN CARTESIAN | | 1 | 6 | 2 |
| 18 | INDEX FULL SCAN | FK_EQ_SYS_FORWARD_CLR_TYPE_FK | 1 | 2 | 1 |
| 19 | BUFFER SORT | | 1 | 4 | 1 |
| 20 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 |
| 21 | TABLE ACCESS BY INDEX ROWID| EQUITY_TRADE | 9540 | 419K| 447 |
| 22 | INDEX RANGE SCAN | REF_22636_FK | 9540 | | 438 |
| 23 | TABLE ACCESS BY INDEX ROWID | SECURITY | 1 | 19 | 1 |
| 24 | INDEX UNIQUE SCAN | PK_SECURITY | 1 | | |
| 25 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 7 | 1 |
| 26 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | |
| 27 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE_INFO | 1 | 15 | 2 |
| 28 | INDEX UNIQUE SCAN | PK_EQUITY_TRADE_INFO | 1 | | 1 |
----------------------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
8254 recursive calls
0 db block gets
3145 consistent gets
0 physical reads
0 redo size
559 bytes sent via SQL*Net to client
1838 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
83 sorts (memory)
0 sorts (disk)
0 rows processed
|
|
|
|
|
|
|
|
| Re: Performance issue on RAC [message #353790 is a reply to message #347638] |
Wed, 15 October 2008 02:14 |
altafhk
Messages: 45
Registered: June 2007
Location: Islamabad
|
Member |
|
|
Sorry! I found difficult to indent as guided format. I have solved the issue. It was Application side issue. The report uses a procedure that was calling a function. As Oracle corporation has stopped support of Developer 6i with Oracle 10g and above, and it seemed that type of issue. I replaced sql with that function. I also provided optimizer hints /*+ choose*/ and the report runs now reducing time from 3 hours to 7 minutes.
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 09:22:05 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
