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 Go to next message
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 #343748 is a reply to message #340479] Thu, 28 August 2008 01:07 Go to previous messageGo to next message
altafhk
Messages: 45
Registered: June 2007
Location: Islamabad
Member

I have noted that we are using form6i and reports6i on this fault-tolerant load-balanced RAC environment, that is why we were facing performance issue. I changed sp parameter Optimizer_feature_enable to 8.1.7 that was 10.2.0.1 before and the reports boosted to 99%. But, the forms became slow. Like, commission calculations and posting form now taking 2 hours to complete transaction while it was taking 5 minutes before.

Please, anybody help me how to resolve performance issue of both forms and reports. Otherwise I would have to revert back Optimizer_feature_enable to 10.2.0.1

Please check statspack report.
  • Attachment: spreport2.lst
    (Size: 129.97KB, Downloaded 158 times)
Re: Performance issue on RAC [message #343802 is a reply to message #340479] Thu, 28 August 2008 03:48 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 #343993 is a reply to message #343802] Thu, 28 August 2008 09:47 Go to previous messageGo to next message
altafhk
Messages: 45
Registered: June 2007
Location: Islamabad
Member

Thank you for investigating my problem. I am sending Explain query.
  • Attachment: explain.txt
    (Size: 2.58KB, Downloaded 163 times)
Re: Performance issue on RAC [message #344004 is a reply to message #343993] Thu, 28 August 2008 10:10 Go to previous messageGo to next message
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 #344332 is a reply to message #340479] Fri, 29 August 2008 07:27 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
What indexes exists for EQUITY_TRADE and EQUITY_TRADE_INFO tables?
Re: Performance issue on RAC [message #344482 is a reply to message #344332] Sat, 30 August 2008 00:27 Go to previous messageGo to next message
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 #344484 is a reply to message #340479] Sat, 30 August 2008 00:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
what problem are your trying to solve?
Re: Performance issue on RAC [message #344490 is a reply to message #344484] Sat, 30 August 2008 01:04 Go to previous messageGo to next message
altafhk
Messages: 45
Registered: June 2007
Location: Islamabad
Member

Sir, I am facing performance problems.
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.

When I changed optimizer_features_enable parameter to 8.1.7, I noted performance of report boosted to 15 minutes from 2.5 hours, but a form that performs some calculations and posts transactions to GL became slow. The form was performing task in 3 minutes before, but then it was taking about an hour.

Please help me.
Re: Performance issue on RAC [message #344595 is a reply to message #340479] Sat, 30 August 2008 22:26 Go to previous messageGo to next message
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 #344672 is a reply to message #344595] Sun, 31 August 2008 22:24 Go to previous messageGo to next message
altafhk
Messages: 45
Registered: June 2007
Location: Islamabad
Member

Explain plan, data and in fact all parameters are same in production and test servers. Only the production server is cluster db. I checked statistics from both servers. Only SQL optimization recommendations I found through.

[Updated on: Sun, 31 August 2008 22:26]

Report message to a moderator

Re: Performance issue on RAC [message #346602 is a reply to message #344672] Tue, 09 September 2008 01:57 Go to previous messageGo to next message
altafhk
Messages: 45
Registered: June 2007
Location: Islamabad
Member

I have got rdf from vendor. There are some before/after report triggers and PLLs that might degrade performance, which are not included in this report. But now I got complete sql query. Can anybody suggest better sql techniques to incorporate for the purpose of increasing generate time.
  • Attachment: RM_Code.txt
    (Size: 6.56KB, Downloaded 198 times)
Re: Performance issue on RAC [message #346957 is a reply to message #346602] Wed, 10 September 2008 03:57 Go to previous messageGo to next message
altafhk
Messages: 45
Registered: June 2007
Location: Islamabad
Member

Can anybody identify the performance problem in attached procedure that populates temporary tables.
Re: Performance issue on RAC [message #346958 is a reply to message #346957] Wed, 10 September 2008 04:00 Go to previous messageGo to next message
altafhk
Messages: 45
Registered: June 2007
Location: Islamabad
Member

I am attaching identified SQL queries that take 70% to 90% cpu utilization.
Looking for the support.
  • Attachment: ashquery.txt
    (Size: 8.77KB, Downloaded 252 times)
Re: Performance issue on RAC [message #347330 is a reply to message #346958] Thu, 11 September 2008 05:31 Go to previous messageGo to next message
altafhk
Messages: 45
Registered: June 2007
Location: Islamabad
Member

How can I improve execution plan, please see my file
Re: Performance issue on RAC [message #347501 is a reply to message #340479] Thu, 11 September 2008 22:17 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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 Go to previous messageGo to next message
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 #347574 is a reply to message #347564] Fri, 12 September 2008 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still don't format anything.

Regards
Michel
Re: Performance issue on RAC [message #347638 is a reply to message #340479] Fri, 12 September 2008 08:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
altafhk,
You choose to ignore Posting Guidelines, we choose to ignore your posts.

You're On Your Own (YOYO)!
Re: Performance issue on RAC [message #353790 is a reply to message #347638] Wed, 15 October 2008 02:14 Go to previous message
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.
Previous Topic: MAJOR performance issues since upgrade to 10G
Next Topic: OEM performance issue
Goto Forum:
  


Current Time: Sat Dec 10 05:05:53 CST 2016

Total time taken to generate the page: 0.11088 seconds