dcsimg
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: analyze SYS or SYSTEM

  1. #1
    Join Date
    Mar 2006
    Posts
    176

    analyze SYS or SYSTEM

    I am on 10.2.0.2

    1. could you please tell me if there is a benefit to ANALYZE SYS schema?
    2. I have an performance issue, I have DEV and PRD, all of the parameter are the same, alsmost everything is the same between the two beside HARDWARE. my question why do I have to different execution plan when I trace the SQL?

    thanks

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    1-- Why in the world you would like to to that?
    2-- Something is different, most likely:
    - Data
    - Performance stats
    - Available indexes
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2006
    Posts
    176
    I know Oracle doesn't recommend to analyze SYS and SYSTEM on 9i but I thought I saw somewhere on the internet saying you can analyze SYS and SYSTEM on 10G now so I just throw out there.

    Any way, I took the same query and turn on the trace and execute on the environment, there are significant different between the FETCH time on PROD. what could make the fecth time longer?

    thanks,

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    only sys, not system - try it, you can always delete the stats after

  5. #5
    Join Date
    Mar 2006
    Posts
    176
    thanks, could you please tell me the rule of thumb for longer FETCH time? the possible reasons that could make the FECTH time longer?

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    post the plans and statistics about the query, there is no ROT

  7. #7
    Join Date
    Mar 2006
    Posts
    176
    Davey23UK, here is what I have in PRD

    Code:
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.04       0.04          0          0          0           0
    Execute      1      0.01       0.00          0          0          0           0
    Fetch        1      1.52       1.49          0      81344          0           5
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      1.57       1.53          0      81344          0           5
    
    Misses in library cache during parse: 1
    Optimizer mode: FIRST_ROWS
    Parsing user id: 5  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          5  SORT ORDER BY (cr=81344 pr=0 pw=0 time=1492240 us)
          5   FILTER  (cr=81344 pr=0 pw=0 time=1403371 us)
          5    NESTED LOOPS  (cr=81344 pr=0 pw=0 time=1402881 us)
      40419     NESTED LOOPS  (cr=504 pr=0 pw=0 time=243069 us)
          1      TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=116 us)
          1       INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=73 us)(object id 44)
      40419      TABLE ACCESS FULL ARGUMENT$ (cr=502 pr=0 pw=0 time=202534 us)
          5     TABLE ACCESS BY INDEX ROWID OBJ$ (cr=80840 pr=0 pw=0 time=1207571 us)
      40419      INDEX UNIQUE SCAN I_OBJ1 (cr=40421 pr=0 pw=0 time=484684 us)(object id 36)
          1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=450 us)
          0    HASH JOIN  (cr=0 pr=0 pw=0 time=0 us)
          0     INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us)(object id 103)
          0     FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)
    Last edited by davey23uk; 09-16-2007 at 07:16 AM.

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    1.5 seconds, not really a lot is it

  9. #9
    Join Date
    Mar 2006
    Posts
    176
    Thanks Davey23UK,

    I agreed it's only 1.5 sec but the user complained so much about the performance on PRD compared to DEV, below is what I have in PRD:
    Code:
    Cache Sizes                       Begin        End
    ~~~~~~~~~~~                  ---------- ----------
                   Buffer Cache:       100M             Std Block Size:         8K
               Shared Pool Size:       152M                 Log Buffer:     2,048K
    
    Load Profile                            Per Second       Per Transaction
    ~~~~~~~~~~~~                       ---------------       ---------------
                      Redo size:             24,280.23              7,651.63
                  Logical reads:             34,763.94             10,955.44
                  Block changes:                153.80                 48.47
                 Physical reads:                  3.66                  1.15
                Physical writes:                  3.14                  0.99
                     User calls:                 75.44                 23.77
                         Parses:                 39.34                 12.40
                    Hard parses:                  4.61                  1.45
                          Sorts:                  9.91                  3.12
                         Logons:                  0.00                  0.00
                       Executes:                 62.84                 19.80
                   Transactions:                  3.17
    
      % Blocks changed per Read:    0.44    Recursive Call %:    82.97
     Rollback per transaction %:    5.51       Rows per Sort:   482.30
    
    Instance Efficiency Percentages
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                Buffer Nowait %:  100.00       Redo NoWait %:  100.00
                Buffer  Hit   %:   99.99    In-memory Sort %:  100.00
                Library Hit   %:   92.14        Soft Parse %:   88.28
             Execute to Parse %:   37.40         Latch Hit %:   99.75
    Parse CPU to Parse Elapsd %:   99.59     % Non-Parse CPU:   71.41
    
     Shared Pool Statistics        Begin   End
                                   ------  ------
                 Memory Usage %:   90.63   91.97
        % SQL with executions>1:   52.88   62.10
      % Memory for SQL w/exec>1:   50.72   55.24
    
    Top 5 Timed Events                                                    Avg %Total
    ~~~~~~~~~~~~~~~~~~                                                   wait   Call
    Event                                            Waits    Time (s)   (ms)   Time
    ----------------------------------------- ------------ ----------- ------ ------
    CPU time                                                     1,901          96.3
    log file sync                                    5,930          29      5    1.5
    log file parallel write                          6,676          27      4    1.4
    Log archive I/O                                    275           5     17     .2
    log file sequential read                           106           3     29     .2
              -------------------------------------------------------------
    Host CPU  (CPUs: 7)
    ~~~~~~~~              Load Average
                          Begin     End      User  System    Idle     WIO     WCPU
                        ------- -------   ------- ------- ------- ------- --------
                           1.05    0.82     19.63    7.17   73.21    1.52    0.08
    
    Instance CPU
    ~~~~~~~~~~~~
                  % of total CPU for Instance:   14.46
                  % of busy  CPU for Instance:   53.96
      %DB time waiting for CPU - Resource Mgr:
    
    Memory Statistics                       Begin          End
    ~~~~~~~~~~~~~~~~~                ------------ ------------
                      Host Mem (MB):     15,976.1     15,976.1
                       SGA use (MB):        400.0        400.0
                       PGA use (MB):        135.2        131.8
        % Host Mem used for SGA+PGA:          3.3          3.3
              -------------------------------------------------------------
    
    Time Model System Stats  DB/Inst: PRD/PRD  Snaps: 11-21
    -> Ordered by % of DB time desc, Statistic name
    
    Statistic                                       Time (s) % of DB time
    ----------------------------------- -------------------- ------------
    DB CPU                                           1,859.7         98.7
    sql execute elapsed time                         1,305.3         69.3
    parse time elapsed                                 667.4         35.4
    hard parse elapsed time                            654.9         34.7
    hard parse (sharing criteria) elaps                 51.0          2.7
    hard parse (bind mismatch) elapsed                  44.8          2.4
    PL/SQL compilation elapsed time                     20.7          1.1
    sequence load elapsed time                           3.3           .2
    PL/SQL execution elapsed time                        1.9           .1
    connection management call elapsed                   0.6           .0
    repeated bind elapsed time                           0.2           .0
    failed parse elapsed time                            0.0           .0
    DB time                                          1,884.9
    background elapsed time                             50.6
    background cpu time                                 20.1
              -------------------------------------------------------------
    Wait Events  DB/Inst: PRD/PRD  Snaps: 11-21
    -> s - second, cs - centisecond,  ms - millisecond, us - microsecond
    -> %Timeouts:  value of 0 indicates value was < .5%.  Value of null is truly 0
    -> Only events with Total Wait Time (s) >= .001 are shown
    -> ordered by Total Wait Time desc, Waits desc (idle events last)
    
                                                                        Avg
                                                    %Time Total Wait   wait    Waits
    Event                                    Waits  -outs   Time (s)   (ms)     /txn
    --------------------------------- ------------ ------ ---------- ------ --------
    log file sync                            5,930      0         29      5      1.0
    log file parallel write                  6,676      0         27      4      1.1
    Log archive I/O                            275      0          5     17      0.0
    log file sequential read                   106      0          3     29      0.0
    db file sequential read                  1,632      0          3      2      0.3
    db file parallel write                   1,801      0          3      2      0.3
    control file parallel write                761      0          1      2      0.1
    db file scattered read                     763      0          0      0      0.1
    log file switch completion                   3      0          0     89      0.0
    SQL*Net more data to client              1,673      0          0      0      0.3
    log file single write                        4      0          0     33      0.0
    latch: shared pool                         159      0          0      1      0.0
    control file sequential read             1,363      0          0      0      0.2
    Data file init write                        25      0          0      1      0.0
    rdbms ipc reply                             20      0          0      1      0.0
    db file single write                        12      0          0      1      0.0
    direct path write                           13      0          0      0      0.0
    enq: TX - row lock contention                1      0          0      5      0.0
    LGWR wait for redo copy                     61      0          0      0      0.0
    latch: cache buffers chains                 78      0          0      0      0.0
    buffer busy waits                            8      0          0      0      0.0
    SQL*Net break/reset to client                2      0          0      1      0.0
    SQL*Net message from client            132,807      0     28,416    214     22.5
    Streams AQ: qmn slave idle wait             65      0      1,800  27685      0.0
    Streams AQ: qmn coordinator idle           140     54      1,800  12854      0.0
    SQL*Net message to client              132,808      0          0      0     22.5
    SQL*Net more data from client            5,418      0          0      0      0.9
              -------------------------------------------------------------
    again, I am on 10G and I only see CPU is problem but not sure where to tackle beside the two query and I put a question on here earlier. Do you see anything that you can recommend?

    btw, log file sync is another issue, log buffer is 2M now and I am thinking to increase to 10M, your thoughts?


    thanks
    Last edited by matthew00; 09-16-2007 at 05:53 PM.

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    not possible to notice the difference between 0 secs and 1.5 secs

    anyway, need both sets of data to compare

Posting Permissions

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


Click Here to Expand Forum to Full Width