1 2 Previous Next 17 Replies Latest reply: Jan 22, 2009 3:27 AM by Jonathan Lewis RSS

    update seg$  top 2nd in SQL ordered by Gets in statspack report

    615488
      Hi,

      oracle version:8.1.7.0.0.

      i had generated statspack report in that sql order by gets :

      SQL ordered by Gets for DB: PINDB Instance: pindb Snaps: 203 -204
      -> End Buffer Gets Threshold: 10000
      -> Note that resources reported for PL/SQL includes the resources used by
      all SQL statements called within the PL/SQL code. As individual SQL
      statements are also reported, it is possible and valid for the summed
      total % to exceed 100

      Buffer Gets Executions Gets per Exec % Total Hash Value
      --------------- ------------ -------------- ------- ------------
      57,332,137 71 807,494.9 36.3 1161928628
      select C.BILL_OBJ_ID0,B.BUFFER_BUF,A.ACCOUNT_NO,NVL(BB.START_T,0
      ),NVL(BB.END_T,0), nvl((select bill_no from bill_t where
      account_obj_id0 =A.POID_ID0 and bill_no like 'PO%' and end_t=( s
      elect max(end_t) from bill_t where account_obj_id0 =A.POID_I
      D0 and start_t!=end_t and bill_no like 'PO%' and end_t < (se

      23,853,967 1,860 12,824.7 15.1 2249281901
      update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
      =:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13
      , 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh
      int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1
      and file#=:2 and block#=:3



      i could not understand what this query (update seg$ set ) is this and why it is taking more intensive query(cpu heavy sql)

      Thanks

      Prakash

      Edited by: prakashdba on Jan 17, 2009 6:26 AM

      Edited by: prakashdba on Jan 17, 2009 6:28 AM
        • 1. Re: update seg$  top 2nd in SQL ordered by Gets in statspack report
          Charles Hooper
          seg$ is related to segment management (extent allocation/deallocation, for instance) in dictionary managed tablespaces.

          See "Structures in the Data Dictionary":
          http://download.oracle.com/docs/cd/A58617_01/server.804/a58397/ch17.htm

          While possibly not the cause, you might want to make certain that your temp tablespace is a true temp tablespace ideally located in a locally managed tablespace, and the users are set to use that as their default temp tablespace. It has been a while, but I believe that there are some issues (bugs) related to locally managed tablespaces in the base release of Oracle 8.1.7, so you may want to consult Metalink before considering moving all data and indexes to locally managed tablespaces in order to limit updates of seg$.

          Charles Hooper
          IT Manager/Oracle DBA
          K&M Machine-Fabricating, Inc.
          • 2. Re: update seg$  top 2nd in SQL ordered by Gets in statspack report
            615488
            Hi Charles Hooper,

            Thanks for your inputs.

            we have oracle user called PIN where application will interact with the database. the statspack report was taken on peak hour may oracle server is doing allocating and deallocating of extents in temporary tablespace .

            SQL> select temporary_tablespace from dba_users where username='PIN';

            TEMPORARY_TABLESPACE
            ------------------------------
            PINTEMP

            SQL> select tablespace_name, extent_management, contents from dba_tablespaces where tablespace_name='PINTEMP';

            TABLESPACE_NAME EXTENT_MAN CONTENTS
            ------------------------------ ---------- ---------
            PINTEMP DICTIONARY TEMPORARY

            SQL> select default_tablespace from dba_users where username='PIN';

            DEFAULT_TABLESPACE
            ------------------------------
            PIN00

            SQL> select tablespace_name, extent_management from dba_tablespaces where tablespace_name='PIN00';

            TABLESPACE_NAME EXTENT_MAN
            ------------------------------ ----------
            PIN00 DICTIONARY




            so this heavy cpu sql can be overcome by creating locally managed temp tablespace, your advice is greately appreciated.


            Thanks

            Prakash
            • 3. Re: update seg$  top 2nd in SQL ordered by Gets in statspack report
              Charles Hooper
              prakashdba wrote:
              so this heavy cpu sql can be overcome by creating locally managed temp tablespace, your advice is greately appreciated.


              Thanks

              Prakash
              Prakash,

              Having the temporary tablespace defined as a permanent dictionary managed tablespace is one of the possibilities for excessive calls to update seg$. Based on what you posted, for at least SQL statements executed by user PIN, it appears that a dictionary managed temporary tablespace will be used. One possibility for reducing the update seg$ calls for user PIN is to create a locally managed temporary tablespace with a uniform 1MB extent size. This may or may not make a significant difference in CPU utilization caused by a deduction in the amount of logical IO to update the data dictionary.

              Suggestions:
              * Post the entire Statspack report so that people reading the forum may be able to help you determine if there are other possible causes of the CPU utilization. If you do post the entire Statspack report use the { code } tag (without spaces) before and after the Statspack report so that the spacing in the report is not lost.
              * Enable a 10046 trace at level 8 (wait events) or level 12 (wait events with bind variable capture) for the sessions used by the user PIN - this trace file will tell you the source of the CPU consumption for the session. Search this forum for methods of enabling a 10046 trace and for manually decoding a 10046 trace file (I previously described how to enable 10046 trace files and how to decode those files).

              Charles Hooper
              IT Manager/Oracle DBA
              K&M Machine-Fabricating, Inc.
              • 4. Re: update seg$  top 2nd in SQL ordered by Gets in statspack report
                615488
                Hi Charles Hooper,

                i am unbale to post the entire report since the limit of my post
                Having the temporary tablespace defined as a permanent dictionary managed tablespace is one of the possibilities for excessive calls to update seg$.
                SQL> select tablespace_name, contents, extent_management from dba_tablespaces where tablespace_name='PINTEMP';

                TABLESPACE_NAME CONTENTS EXTENT_MAN
                ------------------------------ --------- ----------
                PINTEMP TEMPORARY DICTIONARY
                STATSPACK report for
                
                DB Name         DB Id    Instance     Inst Num Release     OPS Host
                ------------ ----------- ------------ -------- ----------- --- ------------
                PINDB         3730403337 pindb               1 8.1.7.0.0   NO  suportal01
                
                                Snap Id     Snap Time      Sessions
                                ------- ------------------ --------
                 Begin Snap:        213 16-Jan-09 11:59:11      192
                   End Snap:        214 16-Jan-09 12:41:53      192
                    Elapsed:                  42.70 (mins)
                
                Cache Sizes
                ~~~~~~~~~~~
                           db_block_buffers:     409600          log_buffer:     163840
                              db_block_size:       4096    shared_pool_size: 1310720000
                
                Load Profile
                ~~~~~~~~~~~~                            Per Second       Per Transaction
                                                   ---------------       ---------------
                                  Redo size:             64,597.38             11,022.94
                              Logical reads:             70,627.02             12,051.85
                              Block changes:                321.39                 54.84
                             Physical reads:             19,396.30              3,309.80
                            Physical writes:                995.68                169.90
                                 User calls:                258.40                 44.09
                                     Parses:                 78.70                 13.43
                                Hard parses:                  1.41                  0.24
                                      Sorts:                 23.18                  3.96
                                     Logons:                  1.47                  0.25
                                   Executes:                244.28                 41.68
                               Transactions:                  5.86
                
                  % Blocks changed per Read:    0.46    Recursive Call %:   71.46
                 Rollback per transaction %:    4.12       Rows per Sort: #######
                
                Instance Efficiency Percentages (Target 100%)
                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                            Buffer Nowait %:   99.99       Redo NoWait %:  100.00
                            Buffer  Hit   %:   72.54    In-memory Sort %:   99.79
                            Library Hit   %:   98.95        Soft Parse %:   98.21
                         Execute to Parse %:   67.78         Latch Hit %:   99.93
                Parse CPU to Parse Elapsd %:   34.48     % Non-Parse CPU:   99.99
                
                 Shared Pool Statistics        Begin   End
                                               ------  ------
                             Memory Usage %:   81.25   82.72
                    % SQL with executions>1:   70.55   69.97
                  % Memory for SQL w/exec>1:   40.98   40.97
                
                Top 5 Wait Events
                ~~~~~~~~~~~~~~~~~                                             Wait     % Total
                Event                                               Waits  Time (cs)   Wt Time
                -------------------------------------------- ------------ ------------ -------
                db file sequential read                         4,790,767        2,274   90.67
                direct path write                                 258,972          202    8.05
                direct path read                                  206,976           13     .52
                log buffer space                                        7            8     .32
                latch free                                          8,320            5     .20
                          -------------------------------------------------------------
                Wait Events for DB: PINDB  Instance: pindb  Snaps: 213 -214
                -> cs - centisecond -  100th of a second
                -> ms - millisecond - 1000th of a second
                -> ordered by wait time desc, waits desc (idle events last)
                
                                                                                    Avg
                                                                     Total Wait    wait  Waits
                Event                               Waits   Timeouts  Time (cs)    (ms)   /txn
                ---------------------------- ------------ ---------- ----------- ------ ------
                db file sequential read         4,790,767          0       2,274      0  319.1
                direct path write                 258,972          0         202      0   17.2
                direct path read                  206,976          0          13      0   13.8
                log buffer space                        7          0           8     11    0.0
                latch free                          8,320      8,014           5      0    0.6
                control file sequential read          479          0           4      0    0.0
                refresh controlfile command            10          0           2      2    0.0
                db file scattered read          7,832,262          0           0      0  521.7
                SQL*Net more data to client       199,280          0           0      0   13.3
                file open                          72,190          0           0      0    4.8
                log file parallel write            23,251          2           0      0    1.5
                direct path read (lob)             21,592          0           0      0    1.4
                db file parallel write             20,133          0           0      0    1.3
                buffer busy waits                  17,416          9           0      0    1.2
                log file sync                      15,153          7           0      0    1.0
                enqueue                            10,486      2,664           0      0    0.7
                log file sequential read            4,812          0           0      0    0.3
                SQL*Net more data from dblin        3,085          0           0      0    0.2
                SQL*Net message from dblink         2,614          0           0      0    0.2
                SQL*Net message to dblink           2,614          0           0      0    0.2
                direct path write (lob)             2,005          0           0      0    0.1
                control file parallel write           981          0           0      0    0.1
                SQL*Net break/reset to clien          786          0           0      0    0.1
                LGWR wait for redo copy                48          3           0      0    0.0
                file identify                          24          0           0      0    0.0
                log file single write                  12          0           0      0    0.0
                log file switch completion              8          0           0      0    0.0
                single-task message                     1          0           0      0    0.0
                SQL*Net message from client       650,041          0     252,433      4   43.3
                SQL*Net message to client         650,045          0           0      0   43.3
                SQL*Net more data from clien       29,131          0           0      0    1.9
                Null event                          2,474      2,474           0      0    0.2
                virtual circuit status                252        252           0      0    0.0
                          -------------------------------------------------------------
                Background Wait Events for DB: PINDB  Instance: pindb  Snaps: 213 -214
                -> ordered by wait time desc, waits desc (idle events last)
                
                                                                                    Avg
                                                                     Total Wait    wait  Waits
                Event                               Waits   Timeouts  Time (cs)    (ms)   /txn
                ---------------------------- ------------ ---------- ----------- ------ ------
                log file parallel write            23,251          2           0      0    1.5
                db file parallel write             20,132          0           0      0    1.3
                log file sequential read            4,812          0           0      0    0.3
                file open                           3,996          0           0      0    0.3
                direct path read                    1,914          0           0      0    0.1
                control file parallel write           981          0           0      0    0.1
                direct path write                     636          0           0      0    0.0
                control file sequential read          361          0           0      0    0.0
                db file scattered read                180          0           0      0    0.0
                db file sequential read               143          0           0      0    0.0
                latch free                            105        103           0      0    0.0
                LGWR wait for redo copy                48          3           0      0    0.0
                file identify                          24          0           0      0    0.0
                log file single write                  12          0           0      0    0.0
                rdbms ipc message                  95,057      7,944           0      0    6.3
                pmon timer                            831        829           0      0    0.1
                smon timer                              8          7           0      0    0.0
                Null event                              1          1           0      0    0.0
                          -------------------------------------------------------------
                SQL ordered by Gets for DB: PINDB  Instance: pindb  Snaps: 213 -214
                -> End Buffer Gets Threshold:   10000
                -> Note that resources reported for PL/SQL includes the resources used by
                   all SQL statements called within the PL/SQL code.  As individual SQL
                   statements are also reported, it is possible and valid for the summed
                   total % to exceed 100
                
                  Buffer Gets    Executions  Gets per Exec  % Total  Hash Value
                --------------- ------------ -------------- ------- ------------
                     39,902,588           43      927,967.2    22.1   1161928628
                select C.BILL_OBJ_ID0,B.BUFFER_BUF,A.ACCOUNT_NO,NVL(BB.START_T,0
                ),NVL(BB.END_T,0),    nvl((select bill_no from bill_t     where
                account_obj_id0 =A.POID_ID0 and bill_no like 'PO%' and end_t=( s
                elect max(end_t) from bill_t     where account_obj_id0 =A.POID_I
                D0 and start_t!=end_t and bill_no like 'PO%'     and end_t < (se
                
                     18,684,805        1,353       13,809.9    10.3   2249281901
                update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
                =:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13
                , 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh
                int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1
                 and file#=:2 and block#=:3
                
                
                :
                :
                :
                :
                :
                
                
                File IO Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                ->ordered by Tablespace, File
                
                Tablespace               Filename
                ------------------------ ----------------------------------------------------
                                 Av      Av     Av                    Av        Buffer Av Buf
                         Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
                -------------- ------- ------ ------- ------------ -------- ---------- ------
                RBS                      /oracle3/oradata/pindb/rbs01.dbf
                           151       0    0.0     1.0       25,364       10         18    0.0
                                         /oracle7/oradata/rbs02.dbf
                           109       0    0.0     1.0       10,538        4          0
                
                SYSTEM                   /oracle1/oradata/pindb/system01.dbf
                         3,223       1    0.0     1.8        5,228        2          0
                                         /oracle1/oradata/pindb/system02.dbf
                           791       0    0.0     1.5           28        0          0
                
                TEMP                     /oracle13/oradata/pindb/temp01.dbf
                         1,680       1    0.0     8.1          816        0          0
                                         /oracle4/oradata/pindb/temp01.dbf
                             3       0    0.0     1.0            3        0          0
                
                TOOLS                    /oracle14/oradata/pindb/tools01.dbf
                             3       0    0.0     1.0            3        0          0
                
                          -------------------------------------------------------------
                Buffer Pool Statistics for DB: PINDB  Instance: pindb  Snaps: 213 -214
                -> Pools   D: default pool,  K: keep pool,  R: recycle pool
                
                                                                      Free    Write     Buffer
                       Buffer    Consistent    Physical   Physical  Buffer Complete       Busy
                P        Gets          Gets       Reads     Writes   Waits    Waits      Waits
                - ----------- ------------- ----------- ---------- ------- -------- ----------
                D  47,803,200             0  47,757,603    234,201       0        0     17,415
                          -------------------------------------------------------------
                
                
                
                
                
                Buffer wait Statistics for DB: PINDB  Instance: pindb  Snaps: 213 -214
                -> ordered by wait time desc, waits desc
                
                                                 Tot Wait    Avg
                Class                    Waits  Time (cs) Time (cs)
                ------------------ ----------- ---------- ---------
                data block              11,674          0         0
                segment header           5,628          0         0
                bitmap block                87          0         0
                undo header                 20          0         0
                bitmap index block           7          0         0
                          -------------------------------------------------------------
                
                
                
                
                
                Enqueue activity for DB: PINDB  Instance: pindb  Snaps: 213 -214
                -> ordered by waits desc, gets desc
                
                Enqueue            Gets      Waits
                ---------- ------------ ----------
                HW                5,673      2,283
                ST                2,642         68
                TX               22,460         19
                          -------------------------------------------------------------
                Rollback Segment Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                ->A high value for "Pct Waits" suggests more rollback segments may be required
                
                       Trans Table      Pct   Undo Bytes
                RBS No     Gets       Waits     Written        Wraps  Shrinks  Extends
                ------ ------------ ------- --------------- -------- -------- --------
                     0          9.0    0.00               0        0        0        0
                     1      2,588.0    0.00       1,951,754        5        0        0
                     2         12.0    0.00             544        0        0        0
                     3      2,900.0    0.00       2,089,014        5        0        0
                     4      2,746.0    0.00       2,042,170        5        0        0
                     5         22.0    0.00               0        0        0        0
                     6      2,530.0    0.00       1,863,098        2        0        0
                     7      2,466.0    0.00       1,761,994        2        0        0
                     8      2,938.0    0.00       2,172,908        5        0        0
                     9      1,435.0    0.00         619,208        2        0        0
                    10      2,648.0    0.00       1,821,670        4        0        0
                    11      4,300.0    0.00       4,429,678       11        0        0
                    12      2,635.0    0.00       1,922,330        5        0        0
                    13      2,787.0    0.00       1,951,722        5        0        0
                    14      2,227.0    0.00       1,711,232        4        0        0
                    15      2,856.0    0.00       2,177,332        5        0        0
                    16      2,768.0    0.00       2,005,722        4        0        0
                    17      2,602.0    0.00       1,848,938        4        0        0
                    18      2,768.0    0.00       1,950,978        4        0        0
                    19      3,027.0    0.00       2,402,778        5        0        0
                    20      2,572.0    0.00       1,845,408        4        0        0
                    21      2,831.0    0.00       2,124,440        5        0        0
                    23      2,711.0    0.00       2,068,342        1        0        0
                    24      3,010.0    0.00       2,208,754      171        0        0
                    26      2,685.0    0.00       1,941,768        0        0        0
                          -------------------------------------------------------------
                Rollback Segment Storage for DB: PINDB  Instance: pindb  Snaps: 213 -214
                ->Optimal Size should be larger than Avg Active
                
                RBS No    Segment Size      Avg Active    Optimal Size    Maximum Size
                ------ --------------- --------------- --------------- ---------------
                     0         671,744           6,144                         671,744
                     1       4,190,208         523,811       4,194,304      13,627,392
                     2       4,190,208         523,636       4,194,304       5,763,072
                     3       4,190,208         523,858       4,194,304       5,763,072
                     4       4,190,208         523,701       4,194,304      10,481,664
                     5       4,190,208         523,564       4,194,304      30,928,896
                     6       4,714,496         944,059       4,194,304       6,811,648
                     7       4,714,496         919,543       4,194,304       4,714,496
                     8       4,190,208         523,759       4,194,304      11,530,240
                     9       4,190,208         523,759       4,194,304       7,335,936
                    10       4,190,208         523,564       4,194,304       5,763,072
                    11       4,190,208       1,136,667       4,194,304      14,675,968
                    12       4,190,208         523,811       4,194,304      12,054,528
                    13       4,190,208         523,759       4,194,304      22,540,288
                    14       4,190,208         523,564       4,194,304       5,763,072
                    15       4,190,208         523,636       4,194,304      11,005,952
                    16       4,190,208         523,939       4,194,304      16,773,120
                    17       4,190,208         523,564       4,194,304      41,938,944
                    18       4,190,208         523,939       4,194,304       4,190,208
                    19       4,190,208         523,564       4,194,304      13,627,392
                    20       4,190,208         523,759       4,194,304      24,637,440
                    21       4,231,168         528,801       4,194,304      10,604,544
                    23     838,856,704      58,526,127     838,860,800     838,856,704
                    24      19,496,960          17,219     209,715,200      19,496,960
                    26      83,881,984      10,486,530     209,715,200      83,881,984
                          -------------------------------------------------------------
                
                :
                :
                :
                :
                
                
                SGA Memory Summary for DB: PINDB  Instance: pindb  Snaps: 213 -214
                
                SGA regions                       Size in Bytes
                ------------------------------ ----------------
                Database Buffers                  1,677,721,600
                Fixed Size                              102,076
                Redo Buffers                            180,224
                Variable Size                     1,514,905,600
                                               ----------------
                sum                               3,192,909,500
                          -------------------------------------------------------------
                
                
                SGA breakdown difference for DB: PINDB  Instance: pindb  Snaps: 213 -214
                
                Pool        Name                        Begin value      End value  Difference
                ----------- ------------------------ -------------- -------------- -----------
                java pool   free memory                  20,971,520     20,971,520           0
                large pool  PX msg pool                  26,738,712     26,738,712           0
                large pool  free memory                  46,661,608     46,661,608           0
                shared pool Checkpoint queue                673,120        673,120           0
                shared pool DML locks                       491,232        491,232           0
                shared pool KGFF heap                       153,936        153,936           0
                shared pool KGK heap                         20,488         20,488           0
                shared pool KQLS heap                     1,480,992      1,452,624     -28,368
                shared pool PL/SQL DIANA                    558,312        558,312           0
                shared pool PL/SQL MPCODE                   189,832        195,800       5,968
                shared pool PL/SQL PPCODE                    12,184         12,184           0
                shared pool PLS non-lib hp                    2,136          2,136           0
                shared pool PX subheap                      251,264        251,264           0
                shared pool State objects                 1,284,480      1,284,480           0
                shared pool VIRTUAL CIRCUITS              1,388,744      1,388,744           0
                shared pool db_block_buffers             85,196,800     85,196,800           0
                shared pool db_block_hash_buckets        13,861,328     13,861,328           0
                shared pool db_handles                      528,000        528,000           0
                shared pool dictionary cache             11,542,480     12,263,728     721,248
                shared pool enqueue_resources               337,376        337,376           0
                shared pool event statistics per ses      2,308,880      2,308,880           0
                shared pool fixed allocation callbac          1,904          1,904           0
                shared pool free memory                 266,342,128    245,425,320 -20,916,808
                shared pool ktlbk state objects             391,816        391,816           0
                shared pool library cache               193,833,056    197,227,432   3,394,376
                shared pool miscellaneous                 3,433,488      3,433,608         120
                shared pool processes                       643,200        643,200           0
                shared pool sessions                      1,617,280      1,617,280           0
                shared pool simulator trace entries         400,000        400,000           0
                shared pool sql area                    434,592,840    449,453,240  14,860,400
                shared pool state objects               397,783,304    399,742,536   1,959,232
                shared pool table columns                    39,688         41,192       1,504
                shared pool table definiti                   17,016         19,416       2,400
                shared pool transactions                  1,111,120      1,111,120           0
                shared pool trigger defini                    2,200          2,128         -72
                shared pool trigger inform                      664            664           0
                            db_block_buffers          1,677,721,600  1,677,721,600           0
                            fixed_sga                       102,076        102,076           0
                            log_buffer                      163,840        163,840           0
                          -------------------------------------------------------------
                init.ora Parameters for DB: PINDB  Instance: pindb  Snaps: 213 -214
                
                                                                                  End value
                Parameter Name                Begin value                       (if different)
                ----------------------------- --------------------------------- --------------
                _db_file_noncontig_mblock_rea 1
                background_dump_dest          /oracle/admin/pindb/bdump
                compatible                    8.1.0
                control_file_record_keep_time 30
                control_files                 /oracle1/oradata/pindb/control01.
                core_dump_dest                /oracle/admin/pindb/cdump
                cursor_space_for_time         TRUE
                db_block_buffers              409600
                db_block_lru_latches          20
                db_block_size                 4096
                db_file_multiblock_read_count 8
                db_files                      300
                db_name                       pindb
                db_writer_processes           8
                distributed_transactions      20
                hash_area_size                8388608
                hash_multiblock_io_count      8
                instance_name                 pindb
                java_pool_size                20971520
                job_queue_interval            60
                job_queue_processes           4
                large_pool_size               73400320
                log_archive_dest_1            location=/oratranslog
                log_archive_format            arch_%t_%s.arc
                log_archive_start             TRUE
                log_buffer                    163840
                log_checkpoint_interval       1048576
                max_enabled_roles             30
                mts_dispatchers               (PROTOCOL=TCP)(PRE=oracle.aurora.
                mts_max_dispatchers           20
                mts_max_servers               20
                mts_servers                   3
                open_cursors                  700
                open_links                    4
                optimizer_index_cost_adj      30
                os_authent_prefix
                parallel_automatic_tuning     TRUE
                parallel_threads_per_cpu      8
                processes                     600
                query_rewrite_enabled         TRUE
                remote_login_passwordfile     EXCLUSIVE
                rollback_segments             pinroll, pinroll02, pinroll03
                service_names                 pindb
                session_cached_cursors        35
                sessions                      665
                shared_pool_size              1310720000
                sort_area_retained_size       2097152
                sort_area_size                104857600
                sort_multiblock_read_count    8
                star_transformation_enabled   true
                timed_statistics              FALSE
                user_dump_dest                /oracle/admin/pindb/udump
                utl_file_dir                  /oracle2
                          -------------------------------------------------------------
                
                End of Report
                Edited by: prakashdba on Jan 17, 2009 9:06 AM

                Edited by: prakashdba on Jan 17, 2009 9:07 AM
                • 5. Re: update seg$  top 2nd in SQL ordered by Gets in statspack report
                  Charles Hooper
                  Prakash,

                  I previously stated (bold added):
                  "Having the temporary tablespace defined as a permanent dictionary managed tablespace is one of the possibilities for excessive calls to update seg$. *Based on what you posted, for at least SQL statements executed by user PIN, it appears that a dictionary managed temporary tablespace will be used* ."

                  How many CPUs are in the server?

                  Please post the remaining sections of the Statspack report in another reply to this thread. Specifically, I would like to see the section titled "Statistic".

                  It appears that timed_statistics was not enabled when the Statspack Report was created. Please enabled that parameter and collect another Statspack report. Ideally, you should keep the duration of the Statspack report short, possibly 10 minutes, unless the process you are trying to troubleshoot lasts much longer than that.

                  A couple comments, walking through what you posted from top to bottom:
                  Duration of the Statspack is 42.70 (mins) = 2562 seconds
                  db_block_buffers: 409600 =1600M
                  log_buffer: 163840 = 0.15625M
                  23 sorts per second
                  5.86 transactions per second
                  Rows per Sort: ####### (number too large to be printed)
                  In-memory Sort %: 99.79 (most sorts are in memory - this may be misleading, so below)
                  Parse CPU to Parse Elapsd %: 34.48 - might be interesting to investigate
                  db file sequential read (single block reads) 4,790,767 waits requiring 22.74 seconds = 0.000004746630 per single block read (likely from file system cache)
                  direct path write (typically write to temp) 258,972 waits requiring 2.02 seconds
                  direct path read (typically read from temp) 206,976 waits requiring 0.13 seconds
                  log buffer space (possibly indicates log_buffer is too small) 7 waits requiring 0.08 seconds
                  db file scattered read (multiple block read) 7,832,262 requiring less than 0.01 seconds (I can't explain this short duration, a bug, or related to timed_statistics)

                  SYSTEM and TEMP tablespace are showing a lot of activity, what tablespace contains the user data for user PIN?

                  Buffer pool statistics shows 0 consistent gets (interesting?)?

                  33% of the buffer busy waits are related to the segment header, which might indicate freelist contention, but in this case I am not certain that is true.

                  While enqueue is reported as requiring less that 0.01 seconds of time, there were 2,283 waits for the high watermark enqueue indicating that the high watermark for the segments were being adjusted, and multiple sessions were requesting the change to the high watermark.

                  The optimal size is specified for each rollback segment, and the optimal size in most cases is much smaller than the maximum size. This may or may not be an issue in this particular case (the rollback segments will shrink back to the optimal size, possibly leading to performance issues).

                  db_file_multiblock_read_count = 8 (of 4KB blocks, multiblock read size is a very small at 32KB)
                  db_writer_processes = 8 (this may be a problem)
                  hash_area_size = 8M (small compared to sort_area_size, defaults to twice sort_area_size)
                  log_buffer = 0.15625M (this seems small)
                  sort_area_retained_size = 2M
                  sort_area_size = 100M (note that there is a potential for problems when this differs from sort_area_retained_size - see below)
                  timed_statistics = FALSE (should be set to TRUE if you are investigating performance issues)

                  Paraphrased from "Cost-Based Oracle Fundamentals"
                  "SORT_AREA_RETAINED_SIZE should be set to the same value as SORT_AREA_SIZE. Setting the value of SORT_AREA_RETAINED_SIZE to a smaller value may cause the physical writes direct statistic to increase due to Oracle dumping the entire data set to disk before returning the result to the client. This behavior will not show as a sort to disk. Memory is first allocated from the UGA until the SORT_AREA_RETAINED_SIZE is reached, then memory is allocated from the PGA. Even if the sort completed entirely in memory, the entire dataset must be dumped to disk if it is split between the UGA and PGA."

                  I would recommend:
                  Decreasing the value of SORT_AREA_SIZE (100MB seems large)
                  Increasing the value of SORT_AREA_RETAINED_SIZE to be the same size as SORT_AREA_SIZE
                  Increasing the value of LOG_BUFFER, possibly to 1M (although this will likely not result in much of a performance increase in your case)
                  Setting TIMED_STATISTICS to TRUE
                  Determining why DB_WRITER_PROCESSES is set to 8
                  Capturing another Statspack report after implementing/reviewing the above - a shorter duration report might help.

                  Charles Hooper
                  IT Manager/Oracle DBA
                  K&M Machine-Fabricating, Inc.
                  • 6. Re: update seg$  top 2nd in SQL ordered by Gets in statspack report
                    Jonathan Lewis
                    I've written up a quick note about this extract [on my blog|http://jonathanlewis.wordpress.com/2009/01/18/analysing-statspack-11/]
                    . It looks like you've really smashed up your seg$/uet$ cluster, and the optimizer is a bit confused as a result.

                    In passing, the location of the second file in your RBS tablespace doesn't match the conventions of the rest of the files - you might want to correct this some time to reduce the risk of error in backup or recovery.

                    Regards
                    Jonathan Lewis
                    http://jonathanlewis.wordpress.com
                    http://www.jlcomp.demon.co.uk

                    "The temptation to form premature theories upon insufficient data is the bane of our profession."
                    Sherlock Holmes (Sir Arthur Conan Doyle) in "The Valley of Fear".
                    • 7. Re: update seg$  top 2nd in SQL ordered by Gets in statspack report
                      615488
                      hi Charles Hooper,

                      How many CPUs are in the server?
                      SQL> show parameter cpu

                      NAME TYPE VALUE
                      ------------------------------------ ------- ------------------------------
                      cpu_count integer 4
                      parallel_threads_per_cpu integer 8

                      Please post the remaining sections of the Statspack report in another reply to this thread. Specifically, I would like to see the section titled "Statistic".
                      
                      It appears that timed_statistics was not enabled when the Statspack Report was created
                      
                      timed_statistics = FALSE (should be set to TRUE if you are investigating performance issues)
                      i enabled timed_statistics when i was taking statspack report connecting to perfstat user using;
                      sql>alter session set timed_statistics=true;

                      i am posting entire statspack report
                      SYSTEM and TEMP tablespace are showing a lot of activity, what tablespace contains the user data for user PIN?
                      PIN00
                      Determining why DB_WRITER_PROCESSES is set to 8
                      SQL> show parameter disk_asynch_io

                      NAME TYPE VALUE
                      ------------------------------------ ------- ------------------------------
                      disk_asynch_io boolean TRUE
                      STATSPACK report for
                      
                      DB Name         DB Id    Instance     Inst Num Release     OPS Host
                      ------------ ----------- ------------ -------- ----------- --- ------------
                      PINDB         3730403337 pindb               1 8.1.7.0.0   NO  suportal01
                      
                                      Snap Id     Snap Time      Sessions
                                      ------- ------------------ --------
                       Begin Snap:        213 16-Jan-09 11:59:11      192
                         End Snap:        214 16-Jan-09 12:41:53      192
                          Elapsed:                  42.70 (mins)
                      
                      Cache Sizes
                      ~~~~~~~~~~~
                                 db_block_buffers:     409600          log_buffer:     163840
                                    db_block_size:       4096    shared_pool_size: 1310720000
                      
                      Load Profile
                      ~~~~~~~~~~~~                            Per Second       Per Transaction
                                                         ---------------       ---------------
                                        Redo size:             64,597.38             11,022.94
                                    Logical reads:             70,627.02             12,051.85
                                    Block changes:                321.39                 54.84
                                   Physical reads:             19,396.30              3,309.80
                                  Physical writes:                995.68                169.90
                                       User calls:                258.40                 44.09
                                           Parses:                 78.70                 13.43
                                      Hard parses:                  1.41                  0.24
                                            Sorts:                 23.18                  3.96
                                           Logons:                  1.47                  0.25
                                         Executes:                244.28                 41.68
                                     Transactions:                  5.86
                      
                        % Blocks changed per Read:    0.46    Recursive Call %:   71.46
                       Rollback per transaction %:    4.12       Rows per Sort: #######
                      
                      Instance Efficiency Percentages (Target 100%)
                      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                  Buffer Nowait %:   99.99       Redo NoWait %:  100.00
                                  Buffer  Hit   %:   72.54    In-memory Sort %:   99.79
                                  Library Hit   %:   98.95        Soft Parse %:   98.21
                               Execute to Parse %:   67.78         Latch Hit %:   99.93
                      Parse CPU to Parse Elapsd %:   34.48     % Non-Parse CPU:   99.99
                      
                       Shared Pool Statistics        Begin   End
                                                     ------  ------
                                   Memory Usage %:   81.25   82.72
                          % SQL with executions>1:   70.55   69.97
                        % Memory for SQL w/exec>1:   40.98   40.97
                      
                      Top 5 Wait Events
                      ~~~~~~~~~~~~~~~~~                                             Wait     % Total
                      Event                                               Waits  Time (cs)   Wt Time
                      -------------------------------------------- ------------ ------------ -------
                      db file sequential read                         4,790,767        2,274   90.67
                      direct path write                                 258,972          202    8.05
                      direct path read                                  206,976           13     .52
                      log buffer space                                        7            8     .32
                      latch free                                          8,320            5     .20
                                -------------------------------------------------------------
                      Wait Events for DB: PINDB  Instance: pindb  Snaps: 213 -214
                      -> cs - centisecond -  100th of a second
                      -> ms - millisecond - 1000th of a second
                      -> ordered by wait time desc, waits desc (idle events last)
                      
                                                                                          Avg
                                                                           Total Wait    wait  Waits
                      Event                               Waits   Timeouts  Time (cs)    (ms)   /txn
                      ---------------------------- ------------ ---------- ----------- ------ ------
                      db file sequential read         4,790,767          0       2,274      0  319.1
                      direct path write                 258,972          0         202      0   17.2
                      direct path read                  206,976          0          13      0   13.8
                      log buffer space                        7          0           8     11    0.0
                      latch free                          8,320      8,014           5      0    0.6
                      control file sequential read          479          0           4      0    0.0
                      refresh controlfile command            10          0           2      2    0.0
                      db file scattered read          7,832,262          0           0      0  521.7
                      SQL*Net more data to client       199,280          0           0      0   13.3
                      file open                          72,190          0           0      0    4.8
                      log file parallel write            23,251          2           0      0    1.5
                      direct path read (lob)             21,592          0           0      0    1.4
                      db file parallel write             20,133          0           0      0    1.3
                      buffer busy waits                  17,416          9           0      0    1.2
                      log file sync                      15,153          7           0      0    1.0
                      enqueue                            10,486      2,664           0      0    0.7
                      log file sequential read            4,812          0           0      0    0.3
                      SQL*Net more data from dblin        3,085          0           0      0    0.2
                      SQL*Net message from dblink         2,614          0           0      0    0.2
                      SQL*Net message to dblink           2,614          0           0      0    0.2
                      direct path write (lob)             2,005          0           0      0    0.1
                      control file parallel write           981          0           0      0    0.1
                      SQL*Net break/reset to clien          786          0           0      0    0.1
                      LGWR wait for redo copy                48          3           0      0    0.0
                      file identify                          24          0           0      0    0.0
                      log file single write                  12          0           0      0    0.0
                      log file switch completion              8          0           0      0    0.0
                      single-task message                     1          0           0      0    0.0
                      SQL*Net message from client       650,041          0     252,433      4   43.3
                      SQL*Net message to client         650,045          0           0      0   43.3
                      SQL*Net more data from clien       29,131          0           0      0    1.9
                      Null event                          2,474      2,474           0      0    0.2
                      virtual circuit status                252        252           0      0    0.0
                                -------------------------------------------------------------
                      Background Wait Events for DB: PINDB  Instance: pindb  Snaps: 213 -214
                      -> ordered by wait time desc, waits desc (idle events last)
                      
                                                                                          Avg
                                                                           Total Wait    wait  Waits
                      Event                               Waits   Timeouts  Time (cs)    (ms)   /txn
                      ---------------------------- ------------ ---------- ----------- ------ ------
                      log file parallel write            23,251          2           0      0    1.5
                      db file parallel write             20,132          0           0      0    1.3
                      log file sequential read            4,812          0           0      0    0.3
                      file open                           3,996          0           0      0    0.3
                      direct path read                    1,914          0           0      0    0.1
                      control file parallel write           981          0           0      0    0.1
                      direct path write                     636          0           0      0    0.0
                      control file sequential read          361          0           0      0    0.0
                      db file scattered read                180          0           0      0    0.0
                      db file sequential read               143          0           0      0    0.0
                      latch free                            105        103           0      0    0.0
                      LGWR wait for redo copy                48          3           0      0    0.0
                      file identify                          24          0           0      0    0.0
                      log file single write                  12          0           0      0    0.0
                      rdbms ipc message                  95,057      7,944           0      0    6.3
                      pmon timer                            831        829           0      0    0.1
                      smon timer                              8          7           0      0    0.0
                      Null event                              1          1           0      0    0.0
                                -------------------------------------------------------------
                      SQL ordered by Gets for DB: PINDB  Instance: pindb  Snaps: 213 -214
                      -> End Buffer Gets Threshold:   10000
                      -> Note that resources reported for PL/SQL includes the resources used by
                         all SQL statements called within the PL/SQL code.  As individual SQL
                         statements are also reported, it is possible and valid for the summed
                         total % to exceed 100
                      
                        Buffer Gets    Executions  Gets per Exec  % Total  Hash Value
                      --------------- ------------ -------------- ------- ------------
                           39,902,588           43      927,967.2    22.1   1161928628
                      select C.BILL_OBJ_ID0,B.BUFFER_BUF,A.ACCOUNT_NO,NVL(BB.START_T,0
                      ),NVL(BB.END_T,0),    nvl((select bill_no from bill_t     where
                      account_obj_id0 =A.POID_ID0 and bill_no like 'PO%' and end_t=( s
                      elect max(end_t) from bill_t     where account_obj_id0 =A.POID_I
                      D0 and start_t!=end_t and bill_no like 'PO%'     and end_t < (se
                      
                           18,684,805        1,353       13,809.9    10.3   2249281901
                      update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
                      =:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13
                      , 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh
                      int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1
                       and file#=:2 and block#=:3
                      
                           18,680,598        1,353       13,806.8    10.3   1839874543
                      select file#,block#,length from uet$ where ts#=:1 and segfile#=:
                      2 and segblock#=:3 and ext#=:4
                      
                           13,604,531           54      251,935.8     7.5   2873239600
                      select buffer_buf from event_item_transfer_buf_t where obj_id0 =
                       :1 and rec_id = :2
                      
                            5,508,811           43      128,111.9     3.0    169052458
                      select CUST_ACCT_ID ,nvl(IQARA_PARSE_VALUE(PLAN_POID),'NULL') PL
                      AN ,ACCOUNT_STATUS ,lower(WEB_ACCESS_USERID) ,B.POID_ID0 ,REQUES
                      T_TYPE ,NVL(REASON_CODE,'NULL') ,TASK_ID ,B.BILL_TYPE ,A.BILLING
                      _TYPE ,STATUS PORTAL_STATUS ,B.LAST_STATUS_T ,C.PLAN_OBJ_ID0  fr
                      om IQARA_BILLING_TASKS@METASOLV A ,ACCOUNT_T B ,(select distinct
                      
                            3,398,460            2    1,699,230.0     1.9    292842509
                      select (a.created_date),s2diq(b.LAST_STATUS_T),b.account_no,b.po
                      id_id0,plan_obj_id0 from iqara_cust_invoice_t a,account_t b  whe
                      re a.account_no=b.account_no  and b.status=10103  and a.invoice_
                      date>='1-sep-2008'  and a.invoice_date<'1-jan-2009'  and a.creat
                      ed_date>s2diq(b.last_status_t+172800)  AND A.INVOICE_TYPE !=1  -
                      
                            3,186,164           22      144,825.6     1.8   3338395017
                      select upper(a.city) as city,count(bill_type) as count,case when
                       bill_type=10000 then 'Prepaid' when bill_type=10001 then 'Suvid
                      ha' end  from  account_nameinfo_t a, account_t b, (select distin
                      ct obj_id0,plan_obj_id0 from account_products_t where plan_obj_i
                      d0>0) c,  iq_prov_tags_t d where  a.obj_id0 = b.poid_id0 and b.p
                      
                            2,389,751           53       45,089.6     1.3   1094410509
                      select poid_type from iqara_online_dealer_renewals_t where poid_
                      id0 = :1
                      
                            2,317,204           16      144,825.3     1.3   1363642522
                      select upper(a.city) as city,count(bill_type) as count,case when
                       bill_type=10000 then 'Prepaid' when bill_type=10001 then 'Suvid
                      ha' end  from  account_nameinfo_t a, account_t b, (select distin
                      SQL ordered by Gets for DB: PINDB  Instance: pindb  Snaps: 213 -214
                      -> End Buffer Gets Threshold:   10000
                      -> Note that resources reported for PL/SQL includes the resources used by
                         all SQL statements called within the PL/SQL code.  As individual SQL
                         statements are also reported, it is possible and valid for the summed
                         total % to exceed 100
                      
                        Buffer Gets    Executions  Gets per Exec  % Total  Hash Value
                      --------------- ------------ -------------- ------- ------------
                      ct obj_id0,plan_obj_id0 from account_products_t where plan_obj_i
                      d0>0) c,  iq_prov_tags_t d where  a.obj_id0 = b.poid_id0 and b.p
                      
                            2,209,410            2    1,104,705.0     1.2    936641418
                      SELECT UNIQUE a.POID_ID0 as poid,A.ACCOUNT_NO as acno, C.LOGIN,
                       SALUTATION || '' || FIRST_NAME || ' ' || MIDDLE_NAME || ' ' ||
                      LAST_NAME  as NAME   FROM ACCOUNT_T A, (SELECT OBJ_ID0, CURRENT_
                      BAL FROM ACCOUNT_BALANCES_T WHERE REC_ID=1000050) BM1,   (SELECT
                       OBJ_ID0, CURRENT_BAL FROM ACCOUNT_BALANCES_T WHERE REC_ID=10001
                      
                            1,892,571            1    1,892,571.0     1.0    135828115
                      select (a.created_date),s2diq(b.LAST_STATUS_T),b.account_no,b.po
                      id_id0,plan_obj_id0 from iqara_cust_invoice_t a,account_t b  whe
                      re a.account_no=b.account_no  and b.status=10103  and a.invoice_
                      date>='1-sep-2008'  and a.invoice_date<'1-jan-2009'  and a.creat
                      ed_date>s2diq(b.last_status_t+2592000)  --AND A.INVOICE_TYPE !=1
                      
                            1,892,442            1    1,892,442.0     1.0    688649453
                      select (a.created_date),s2diq(b.LAST_STATUS_T) from iqara_cust_i
                      nvoice_t a,account_t b  where a.account_no=b.account_no  and b.s
                      tatus=10103  and a.invoice_date>='1-sep-2008'  and a.invoice_dat
                      e<'1-jan-2009'  and a.created_date>s2diq(b.last_status_t+172800)
                        --and a.PLAN_DESCR=' '
                      
                            1,699,864            1    1,699,864.0     0.9    823194862
                      select (a.created_date),s2diq(b.LAST_STATUS_T),b.account_no,b.po
                      id_id0,plan_obj_id0 from iqara_cust_invoice_t a,account_t b  whe
                      re a.account_no=b.account_no  and b.status=10103  and a.invoice_
                      date>='1-sep-2008'  and a.invoice_date<'1-jan-2009'  --and a.cre
                      ated_date>s2diq(b.last_status_t)  AND A.INVOICE_TYPE !=1  --and
                      
                            1,699,307            1    1,699,307.0     0.9    654380023
                      select (a.created_date),s2diq(b.LAST_STATUS_T),b.account_no,b.po
                      id_id0,plan_obj_id0 from iqara_cust_invoice_t a,account_t b  whe
                      re a.account_no=b.account_no  and b.status=10103  and a.invoice_
                      date>='1-sep-2008'  and a.invoice_date<'1-jan-2009'  and a.creat
                      ed_date>s2diq(b.last_status_t)  AND A.INVOICE_TYPE !=1  --and a.
                      
                            1,446,915            3      482,305.0     0.8   2468016580
                      SELECT UNIQUE a.POID_ID0 as poid,A.ACCOUNT_NO as acno, C.LOGIN,
                       SALUTATION || '' || FIRST_NAME || ' ' || MIDDLE_NAME || ' ' ||
                      LAST_NAME  as NAME   FROM ACCOUNT_T A, (SELECT OBJ_ID0, CURRENT_
                      
                                -------------------------------------------------------------
                      SQL ordered by Reads for DB: PINDB  Instance: pindb  Snaps: 213 -214
                      -> End Disk Reads Threshold:    1000
                      
                       Physical Reads  Executions  Reads per Exec % Total  Hash Value
                      --------------- ------------ -------------- ------- ------------
                           13,458,740           54      249,235.9    27.1   2873239600
                      select buffer_buf from event_item_transfer_buf_t where obj_id0 =
                       :1 and rec_id = :2
                      
                            8,254,134           43      191,956.6    16.6   1161928628
                      select C.BILL_OBJ_ID0,B.BUFFER_BUF,A.ACCOUNT_NO,NVL(BB.START_T,0
                      ),NVL(BB.END_T,0),    nvl((select bill_no from bill_t     where
                      account_obj_id0 =A.POID_ID0 and bill_no like 'PO%' and end_t=( s
                      elect max(end_t) from bill_t     where account_obj_id0 =A.POID_I
                      D0 and start_t!=end_t and bill_no like 'PO%'     and end_t < (se
                      
                            2,732,610           43       63,549.1     5.5    169052458
                      select CUST_ACCT_ID ,nvl(IQARA_PARSE_VALUE(PLAN_POID),'NULL') PL
                      AN ,ACCOUNT_STATUS ,lower(WEB_ACCESS_USERID) ,B.POID_ID0 ,REQUES
                      T_TYPE ,NVL(REASON_CODE,'NULL') ,TASK_ID ,B.BILL_TYPE ,A.BILLING
                      _TYPE ,STATUS PORTAL_STATUS ,B.LAST_STATUS_T ,C.PLAN_OBJ_ID0  fr
                      om IQARA_BILLING_TASKS@METASOLV A ,ACCOUNT_T B ,(select distinct
                      
                            1,598,115           22       72,641.6     3.2   3338395017
                      select upper(a.city) as city,count(bill_type) as count,case when
                       bill_type=10000 then 'Prepaid' when bill_type=10001 then 'Suvid
                      ha' end  from  account_nameinfo_t a, account_t b, (select distin
                      ct obj_id0,plan_obj_id0 from account_products_t where plan_obj_i
                      d0>0) c,  iq_prov_tags_t d where  a.obj_id0 = b.poid_id0 and b.p
                      
                            1,209,882           16       75,617.6     2.4   1363642522
                      select upper(a.city) as city,count(bill_type) as count,case when
                       bill_type=10000 then 'Prepaid' when bill_type=10001 then 'Suvid
                      ha' end  from  account_nameinfo_t a, account_t b, (select distin
                      ct obj_id0,plan_obj_id0 from account_products_t where plan_obj_i
                      d0>0) c,  iq_prov_tags_t d where  a.obj_id0 = b.poid_id0 and b.p
                      
                            1,145,108           53       21,605.8     2.3   1094410509
                      select poid_type from iqara_online_dealer_renewals_t where poid_
                      id0 = :1
                      
                            1,034,790           14       73,913.6     2.1    637939588
                      update iqara_online_payments_t set poid_rev = poid_rev + 1, mod_
                      t = :mod_t, last_status_t=:last_status_t, prior_payment_status=:
                      prior_payment_status where poid_id0 = :poid_id0
                      
                              887,243           14       63,374.5     1.8   2067691532
                      SELECT DISTINCT A.ACCOUNT_NO,B.ADDRESS,B.CITY,B.STATE,(B.FIRST_N
                      AME || ' ' || B.LAST_NAME) as NAME, ROUND(C.DUENOW,2) as DUENOW,
                      D.PLAN_OBJ_ID0, ROUND(UsedMB.CURRENT_BAL,2) as UsedMB_bal,ROUND(
                      DiscountMB.CURRENT_BAL,2) as DiscountMB_bal,ROUND(FreeMB.CURRENT
                      _BAL,2) as FreeMB_bal, ROUND(UsedHR.CURRENT_BAL,2) as UsedHR_bal
                      
                              882,225            3      294,075.0     1.8   2468016580
                      SELECT UNIQUE a.POID_ID0 as poid,A.ACCOUNT_NO as acno, C.LOGIN,
                       SALUTATION || '' || FIRST_NAME || ' ' || MIDDLE_NAME || ' ' ||
                      LAST_NAME  as NAME   FROM ACCOUNT_T A, (SELECT OBJ_ID0, CURRENT_
                      BAL FROM ACCOUNT_BALANCES_T WHERE REC_ID=1000050) BM1,   (SELECT
                       OBJ_ID0, CURRENT_BAL FROM ACCOUNT_BALANCES_T WHERE REC_ID=10001
                      • 8. Re: update seg$  top 2nd in SQL ordered by Gets in statspack report
                        615488
                        2nd part:
                        SQL ordered by Reads for DB: PINDB  Instance: pindb  Snaps: 213 -214
                        -> End Disk Reads Threshold:    1000
                        
                         Physical Reads  Executions  Reads per Exec % Total  Hash Value
                        --------------- ------------ -------------- ------- ------------
                                486,047            4      121,511.8     1.0    447397635
                           select account_no,created,city,days,type,servType from( selec
                        t distinct a.account_no,s2diq(a.created_t) as created,b.CITY, ro
                        und(s2diq(a.created_t)-s2diq(a.last_status_t),0) as days, decode
                        (PAYMENT_MODE,201,'ePayment',203,'Master Pin Suvidha',204,'Maste
                        r Pin Prepaid',2021,'DC Payment',2022,'IDC Payment',2023,'Drop B
                        
                                484,882            4      121,220.5     1.0     54622780
                           select account_no,created,city,days,type,servType from( selec
                        t distinct a.account_no,s2diq(a.created_t) as created,b.CITY, ro
                        und(s2diq(a.created_t)-s2diq(a.last_status_t),0) as days, decode
                        (PAYMENT_MODE,201,'ePayment',203,'Master Pin Suvidha',204,'Maste
                        r Pin Prepaid',2021,'DC Payment',2022,'IDC Payment',2023,'Drop B
                        
                                426,305            6       71,050.8     0.9   3581943831
                        update iqara_online_payments_t set poid_rev = poid_rev + 1, mod_
                        t = :mod_t, plan_obj_DB=:plan_obj_DB, plan_obj_ID0=:plan_obj_ID0
                        , plan_obj_TYPE=:plan_obj_TYPE, plan_obj_REV=:plan_obj_REV, acco
                        unt_no=:account_no, account_obj_DB=:account_obj_DB, account_obj_
                        ID0=:account_obj_ID0, account_obj_TYPE=:account_obj_TYPE, accoun
                        
                                426,303           10       42,630.3     0.9   2607830512
                        select   status, poid_DB, poid_ID0, poid_TYPE, poid_REV from iqa
                        ra_online_payments_t where (iqara_online_payments_t.poid_ID0 = :
                        1 and iqara_online_payments_t.poid_DB = 1 )
                        
                                421,079            6       70,179.8     0.8   4007814664
                        select poid_type from iqara_online_payments_t where poid_id0 = :
                        1
                        
                                365,443            6       60,907.2     0.7   2419891991
                        select count(*) as cnt from iqara_online_payments_t where  accou
                        nt_no='114379' and s2diq(created_t) >= (sysdate-1) and status =
                        0 and payment_mode = 201
                        
                                361,982           24       15,082.6     0.7   2310962583
                        SELECT  distinct parent_obj_id0,trans_id,sum(amount) as amount
                           FROM  iqara_online_dealer_renewals_t dt      WHERE  dt.status
                         in (1,3,5,6)      --and dt.receipt_no is not null     and dt.cr
                        edit_status=1     group by parent_obj_id0,trans_id
                        
                                  -------------------------------------------------------------
                        SQL ordered by Executions for DB: PINDB  Instance: pindb  Snaps: 213 -214
                        -> End Executions Threshold:     100
                        
                         Executions   Rows Processed    Rows per Exec   Hash Value
                        ------------ ---------------- ---------------- ------------
                              86,690          102,185              1.2   2373638391
                        SELECT ((TO_DATE(TO_CHAR(:b1,'dd-MM-YYYY HH:MI:SS AM'),'dd-MM-YY
                        YY HH:MI:SS AM') - TO_DATE('01-JAN-1970','dd-MON-YYYY') ) * 24
                        * 3600 ) - (5.5 * 3600 )    FROM DUAL
                        
                              54,613           50,564              0.9   1578065311
                        select  distinct earned_start_t, earned_end_t, end_t, sys_descr,
                         poid_DB, poid_ID0, poid_TYPE, poid_REV from event_t where (even
                        t_t.poid_ID0 = :1 and event_t.poid_DB = 1 )
                        
                              53,552            1,345              0.0    904892542
                        select  file#,block#,length from fet$       where length>=:1 and
                         ts#=:2 and file#=:3
                        
                              52,207              904              0.0   2714675196
                        select file#, block# from fet$ where ts#=:1 and file#=:2
                        
                              17,126          143,676              8.4   2796228136
                        select rec_id, credit_floor, credit_limit, credit_thresholds, cu
                        rrent_bal, flags, next_bal, open_bal, reserved_amount from accou
                        nt_balances_t where obj_id0 = :1
                        
                              11,441                0              0.0   2570684848
                        set transaction read write
                        
                              11,071                0              0.0    242587281
                        commit
                        
                              10,768           11,835              1.1   2429512407
                        select poid_DB, poid_ID0, poid_TYPE, poid_REV, last_bill_obj_DB,
                         last_bill_obj_ID0, last_bill_obj_TYPE, last_bill_obj_REV, pendi
                        ng_recv, group_obj_DB, group_obj_ID0, group_obj_TYPE, group_obj_
                        REV, bill_obj_DB, bill_obj_ID0, bill_obj_TYPE, bill_obj_REV, bil
                        l_type, actg_next_t, billing_status, actg_future_t, next_bill_ob
                        
                              10,393           22,735              2.2    103177651
                        select rec_id, created_t, cycle_discount, cycle_disc_amt, cycle_
                        end_t, cycle_fee_amt, cycle_fee_flags, cycle_start_t, deal_obj_D
                        B, deal_obj_ID0, deal_obj_TYPE, deal_obj_REV, descr, flags, last
                        _modified_t, mmc_end_t, mmc_start_t, mmc_type, node_location, pl
                        an_obj_DB, plan_obj_ID0, plan_obj_TYPE, plan_obj_REV, product_ob
                        
                              10,289           11,386              1.1   2610003731
                        select poid_DB, poid_ID0, poid_TYPE, poid_REV, mod_t, account_no
                        , brand_obj_DB, brand_obj_ID0, brand_obj_TYPE, brand_obj_REV, ti
                        mezone_id, status, status_flags, currency, currency_secondary, b
                        ill_when, bill_type, group_obj_DB, group_obj_ID0, group_obj_TYPE
                        , group_obj_REV, next_bill_obj_DB, next_bill_obj_ID0, next_bill_
                        
                               9,822            8,915              0.9   4127767666
                        insert into you_cust_invoice_event_t ( event_total, start_t, cre
                        ated_t, end_t, event_obj_DB, event_obj_ID0, event_obj_TYPE, even
                        t_obj_REV, event_order, resource_id, event_id, event_descr, rec_
                        id, obj_id0 ) values ( :event_total, :start_t, :created_t, :end_
                        t, :event_obj_DB, :event_obj_ID0, :event_obj_TYPE, :event_obj_RE
                        SQL ordered by Executions for DB: PINDB  Instance: pindb  Snaps: 213 -214
                        -> End Executions Threshold:     100
                        
                         Executions   Rows Processed    Rows per Exec   Hash Value
                        ------------ ---------------- ---------------- ------------
                        
                               9,732           10,737              1.1   1206308799
                        update account_t set poid_db = poid_db where poid_id0 = :1
                        
                               9,613           10,731              1.1   3014734230
                        insert into event_session_dialup_t ( bytes_out, bytes_in, login,
                         termserv_id, ani, descr, dnis, ipaddr, network, packets_in, pac
                        kets_out, speed, status, term_cause, termserv_port, timezone, tr
                        ans_id, type, units, obj_id0 ) values ( :bytes_out, :bytes_in, :
                        login, :termserv_id, :ani, :descr, :dnis, :ipaddr, :network, :pa
                        
                               9,215           10,330              1.1   2771817915
                        insert into event_total_t ( amount, rec_id, obj_id0 ) values ( :
                        amount, :rec_id, :obj_id0 )
                        
                               9,114           10,227              1.1   1159992683
                        insert into event_bal_impacts_t ( quantity, account_obj_DB, acco
                        unt_obj_ID0, account_obj_TYPE, account_obj_REV, impact_type, amo
                        unt, amount_deferred, product_obj_DB, product_obj_ID0, product_o
                        bj_TYPE, product_obj_REV, rate_obj_DB, rate_obj_ID0, rate_obj_TY
                        PE, rate_obj_REV, gl_id, percent, lineage, resource_id, rate_tag
                        
                               9,032           77,601              8.6    208068356
                        select  distinct poid_DB, poid_ID0, poid_TYPE, poid_REV, end_t,
                        sys_descr, rerate_obj_DB, rerate_obj_ID0, rerate_obj_TYPE, rerat
                        e_obj_REV, invoice_data, timezone_adj_end_t, rated_timezone_id f
                        rom event_t where (event_t.item_obj_ID0 = :1 and event_t.item_ob
                        j_DB = 1 ) and event_t.poid_type in ( select type_str from confi
                        
                               8,857            9,644              1.1    238111709
                        select poid_DB, poid_ID0, poid_TYPE, poid_REV from account_t whe
                        re poid_id0 = :1
                        
                               8,830            9,927              1.1    477608444
                        update account_balances_t set current_bal=:current_bal, credit_l
                        imit=:credit_limit, credit_floor=:credit_floor, credit_threshold
                        s=:credit_thresholds where obj_id0 = :obj_id0 and rec_id = :rec_
                        id
                        
                               8,719            8,260              0.9   3224009371
                        insert into you_cust_invoice_event_t ( event_total, created_t, s
                        tart_t, end_t, event_obj_DB, event_obj_ID0, event_obj_TYPE, even
                        t_obj_REV, event_order, resource_id, event_descr, event_id, rec_
                        id, obj_id0 ) values ( :event_total, :created_t, :start_t, :end_
                        t, :event_obj_DB, :event_obj_ID0, :event_obj_TYPE, :event_obj_RE
                        
                               8,398            8,392              1.0   1303418135
                        select  distinct poid_DB, poid_ID0, poid_TYPE, poid_REV, account
                        
                                  -------------------------------------------------------------
                        SQL ordered by Version Count for DB: PINDB  Instance: pindb  Snaps: 213 -214
                        -> End Version Count Threshold:        20
                        
                         Version
                           Count  Executions   Hash Value
                        -------- ------------ ------------
                              46          118   1312205625
                        select a.default_cpu_cost, a.default_io_cost        from associa
                        tion$ a                                 where a.obj# = :1
                                                      and a.property = :2
                        
                              46          248   1595526224
                        select u.name, o.name                                    from as
                        sociation$ a, user$ u, obj$ o                     where a.obj# =
                         :1                                          and a.property = :2
                                                              and a.statstype# = o.obj#
                                                       and u.user# = o.owner#
                        
                              40          115   1226596809
                        insert into event_t ( poid_DB, poid_ID0, poid_TYPE, poid_REV, na
                        me, userid_DB, userid_ID0, userid_TYPE, userid_REV, session_obj_
                        DB, session_obj_ID0, session_obj_TYPE, session_obj_REV, account_
                        obj_DB, account_obj_ID0, account_obj_TYPE, account_obj_REV, prog
                        ram_name, start_t, end_t, sys_descr, earned_start_t, earned_end_
                        
                              38           61   1480986974
                        insert into event_t ( poid_DB, poid_ID0, poid_TYPE, poid_REV, na
                        me, userid_DB, userid_ID0, userid_TYPE, userid_REV, session_obj_
                        DB, session_obj_ID0, session_obj_TYPE, session_obj_REV, account_
                        obj_DB, account_obj_ID0, account_obj_TYPE, account_obj_REV, prog
                        ram_name, start_t, end_t, descr, sys_descr, earned_start_t, earn
                        
                              36            9    199702406
                        select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,
                        i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i
                        .distkey, i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.sa
                        mplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt
                        ,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),n
                        
                              36           16    395844583
                        select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
                        e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180
                        ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
                        rage,nvl(deflength,0),default$,rowid,col#,property, charsetid,ch
                        arsetform,spare1,spare2 from col$ where obj#=:1 order by intcol#
                        
                              36            6   4195740643
                        select pos#,intcol#,col#,spare1 from icol$ where obj#=:1
                        
                              31            1   1658114582
                        insert into event_t ( poid_DB, poid_ID0, poid_TYPE, poid_REV, na
                        me, userid_DB, userid_ID0, userid_TYPE, userid_REV, session_obj_
                        DB, session_obj_ID0, session_obj_TYPE, session_obj_REV, account_
                        obj_DB, account_obj_ID0, account_obj_TYPE, account_obj_REV, prog
                        ram_name, start_t, end_t, descr, sys_descr, currency, item_obj_D
                        
                              31          110   2147790797
                        insert into event_t ( poid_DB, poid_ID0, poid_TYPE, poid_REV, na
                        me, userid_DB, userid_ID0, userid_TYPE, userid_REV, session_obj_
                        DB, session_obj_ID0, session_obj_TYPE, session_obj_REV, account_
                        SQL ordered by Version Count for DB: PINDB  Instance: pindb  Snaps: 213 -214
                        -> End Version Count Threshold:        20
                        
                         Version
                           Count  Executions   Hash Value
                        -------- ------------ ------------
                        obj_DB, account_obj_ID0, account_obj_TYPE, account_obj_REV, prog
                        ram_name, start_t, end_t, descr, sys_descr, item_obj_DB, item_ob
                        
                              30          186   1668540021
                        select i.obj# from ind$ i, obj$ o    where i.obj# = o.obj# and i
                        .bo# = :1 and o.name = :2
                        
                              29           59    376315213
                        insert into event_t ( rounding_mode, unit, end_t, start_t, progr
                        am_name, poid_DB, poid_ID0, poid_TYPE, poid_REV, userid_DB, user
                        id_ID0, userid_TYPE, userid_REV, net_quantity, name, incr_quanti
                        ty, min_quantity, unrated_quantity, sys_descr, timezone_adj_end_
                        t, timezone_adj_start_t, rum_name, session_obj_DB, session_obj_I
                        
                              24           63   3915973553
                        insert into audit_account_products_t ( product_obj_DB, product_o
                        bj_ID0, product_obj_TYPE, product_obj_REV, descr, quantity, stat
                        us, purchase_start_t, purchase_end_t, purchase_discount, cycle_s
                        tart_t, cycle_end_t, cycle_discount, usage_start_t, usage_end_t,
                         usage_discount, node_location, deal_obj_DB, deal_obj_ID0, deal_
                        
                              23           42   1999682139
                        insert into event_tax_jurisdictions_t ( element_id, type, amount
                        , name, percent, amount_taxed, amount_exempt, amount_gross, rec_
                        id, obj_id0 ) values ( :element_id, :type, :amount, :name, :perc
                        ent, :amount_taxed, :amount_exempt, :amount_gross, :rec_id, :obj
                        _id0 )
                        
                              23           64   2239452364
                        insert into event_billing_product_action_t ( product_obj_DB, pro
                        duct_obj_ID0, product_obj_TYPE, product_obj_REV, descr, quantity
                        , purchase_start_t, purchase_end_t, purchase_discount, cycle_sta
                        rt_t, cycle_end_t, cycle_discount, usage_start_t, usage_end_t, u
                        sage_discount, deal_obj_DB, deal_obj_ID0, deal_obj_TYPE, deal_ob
                        
                              23          120   3184449653
                        insert into event_t ( poid_DB, poid_ID0, poid_TYPE, poid_REV, na
                        me, userid_DB, userid_ID0, userid_TYPE, userid_REV, session_obj_
                        DB, session_obj_ID0, session_obj_TYPE, session_obj_REV, account_
                        
                                  -------------------------------------------------------------
                        Instance Activity Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                        
                        Statistic                                    Total   per Second    per Trans
                        --------------------------------- ---------------- ------------ ------------
                        CPU used by this session                     1,248          0.5          0.1
                        CPU used when call started                   1,248          0.5          0.1
                        CR blocks created                           11,920          4.7          0.8
                        DBWR buffers scanned                       598,813        233.7         39.9
                        DBWR checkpoint buffers written            228,644         89.2         15.2
                        DBWR checkpoints                                 3          0.0          0.0
                        DBWR free buffers found                    594,358        232.0         39.6
                        DBWR lru scans                               1,730          0.7          0.1
                        DBWR make free requests                      1,765          0.7          0.1
                        DBWR revisited being-written buff                0          0.0          0.0
                        DBWR summed scan depth                     598,813        233.7         39.9
                        DBWR transaction table writes               12,597          4.9          0.8
                        DBWR undo block writes                      29,307         11.4          2.0
                        DDL statements parallelized                      0          0.0          0.0
                        DFO trees parallelized                           0          0.0          0.0
                        PX local messages recv'd                         0          0.0          0.0
                        PX local messages sent                           0          0.0          0.0
                        Parallel operations downgraded 25                0          0.0          0.0
                        Parallel operations downgraded 50                0          0.0          0.0
                        SQL*Net roundtrips to/from client          639,922        249.8         42.6
                        SQL*Net roundtrips to/from dblink            2,614          1.0          0.2
                        background checkpoints completed                 3          0.0          0.0
                        background checkpoints started                   3          0.0          0.0
                        background timeouts                          8,378          3.3          0.6
                        branch node splits                              21          0.0          0.0
                        buffer is not pinned count             152,753,273     59,622.7     10,174.1
                        buffer is pinned count                 143,971,118     56,194.8      9,589.1
                        bytes received via SQL*Net from c      193,306,149     75,451.3     12,875.1
                        bytes received via SQL*Net from d        7,012,993      2,737.3        467.1
                        bytes sent via SQL*Net to client       580,288,541    226,498.3     38,649.8
                        bytes sent via SQL*Net to dblink           460,047        179.6         30.6
                        calls to get snapshot scn: kcmgss          770,539        300.8         51.3
                        calls to kcmgas                             19,882          7.8          1.3
                        calls to kcmgcs                             20,531          8.0          1.4
                        change write time                               28          0.0          0.0
                        cleanouts and rollbacks - consist            1,414          0.6          0.1
                        cleanouts only - consistent read             2,886          1.1          0.2
                        cluster key scan block gets             37,764,461     14,740.2      2,515.3
                        cluster key scans                          117,229         45.8          7.8
                        commit cleanout failures: block l            2,111          0.8          0.1
                        commit cleanout failures: buffer             5,134          2.0          0.3
                        commit cleanout failures: callbac               34          0.0          0.0
                        commit cleanout failures: cannot                98          0.0          0.0
                        commit cleanouts                           262,030        102.3         17.5
                        commit cleanouts successfully com          254,653         99.4         17.0
                        consistent changes                          22,313          8.7          1.5
                        consistent gets                        167,023,653     65,192.7     11,124.5
                        current blocks converted for CR
                        cursor authentications                       2,245          0.9          0.2
                        data blocks consistent reads - un           20,278          7.9          1.4
                        db block changes                           823,402        321.4         54.8
                        db block gets                           13,992,530      5,461.6        932.0
                        deferred (CURRENT) block cleanout           62,389         24.4          4.2
                        dirty buffers inspected                      1,072          0.4          0.1
                        enqueue conversions                          4,500          1.8          0.3
                        enqueue deadlocks                                0          0.0          0.0
                        Instance Activity Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                        
                        Statistic                                    Total   per Second    per Trans
                        --------------------------------- ---------------- ------------ ------------
                        enqueue releases                           117,694         45.9          7.8
                        enqueue requests                           117,693         45.9          7.8
                        enqueue timeouts                             2,473          1.0          0.2
                        enqueue waits                                2,370          0.9          0.2
                        exchange deadlocks                               0          0.0          0.0
                        execute count                              625,851        244.3         41.7
                        free buffer inspected                        5,850          2.3          0.4
                        free buffer requested                   47,795,478     18,655.5      3,183.4
                        hot buffers moved to head of LRU         1,122,003        437.9         74.7
                        immediate (CR) block cleanout app            4,300          1.7          0.3
                        immediate (CURRENT) block cleanou           11,698          4.6          0.8
                        index fast full scans (direct rea                0          0.0          0.0
                        index fast full scans (full)                    93          0.0          0.0
                        index fast full scans (rowid rang                0          0.0          0.0
                        leaf node splits                             2,845          1.1          0.2
                        logons cumulative                            3,778          1.5          0.3
                        logons current
                        messages received                           69,802         27.3          4.7
                        messages sent                               69,802         27.3          4.7
                        no buffer to keep pinned count           7,895,887      3,081.9        525.9
                        no work - consistent read gets         145,078,213     56,626.9      9,662.9
                        opened cursors cumulative                  197,581         77.1         13.2
                        opened cursors current
                        parse count (hard)                           3,613          1.4          0.2
                        parse count (total)                        201,636         78.7         13.4
                        parse time cpu                                  10          0.0          0.0
                        parse time elapsed                              29          0.0          0.0
                        physical reads                          49,693,308     19,396.3      3,309.8
                        physical reads direct                    1,871,990        730.7        124.7
                        physical reads direct (lob)                 69,771         27.2          4.7
                        physical writes                          2,550,928        995.7        169.9
                        physical writes direct                   2,311,446        902.2        154.0
                        physical writes direct (lob)                 5,334          2.1          0.4
                        physical writes non checkpoint           2,405,485        938.9        160.2
                        pinned buffers inspected                     2,374          0.9          0.2
                        prefetched blocks                       35,129,614     13,711.8      2,339.8
                        prefetched blocks aged out before                0          0.0          0.0
                        process last non-idle time                       0          0.0          0.0
                        queries parallelized                             0          0.0          0.0
                        recursive calls                          1,657,300        646.9        110.4
                        recursive cpu usage                          1,204          0.5          0.1
                        redo blocks written                        345,641        134.9         23.0
                        redo buffer allocation retries                  15          0.0          0.0
                        redo entries                               432,852        169.0         28.8
                        redo log space requests                          8          0.0          0.0
                        redo log space wait time                         0          0.0          0.0
                        redo size                              165,498,496     64,597.4     11,022.9
                        redo synch time                                  0          0.0          0.0
                        redo synch writes                           15,069          5.9          1.0
                        redo wastage                             5,901,528      2,303.5        393.1
                        redo write time                                  0          0.0          0.0
                        redo writer latching time                        0          0.0          0.0
                        redo writes                                 23,251          9.1          1.6
                        rollback changes - undo records a               70          0.0          0.0
                        rollbacks only - consistent read            10,239          4.0          0.7
                        rows fetched via callback                1,948,590        760.6        129.8
                        Instance Activity Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                        
                        Statistic                                    Total   per Second    per Trans
                        --------------------------------- ---------------- ------------ ------------
                        session connect time                             0          0.0          0.0
                        session cursor cache count                     104          0.0          0.0
                        session cursor cache hits                  153,174         59.8         10.2
                        session logical reads                  180,946,413     70,627.0     12,051.9
                        session pga memory                   4,675,981,144  1,825,129.3    311,441.4
                        session pga memory max               4,815,156,368  1,879,452.1    320,711.1
                        session uga memory                      58,240,824     22,732.6      3,879.1
                        session uga memory max               1,264,810,792    493,681.0     84,242.1
                        sorts (disk)                                   123          0.1          0.0
                        sorts (memory)                              59,270         23.1          4.0
                        sorts (rows)                            83,881,987     32,740.8      5,586.9
                        summed dirty queue length                      102          0.0          0.0
                        switch current to new buffer
                        table fetch by rowid                    89,943,968     35,106.9      5,990.7
                        table fetch continued row                9,440,509      3,684.8        628.8
                        table scan blocks gotten                66,969,962     26,139.7      4,460.5
                        table scan rows gotten               1,186,879,123    463,262.7     79,051.5
                        table scans (long tables)                    1,403          0.6          0.1
                        table scans (rowid ranges)                       0          0.0          0.0
                        table scans (short tables)                 143,938         56.2          9.6
                        total file opens                            72,175         28.2          4.8
                        transaction rollbacks                            1          0.0          0.0
                        transaction tables consistent rea               30          0.0          0.0
                        transaction tables consistent rea            2,023          0.8          0.1
                        user calls                                 662,008        258.4         44.1
                        user commits                                14,396          5.6          1.0
                        user rollbacks                                 618          0.2          0.0
                        write clones created in backgroun               25          0.0          0.0
                        write clones created in foregroun            9,347          3.7          0.6
                                  -------------------------------------------------------------
                        • 9. Re: update seg$  top 2nd in SQL ordered by Gets in statspack report
                          615488
                          3rd part:
                                   -------------------------------------------------------------
                          Tablespace IO Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                          ->ordered by IOs (Reads + Writes) desc
                          
                          Tablespace
                          ------------------------------
                                           Av      Av     Av                    Av        Buffer Av Buf
                                   Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
                          -------------- ------- ------ ------- ------------ -------- ---------- ------
                          PIN00
                              12,378,535   4,832    0.0     3.8       57,398       22     17,202    0.0
                          PINTEMP
                                 203,644      79    0.0     9.1      258,252      101          0    0.0
                          PINX00
                                 271,849     106    0.0     1.3      126,935       50        199    0.0
                          RBS
                                     260       0    0.0     1.0       35,902       14         18    0.0
                          SYSTEM
                                   4,014       2    0.0     1.8        5,256        2          0    0.0
                          PERFSTAT
                                   3,399       1    6.7     1.0        5,404        2          0    0.0
                          PINRBS
                                      74       0    0.0     1.0        6,060        2          2    0.0
                          TEMP
                                   1,683       1    0.0     8.1          819        0          0    0.0
                          INDX
                                       3       0    0.0     1.0            3        0          0    0.0
                          TOOLS
                                       3       0    0.0     1.0            3        0          0    0.0
                                    -------------------------------------------------------------
                          File IO Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                          ->ordered by Tablespace, File
                          
                          Tablespace               Filename
                          ------------------------ ----------------------------------------------------
                                           Av      Av     Av                    Av        Buffer Av Buf
                                   Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
                          -------------- ------- ------ ------- ------------ -------- ---------- ------
                          INDX                     /oracle4/oradata/pindb/indx01.dbf
                                       3       0    0.0     1.0            3        0          0
                          
                          PERFSTAT                 /oracle15/oradata/pindb/perfstat.dbf
                                   3,399       1    6.7     1.0        5,404        2          0
                          
                          PIN00                    /oracle1/oradata/pindb/pin11.dbf
                                  46,239      18    0.0     2.1          229        0         91    0.0
                                                   /oracle1/oradata/pindb004.dbf
                                  13,246       5    0.0     2.5            3        0         26    0.0
                                                   /oracle1/oradata/pindbnew/pin018.dbf
                                  14,526       6    0.0     2.6          197        0         47    0.0
                                                   /oracle10/oradata/pin06.dbf
                                 149,196      58    0.0     2.2          485        0         90    0.0
                                                   /oracle11/oradata/pindb/pin019.dbf
                                  11,569       5    0.0     1.7            3        0         59    0.0
                                                   /oracle11/oradata/pindb/pin020.dbf
                                  35,017      14    0.0     2.1          243        0         61    0.0
                                                   /oracle11/oradata/pindb/pin021.dbf
                                  35,471      14    0.0     2.1          181        0         82    0.0
                                                   /oracle11/oradata/pindb/pin023.dbf
                                  28,820      11    0.0     2.5           95        0        100    0.0
                                                   /oracle11/oradata/pindb/pin024.dbf
                                  11,295       4    0.0     1.7            3        0         63    0.0
                                                   /oracle11/oradata/pindb/pin025.dbf
                                  53,374      21    0.0     2.3          209        0        154    0.0
                                                   /oracle12/oradata/pin031.dbf
                                  50,359      20    0.0     2.4          233        0         28    0.0
                                                   /oracle12/oradata/pindb/pin022.dbf
                                  25,048      10    0.0     2.1           89        0         81    0.0
                                                   /oracle12/oradata/pindb/pin026.dbf
                                  33,710      13    0.0     2.5          108        0         14    0.0
                                                   /oracle12/oradata/pindb/pin027.dbf
                                  35,875      14    0.0     2.2            5        0         78    0.0
                                                   /oracle12/oradata/pindb/pin028.dbf
                                  64,385      25    0.0     2.4          209        0        108    0.0
                                                   /oracle12/oradata/pindb/pin029.dbf
                                  74,028      29    0.0     2.1          115        0        141    0.0
                                                   /oracle12/oradata/pindb/pin030.dbf
                                  49,181      19    0.0     2.5            3        0         73    0.0
                                                   /oracle12/oradata/pindb/pin032.dbf
                                  37,767      15    0.0     2.6           79        0         81    0.0
                                                   /oracle12/oradata/pindb/pin033.dbf
                                  82,755      32    0.0     2.7           98        0        122    0.0
                                                   /oracle13/oradata/pindb/pin035.dbf
                                  57,078      22    0.0     2.9            3        0         35    0.0
                                                   /oracle13/oradata/pindb/pin036.dbf
                                  81,059      32    0.0     3.5          159        0         37    0.0
                                                   /oracle13/oradata/pindb/pin037.dbf
                                 137,584      54    0.0     3.5          170        0         47    0.0
                                                   /oracle13/oradata/pindb/pin038.dbf
                                 105,798      41    0.0     3.3          176        0         39    0.0
                          File IO Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                          ->ordered by Tablespace, File
                          
                          Tablespace               Filename
                          ------------------------ ----------------------------------------------------
                                           Av      Av     Av                    Av        Buffer Av Buf
                                   Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
                          -------------- ------- ------ ------- ------------ -------- ---------- ------
                          PIN00                    /oracle13/oradata/pindb/pin039.dbf
                                  51,570      20    0.0     4.1           70        0         16    0.0
                                                   /oracle13/oradata/pindb/pin040.dbf
                                  89,114      35    0.0     3.9          175        0        120    0.0
                                                   /oracle13/oradata/pindb/pin041.dbf
                                 170,851      67    0.0     3.6          121        0        259    0.0
                                                   /oracle13/oradata/pindb/pin042.dbf
                                 133,630      52    0.0     4.3          213        0        119    0.0
                                                   /oracle13/oradata/pindb/pin043.dbf
                                  87,048      34    0.0     4.2          226        0         43    0.0
                                                   /oracle13/oradata/pindb/pin044.dbf
                                  66,017      26    0.0     4.1            3        0         37    0.0
                                                   /oracle13/oradata/pindb/pin045.dbf
                                 123,621      48    0.0     3.9          931        0         77    0.0
                                                   /oracle14/oradata/pindb/pin00db2.dbf
                                 211,353      82    0.0     3.1        3,178        1      5,723    0.0
                                                   /oracle14/oradata/pindb/pin01db2.dbf
                                  66,584      26    0.0     2.5          400        0          3    0.0
                                                   /oracle14/oradata/pindb/pin046.dbf
                                  89,545      35    0.0     4.8            3        0         16    0.0
                                                   /oracle14/oradata/pindb/pin047.dbf
                                 136,512      53    0.0     4.5          169        0        120    0.0
                                                   /oracle14/oradata/pindb/pin048.dbf
                                 119,514      47    0.0     4.7          136        0        131    0.0
                                                   /oracle14/oradata/pindb/pin049.dbf
                                 164,093      64    0.0     4.1          138        0         49    0.0
                                                   /oracle14/oradata/pindb/pin050.dbf
                                 134,010      52    0.0     4.7          294        0         79    0.0
                                                   /oracle14/oradata/pindb/pin051.dbf
                                 150,817      59    0.0     4.2          243        0        150    0.0
                                                   /oracle14/oradata/pindb/pin052.dbf
                                 169,047      66    0.0     4.2          262        0         86    0.0
                                                   /oracle14/oradata/pindb/pin053.dbf
                                  94,953      37    0.0     4.6            3        0         59    0.0
                                                   /oracle14/oradata/pindb/pin054.dbf
                                 116,181      45    0.0     3.8          308        0        116    0.0
                                                   /oracle14/oradata/pindb/pin055.dbf
                                 269,612     105    0.0     4.3          419        0        191    0.0
                                                   /oracle14/oradata/pindb/pin056.dbf
                                 132,060      52    0.0     4.2          152        0         47    0.0
                                                   /oracle14/oradata/pindb/pin057.dbf
                                 108,630      42    0.0     3.8          319        0        123    0.0
                                                   /oracle14/oradata/pindb/pin058.dbf
                                  84,527      33    0.0     3.7          244        0         55    0.0
                                                   /oracle14/oradata/pindb/pin059.dbf
                                  71,689      28    0.0     3.9          278        0         63    0.0
                                                   /oracle14/oradata/pindb/pin060.dbf
                                 145,793      57    0.0     4.0          268        0         91    0.0
                                                   /oracle14/oradata/pindb/pin061.dbf
                                 116,526      45    0.0     3.7          140        0        151    0.0
                                                   /oracle14/oradata/pindb/pin062.dbf
                                 160,056      62    0.0     4.6          319        0         62    0.0
                          File IO Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                          ->ordered by Tablespace, File
                          
                          Tablespace               Filename
                          ------------------------ ----------------------------------------------------
                                           Av      Av     Av                    Av        Buffer Av Buf
                                   Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
                          -------------- ------- ------ ------- ------------ -------- ---------- ------
                          PIN00                    /oracle14/oradata/pindb/pin063.dbf
                                 110,840      43    0.0     4.5          253        0         19    0.0
                                                   /oracle14/oradata/pindb/pin064.dbf
                                  63,370      25    0.0     4.1          137        0         24    0.0
                                                   /oracle14/oradata/pindb/pin065.dbf
                                 133,979      52    0.0     4.2          369        0         35    0.0
                                                   /oracle14/oradata/pindb/pin066.dbf
                                 120,438      47    0.0     3.9          224        0         57    0.0
                                                   /oracle14/oradata/pindb/pin067.dbf
                                 149,488      58    0.0     4.2          334        0        259    0.0
                                                   /oracle14/oradata/pindb/pin068.dbf
                                  93,061      36    0.0     5.0          180        0        101    0.0
                                                   /oracle14/oradata/pindb/pin069.dbf
                                  64,104      25    0.0     3.8            3        0        119    0.0
                                                   /oracle14/oradata/pindb/pin070.dbf
                                 155,655      61    0.0     4.7          366        0        108    0.0
                                                   /oracle14/oradata/pindb/pin071.dbf
                                 132,219      52    0.0     4.5          247        0        122    0.0
                                                   /oracle14/oradata/pindb/pin072.dbf
                                  56,547      22    0.0     3.3            3        0         26    0.0
                                                   /oracle15/oradata/pindb/pin073.dbf
                                 163,766      64    0.0     5.0          396        0        122    0.0
                                                   /oracle15/oradata/pindb/pin074.dbf
                                 145,886      57    0.0     4.5          302        0         95    0.0
                                                   /oracle15/oradata/pindb/pin075.dbf
                                 138,698      54    0.0     4.1          258        0        124    0.0
                                                   /oracle15/oradata/pindb/pin076.dbf
                                 161,518      63    0.0     4.7          356        0         89    0.0
                                                   /oracle15/oradata/pindb/pin077.dbf
                                 111,873      44    0.0     4.9          265        0         46    0.0
                                                   /oracle15/oradata/pindb/pin079.dbf
                                 130,001      51    0.0     4.7          305        0         78    0.0
                                                   /oracle15/oradata/pindb/pin080.dbf
                                 131,715      51    0.0     4.5          354        0         54    0.0
                                                   /oracle15/oradata/pindb/pin081.dbf
                                 136,383      53    0.0     4.3          194        0         83    0.0
                                                   /oracle15/oradata/pindb/pin082.dbf
                                 131,902      51    0.0     4.2          306        0         99    0.0
                                                   /oracle15/oradata/pindb/pin083.dbf
                                 134,494      52    0.0     4.2          160        0         75    0.0
                                                   /oracle15/oradata/pindb/pin084.dbf
                                 114,960      45    0.0     4.2          126        0         77    0.0
                                                   /oracle15/oradata/pindb/pin085.dbf
                                 106,876      42    0.0     4.6          178        0         90    0.0
                                                   /oracle15/oradata/pindb/pin086.dbf
                                 235,087      92    0.0     4.0          342        0        156    0.0
                                                   /oracle15/oradata/pindb/pin087.dbf
                                 218,650      85    0.0     3.7          380        0        194    0.0
                                                   /oracle15/oradata/pindb/pin088.dbf
                                 232,166      91    0.0     3.9          293        0        168    0.0
                                                   /oracle15/oradata/pindb/pin089.dbf
                                 214,934      84    0.0     3.8          366        0        256    0.0
                          File IO Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                          ->ordered by Tablespace, File
                          
                          Tablespace               Filename
                          ------------------------ ----------------------------------------------------
                                           Av      Av     Av                    Av        Buffer Av Buf
                                   Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
                          -------------- ------- ------ ------- ------------ -------- ---------- ------
                          PIN00                    /oracle15/oradata/pindb/pin090.dbf
                                 226,601      88    0.0     3.7          239        0        263    0.0
                                                   /oracle15/oradata/pindb/pin091.dbf
                                 210,361      82    0.0     3.5          381        0        179    0.0
                                                   /oracle15/oradata/pindb/pin092.dbf
                                 215,778      84    0.0     3.9          292        0        222    0.0
                                                   /oracle15/oradata/pindb/pin093.dbf
                                 207,726      81    0.0     3.6          252        0        269    0.0
                                                   /oracle15/oradata/pindb/pin094.dbf
                                 222,908      87    0.0     3.9          131        0        301    0.0
                                                   /oracle15/oradata/pindb/pin095.dbf
                                 216,757      85    0.0     3.7          173        0        261    0.0
                                                   /oracle15/oradata/pindb/pin096.dbf
                                 197,576      77    0.0     3.5          187        0        155    0.0
                                                   /oracle15/oradata/pindb/pin097.dbf
                                 161,424      63    0.0     3.6          104        0        225    0.0
                                                   /oracle15/oradata/pindb/pin098.dbf
                                 174,544      68    0.0     4.1          360        0        216    0.0
                                                   /oracle15/oradata/pindb/pin100.dbf
                                 172,757      67    0.0     4.0          197        0        146    0.0
                                                   /oracle15/oradata/pindb/pin101.dbf
                                 145,690      57    0.0     3.8          217        0        106    0.0
                                                   /oracle15/oradata/pindb/pin102.dbf
                                 151,375      59    0.0     4.1          148        0         88    0.0
                                                   /oracle15/oradata/pindb/pin103.dbf
                                 123,238      48    0.0     4.0           80        0         77    0.0
                                                   /oracle15/oradata/pindb/pin104.dbf
                                  76,536      30    0.0     5.2          101        0         37    0.0
                                                   /oracle15/oradata/pindb/pin105.dbf
                                  51,804      20    0.0     4.7           76        0         29    0.0
                                                   /oracle15/oradata/pindb/pin106.dbf
                                  41,867      16    0.0     4.6          110        0         16    0.0
                                                   /oracle16/oradata/pindb/pin107.dbf
                                 123,445      48    0.0     3.8          418        0        153    0.0
                                                   /oracle16/oradata/pindb/pin108.dbf
                                  59,915      23    0.0     4.8          155        0         54    0.0
                                                   /oracle16/oradata/pindb/pin109.dbf
                                      12       0    0.0     1.0            3        0          0
                                                   /oracle16/oradata/pindb/pin110.dbf
                                  83,769      33    0.0     3.5           56        0         92    0.0
                                                   /oracle16/oradata/pindb/pin111.dbf
                                 163,527      64    0.0     4.0        1,605        1        157    0.0
                                                   /oracle16/oradata/pindb/pin112.dbf
                                 145,895      57    0.0     3.6        5,930        2        200    0.0
                                                   /oracle16/oradata/pindb/pin113.dbf
                                 113,921      44    0.0     3.2        3,148        1        137    0.0
                                                   /oracle16/oradata/pindb/pin114.dbf
                                  87,418      34    0.0     3.0       11,088        4        125    0.0
                                                   /oracle2/oradata/pindb/pin078.dbf
                                 128,740      50    0.0     4.8          269        0        155    0.0
                                                   /oracle4/oradata/pin10.dbf
                                 115,921      45    0.0     2.6          619        0        198    0.0
                          File IO Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                          ->ordered by Tablespace, File
                          
                          Tablespace               Filename
                          ------------------------ ----------------------------------------------------
                                           Av      Av     Av                    Av        Buffer Av Buf
                                   Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
                          -------------- ------- ------ ------- ------------ -------- ---------- ------
                          PIN00                    /oracle5/oradata/pin03.dbf
                                  22,757       9    0.0     2.6          103        0         19    0.0
                                                   /oracle5/oradata/pin04.dbf
                                  23,009       9    0.0     2.8          174        0          4    0.0
                                                   /oracle5/oradata/pin05.dbf
                                  30,566      12    0.0     1.9           70        0         75    0.0
                                                   /oracle50/oradata/pin00010.dbf
                                  13,933       5    0.0     2.6           66        0         23    0.0
                                                   /oracle50/oradata/pin09.dbf
                                 129,705      51    0.0     3.2          533        0        227    0.0
                                                   /oracle6/oradata/pin07.dbf
                                  44,889      18    0.0     2.2          323        0         21    0.0
                                                   /oracle6/oradata/pindb/pin034.dbf
                                  66,923      26    0.0     3.3          113        0        143    0.0
                                                   /oracle7/oradata/pin030.dbf
                                  38,642      15    0.0     2.6          105        0         48    0.0
                                                   /oracle7/oradata/pin08.dbf
                                  83,755      33    0.0     2.5        1,737        1         66    0.0
                                                   /oracle8/oradata/pindb001.dbf
                                  36,024      14    0.0     2.1          254        0         54    0.0
                                                   /oracle8/oradata/pindb10.dbf
                                  38,149      15    0.0     2.3          170        0         44    0.0
                                                   /oracle9/oradata/pindb/pin012.dbf
                                  35,947      14    0.0     2.3          250        0         76    0.0
                                                   /oracle9/oradata/pindb/pin013.dbf
                                  25,787      10    0.0     2.1           83        0         36    0.0
                                                   /oracle9/oradata/pindb/pin014.dbf
                                  13,053       5    0.0     2.5            3        0         13    0.0
                                                   /oracle9/oradata/pindb/pin015.dbf
                                  15,908       6    0.0     3.1           82        0          1    0.0
                                                   /oracle9/oradata/pindb/pin016.dbf
                                  13,249       5    0.0     2.7            4        0          5    0.0
                                                   /oracle9/oradata/pindb/pin017.dbf
                                  22,897       9    0.0     2.4          128        0         61    0.0
                                                   /orapurge/oradata/pindb/pin115.dbf
                                  18,494       7    0.0     3.6        8,057        3        106    0.0
                          • 10. Re: update seg$  top 2nd in SQL ordered by Gets in statspack report
                            615488
                            4th part:
                            PINRBS                   /oracle3/pinrbs.dbf
                                        50       0    0.0     1.0        3,062        1          2    0.0
                                                     /oracle7/oradata/pinrbs01.dbf
                                        14       0    0.0     1.0        1,038        0          0
                                                     /oracle8/oradata/pinrbs02.dbf
                                        10       0    0.0     1.0        1,960        1          0
                            
                            PINTEMP                  /oracle1/oradata/pindb/pintemp02.dbf
                                        33       0    0.0   #####           20        0          0
                                                     /oracle1/oradata/pindb/pintmp05.dbf
                                        14       0    0.0    79.9            9        0          0
                                                     /oracle13/oradata/pindb/pintemp06.dbf
                                       289       0    0.0   #####          168        0          0
                                                     /oracle13/oradata/pindb/pintemp07.dbf
                                     1,391       1    0.0    17.0        1,348        1          0
                            File IO Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                            ->ordered by Tablespace, File
                            
                            Tablespace               Filename
                            ------------------------ ----------------------------------------------------
                                             Av      Av     Av                    Av        Buffer Av Buf
                                     Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
                            -------------- ------- ------ ------- ------------ -------- ---------- ------
                            PINTEMP                  /oracle14/oradata/pindb/pintemp08.dbf
                                       267       0    0.0    55.1          412        0          0
                                                     /oracle4/pintemp.dbf
                                     2,549       1    0.0    39.1        2,303        1          0
                                                     /oracle50/oradata/pintemp04.dbf
                                   197,296      77    0.0     8.1      252,408       99          0
                                                     /oracle6/oradata/pintemp03.dbf
                                     1,379       1    0.0    16.3        1,340        1          0
                                                     /oracle8/oradata/pintmp001.dbf
                                       426       0    0.0   #####          244        0          0
                            
                            PINX00                   /oracle1/oradata/pindbnew/pinx012.dbf
                                       777       0    0.0     1.2           86        0          0
                                                     /oracle11/oradata/pindb/pinx013.dbf
                                       769       0    0.0     1.2           49        0          0
                                                     /oracle11/oradata/pindb/pinx015.dbf
                                       964       0    0.0     1.1          126        0          0
                                                     /oracle11/oradata/pindb/pinx016.dbf
                                       283       0    0.0     1.0           57        0          0
                                                     /oracle11/oradata/pindb/pinx017.dbf
                                     1,149       0    0.0     1.1          194        0          0
                                                     /oracle11/oradata/pindb/pinx018.dbf
                                     1,406       1    0.0     1.1          219        0          0
                                                     /oracle11/oradata/pindb/pinx019.dbf
                                     1,340       1    0.0     1.1          250        0          0
                                                     /oracle11/oradata/pindb/pinx040.dbf
                                     2,082       1    0.0     1.0          324        0          0
                                                     /oracle12/oradata/pindb/pinx014.dbf
                                       916       0    0.0     1.2          105        0          0
                                                     /oracle12/oradata/pindb/pinx041.dbf
                                     2,009       1    0.0     1.1          270        0          1    0.0
                                                     /oracle13/oradata/pindb/pin024.dbf
                                       855       0    0.0     1.2           51        0          0
                                                     /oracle13/oradata/pindb/pinx021.dbf
                                     1,358       1    0.0     1.1          196        0          0
                                                     /oracle13/oradata/pindb/pinx022.dbf
                                     1,251       0    0.0     1.1          174        0          0
                                                     /oracle13/oradata/pindb/pinx023.dbf
                                     1,488       1    0.0     1.1          300        0          0
                                                     /oracle14/oradata/pindb/pinx025.dbf
                                     1,179       0    0.0     1.1          202        0          0
                                                     /oracle14/oradata/pindb/pinx026.dbf
                                     1,131       0    0.0     1.0          150        0          0
                                                     /oracle14/oradata/pindb/pinx027.dbf
                                     1,333       1    0.0     1.1          158        0          0
                                                     /oracle14/oradata/pindb/pinx028.dbf
                                     1,446       1    0.0     1.1          208        0          0
                                                     /oracle14/oradata/pindb/pinx029.dbf
                                     1,268       0    0.0     1.1          273        0          0
                                                     /oracle14/oradata/pindb/pinx030.dbf
                                     2,512       1    0.0     1.1          538        0          0
                                                     /oracle14/oradata/pindb/pinx031.dbf
                            File IO Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                            ->ordered by Tablespace, File
                            
                            Tablespace               Filename
                            ------------------------ ----------------------------------------------------
                                             Av      Av     Av                    Av        Buffer Av Buf
                                     Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
                            -------------- ------- ------ ------- ------------ -------- ---------- ------
                                     1,330       1    0.0     1.0          269        0          0
                            PINX00                   /oracle14/oradata/pindb/pinx032.dbf
                                     1,616       1    0.0     1.1          296        0          0
                                                     /oracle14/oradata/pindb/pinx033.dbf
                                     1,687       1    0.0     1.1          230        0          0
                                                     /oracle14/oradata/pindb/pinx034.dbf
                                     2,158       1    0.0     1.1          226        0          0
                                                     /oracle14/oradata/pindb/pinx035.dbf
                                     2,350       1    0.0     1.1          279        0          0
                                                     /oracle14/oradata/pindb/pinx036.dbf
                                     1,858       1    0.0     1.1          265        0          0
                                                     /oracle14/oradata/pindb/pinx037.dbf
                                     1,507       1    0.0     1.0          205        0          0
                                                     /oracle14/oradata/pindb/pinx038.dbf
                                     1,949       1    0.0     1.1          316        0          0
                                                     /oracle15/oradata/pindb/pinx039.dbf
                                     1,926       1    0.0     1.1          306        0          0
                                                     /oracle15/oradata/pindb/pinx043.dbf
                                       904       0    0.0     1.1          180        0          0
                                                     /oracle15/oradata/pindb/pinx044.dbf
                                     1,807       1    0.0     1.1          540        0          3    0.0
                                                     /oracle15/oradata/pindb/pinx045.dbf
                                     2,568       1    0.0     1.3          814        0          0
                                                     /oracle15/oradata/pindb/pinx046.dbf
                                     2,652       1    0.0     1.2          348        0          2    0.0
                                                     /oracle15/oradata/pindb/pinx047.dbf
                                     3,317       1    0.0     1.2          523        0          2    0.0
                                                     /oracle15/oradata/pindb/pinx048.dbf
                                     2,885       1    0.0     1.2          301        0          0
                                                     /oracle15/oradata/pindb/pinx049.dbf
                                     2,752       1    0.0     1.2          420        0          3    0.0
                                                     /oracle15/oradata/pindb/pinx050.dbf
                                     2,769       1    0.0     1.2          353        0          4    0.0
                                                     /oracle15/oradata/pindb/pinx051.dbf
                                     2,790       1    0.0     1.2          430        0          5    0.0
                                                     /oracle15/oradata/pindb/pinx052.dbf
                                     3,268       1    0.0     1.2          422        0          1    0.0
                                                     /oracle15/oradata/pindb/pinx053.dbf
                                    16,413       6    0.0     1.9          458        0         10    0.0
                                                     /oracle15/oradata/pindb/pinx054.dbf
                                    17,568       7    0.0     1.9          437        0          4    0.0
                                                     /oracle15/oradata/pindb/pinx055.dbf
                                    18,093       7    0.0     1.9          478        0          3    0.0
                                                     /oracle15/oradata/pindb/pinx056.dbf
                                     5,135       2    0.0     1.4          417        0          0
                                                     /oracle15/oradata/pindb/pinx057.dbf
                                     5,501       2    0.0     1.3          496        0          6    0.0
                                                     /oracle15/oradata/pindb/pinx058.dbf
                                     3,413       1    0.0     1.4          225        0          1    0.0
                                                     /oracle15/oradata/pindb/pinx059.dbf
                                     4,600       2    0.0     1.4          386        0          1    0.0
                                                     /oracle16/oradata/pindb/pinx060.dbf
                            File IO Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                            ->ordered by Tablespace, File
                            
                            Tablespace               Filename
                            ------------------------ ----------------------------------------------------
                                             Av      Av     Av                    Av        Buffer Av Buf
                                     Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
                            -------------- ------- ------ ------- ------------ -------- ---------- ------
                                     6,098       2    0.0     1.4          668        0          1    0.0
                            PINX00                   /oracle16/oradata/pindb/pinx061.dbf
                                     6,450       3    0.0     1.4          745        0          1    0.0
                                                     /oracle16/oradata/pindb/pinx062.dbf
                                    10,936       4    0.0     1.3        1,994        1          9    0.0
                                                     /oracle16/oradata/pindb/pinx063.dbf
                                    20,885       8    0.0     1.1        4,920        2         47    0.0
                                                     /oracle16/oradata/pindb/pinx064.dbf
                                    24,189       9    0.0     1.0       21,496        8         47    0.0
                                                     /oracle16/oradata/pindb/pinx065.dbf
                                    12,159       5    0.0     1.1       21,400        8         27    0.0
                                                     /oracle16/oradata/pindb/pinx066.dbf
                                     8,424       3    0.0     1.0       12,627        5          0
                                                     /oracle16/oradata/pindb/pinx067.dbf
                                     8,040       3    0.0     1.0       15,438        6          4    0.0
                                                     /oracle16/oradata/pindb/pinx068.dbf
                                     7,451       3    0.0     1.0       19,900        8          3    0.0
                                                     /oracle2/oradata/pindb/pinx042.dbf
                                     1,829       1    0.0     1.1          415        0          8    0.0
                                                     /oracle20/oradata/pindb/pinx020.dbf
                                       503       0    0.0     1.0           62        0          0
                                                     /oracle20/oradata/pinx004.dbf
                                     2,162       1    0.0     1.3           46        0          0
                                                     /oracle3/oradata/pindb/pinx003.dbf
                                     4,147       2    0.0     1.2          255        0          0
                                                     /oracle4/pinx002.dbf
                                     1,110       0    0.0     1.3          189        0          0
                                                     /oracle50/oradata/pinx006.dbf
                                     4,527       2    0.0     1.2        1,488        1          1    0.0
                                                     /oracle50/oradata/pinx007.dbf
                                       884       0    0.0     1.2           71        0          0
                                                     /oracle7/oradata/pinx005.dbf
                                       744       0    0.0     1.0          362        0          0
                                                     /oracle8/oradata/pindb002.dbf
                                       902       0    0.0     1.2           45        0          0
                                                     /oracle8/oradata/pindb003.dbf
                                       580       0    0.0     1.3           29        0          0
                                                     /oracle8/oradata/pindb006.dbf
                                       315       0    0.0     1.0           43        0          0
                                                     /oracle9/oradata/pindb/pinx008.dbf
                                       836       0    0.0     1.2           67        0          0
                                                     /oracle9/oradata/pindb/pinx009.dbf
                                       416       0    0.0     1.0           64        0          0
                                                     /oracle9/oradata/pindb/pinx010.dbf
                                       855       0    0.0     1.2          100        0          0
                                                     /oracle9/oradata/pindb/pinx011.dbf
                                       491       0    0.0     1.0          103        0          0
                                                     /oracle9/oradata/pindb/pinx017.dbf
                                       479       0    0.0     1.0           98        0          0
                                                     /orapurge/oradata/pindb/pinx069.dbf
                                     8,105       3    0.0     1.0       10,760        4          5    0.0
                            
                            File IO Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                            ->ordered by Tablespace, File
                            
                            Tablespace               Filename
                            ------------------------ ----------------------------------------------------
                                             Av      Av     Av                    Av        Buffer Av Buf
                                     Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
                            -------------- ------- ------ ------- ------------ -------- ---------- ------
                            RBS                      /oracle3/oradata/pindb/rbs01.dbf
                                       151       0    0.0     1.0       25,364       10         18    0.0
                                                     /oracle7/oradata/rbs02.dbf
                                       109       0    0.0     1.0       10,538        4          0
                            
                            SYSTEM                   /oracle1/oradata/pindb/system01.dbf
                                     3,223       1    0.0     1.8        5,228        2          0
                                                     /oracle1/oradata/pindb/system02.dbf
                                       791       0    0.0     1.5           28        0          0
                            
                            TEMP                     /oracle13/oradata/pindb/temp01.dbf
                                     1,680       1    0.0     8.1          816        0          0
                                                     /oracle4/oradata/pindb/temp01.dbf
                                         3       0    0.0     1.0            3        0          0
                            
                            TOOLS                    /oracle14/oradata/pindb/tools01.dbf
                                         3       0    0.0     1.0            3        0          0
                            
                                      -------------------------------------------------------------
                            Buffer Pool Statistics for DB: PINDB  Instance: pindb  Snaps: 213 -214
                            -> Pools   D: default pool,  K: keep pool,  R: recycle pool
                            
                                                                                  Free    Write     Buffer
                                   Buffer    Consistent    Physical   Physical  Buffer Complete       Busy
                            P        Gets          Gets       Reads     Writes   Waits    Waits      Waits
                            - ----------- ------------- ----------- ---------- ------- -------- ----------
                            D  47,803,200             0  47,757,603    234,201       0        0     17,415
                                      -------------------------------------------------------------
                            
                            
                            
                            
                            
                            Buffer wait Statistics for DB: PINDB  Instance: pindb  Snaps: 213 -214
                            -> ordered by wait time desc, waits desc
                            
                                                             Tot Wait    Avg
                            Class                    Waits  Time (cs) Time (cs)
                            ------------------ ----------- ---------- ---------
                            data block              11,674          0         0
                            segment header           5,628          0         0
                            bitmap block                87          0         0
                            undo header                 20          0         0
                            bitmap index block           7          0         0
                                      -------------------------------------------------------------
                            
                            
                            
                            
                            
                            Enqueue activity for DB: PINDB  Instance: pindb  Snaps: 213 -214
                            -> ordered by waits desc, gets desc
                            
                            Enqueue            Gets      Waits
                            ---------- ------------ ----------
                            HW                5,673      2,283
                            ST                2,642         68
                            TX               22,460         19
                                      -------------------------------------------------------------
                            Rollback Segment Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                            ->A high value for "Pct Waits" suggests more rollback segments may be required
                            
                                   Trans Table      Pct   Undo Bytes
                            RBS No     Gets       Waits     Written        Wraps  Shrinks  Extends
                            ------ ------------ ------- --------------- -------- -------- --------
                                 0          9.0    0.00               0        0        0        0
                                 1      2,588.0    0.00       1,951,754        5        0        0
                                 2         12.0    0.00             544        0        0        0
                                 3      2,900.0    0.00       2,089,014        5        0        0
                                 4      2,746.0    0.00       2,042,170        5        0        0
                                 5         22.0    0.00               0        0        0        0
                                 6      2,530.0    0.00       1,863,098        2        0        0
                                 7      2,466.0    0.00       1,761,994        2        0        0
                                 8      2,938.0    0.00       2,172,908        5        0        0
                                 9      1,435.0    0.00         619,208        2        0        0
                                10      2,648.0    0.00       1,821,670        4        0        0
                                11      4,300.0    0.00       4,429,678       11        0        0
                                12      2,635.0    0.00       1,922,330        5        0        0
                                13      2,787.0    0.00       1,951,722        5        0        0
                                14      2,227.0    0.00       1,711,232        4        0        0
                                15      2,856.0    0.00       2,177,332        5        0        0
                                16      2,768.0    0.00       2,005,722        4        0        0
                                17      2,602.0    0.00       1,848,938        4        0        0
                                18      2,768.0    0.00       1,950,978        4        0        0
                                19      3,027.0    0.00       2,402,778        5        0        0
                                20      2,572.0    0.00       1,845,408        4        0        0
                                21      2,831.0    0.00       2,124,440        5        0        0
                                23      2,711.0    0.00       2,068,342        1        0        0
                                24      3,010.0    0.00       2,208,754      171        0        0
                                26      2,685.0    0.00       1,941,768        0        0        0
                                      -------------------------------------------------------------
                            Rollback Segment Storage for DB: PINDB  Instance: pindb  Snaps: 213 -214
                            ->Optimal Size should be larger than Avg Active
                            
                            RBS No    Segment Size      Avg Active    Optimal Size    Maximum Size
                            ------ --------------- --------------- --------------- ---------------
                                 0         671,744           6,144                         671,744
                                 1       4,190,208         523,811       4,194,304      13,627,392
                                 2       4,190,208         523,636       4,194,304       5,763,072
                                 3       4,190,208         523,858       4,194,304       5,763,072
                                 4       4,190,208         523,701       4,194,304      10,481,664
                                 5       4,190,208         523,564       4,194,304      30,928,896
                                 6       4,714,496         944,059       4,194,304       6,811,648
                                 7       4,714,496         919,543       4,194,304       4,714,496
                                 8       4,190,208         523,759       4,194,304      11,530,240
                                 9       4,190,208         523,759       4,194,304       7,335,936
                                10       4,190,208         523,564       4,194,304       5,763,072
                                11       4,190,208       1,136,667       4,194,304      14,675,968
                                12       4,190,208         523,811       4,194,304      12,054,528
                                13       4,190,208         523,759       4,194,304      22,540,288
                                14       4,190,208         523,564       4,194,304       5,763,072
                                15       4,190,208         523,636       4,194,304      11,005,952
                                16       4,190,208         523,939       4,194,304      16,773,120
                                17       4,190,208         523,564       4,194,304      41,938,944
                                18       4,190,208         523,939       4,194,304       4,190,208
                                19       4,190,208         523,564       4,194,304      13,627,392
                                20       4,190,208         523,759       4,194,304      24,637,440
                                21       4,231,168         528,801       4,194,304      10,604,544
                                23     838,856,704      58,526,127     838,860,800     838,856,704
                                24      19,496,960          17,219     209,715,200      19,496,960
                                26      83,881,984      10,486,530     209,715,200      83,881,984
                            • 11. Re: update seg$  top 2nd in SQL ordered by Gets in statspack report
                              615488
                              5th part:
                                       -------------------------------------------------------------
                              Latch Activity for DB: PINDB  Instance: pindb  Snaps: 213 -214
                              ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
                                willing-to-wait latch get requests
                              ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
                              ->"Pct Misses" for both should be very close to 0.0
                              
                                                                              Pct    Avg                 Pct
                                                                 Get          Get   Slps       NoWait NoWait
                              Latch Name                       Requests      Miss  /Miss     Requests   Miss
                              ----------------------------- -------------- ------ ------ ------------ ------
                              Token Manager                         69,447    0.0    0.0        1,011    0.0
                              active checkpoint queue latch         52,924    0.1    0.0            0
                              archive control                            6    0.0                   0
                              archive process latch                      6    0.0                   0
                              begin backup scn array                 2,085    0.0                   0
                              cache buffer handles                  72,809    0.0    1.0            0
                              cache buffers chains             395,432,399    0.0    0.1   90,706,125    0.0
                              cache buffers lru chain           55,733,018    0.3    0.0   47,780,975    0.3
                              channel handle pool latch              3,441    0.0               3,439    0.0
                              channel operations parent lat          6,882    0.0               3,439    0.0
                              checkpoint queue latch             1,940,709    0.0    0.2            0
                              dml lock allocation                  152,593    0.0    0.0            0
                              enqueue hash chains                  264,611    0.4    0.0            0
                              enqueues                             264,469    0.2    0.0            0
                              event group latch                      3,441    0.0                   0
                              global transaction                     4,752    0.0                   0
                              global tx free list                      419    0.0                   0
                              global tx hash mapping                 1,704    0.0                   0
                              job_queue_processes parameter             40    0.0                   0
                              ktm global data                            8    0.0                   0
                              latch wait list                          623    0.6    0.0          619    0.3
                              library cache                      3,605,352    0.1    0.2        7,196    0.1
                              library cache load lock                  216    0.0                   0
                              list of block allocation              53,691    0.0    0.0            0
                              loader state object freelist          31,314    0.0                   0
                              longop free list                   1,145,350    0.6    0.0            0
                              messages                             368,148    0.1    0.1            0
                              multiblock read objects           23,919,471    0.4    0.0            0
                              ncodef allocation latch                   40    0.0                   0
                              process allocation                     3,441    0.0               3,441    0.0
                              process group creation                 6,880    0.0                   0
                              redo allocation                      479,512    0.0    0.2            0
                              redo writing                         164,982    0.2    0.1            0
                              row cache objects                  1,620,110    0.0    0.2        2,018    0.1
                              sequence cache                        67,061    0.0    0.0            0
                              session allocation                   166,576    0.0    0.2            0
                              session idle bit                   1,399,944    0.0    0.3            0
                              session switching                         40    0.0                   0
                              session timer                            831    0.0                   0
                              shared pool                          529,734    0.1    0.3            0
                              sort extent pool                       2,760    0.0                   0
                              transaction allocation               140,524    0.0    0.1            0
                              transaction branch allocation          1,494    0.0                   0
                              undo global data                     133,432    0.0    0.1            0
                              user lock                             13,778    0.0                   0
                              virtual circuit queues                   420    0.0                   0
                                        -------------------------------------------------------------
                              Latch Sleep breakdown for DB: PINDB  Instance: pindb  Snaps: 213 -214
                              -> ordered by misses desc
                              
                                                              Get                                  Spin &
                              Latch Name                    Requests         Misses      Sleeps Sleeps 1->4
                              -------------------------- -------------- ----------- ----------- ------------
                              cache buffers lru chain        55,733,018     191,282       3,883 187408/3865/
                                                                                                9/0/0
                              multiblock read objects        23,919,471      93,175       1,167 92009/1165/1
                                                                                                /0/0
                              cache buffers chains          395,432,399      30,700       2,109 28894/1585/1
                                                                                                72/49/0
                              longop free list                1,145,350       6,450          66 6384/66/0/0/
                                                                                                0
                              library cache                   3,605,352       4,293         769 3725/396/143
                                                                                                /29/0
                              enqueue hash chains               264,611         995          27 969/25/1/0/0
                              enqueues                          264,469         516          10 506/10/0/0/0
                              row cache objects               1,620,110         384          88 305/71/7/1/0
                              redo writing                      164,982         369          19 351/17/1/0/0
                              shared pool                       529,734         300          76 249/26/25/0/
                                                                                                0
                              messages                          368,148         288          17 271/17/0/0/0
                              checkpoint queue latch          1,940,709         235          44 191/44/0/0/0
                              redo allocation                   479,512          78          16 65/10/3/0/0
                              session idle bit                1,399,944          50          17 35/14/0/1/0
                              active checkpoint queue la         52,924          40           1 39/1/0/0/0
                              session allocation                166,576          29           6 23/6/0/0/0
                              transaction allocation            140,524          23           3 20/3/0/0/0
                              undo global data                  133,432           9           1 8/1/0/0/0
                              cache buffer handles               72,809           1           1 0/1/0/0/0
                                        -------------------------------------------------------------
                              Latch Miss Sources for DB: PINDB  Instance: pindb  Snaps: 213 -214
                              -> only latches with sleeps are shown
                              -> ordered by name, sleeps desc
                              
                                                                                   NoWait             Waiter
                              Latch Name               Where                       Misses     Sleeps  Sleeps
                              ------------------------ -------------------------- ------- ---------- -------
                              active checkpoint queue  kcbbacq: scan active check       0          1       1
                              cache buffer handles     kcbzgs                           0          1       1
                              cache buffers chains     kcbrls: kslbegin                 0      1,163     147
                              cache buffers chains     kcbgtcr: kslbegin                0        454   1,558
                              cache buffers chains     kcbzib: multi-block read:        0        309       0
                              cache buffers chains     kcbgcur: kslbegin                0         84     143
                              cache buffers chains     kcbzgb: scan from tail. no       0         25       0
                              cache buffers chains     kcbzib: finish free bufs         0         17      50
                              cache buffers chains     kcbget: pin buffer               0         13      15
                              cache buffers chains     kcbzwb                           0         13       2
                              cache buffers chains     kcbchg: kslbegin: bufs not       0          7     103
                              cache buffers chains     kcbbxsv                          0          6      11
                              cache buffers chains     kcbbwb1                          0          4      14
                              cache buffers chains     kcbgtcr                          0          4       0
                              cache buffers chains     kcbget: exchange                 0          3       0
                              cache buffers chains     kcbget: exchange rls             0          3       0
                              cache buffers chains     kcbchg: kslbegin: call CR        0          2      49
                              cache buffers chains     kcbibr                           0          1       9
                              cache buffers chains     kcbnlc                           0          1       3
                              cache buffers lru chain  kcbzgb: multiple sets nowa       0      3,034       0
                              cache buffers lru chain  kcbbiop: lru scan                0        510       0
                              cache buffers lru chain  kcbzar: KSLNBEGIN                0        253   3,066
                              cache buffers lru chain  kcbzgm                           0         66     790
                              cache buffers lru chain  kcbzgb: posted for free bu       0         15       8
                              cache buffers lru chain  kcbbioc                          0          3      15
                              checkpoint queue latch   kcbk0rrd: update recovery        0         27       0
                              checkpoint queue latch   kcbkllrba: compute lowest        0          7      30
                              checkpoint queue latch   kcbklbc: Link buffer into        0          5       9
                              checkpoint queue latch   kcbbxsv: move to being wri       0          4       2
                              checkpoint queue latch   kcbbcrcv: check recovery q       0          1       2
                              enqueue hash chains      ksqgtl3                          0         14      24
                              enqueue hash chains      ksqrcl                           0         10       3
                              enqueue hash chains      ksqcmi: get hash chain lat       0          2       0
                              enqueue hash chains      ksqcmi: kslgpl                   0          1       0
                              enqueues                 ksqdel                           0          5       5
                              enqueues                 ksqgtl2                          0          4       1
                              enqueues                 ksqgel: create enqueue           0          1       1
                              library cache            kglhdgn: child:                  0        292      32
                              library cache            kglic                            0        204     350
                              library cache            kglpnal: child: alloc spac       0         65      90
                              library cache            kglpnal: child: before pro       0         46      88
                              library cache            kgllkdl: child: cleanup          0         35       2
                              library cache            kglhdgc: child:                  0         22       4
                              library cache            kglpnp: child                    0          5       9
                              library cache            kglget: child: KGLDSBYD          0          4      38
                              library cache            kglsca: parent                   0          3       0
                              library cache            kgldte: child 0                  0          2      28
                              library cache            kglpin                           0          2      47
                              longop free list         ksuloget                         0         66      66
                              messages                 ksarcv: after wait               0          9       3
                              messages                 ksaamb: after wakeup             0          6      12
                              messages                 ksarcv                           0          2       2
                              multiblock read objects  kcbzib: MBRGET                   0        614     623
                              Latch Miss Sources for DB: PINDB  Instance: pindb  Snaps: 213 -214
                              -> only latches with sleeps are shown
                              -> ordered by name, sleeps desc
                              
                                                                                   NoWait             Waiter
                              Latch Name               Where                       Misses     Sleeps  Sleeps
                              ------------------------ -------------------------- ------- ---------- -------
                              multiblock read objects  kcbzib: MBRFRE                   0        552     543
                              redo allocation          kcrfwr: redo allocation          0         14      12
                              redo allocation          kcrfwi: before write             0          2       1
                              redo writing             kcrfsr                           0         14       1
                              redo writing             kcrfss                           0          2      11
                              redo writing             kcrfwi: after write              0          2       1
                              redo writing             kcrfwcr                          0          1       6
                              row cache objects        kqrpsc: incr stat                0         79       4
                              row cache objects        kqrpre: find obj                 0          8      47
                              row cache objects        kqreqd                           0          1      21
                              session allocation       ksuxds: KSUSFCLC not set         0          3       0
                              session allocation       ksucri                           0          2       4
                              session allocation       ksuxds: not user session         0          1       1
                              session idle bit         ksupuc: clear busy               0         10      12
                              session idle bit         ksupuc: set busy                 0          7       3
                              shared pool              kghfrunp: clatch: nowait         0         86       0
                              shared pool              kghfrunp: alloc: clatch no       0         41       0
                              shared pool              kghalo                           0         15      29
                              shared pool              kghfre                           0          9      26
                              shared pool              kghfnd: req scan                 0          6       0
                              shared pool              kghfnd: min scan                 0          2       0
                              shared pool              kghfrunp: clatch: wait           0          2       3
                              shared pool              kghupr1                          0          2      21
                              shared pool              kghalp                           0          1       0
                              transaction allocation   ktcxba                           0          2       3
                              transaction allocation   ktcdso                           0          1       0
                              undo global data         ktudba: KSLBEGIN                 0          1       0
                                        -------------------------------------------------------------
                              Dictionary Cache Stats for DB: PINDB  Instance: pindb  Snaps: 213 -214
                              ->"Pct Misses"  should be very low (< 2% in most cases)
                              ->"Cache Usage" is the number of cache entries being used
                              ->"Pct SGA"     is the ratio of usage to allocated size for that cache
                              
                                                         Get         Pct     Scan   Pct      Mod  Final  Pct
                              Cache                    Requests     Miss Requests  Miss      Req  Usage  SGA
                              ---------------------- ------------ ------ -------- ----- -------- ------ ----
                              dc_constraints                    0               0              0     10   91
                              dc_database_links               636    0.0        0              0      9   90
                              dc_files                          0               0              0      2   67
                              dc_free_extents              11,161   12.1   53,561   0.0    4,058  1,440  100
                              dc_global_oids                    0               0              0      0    0
                              dc_histogram_data           148,563    0.1        0              0  2,206  100
                              dc_histogram_data_valu       74,648    0.1        0              0    859  100
                              dc_histogram_defs            59,313    0.1        0              0  1,816  100
                              dc_object_ids               206,777    0.0        0              0    464   99
                              dc_objects                   14,900    0.5        0              2    479  100
                              dc_outlines                       0               0              0      0    0
                              dc_profiles                   3,444    0.0        0              0      1   33
                              dc_rollback_segments            432    0.0        0              0     28   80
                              dc_segments                  45,449    0.0        0          1,354    404  100
                              dc_sequence_grants                0               0              0      0    0
                              dc_sequences                  1,088    0.0        0          1,084     18   95
                              dc_synonyms                      47   29.8        0              0     55   96
                              dc_tablespace_quotas          1,354    0.0        0          1,354      6   86
                              dc_tablespaces                5,968    0.0        0              0     11   73
                              dc_used_extents               1,354  100.0        0          1,354 ######  100
                              dc_user_grants               40,940    0.0        0              0     13   45
                              dc_usernames                  7,204    0.0        0              0     13   59
                              dc_users                     65,000    0.0        0              0     15   88
                              ifs_acl_cache_entries             0               0              0      0    0
                                        -------------------------------------------------------------
                              
                              
                              Library Cache Activity for DB: PINDB  Instance: pindb  Snaps: 213 -214
                              ->"Pct Misses"  should be very low
                              
                                                  Get       Pct        Pin        Pct               Invali-
                              Namespace         Requests    Miss     Requests     Miss     Reloads  dations
                              --------------- ------------ ------ -------------- ------ ---------- --------
                              BODY                       6   16.7              6   16.7          0        0
                              CLUSTER                  194    0.0            148    0.0          0        0
                              INDEX                      2    0.0              3   66.7          1        0
                              OBJECT                     0                     0                 0        0
                              PIPE                       0                     0                 0        0
                              SQL AREA              48,707    7.1        490,655    1.4        103        0
                              TABLE/PROCEDURE       48,516    0.2        203,346    0.1         71        0
                              TRIGGER                   88    1.1             88    1.1          0        0
                                        -------------------------------------------------------------
                              SGA Memory Summary for DB: PINDB  Instance: pindb  Snaps: 213 -214
                              
                              SGA regions                       Size in Bytes
                              ------------------------------ ----------------
                              Database Buffers                  1,677,721,600
                              Fixed Size                              102,076
                              Redo Buffers                            180,224
                              Variable Size                     1,514,905,600
                                                             ----------------
                              sum                               3,192,909,500
                                        -------------------------------------------------------------
                              
                              
                              SGA breakdown difference for DB: PINDB  Instance: pindb  Snaps: 213 -214
                              
                              Pool        Name                        Begin value      End value  Difference
                              ----------- ------------------------ -------------- -------------- -----------
                              java pool   free memory                  20,971,520     20,971,520           0
                              large pool  PX msg pool                  26,738,712     26,738,712           0
                              large pool  free memory                  46,661,608     46,661,608           0
                              shared pool Checkpoint queue                673,120        673,120           0
                              shared pool DML locks                       491,232        491,232           0
                              shared pool KGFF heap                       153,936        153,936           0
                              shared pool KGK heap                         20,488         20,488           0
                              shared pool KQLS heap                     1,480,992      1,452,624     -28,368
                              shared pool PL/SQL DIANA                    558,312        558,312           0
                              shared pool PL/SQL MPCODE                   189,832        195,800       5,968
                              shared pool PL/SQL PPCODE                    12,184         12,184           0
                              shared pool PLS non-lib hp                    2,136          2,136           0
                              shared pool PX subheap                      251,264        251,264           0
                              shared pool State objects                 1,284,480      1,284,480           0
                              shared pool VIRTUAL CIRCUITS              1,388,744      1,388,744           0
                              shared pool db_block_buffers             85,196,800     85,196,800           0
                              shared pool db_block_hash_buckets        13,861,328     13,861,328           0
                              shared pool db_handles                      528,000        528,000           0
                              shared pool dictionary cache             11,542,480     12,263,728     721,248
                              shared pool enqueue_resources               337,376        337,376           0
                              shared pool event statistics per ses      2,308,880      2,308,880           0
                              shared pool fixed allocation callbac          1,904          1,904           0
                              shared pool free memory                 266,342,128    245,425,320 -20,916,808
                              shared pool ktlbk state objects             391,816        391,816           0
                              shared pool library cache               193,833,056    197,227,432   3,394,376
                              shared pool miscellaneous                 3,433,488      3,433,608         120
                              shared pool processes                       643,200        643,200           0
                              shared pool sessions                      1,617,280      1,617,280           0
                              shared pool simulator trace entries         400,000        400,000           0
                              shared pool sql area                    434,592,840    449,453,240  14,860,400
                              shared pool state objects               397,783,304    399,742,536   1,959,232
                              shared pool table columns                    39,688         41,192       1,504
                              shared pool table definiti                   17,016         19,416       2,400
                              shared pool transactions                  1,111,120      1,111,120           0
                              shared pool trigger defini                    2,200          2,128         -72
                              shared pool trigger inform                      664            664           0
                                          db_block_buffers          1,677,721,600  1,677,721,600           0
                                          fixed_sga                       102,076        102,076           0
                                          log_buffer                      163,840        163,840           0
                                        -------------------------------------------------------------
                              init.ora Parameters for DB: PINDB  Instance: pindb  Snaps: 213 -214
                              
                                                                                                End value
                              Parameter Name                Begin value                       (if different)
                              ----------------------------- --------------------------------- --------------
                              _db_file_noncontig_mblock_rea 1
                              background_dump_dest          /oracle/admin/pindb/bdump
                              compatible                    8.1.0
                              control_file_record_keep_time 30
                              control_files                 /oracle1/oradata/pindb/control01.
                              core_dump_dest                /oracle/admin/pindb/cdump
                              cursor_space_for_time         TRUE
                              db_block_buffers              409600
                              db_block_lru_latches          20
                              db_block_size                 4096
                              db_file_multiblock_read_count 8
                              db_files                      300
                              db_name                       pindb
                              db_writer_processes           8
                              distributed_transactions      20
                              hash_area_size                8388608
                              hash_multiblock_io_count      8
                              instance_name                 pindb
                              java_pool_size                20971520
                              job_queue_interval            60
                              job_queue_processes           4
                              large_pool_size               73400320
                              log_archive_dest_1            location=/oratranslog
                              log_archive_format            arch_%t_%s.arc
                              log_archive_start             TRUE
                              log_buffer                    163840
                              log_checkpoint_interval       1048576
                              max_enabled_roles             30
                              mts_dispatchers               (PROTOCOL=TCP)(PRE=oracle.aurora.
                              mts_max_dispatchers           20
                              mts_max_servers               20
                              mts_servers                   3
                              open_cursors                  700
                              open_links                    4
                              optimizer_index_cost_adj      30
                              os_authent_prefix
                              parallel_automatic_tuning     TRUE
                              parallel_threads_per_cpu      8
                              processes                     600
                              query_rewrite_enabled         TRUE
                              remote_login_passwordfile     EXCLUSIVE
                              rollback_segments             pinroll, pinroll02, pinroll03
                              service_names                 pindb
                              session_cached_cursors        35
                              sessions                      665
                              shared_pool_size              1310720000
                              sort_area_retained_size       2097152
                              sort_area_size                104857600
                              sort_multiblock_read_count    8
                              star_transformation_enabled   true
                              timed_statistics              FALSE
                              user_dump_dest                /oracle/admin/pindb/udump
                              utl_file_dir                  /oracle2
                                        -------------------------------------------------------------
                              
                              End of Report
                              Thanks

                              Prakash
                              • 12. Re: update seg$  top 2nd in SQL ordered by Gets in statspack report
                                Charles Hooper
                                prakashdba wrote:
                                hi Charles Hooper,
                                How many CPUs are in the server?
                                SQL> show parameter cpu

                                NAME TYPE VALUE
                                ------------------------------------ ------- ------------------------------
                                cpu_count integer 4
                                parallel_threads_per_cpu integer 8
                                >
                                It appears that timed_statistics was not enabled when the Statspack Report was created
                                timed_statistics = FALSE (should be set to TRUE if you are investigating performance issues)
                                i enabled timed_statistics when i was taking statspack report connecting to perfstat user using;
                                sql>alter session set timed_statistics=true;
                                The problem with enabling timed_statistics at the session level for the session collecting the Statspack report is that timing information is only collected for the session collecting the Statspack report - that causes significant problems when trying to determine if the activity in the system is a real performance problem (for instance a calculated average single block read time of 0.000004746630). timed_statistics needs to be enabled for all sessions - there may be a slight performance impact from setting this parameter system-wide, but the benefits will be worthwhile.
                                i am posting entire statspack report
                                Thanks, I will read through the other sections of the report to see if anything else appears to be interesting.
                                SYSTEM and TEMP tablespace are showing a lot of activity, what tablespace contains the user data for user PIN?
                                PIN00
                                Determining why DB_WRITER_PROCESSES is set to 8
                                SQL> show parameter disk_asynch_io

                                NAME TYPE VALUE
                                ------------------------------------ ------- ------------------------------
                                disk_asynch_io boolean TRUE
                                The reason why I suggested investigating why DB_WRITER_PROCESSES=8 is that doing so may decrease performance, see:
                                http://kevinclosson.wordpress.com/2007/08/17/over-configuring-dbwr-processes-part-ii/
                                http://kevinclosson.wordpress.com/2007/08/10/learn-how-to-obliterate-processor-caches-configure-lots-and-lots-of-dbwr-processes/

                                Suggestions:
                                * Enable timed_statistics system-wide and capture another Statspack report (see previous posts for other suggestions) and post the new Statspack report.
                                * Review the link provided by Jonathan Lewis (he saw a couple potential problems that I missed)

                                Charles Hooper
                                IT Manager/Oracle DBA
                                K&M Machine-Fabricating, Inc.
                                • 13. Re: update seg$  top 2nd in SQL ordered by Gets in statspack report
                                  615488
                                  Hi Charles Hooper,


                                  Very much thanks for your valuable time spent on giving suggestions and advices i will go through your suggestions, links, and i will implement according to that.

                                  Cheers

                                  Prakash GR
                                  • 14. Re: update seg$  top 2nd in SQL ordered by Gets in statspack report
                                    615488
                                    Hi Jonathan Lewis,

                                    Thanks for your valuable inputs.

                                    Thanks

                                    Prakash GR
                                    1 2 Previous Next