Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: Perfomance Issue

    Hello

    I'm investigating performance on my Oracle database server and I suspect SGA and I/O on the server is an issue

    it seems that the sql executions do not create any performance issue


    below you can see some of the major sections in the AWR, seeem that SGA is Undersized and PGA is oversized.

    Which parameters I have to investigate, and how can I fix them?

    Thanks in advance!

    HTML Code:
    Host Name        Platform                         CPUs Cores Sockets Memory(GB)
    ---------------- -------------------------------- ---- ----- ------- ----------
    ORCL15           Microsoft Windows x86 64-bit       32    16       2      47.97
    
    Top 5 Timed Foreground Events
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                               Avg                  
                                                              wait   % DB           
    Event                                 Waits     Time(s)   (ms)   time Wait Class
    ------------------------------ ------------ ----------- ------ ------ ----------
    DB CPU                                           10,281          38.9           
    log file sync                       690,584       5,808      8   22.0 Commit    
    db file sequential read             657,551       4,249      6   16.1 User I/O  
    Disk file operations I/O             33,427         795     24    3.0 User I/O  
    db file scattered read               25,531         448     18    1.7 User I/O  
    
    Statistic                                  Value        End Value
    ------------------------- ---------------------- ----------------
    AVG_BUSY_TIME                            649,417                 
    AVG_IDLE_TIME                         65,535,931                 
    AVG_SYS_TIME                             121,154                 
    AVG_USER_TIME                            500,734                 
    BUSY_TIME                             21,780,334                 
    IDLE_TIME                          2,098,176,991                 
    SYS_TIME                               4,854,204                 
    USER_TIME                             16,944,590                 
    RSRC_MGR_CPU_WAIT_TIME                        21                 
    PHYSICAL_MEMORY_BYTES             51,503,353,856                 
    NUM_CPU_CORES                                 16                 
    NUM_CPUS                                      32                 
    NUM_CPU_SOCKETS                                2                 
    
    SGA Target Advisory                       
    
    SGA Target   SGA Size       Est DB     Est Physical
      Size (M)     Factor     Time (s)            Reads
    ---------- ---------- ------------ ----------------
         2,048        0.5    1,480,938       43,022,199
         2,560        0.6      324,314       43,022,199
         3,072        0.8      253,682       23,928,169
         3,584        0.9      232,868       18,009,216
         4,096        1.0      226,239       16,386,912
         4,608        1.1      222,959       15,562,650
         5,120        1.3      220,741       14,821,962
         5,632        1.4      218,253       14,392,625
         6,144        1.5      218,094       14,392,625
         6,656        1.6      218,004       14,392,625
         7,168        1.8      217,913       14,392,625
         7,680        1.9      217,846       14,392,625
         8,192        2.0      217,800       14,392,625
    -------------------------------------------------------------   
    
    PGA Memory Advisory      
    
                                           Estd Extra    Estd P Estd PGA        
    PGA Target    Size           W/A MB   W/A MB Read/    Cache Overallo    Estd
      Est (MB)   Factr        Processed Written to Disk   Hit %    Count    Time
    ---------- ------- ---------------- ---------------- ------ -------- -------
           614     0.1        652,658.5            138.6  100.0        5 4.6E+08
         1,228     0.3        652,658.5              6.7  100.0        0 4.6E+08
         2,456     0.5        652,658.5              6.7  100.0        0 4.6E+08
         3,683     0.8        652,658.5              6.7  100.0        0 4.6E+08
         4,911     1.0        652,658.5              6.7  100.0        0 4.6E+08
         5,893     1.2        652,658.5              0.0  100.0        0 4.6E+08
         6,875     1.4        652,658.5              0.0  100.0        0 4.6E+08
         7,858     1.6        652,658.5              0.0  100.0        0 4.6E+08
         8,840     1.8        652,658.5              0.0  100.0        0 4.6E+08
         9,822     2.0        652,658.5              0.0  100.0        0 4.6E+08
        14,733     3.0        652,658.5              0.0  100.0        0 4.6E+08
        19,644     4.0        652,658.5              0.0  100.0        0 4.6E+08
        29,466     6.0        652,658.5              0.0  100.0        0 4.6E+08
        39,288     8.0        652,658.5              0.0  100.0        0 4.6E+08
    -------------------------------------------------------------   
       

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    As you spend most of the time with CPU, I'd look into the Top 10 SQL statements and check their execution plans.

    A high CPU load could also indicate a latch/semaphore problem. Check out the details for the CPU problem using V$SESSION_WAIT and V$SESSION_WAIT_HISTORY

    You can't really do anything about "log file sync" waits apart from buying faster disks or simply doing less DML ("updates")
    Last edited by shammat; 05-18-13 at 13:01.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What I see is that IDLE values are 100 TIMES greater than the BUSY values.
    I don't see any performance problem based upon what you posted.
    Last edited by anacedent; 05-18-13 at 19:25.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •