8 Replies Latest reply: Jan 8, 2013 5:02 AM by Billy~Verreynne RSS

    sga and pga

    949210
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
      PL/SQL Release 10.2.0.1.0 - Production
      "CORE     10.2.0.1.0     Production"
      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production
      on Windows server 2003 with just one database on this machine
      there are 28 concurrent users logged in today and the task assigned to them is to insert into the database using procedures
      data is getting saved but takes too long
      SELECT * FROM V$PGA_TARGET_ADVICE;
      20840448     0.125     ON     35849537536     16564233216     68     597
      41680896     0.25     ON     35849537536     16271416320     69     547
      83361792     0.5     ON     35849537536     6620797952     84     107
      125042688     0.75     ON     35849537536     2936792064     92     11
      166723584     1     ON     35849537536     2130666496     94     0
      200068096     1.2     ON     35849537536     1949061120     95     0
      233412608     1.4     ON     35849537536     1775031296     95     0
      266757120     1.6     ON     35849537536     1727776768     95     0
      300101632     1.8     ON     35849537536     1671278592     96     0
      333447168     2     ON     35849537536     1671278592     96     0
      500170752     3     ON     35849537536     1574583296     96     0
      666894336     4     ON     35849537536     1574583296     96     0
      1000341504     6     ON     35849537536     1574583296     96     0
      1333788672     8     ON     35849537536     1574583296     96     0
      select * from v$Sga_target_advice
      2504     1     50049     1     340360
      626     0.25     50049     1     340360
      1252     0.5     50049     1     340360
      5008     2     50050     1     340360
      3130     1.25     50050     1     340360
      3756     1.5     50050     1     340360
      4382     1.75     50050     1     340360
      1878     0.75     50050     1     340360
      using a dedicated server connection
      thanks in advance

      Edited by: 946207 on Jan 7, 2013 3:38 PM
        • 1. Re: sga and pga
          GReboute
          Hi,
          What is your question ?
          • 2. Re: sga and pga
            949210
            NAME                                               TYPE        VALUE                                                                                                
            -------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
            pga_aggregate_target                               big integer 159M        
            the inserts are too slow
            the same procedure worked fine with upto five users
            the question is: is the memory falling short??
            • 3. Re: sga and pga
              905562
              Why do you think PGA is a culprit on an application exhibiting (based on description) scaling issues?

              What do the traces tell you the sessions are waiting on?
              • 4. Re: sga and pga
                949210
                this is tkprof output of trace file
                TKPROF: Release 10.2.0.1.0 - Production on Mon Jan 7 16:17:03 2013
                
                Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                
                Trace file: C:\oracle\product\10.2.0\db_1\RDBMS\trace\orcl_ora_3488.trc
                Sort options: default
                
                ********************************************************************************
                count    = number of times OCI procedure was executed
                cpu      = cpu time in seconds executing 
                elapsed  = elapsed time in seconds executing
                disk     = number of physical reads of buffers from disk
                query    = number of buffers gotten for consistent read
                current  = number of buffers gotten in current mode (usually for update)
                rows     = number of rows processed by the fetch or execute call
                    0  statements EXPLAINed in this session.
                ********************************************************************************
                Trace file: C:\oracle\product\10.2.0\db_1\RDBMS\trace\orcl_ora_3488.trc
                Trace file compatibility: 10.01.00
                Sort options: default
                
                     112  sessions in tracefile.
                       0  user  SQL statements in trace file.
                       0  internal SQL statements in trace file.
                       0  SQL statements in trace file.
                       0  unique SQL statements in trace file.
                    5065  lines in trace file.
                       0  elapsed seconds in trace file.
                Edited by: 946207 on Jan 7, 2013 4:42 PM
                • 5. Re: sga and pga
                  GReboute
                  There is no SQL in your trace, your system isn't working at all. It seems the issue is elsewhere.
                  • 6. Re: sga and pga
                    Billy~Verreynne
                    946207 wrote:

                    the inserts are too slow
                    the same procedure worked fine with upto five users
                    the question is: is the memory falling short??
                    Wrong question.

                    The question should be - WHAT is causing the performance problem?


                    From Oracle® Database Performance Tuning Guide, paragraph +1.1.2.1 Performance Principles+:
                    >
                    Tuning is driven by identifying the most significant bottleneck and making the appropriate changes to reduce or eliminate the effect of that bottleneck.
                    >

                    Step 1. IDENTIFY the problem.
                    Step 2. Reduce or eliminate that problem.

                    So have you identified the problem? It would not seem so from the details you have posted thus far.
                    • 7. Re: sga and pga
                      949210
                      okay
                      I've read the concepts guide
                      I've not yet read performance tuning guide
                      Please advice on how to identify the problem
                      thank you
                      • 8. Re: sga and pga
                        Billy~Verreynne
                        946207 wrote:

                        Please advice on how to identify the problem
                        Two basic approaches with this specific problem.

                        Read the procedure's PL/SQL code and do a basic sanity check. Does it take Oracle read consistency and multiple version concurrency rules into consideration? Is there serialisation?

                        Run 5+ sessions and execute the procedure in each session. Monitor virtual performance tables (see Reference Guide for details), and specifically the v$session_wait view that will tell you what the wait states are of these sessions.