1 2 3 Previous Next 30 Replies Latest reply: Jun 17, 2009 10:18 AM by Michael C RSS

    waht should be the value of Optimizer_index_cost_adj  in oracle 9i

    user00726
      Hi all,

      We have a oracle 9i database.......

      My wuestion what should be the value of all the below parameters...
      SQL> show parameter optimizer
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- --------------
      optimizer_dynamic_sampling           integer     1
      optimizer_features_enable            string      9.2.0.1
      optimizer_index_caching              integer     0
      optimizer_index_cost_adj             integer     100
      optimizer_max_permutations           integer     2000
      optimizer_mode                       string      CHOOSE
      SQL>
      
      
      
      SQL> 1
        1* select EVENT,TOTAL_WAITS,AVERAGE_WAIT from v$system_event
      SQL> 2 order by 2;
      SQL> /
      
      EVENT                               TOTAL_WAITS AVERAGE_WAIT
      ----------------------------------- ----------- ------------
      reliable message                              1            0
      wait for sync ack                             1           49
      wait for tmc2 to complete                     1            3
      refresh controlfile command                   1            4
      control file heartbeat                        1          391
      global cache cancel wait                      2            0
      global cache assume wait                      2            0
      instance state change                         2            0
      log buffer space                              3            2
      ges LMD to inherit communication ch           4            5
      annels
      
      EVENT                               TOTAL_WAITS AVERAGE_WAIT
      ----------------------------------- ----------- ------------
      
      ges reconfiguration to start                  4            2
      log switch/archive                            4          818
      library cache load lock                       4            1
      ges cgs registration                          6           20
      switch logfile command                        7           77
      sbtinit                                       8            0
      ges global resource directory to be           8            3
       frozen
      
      ges lmd/lmses to unfreeze in rcfg -          12            3
      
      EVENT                               TOTAL_WAITS AVERAGE_WAIT
      ----------------------------------- ----------- ------------
       mrcvr
      
      lock deadlock retry                          16            0
      lock escalate retry                          17           41
      buffer deadlock                              19            0
      retry contact SCN lock master                22            9
      ges lmd/lmses to freeze in rcfg - m          23            3
      rcvr
      
      global cache domain validation               26          100
      ges global resource directory to be          29            2
      
      EVENT                               TOTAL_WAITS AVERAGE_WAIT
      ----------------------------------- ----------- ------------
       unfrozen
      
      latch activity                               30           11
      log file switch completion                   37           10
      wait for votes                               38            4
      buffer busy global cache                     41            3
      kjctssqmg: quick message send wait           44            0
      log file single write                        63            0
      slave TJ process wait                        69            2
      ges inquiry response                         94            0
      control file single write                   136            3
      
      EVENT                               TOTAL_WAITS AVERAGE_WAIT
      ----------------------------------- ----------- ------------
      db file single write                        147            0
      ges enter server mode                       159          188
      ges LMON to get to FTDONE                   175            3
      inactive session                            196           97
      global cache busy                           211           41
      global cache freelist wait                  468           23
      ges2 LMON to wake up LMD - mrcvr            508            3
      ges2 LMON to wake up lms - mrcvr 2         1002            3
      wait list latch free                       1043            3
      name-service call wait                     1379           15
      PX Deq: Table Q Normal                     1675            1
      
      EVENT                               TOTAL_WAITS AVERAGE_WAIT
      ----------------------------------- ----------- ------------
      PX Deq: Msg Fragment                       1861            0
      log file sequential read                   2954            2
      local write wait                           3200            1
      PX Deq: Signal ACK                         6163            1
      BFILE closure                              7340            0
      BFILE open                                 7340            0
      PX qref latch                              7661            2
      undo segment extension                     7830            0
      PX Deq: Parse Reply                        9037            1
      global cache quiesce wait                  9170            1
      cr request retry                          10804            0
      
      EVENT                               TOTAL_WAITS AVERAGE_WAIT
      ----------------------------------- ----------- ------------
      PX Deq: Join ACK                          11403            1
      IPC send completion sync                  12233            0
      LGWR wait for redo copy                   12463            0
      library cache pin                         16747            1
      global cache null to s                    17238            0
      global cache open s                       18276            0
      PX Deq Credit: need buffer                28738            2
      rdbms ipc reply                           30448            0
      PX Deq: Execution Msg                     36837           81
      BFILE get length                          42313            0
      buffer busy global CR                     48220            3
      
      EVENT                               TOTAL_WAITS AVERAGE_WAIT
      ----------------------------------- ----------- ------------
      buffer busy waits                         61937            4
      BFILE read                                72180            0
      BFILE internal seek                       72180            0
      SQL*Net break/reset to client             95852            0
      process startup                           97296           16
      smon timer                               105682         3297
      direct path read (lob)                   110572            0
      PX Idle Wait                             123179          240
      KJC: Wait for msg sends to complete      156664            0
      PX Deq Credit: send blkd                 206833            1
      SQL*Net more data from client            207782            2
      
      EVENT                               TOTAL_WAITS AVERAGE_WAIT
      ----------------------------------- ----------- ------------
      global cache open x                      247251            0
      log file sync                            279795            1
      global cache null to x                   287788            0
      library cache lock                       310497            0
      db file parallel write                   389995            1
      global cache s to x                      413101            0
      wait for master scn                     1223340            0
      control file parallel write             1228520            0
      pmon timer                              1264499          285
      async disk IO                           1372835            0
      row cache lock                          1695921            0
      
      EVENT                               TOTAL_WAITS AVERAGE_WAIT
      ----------------------------------- ----------- ------------
      log file parallel write                 2052993            0
      direct path write                       2294039            0
      PX Deq: Execute Reply                   2794431            0
      jobq slave wait                         2826270          291
      db file parallel read                   3651422            5
      control file sequential read            4955734            0
      ksxr poll remote instances              5892683            0
      DFS lock handle                         6082717            0
      latch free                              6445502            2
      PX Deq: reap credit                     7377155            0
      direct path read                       11164536            0
      
      EVENT                               TOTAL_WAITS AVERAGE_WAIT
      ----------------------------------- ----------- ------------
      enqueue                                25477002            1
      CGS wait for IPC msg                   34538272            0
      db file scattered read                 46061111            0
      rdbms ipc message                      51128984           76
      db file sequential read                62452600            1
      ges remote message                    110882900            3
      global cache cr request               129116276            0
      gcs remote message                    183741952            4
      SQL*Net more data to client           567238690            0
      SQL*Net message from client           782068658           13
      SQL*Net message to client             782069617            0
      
      111 rows selected.
      
      SQL>
      
      I the dbazine.com , igot to know that ideally the value for the optimizer_index_cost_adj should be calulated as (df file sequential read/db file scattered read ) * 100
      
      And i am getting the value 135.....
      and what about all the other paramters.......

      Pls suggest me....

      Does it have a impact on the perfomance of the database or not......

      I have one more question ,the question i have been asked how you will calcualate the value of db_block_size, give me a calulation...

      Please throw some lights on the same....
        • 1. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
          P.Forstmann
          Do you have performance issue ?
          Have you identified slow database session or slow queries ?
          Please post some Statspack reports and SQL trace/TKPROF report of slow database queries/database sessions.

          No one can tell how what will be the effect of changing a instance parameter on your system: only you can tell by testing it in the right environment.
          • 2. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
            Jonathan Lewis
            >
            PX Deq: reap credit 7377155 0
            direct path read 11164536 0
            enqueue 25477002 1
            CGS wait for IPC msg 34538272 0
            db file scattered read 46061111 0
            rdbms ipc message 51128984 76
            db file sequential read 62452600 1
            ges remote message 110882900 3
            global cache cr request 129116276 0
            gcs remote message 183741952 4
            I the dbazine.com , igot to know that ideally the value for the optimizer_index_cost_adj should be calulated as (df file sequential read/db file scattered read ) * 100
            
            Does it have a impact on the perfomance of the database or not......
            
            I have one more question ,the question i have been asked how you will calcualate the value of db_block_size, give me a calulation...
            Searching www.dbazine.com for optimizer_index_cost_adj, the first hit was an article I wrote several years ago which includes this warning:

            <ul>
            On a well-written system, it might be possible to compare the multiblock disk read times and single block disk read times (“db file scattered read,” “db file sequential read”) from v$system_event. This could give you some indication of the effective performance of your hardware under a real load.

            +However, as [ed: now-defunct reference] shows, a few very badly behaved sessions protected by a file system buffer could give very misleading results at the system level. To work around this problem you might choose to work with the same events, but use the v$session_event view to identify and eliminate the rogue sessions.+
            </ul>

            Ideally, you don't set the optimizer_index_cost_adj from 9i onwards, you enable CPU costing (which is enabled by default in 10g) but you still need to ensure (in cases like yours) that you get some rational figures for the hardware.

            Before you worry about such global changes though, you first need to spend a little time checking whether your entire system is being damaged by a couple of design flaws, some misleading statistics, or a few extreme SQL statements -- or whether the problem really is a generic configuration problem that needs a little tweaking to some of the less desirable parameters.

            Looking at your waits I see you are running RAC, have a lot of cross-instance traffic, make use of parallel execution, do a lot of serial table scans and (probably) serial index fast full scans, and get a lot of I/O protection from a cache somewhere outside Oracle. Basically your system doesn't seem to be working very efficiently (and, by the way, changing the block size from whatever you've got probably isn't going to make a lot of difference).

            First steps:
            Set up statspack and start taking snapshots so that you can get an idea of what's happening hour by hour. (A system level summary since database startup doesn't really tell us anything significant and can easily be misinterpreted).

            Think about individual jobs, or reports, that seem to take a long time to run - and ask yourself whether the amount of work they do is reasonable considering what they're supposed to achieve. If you identify some tasks by thinking this way trace them to see where they spend the time.

            Second steps:
            You should by now have some indications of SQL that is resource intensive. The selection will give you some clues about problems that need to be addressed. Typical issues are: missing indexes, missing or misleading statistics, excessive use of parallel query, concurrent activity across multiple RAC nodes that should be isolated to just one node, badly written SQL, badly structured code that causes excessive executions of SQL statements.

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

            "For every expert there is an equal and opposite expert."
            Arthur C. Clarke
            • 3. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
              user00726
              Statspack report of Second Intance
              STATSPACK report for
              
              DB Name         DB Id    Instance     Inst Num Release     Cluster Host
              ------------ ----------- ------------ -------- ----------- ------- ------------
              ia          1372079993 ia12              2 9.2.0.6.0   YES     ia2
              
                            Snap Id     Snap Time      Sessions Curs/Sess Comment
                          --------- ------------------ -------- --------- -------------------
              Begin Snap:        63 10-Jun-09 15:38:20    3,339      48.7
                End Snap:        64 10-Jun-09 15:49:03    3,353      48.8
                 Elapsed:               10.72 (mins)
              
              Cache Sizes (end)
              ~~~~~~~~~~~~~~~~~
                             Buffer Cache:     4,288M      Std Block Size:          8K
                         Shared Pool Size:       608M          Log Buffer:        977K
              
              Load Profile
              ~~~~~~~~~~~~                            Per Second       Per Transaction
                                                 ---------------       ---------------
                                Redo size:              7,103.90             62,572.71
                            Logical reads:              2,459.53             21,664.07
                            Block changes:                 14.79                130.27
                           Physical reads:                127.72              1,124.99
                          Physical writes:                 10.03                 88.32
                               User calls:                 56.99                501.97
                                   Parses:                  8.79                 77.45
                              Hard parses:                  0.15                  1.33
                                    Sorts:                  3.05                 26.84
                                   Logons:                  0.09                  0.75
                                 Executes:                 19.48                171.56
                             Transactions:                  0.11
              
                % Blocks changed per Read:    0.60    Recursive Call %:     33.25
               Rollback per transaction %:   30.14       Rows per Sort:    336.98
              
              Instance Efficiency Percentages (Target 100%)
              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                          Buffer Nowait %:  100.00       Redo NoWait %:    100.00
                          Buffer  Hit   %:   94.81    In-memory Sort %:    100.00
                          Library Hit   %:   98.21        Soft Parse %:     98.28
                       Execute to Parse %:   54.85         Latch Hit %:     99.95
              Parse CPU to Parse Elapsd %:   90.95     % Non-Parse CPU:     98.89
              
               Shared Pool Statistics        Begin   End
                                             ------  ------
                           Memory Usage %:   88.45   88.57
                  % SQL with executions>1:   51.72   51.70
                % Memory for SQL w/exec>1:   58.23   58.25
              
              Top 5 Timed Events
              ~~~~~~~~~~~~~~~~~~                                                     % Total
              Event                                               Waits    Time (s) Ela Time
              -------------------------------------------- ------------ ----------- --------
              CPU time                                                          163    54.47
              db file sequential read                            12,376          51    16.99
              db file scattered read                             11,268          43    14.51
              global cache cr request                            34,878          18     6.00
              db file parallel read                                 924          14     4.75
                        -------------------------------------------------------------
              Cluster Statistics for DB: ia  Instance: ia12  Snaps: 63 -64
              
              Global Cache Service - Workload Characteristics
              -----------------------------------------------
              Ave global cache get time (ms):                            0.9
              Ave global cache convert time (ms):                        1.9
              
              Ave build time for CR block (ms):                          0.0
              Ave flush time for CR block (ms):                          0.0
              Ave send time for CR block (ms):                           0.2
              Ave time to process CR block request (ms):                 0.3
              Ave receive time for CR block (ms):                        1.5
              
              Ave pin time for current block (ms):                       0.0
              Ave flush time for current block (ms):                     0.0
              Ave send time for current block (ms):                      0.2
              Ave time to process current block request (ms):            0.3
              Ave receive time for current block (ms):                   2.5
              
              Global cache hit ratio:                                    7.7
              Ratio of current block defers:                             0.0
              % of messages sent for buffer gets:                        6.2
              % of remote buffer gets:                                   1.2
              Ratio of I/O for coherence:                                1.2
              Ratio of local vs remote work:                             5.2
              Ratio of fusion vs physical writes:                        0.0
              
              Global Enqueue Service Statistics
              ---------------------------------
              Ave global lock get time (ms):                             0.3
              Ave global lock convert time (ms):                         0.9
              Ratio of global lock gets vs global lock releases:         1.1
              
              GCS and GES Messaging statistics
              --------------------------------
              Ave message sent queue time (ms):                          0.3
              Ave message sent queue time on ksxp (ms):                  0.9
              Ave message received queue time (ms):                      0.2
              Ave GCS message process time (ms):                         0.1
              Ave GES message process time (ms):                         0.1
              % of direct sent messages:                                12.9
              % of indirect sent messages:                              47.8
              % of flow controlled messages:                            39.3
                        -------------------------------------------------------------
              GES Statistics for DB: ia  Instance: ia12  Snaps: 63 -64
              
              Statistic                                    Total   per Second    per Trans
              --------------------------------- ---------------- ------------ ------------
              dynamically allocated gcs resourc                0          0.0          0.0
              dynamically allocated gcs shadows                0          0.0          0.0
              flow control messages received                   0          0.0          0.0
              flow control messages sent                       0          0.0          0.0
              gcs ast xid                                      0          0.0          0.0
              gcs blocked converts                           473          0.7          6.5
              gcs blocked cr converts                        538          0.8          7.4
              gcs compatible basts                             0          0.0          0.0
              gcs compatible cr basts (global)                35          0.1          0.5
              gcs compatible cr basts (local)             36,997         57.5        506.8
              gcs cr basts to PIs                              0          0.0          0.0
              gcs cr serve without current lock                0          0.0          0.0
              gcs error msgs                                   0          0.0          0.0
              gcs flush pi msgs                                0          0.0          0.0
              gcs forward cr to pinged instance                0          0.0          0.0
              gcs immediate (compatible) conver               74          0.1          1.0
              gcs immediate (null) converts                  314          0.5          4.3
              gcs immediate cr (compatible) con           22,310         34.7        305.6
              gcs immediate cr (null) converts            43,308         67.4        593.3
              gcs msgs process time(ms)                   12,527         19.5        171.6
              gcs msgs received                           99,877        155.3      1,368.2
              gcs out-of-order msgs                            0          0.0          0.0
              gcs pings refused                                0          0.0          0.0
              gcs queued converts                              0          0.0          0.0
              gcs recovery claim msgs                          0          0.0          0.0
              gcs refuse xid                                   0          0.0          0.0
              gcs retry convert request                        0          0.0          0.0
              gcs side channel msgs actual                   531          0.8          7.3
              gcs side channel msgs logical               48,960         76.1        670.7
              gcs write notification msgs                      0          0.0          0.0
              gcs write request msgs                           4          0.0          0.1
              gcs writes refused                               0          0.0          0.0
              ges msgs process time(ms)                      429          0.7          5.9
              ges msgs received                            7,900         12.3        108.2
              global posts dropped                             0          0.0          0.0
              global posts queue time                          0          0.0          0.0
              global posts queued                              0          0.0          0.0
              global posts requested                           0          0.0          0.0
              global posts sent                                0          0.0          0.0
              implicit batch messages received             7,092         11.0         97.2
              implicit batch messages sent                 7,791         12.1        106.7
              lmd msg send time(ms)                          158          0.2          2.2
              lms(s) msg send time(ms)                         0          0.0          0.0
              messages flow controlled                    41,096         63.9        563.0
              messages received actual                    49,778         77.4        681.9
              messages received logical                  107,768        167.6      1,476.3
              messages sent directly                      13,531         21.0        185.4
              messages sent indirectly                    50,000         77.8        684.9
              msgs causing lmd to send msgs                3,072          4.8         42.1
              msgs causing lms(s) to send msgs               547          0.9          7.5
              msgs received queue time (ms)               22,340         34.7        306.0
              msgs received queued                       107,754        167.6      1,476.1
              msgs sent queue time (ms)                   14,027         21.8        192.2
              msgs sent queue time on ksxp (ms)           37,686         58.6        516.2
              msgs sent queued                            49,999         77.8        684.9
              msgs sent queued on ksxp                    41,463         64.5        568.0
              GES Statistics for DB: ia  Instance: ia12  Snaps: 63 -64
              
              Statistic                                    Total   per Second    per Trans
              --------------------------------- ---------------- ------------ ------------
              process batch messages received              8,662         13.5        118.7
              process batch messages sent                 10,826         16.8        148.3
                        -------------------------------------------------------------
              Wait Events for DB: ia  Instance: ia12  Snaps: 63 -64
              -> s  - second
              -> cs - centisecond -     100th of a second
              -> ms - millisecond -    1000th of a second
              -> us - microsecond - 1000000th of a second
              -> ordered by wait time desc, waits desc (idle events last)
              
                                                                                 Avg
                                                                   Total Wait   wait    Waits
              Event                               Waits   Timeouts   Time (s)   (ms)     /txn
              ---------------------------- ------------ ---------- ---------- ------ --------
              db file sequential read            12,376          0         51      4    169.5
              db file scattered read             11,268          0         43      4    154.4
              global cache cr request            34,878         47         18      1    477.8
              db file parallel read                 924          0         14     15     12.7
              process startup                        20          0          2    115      0.3
              control file sequential read          905          0          2      2     12.4
              log file parallel write               343          0          1      3      4.7
              control file parallel write           214          0          1      5      2.9
              enqueue                             2,436          0          1      0     33.4
              global cache null to x                 74          0          0      6      1.0
              global cache open x                   426          4          0      1      5.8
              DFS lock handle                       778          0          0      0     10.7
              row cache lock                        184          0          0      1      2.5
              log file sync                          51          0          0      5      0.7
              global cache s to x                   396          0          0      1      5.4
              CGS wait for IPC msg                4,117      3,999          0      0     56.4
              library cache lock                    301          0          0      0      4.1
              latch free                            333          8          0      0      4.6
              SQL*Net break/reset to clien           64          0          0      1      0.9
              SQL*Net more data to client           950          0          0      0     13.0
              BFILE get length                       37          0          0      2      0.5
              BFILE read                             58          0          0      1      0.8
              db file parallel write                 13          0          0      2      0.2
              global cache open s                    49          0          0      1      0.7
              ksxr poll remote instances            868        632          0      0     11.9
              library cache pin                      22          0          0      1      0.3
              global cache null to s                 12          0          0      1      0.2
              BFILE open                              6          0          0      0      0.1
              BFILE internal seek                    58          0          0      0      0.8
              BFILE closure                           6          0          0      0      0.1
              KJC: Wait for msg sends to c            1          0          0      0      0.0
              SQL*Net message from client        36,568          0     64,925   1775    500.9
              jobq slave wait                       547        540      1,596   2917      7.5
              gcs remote message                 51,705     13,211      1,237     24    708.3
              ges remote message                 18,160     12,887        626     34    248.8
              SQL*Net more data from clien           55          0          1     26      0.8
              SQL*Net message to client          36,582          0          0      0    501.1
                        -------------------------------------------------------------
              Background Wait Events for DB: ia  Instance: ia12  Snaps: 63 -64
              -> ordered by wait time desc, waits desc (idle events last)
              
                                                                                 Avg
                                                                   Total Wait   wait    Waits
              Event                               Waits   Timeouts   Time (s)   (ms)     /txn
              ---------------------------- ------------ ---------- ---------- ------ --------
              control file sequential read          855          0          2      2     11.7
              log file parallel write               343          0          1      3      4.7
              control file parallel write           213          0          1      5      2.9
              enqueue                             2,402          0          1      0     32.9
              DFS lock handle                       778          0          0      0     10.7
              CGS wait for IPC msg                4,116      3,998          0      0     56.4
              latch free                            305          6          0      0      4.2
              db file parallel write                 13          0          0      2      0.2
              ksxr poll remote instances            865        631          0      0     11.8
              db file parallel read                   1          0          0     12      0.0
              global cache cr request                 7          0          0      1      0.1
              rdbms ipc message                   7,032      5,397      5,440    774     96.3
              gcs remote message                 51,706     13,214      1,237     24    708.3
              smon timer                             15          0        632  42128      0.2
              ges remote message                 18,153     12,888        626     35    248.7
              pmon timer                            216        214        625   2892      3.0
                        -------------------------------------------------------------
              SQL ordered by Gets for DB: ia  Instance: ia12  Snaps: 63 -64
              -> 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
              
                                                                   CPU      Elapsd
                Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
              --------------- ------------ -------------- ------ -------- --------- ----------
                      642,009           24       26,750.4   40.6    12.00     12.15 1772835295
              select decode(level,1,'',2,'  ',3,'     ',4,'       ',5,'
                ', '           ') || decode(:1,'ENG',menu_option_desc,menu_opt
              ion_desc_bl) "OPTION", menu_parent_id "PARENT", menu_action_type
               "TYPE",menu_action "ACTION", decode(level,1,'',2,' ',3,'  ',4,'
                 ',5,'    ', '     ') ||decode(menu_action_type, 'M', '+', 'o'
              
                      114,012           36        3,167.0    7.2     7.54      9.50 2722142004
              SELECT ROWID,MPS_MRH_SYS_ID,MRH_PHOTO,MRH_SIGNATURE FROM OM_MEM_
              PHOTO_SIGN_DETL WHERE (MPS_MRH_SYS_ID=:1)
              
                       94,710           35        2,706.0    6.0     9.83     12.41 1641471488
              SELECT ROWID,MSEMD_SYS_ID,MSEMD_MRH_SYS_ID,MSEMD_CR_DT,MSEMD_CR_
              UID,MSEMD_UPD_DT,MSEMD_UPD_UID,MSEMD_SEMINAR_CODE,MSEMD_LOCN_COD
              E,MSEMD_HELD_DT,MSEMD_CPE_HRS FROM OM_MEM_SEMINAR_DETL WHERE (MS
              EMD_MRH_SYS_ID=:1)
              
                       89,975           35        2,570.7    5.7     8.65     10.54 2001308163
              SELECT MCH_LIFE_MEM_NO,MCH_ENROL_DT   FROM OT_MEM_CABF_HEAD  WHE
              RE MCH_MRH_MRN = :b1  AND MCH_APPR_UID IS NOT NULL   AND NVL(MCH
              _CLO_STATUS,0) != 1
              
                       79,272            6       13,212.0    5.0    12.66     15.36 4142254844
              SELECT LTRIM(RTRIM(DECODE(TIT_NAME,'MR.','CA.','MS.','CA.','MRS.
              ','CA.') || ' '  || MRH_FIRST_NAME  || ' '  || MRH_MIDDLE_NAME
              || ' '  || MRH_SUR_NAME  || ' '  || DECODE(MRH_APPR_UID, NULL ,
              NULL ,DECODE(MRH_MEM_STATUS,2, NULL ,DECODE(MRH_FELLOW_STATUS_YN
              ,'Y','FCA','ACA')))  || DECODE(MRH_RESI_STATUS,'A','
              
                       52,066            8        6,508.3    3.3     4.68      5.77 1838125769
              SELECT MRH_DT,MRH_FIRST_NAME,MRH_MIDDLE_NAME,MRH_SUR_NAME,MRH_ST
              ATUS   FROM OM_MEM_REG_HEAD  WHERE DECODE(:b1,1,MRH_MRN,MRH_MFCH
              _TEMP_REF_NO) = :b2
              
                       35,327            6        5,887.8    2.2     3.81      7.09    1617597
              SELECT SRN,ACTIVITYDESCRIPTION,STATUS,DOCUMENTNO,DOCUMENTDATE FR
              OM OV_ART_TRANS_STATUS WHERE (SRN=:1) order by DOCUMENTDATE
              
                       34,714           91          381.5    2.2     0.59      0.66 3019857179
              SELECT COUNT(*)   FROM MENU_MENUS  WHERE MENU_PARENT_ID = '*'  A
              ND EXISTS  (SELECT 1   FROM MENU_USER_MENUS  WHERE UM_GROUP_ID =
                (SELECT USER_GROUP_ID   FROM MENU_USER  WHERE USER_ID = :b1 )
               AND UM_MENU_ID = MENU_ID )  AND SUBSTR(MENU_ID,0,1) IN (SELECT
              REGION_CODE   FROM OM_REGION  WHERE REGION_TXN_NO_PREFIX IS NOT
              
                       24,196            1       24,196.0    1.5     9.24     12.22 2948768197
              Module: sqlplus@ia2 (TNS V1-V3)
              BEGIN perfstat.statspack.snap(i_snap_level=>7); END;
              
                       16,407            9        1,823.0    1.0     2.85      3.78 1550456472
              SQL ordered by Gets for DB: ia  Instance: ia12  Snaps: 63 -64
              -> 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
              
                                                                   CPU      Elapsd
                Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
              --------------- ------------ -------------- ------ -------- --------- ----------
              SELECT COUNT('X')   FROM OS_STU_FEE_STATUS  WHERE SFS_STUD_SRN =
               :b1  AND SFS_COURSE_CODE = :b2
              
                       16,299            3        5,433.0    1.0     3.25      4.96 2186070964
              SELECT /*+ INDEX(OT_STUDENT_FEE_COL_HEAD OT_STUDENT_FEE_COL_HEAD
              _UK01)  */SFCH_SYS_ID,SFCH_DT,DECODE(NVL(SSTN_SRN,SFCH_TEMP_REF_
              NO),SSTN_SRN, NULL ,SFCH_TEMP_REF_NO) SFCH_TEMP_REF_NO,NVL(SFCH_
              STUD_SRN,SSTN_SRN) SFCH_STUD_SRN,SFCH_COURSE_CODE,SFCH_SCHEME_CO
              DE,SFCH_EXMP_STUD_YN,SFCH_EXMP_STUD_REASON,DEH_APPLICATION_DT,SF
              
                       11,216            2        5,608.0    0.7     2.22      5.83 1196291799
              UPDATE OT_PENDENCY_DETL SET PED_ENT_VALUE='N' WHERE PED_TXN_CODE
               = :b1  AND PED_REG_NO = :b2  AND PED_PD_CODE = :b3
              
                       11,195            4        2,798.8    0.7     0.52      0.49   25055520
              Module: T.O.A.D.
              select /*+ CHOOSE */ distinct tabs.table_name, tabs.owner , NULL
               as partitioned, NULL as iot_type   , TEMPORARY  from DBA_TABLES
               tabs  WHERE  (tabs.table_name like :txt )
              
                       10,335           15          689.0    0.7     1.26      1.30 1734754400
              SELECT ROWID,PIIPD_ia_EXAM_APPEARED,PIIPD_REG_NO,PIIPD_MTH,PII
              PD_YR,PIIPD_ROLL_NO,PIIPD_EXT_EXAM_APPEARED,PIIPD_EXAM_CODE_1,PI
              IPD_SUBJ_CODE_1,PIIPD_GROUP_1,PIIPD_ROLL_NO_1,PIIPD_LAST_PAPER_D
              T_1,PIIPD_EXAM_CODE_2,PIIPD_SUBJ_CODE_2,PIIPD_GROUP_2,PIIPD_ROLL
              _NO_2,PIIPD_LAST_PAPER_DT_2,PIIPD_GRADUATE_MTH,PIIPD_GRADUATE_YR
              
                        9,800           24          408.3    0.6     0.18      0.15  686031059
              SELECT DECODE(:b1,'ENG',INITCAP(LOWER(MENU_OPTION_DESC)),MENU_OP
              TION_DESC_BL),MENU_ID,MENU_SEQ_NO   FROM MENU_MENUS  WHERE MENU_
              PARENT_ID = '*'  AND EXISTS  (SELECT 1   FROM MENU_USER_MENUS  W
              HERE UM_GROUP_ID =  (SELECT USER_GROUP_ID   FROM MENU_USER  WHER
              E USER_ID = :b2 )  AND UM_MENU_ID = MENU_ID ) ORDER BY MENU_DISP
              
                        9,633            3        3,211.0    0.6     1.71      3.49  316093672
              SELECT MAX(MCAH_RECP_DT)   FROM OT_MEM_CHG_ADDR_HEAD  WHERE MCAH
              _MRH_MRN = :b1  AND MCAH_MAIN_TYPE = 1  AND MCAH_DOC_STATUS = 5
              
                        7,512           12          626.0    0.5     0.85      0.85 4201802941
              SELECT COUNT(ROWID)   FROM OS_FIRM_DETL  WHERE FD_PARENT_FRN = :
              b1  AND FD_TYPE = '2'  AND FD_BR_CODE IS NULL  AND FD_CLOSE_DT I
              S NULL
              
                        7,496            4        1,874.0    0.5     0.22      0.23  514683475
              Module: T.O.A.D.
               select /*+ CHOOSE */ a1.constraint_name,   c1.column_name, c1.p
              osition  from  DBA_CONS_COLUMNS C1, DBA_CONSTRAINTS A1  where A1
              .table_name = C1.Table_name  And  A1.constraint_name = C1.Constr
              aint_Name  and A1.constraint_type = 'P'  and A1.table_name =:Tab
              Name  and C1.owner = A1.Owner  and A1.owner =:Owner  ORDER BY 3
              SQL ordered by Gets for DB: ia  Instance: ia12  Snaps: 63 -64
              -> 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
              
                                                                   CPU      Elapsd
                Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
              --------------- ------------ -------------- ------ -------- --------- ----------
              
                        6,422            2        3,211.0    0.4     0.66      0.74 1306726861
              SELECT ROWID,MCAH_DT,MCAH_TXN_CODE,MCAH_NO,MCAH_AMD_NO,MCAH_REF_
              FROM,MCAH_REF_TXN_CODE,MCAH_REF_NO,MCAH_DAK_CODE,MCAH_MRH_MRN,MC
              
                        -------------------------------------------------------------
              Edited by: user00726 on Jun 10, 2009 1:28 AM
              • 4. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                user00726
                Statspack report of First instance(Partially)
                STATSPACK report for
                
                DB Name         DB Id    Instance     Inst Num Release     Cluster Host
                ------------ ----------- ------------ -------- ----------- ------- ------------
                IA          1372079993 IA11              1 9.2.0.6.0   YES     IA1
                
                              Snap Id     Snap Time      Sessions Curs/Sess Comment
                            --------- ------------------ -------- --------- -------------------
                Begin Snap:        73 10-Jun-09 15:37:47  #######        .0
                  End Snap:        74 10-Jun-09 15:49:07  #######        .0
                   Elapsed:               11.33 (mins)
                
                Cache Sizes (end)
                ~~~~~~~~~~~~~~~~~
                               Buffer Cache:     4,288M      Std Block Size:          8K
                           Shared Pool Size:       608M          Log Buffer:        977K
                
                Load Profile
                ~~~~~~~~~~~~                            Per Second       Per Transaction
                                                   ---------------       ---------------
                                  Redo size:              6,132.09             47,384.32
                              Logical reads:              3,383.24             26,143.19
                              Block changes:                 12.44                 96.11
                             Physical reads:                 88.21                681.66
                            Physical writes:                  0.07                  0.55
                                 User calls:                 68.43                528.76
                                     Parses:                  7.54                 58.27
                                Hard parses:                  0.13                  1.03
                                      Sorts:                 10.75                 83.10
                                     Logons:                  0.08                  0.61
                                   Executes:                 29.76                229.94
                               Transactions:                  0.13
                
                  % Blocks changed per Read:    0.37    Recursive Call %:     36.66
                 Rollback per transaction %:   14.77       Rows per Sort:   5287.46
                
                Instance Efficiency Percentages (Target 100%)
                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                            Buffer Nowait %:  100.00       Redo NoWait %:    100.00
                            Buffer  Hit   %:   97.39    In-memory Sort %:    100.00
                            Library Hit   %:   98.90        Soft Parse %:     98.23
                         Execute to Parse %:   74.66         Latch Hit %:     99.97
                Parse CPU to Parse Elapsd %:   75.00     % Non-Parse CPU:     99.76
                
                 Shared Pool Statistics        Begin   End
                                               ------  ------
                             Memory Usage %:   90.61   90.31
                    % SQL with executions>1:   75.01   75.61
                  % Memory for SQL w/exec>1:   73.57   74.35
                
                Top 5 Timed Events
                ~~~~~~~~~~~~~~~~~~                                                     % Total
                Event                                               Waits    Time (s) Ela Time
                -------------------------------------------- ------------ ----------- --------
                CPU time                                                          723    84.98
                db file sequential read                            20,226          72     8.43
                global cache cr request                            35,690          22     2.55
                db file parallel read                                 599          13     1.57
                db file scattered read                              6,868           9     1.12
                          -------------------------------------------------------------
                Cluster Statistics for DB: IA  Instance: IA11  Snaps: 73 -74
                
                Global Cache Service - Workload Characteristics
                -----------------------------------------------
                Ave global cache get time (ms):                            0.9
                Ave global cache convert time (ms):                        1.7
                
                Ave build time for CR block (ms):                          0.1
                Ave flush time for CR block (ms):                          0.2
                Ave send time for CR block (ms):                           0.3
                Ave time to process CR block request (ms):                 0.5
                Ave receive time for CR block (ms):                        1.6
                
                Ave pin time for current block (ms):                       0.1
                Ave flush time for current block (ms):                     0.0
                Ave send time for current block (ms):                      0.3
                Ave time to process current block request (ms):            0.3
                Ave receive time for current block (ms):                   1.8
                
                Global cache hit ratio:                                    5.9
                Ratio of current block defers:                             0.0
                % of messages sent for buffer gets:                        4.5
                % of remote buffer gets:                                   1.7
                Ratio of I/O for coherence:                                1.6
                Ratio of local vs remote work:                             2.6
                Ratio of fusion vs physical writes:                        0.1
                
                Global Enqueue Service Statistics
                ---------------------------------
                Ave global lock get time (ms):                             0.6
                Ave global lock convert time (ms):                         0.1
                Ratio of global lock gets vs global lock releases:         1.1
                
                GCS and GES Messaging statistics
                --------------------------------
                Ave message sent queue time (ms):                          0.4
                Ave message sent queue time on ksxp (ms):                  1.0
                Ave message received queue time (ms):                      0.3
                Ave GCS message process time (ms):                         0.1
                Ave GES message process time (ms):                         0.2
                % of direct sent messages:                                21.8
                % of indirect sent messages:                              45.9
                % of flow controlled messages:                            32.3
                          -------------------------------------------------------------
                GES Statistics for DB: IA  Instance: IA11  Snaps: 73 -74
                
                Statistic                                    Total   per Second    per Trans
                --------------------------------- ---------------- ------------ ------------
                dynamically allocated gcs resourc                0          0.0          0.0
                dynamically allocated gcs shadows                0          0.0          0.0
                flow control messages received                   0          0.0          0.0
                flow control messages sent                       0          0.0          0.0
                gcs ast xid                                      0          0.0          0.0
                gcs blocked converts                           348          0.5          4.0
                gcs blocked cr converts                        404          0.6          4.6
                gcs compatible basts                             0          0.0          0.0
                gcs compatible cr basts (global)                 8          0.0          0.1
                gcs compatible cr basts (local)             19,494         28.7        221.5
                gcs cr basts to PIs                              0          0.0          0.0
                gcs cr serve without current lock                0          0.0          0.0
                gcs error msgs                                   0          0.0          0.0
                gcs flush pi msgs                                1          0.0          0.0
                gcs forward cr to pinged instance                0          0.0          0.0
                gcs immediate (compatible) conver              130          0.2          1.5
                gcs immediate (null) converts                  551          0.8          6.3
                gcs immediate cr (compatible) con           34,489         50.7        391.9
                gcs immediate cr (null) converts            42,101         61.9        478.4
                gcs msgs process time(ms)                   10,417         15.3        118.4
                gcs msgs received                          103,495        152.2      1,176.1
                gcs out-of-order msgs                            0          0.0          0.0
                gcs pings refused                                0          0.0          0.0
                gcs queued converts                              0          0.0          0.0
                gcs recovery claim msgs                          0          0.0          0.0
                gcs refuse xid                                   0          0.0          0.0
                gcs retry convert request                        0          0.0          0.0
                gcs side channel msgs actual                   571          0.8          6.5
                gcs side channel msgs logical               51,634         75.9        586.8
                gcs write notification msgs                      0          0.0          0.0
                gcs write request msgs                           5          0.0          0.1
                gcs writes refused                               0          0.0          0.0
                ges msgs process time(ms)                      907          1.3         10.3
                ges msgs received                            5,870          8.6         66.7
                global posts dropped                             0          0.0          0.0
                global posts queue time                          0          0.0          0.0
                global posts queued                              0          0.0          0.0
                global posts requested                           0          0.0          0.0
                global posts sent                                0          0.0          0.0
                implicit batch messages received             8,055         11.8         91.5
                implicit batch messages sent                 7,357         10.8         83.6
                lmd msg send time(ms)                          145          0.2          1.6
                lms(s) msg send time(ms)                         1          0.0          0.0
                messages flow controlled                    36,249         53.3        411.9
                messages received actual                    43,332         63.7        492.4
                messages received logical                  109,357        160.8      1,242.7
                messages sent directly                      24,417         35.9        277.5
                messages sent indirectly                    51,409         75.6        584.2
                msgs causing lmd to send msgs                2,166          3.2         24.6
                msgs causing lms(s) to send msgs               824          1.2          9.4
                msgs received queue time (ms)               31,277         46.0        355.4
                msgs received queued                       109,356        160.8      1,242.7
                msgs sent queue time (ms)                   20,500         30.1        233.0
                msgs sent queue time on ksxp (ms)           51,916         76.3        590.0
                msgs sent queued                            51,418         75.6        584.3
                msgs sent queued on ksxp                    52,686         77.5        598.7
                GES Statistics for DB: IA  Instance: IA11  Snaps: 73 -74
                
                Statistic                                    Total   per Second    per Trans
                --------------------------------- ---------------- ------------ ------------
                process batch messages received             11,265         16.6        128.0
                process batch messages sent                  8,746         12.9         99.4
                          -------------------------------------------------------------
                Wait Events for DB: IA  Instance: IA11  Snaps: 73 -74
                -> s  - second
                -> cs - centisecond -     100th of a second
                -> ms - millisecond -    1000th of a second
                -> us - microsecond - 1000000th of a second
                -> ordered by wait time desc, waits desc (idle events last)
                
                                                                                   Avg
                                                                     Total Wait   wait    Waits
                Event                               Waits   Timeouts   Time (s)   (ms)     /txn
                ---------------------------- ------------ ---------- ---------- ------ --------
                db file sequential read            20,226          0         72      4    229.8
                global cache cr request            35,690         37         22      1    405.6
                db file parallel read                 599          0         13     22      6.8
                db file scattered read              6,868          0          9      1     78.0
                process startup                        21          0          2    110      0.2
                enqueue                             5,161          0          2      0     58.6
                log file parallel write               465          0          1      3      5.3
                latch free                          1,222         53          1      1     13.9
                control file sequential read          939          0          1      1     10.7
                control file parallel write           226          0          1      3      2.6
                wait for master scn                   223          0          1      2      2.5
                log file sync                          76          0          0      5      0.9
                global cache null to x                168          0          0      2      1.9
                row cache lock                        140          0          0      3      1.6
                CGS wait for IPC msg                9,373      9,318          0      0    106.5
                global cache null to s                 13          0          0     20      0.1
                global cache open x                   332          2          0      1      3.8
                library cache lock                    301          0          0      1      3.4
                DFS lock handle                       599          0          0      0      6.8
                global cache s to x                   215          6          0      1      2.4
                db file parallel write                 20          0          0      4      0.2
                SQL*Net more data to client           602          0          0      0      6.8
                buffer busy waits                      13          0          0      3      0.1
                global cache open s                    60          0          0      1      0.7
                ksxr poll remote instances          1,021        682          0      0     11.6
                library cache pin                      77          0          0      0      0.9
                buffer busy global CR                  13          0          0      2      0.1
                BFILE get length                        7          0          0      1      0.1
                SQL*Net break/reset to clien           32          0          0      0      0.4
                KJC: Wait for msg sends to c            4          0          0      0      0.0
                SQL*Net message from client        46,425          0     66,449   1431    527.6
                jobq slave wait                       533        524      1,555   2917      6.1
                gcs remote message                 45,953     14,233      1,309     28    522.2
                ges remote message                 19,231     13,641        662     34    218.5
                SQL*Net more data from clien           56          0          1     21      0.6
                SQL*Net message to client          46,435          0          0      0    527.7
                          -------------------------------------------------------------
                Background Wait Events for DB: IA  Instance: IA11  Snaps: 73 -74
                -> ordered by wait time desc, waits desc (idle events last)
                
                                                                                   Avg
                                                                     Total Wait   wait    Waits
                Event                               Waits   Timeouts   Time (s)   (ms)     /txn
                ---------------------------- ------------ ---------- ---------- ------ --------
                enqueue                             5,048          0          2      0     57.4
                log file parallel write               467          0          1      3      5.3
                control file sequential read          888          0          1      1     10.1
                control file parallel write           226          0          1      3      2.6
                wait for master scn                   223          0          1      2      2.5
                latch free                          1,034         39          0      0     11.8
                CGS wait for IPC msg                9,372      9,317          0      0    106.5
                DFS lock handle                       599          0          0      0      6.8
                db file parallel write                 20          0          0      4      0.2
                ksxr poll remote instances          1,023        684          0      0     11.6
                db file sequential read                 1          0          0     11      0.0
                global cache cr request                 3          0          0      2      0.0
                global cache null to x                  4          0          0      1      0.0
                global cache open x                     3          0          0      1      0.0
                rdbms ipc message                   9,298      7,358      7,680    826    105.7
                gcs remote message                 45,957     14,230      1,309     28    522.2
                smon timer                             14          0        695  49644      0.2
                ges remote message                 19,222     13,640        662     34    218.4
                pmon timer                            227        227        661   2913      2.6
                          -------------------------------------------------------------
                SQL ordered by Gets for DB: IA  Instance: IA11  Snaps: 73 -74
                -> 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
                
                                                                     CPU      Elapsd
                  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
                --------------- ------------ -------------- ------ -------- --------- ----------
                        984,711          528        1,865.0   42.8   544.06    562.97 1951937671
                SELECT A.FH_HO_REGION_CODE,DECODE(NVL(MCNT.M_CNT,0),1,2,1) FH_TY
                PE,D.CITY_NAME,A.FH_FRN FH_FRN_M,A.FH_FIRM_NAME FIRM_NAME_M,'1|'
                 RECTYPE,C.MRH_MRN MRH_MRN_M,C.MRH_FIRST_NAME || ' '  || C.MRH_M
                IDDLE_NAME  || ' '  || C.MRH_SUR_NAME  MEM_NAME,O_GET_COP_STATUS
                (C.MRH_MRN,:b1) MRH_COP_STATUS,FCNT.F_CNT   FROM OM_FIRM_HEAD A,
                
                        588,698           22       26,759.0   25.6    11.24     11.76 1772835295
                select decode(level,1,'',2,'  ',3,'     ',4,'       ',5,'
                  ', '           ') || decode(:1,'ENG',menu_option_desc,menu_opt
                ion_desc_bl) "OPTION", menu_parent_id "PARENT", menu_action_type
                 "TYPE",menu_action "ACTION", decode(level,1,'',2,' ',3,'  ',4,'
                   ',5,'    ', '     ') ||decode(menu_action_type, 'M', '+', 'o'
                
                         89,612        1,056           84.9    3.9    19.41     20.10 1549001997
                SELECT DISTINCT A.FH_HO_REGION_CODE RC_B,DECODE(NVL(MCNT.M_CNT,0
                ),1,2,1) FH_TYPE_B,BOCH_BR_NAME,A.FH_FRN FH_FRN_B,A.FH_FIRM_NAME
                 FH_FIRM_NAME_B,'3' RECTYPE,B.BOCH_BR_CODE,CITY_NAME BR_CITY,BOC
                H_ADDR_1,BOCH_ADDR_2,BOCH_ADDR_3,BOCH_ADDR_4,BOCH_ZIP_POSTAL_COD
                E   FROM OM_FIRM_HEAD A,OM_FIRM_OPENCLOSE_BR B,OM_CITY C,(SELECT
                
                         83,509           12        6,959.1    3.6    17.85     27.98 1838125769
                SELECT MRH_DT,MRH_FIRST_NAME,MRH_MIDDLE_NAME,MRH_SUR_NAME,MRH_ST
                ATUS   FROM OM_MEM_REG_HEAD  WHERE DECODE(:b1,1,MRH_MRN,MRH_MFCH
                _TEMP_REF_NO) = :b2
                
                         71,565           41        1,745.5    3.1    27.33     45.25 1938910819
                SELECT COUNT('X')   FROM OM_STUDENT_HEAD  WHERE STUD_REF_NO = :b
                1  AND STUD_REF_TXN_CODE = :b2  AND STUD_SYS_ID != :b3
                
                         41,296            7        5,899.4    1.8     2.68      2.94    1617597
                SELECT SRN,ACTIVITYDESCRIPTION,STATUS,DOCUMENTNO,DOCUMENTDATE FR
                OM OV_ART_TRANS_STATUS WHERE (SRN=:1) order by DOCUMENTDATE
                
                         39,638            3       13,212.7    1.7    10.65     16.11 4142254844
                SELECT LTRIM(RTRIM(DECODE(TIT_NAME,'MR.','CA.','MS.','CA.','MRS.
                ','CA.') || ' '  || MRH_FIRST_NAME  || ' '  || MRH_MIDDLE_NAME
                || ' '  || MRH_SUR_NAME  || ' '  || DECODE(MRH_APPR_UID, NULL ,
                NULL ,DECODE(MRH_MEM_STATUS,2, NULL ,DECODE(MRH_FELLOW_STATUS_YN
                ,'Y','FCA','ACA')))  || DECODE(MRH_RESI_STATUS,'A','
                
                         33,621           89          377.8    1.5     0.55      0.64 3019857179
                SELECT COUNT(*)   FROM MENU_MENUS  WHERE MENU_PARENT_ID = '*'  A
                ND EXISTS  (SELECT 1   FROM MENU_USER_MENUS  WHERE UM_GROUP_ID =
                  (SELECT USER_GROUP_ID   FROM MENU_USER  WHERE USER_ID = :b1 )
                 AND UM_MENU_ID = MENU_ID )  AND SUBSTR(MENU_ID,0,1) IN (SELECT
                REGION_CODE   FROM OM_REGION  WHERE REGION_TXN_NO_PREFIX IS NOT
                
                         32,383           47          689.0    1.4     4.03      4.92 1734754400
                SELECT ROWID,PIIPD_IA_EXAM_APPEARED,PIIPD_REG_NO,PIIPD_MTH,PII
                SQL ordered by Gets for DB: IA  Instance: IA11  Snaps: 73 -74
                -> 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
                Users are trying to pull data in the form .txt from application. It is taking more than a single day,

                Earlier some DBA first rebuild all the indexes then the process would had been completed less than a hour and the size of the data is only 5MB.

                And the another thing is that I can't tunned the SQL coz the sql are running behind the applications.....

                Please suggest me....

                Edited by: user00726 on Jun 10, 2009 1:30 AM
                • 5. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                  701909
                  Earlier some DBA first rebuild all the indexes then the process would had been completed less than a hour
                  Smart move rebuilding the indexes so to run so much faster.
                  And the another thing is that I can't tunned the SQL coz the sql are running behind the applications.....
                  If you have too long sql then set optimizer_index_cost_adj to low value for index access fast responses:

                  http://www.lmgtfy.com/?q=oracle+optimizer_index_cost_adj+faster+performance
                  • 6. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                    Jonathan Lewis
                    user00726 wrote:

                    Users are trying to pull data in the form .txt from application. It is taking more than a single day,

                    Earlier some DBA first rebuild all the indexes then the process would had been completed less than a hour and the size of the data is only 5MB.
                    I'm not sure how to interpret these comments - do you mean that users are simply running reports to generate text files ?

                    Do your comment mean that the work used to take an hour to complete, but after the DBAs rebuilt all the indexes it started taking all day; or was it taking all day before the rebuild and only an hour afterwards ?

                    Is the size of the data really on 5 Megabytes, or was that a typing error ?


                    Looking at the second of the (incomplete) statspack reports, the first question I have to ask is how many CPUs does this machine have. It must be at least two as you've recorded 723 CPU seconds in 11.33 elapsed minutes (and there are only 680 available seconds per CPU in that time).

                    You don't appear to be doing very much work - but since you're on 9.2 it's possible that all your CPU is being used in a way that gets hidden from the reports. For example, if a single long running, CPU-intensive, query started before the 11 minute snapshot and had not completed during the snapshot period, its CPU usage would not show up in the report.

                    Having said that, there are indications of activity that could make the users say the system is slow even though the machine is not loaded - which means you need to think about tracing a few individual sessions. Note:

                    The first SQL statement listed takes 1 CPU second per execution - maybe the users expect this to be instantaneous - get the execution plan from the statspack repository (using sprepsql.sql from $ORACLE_HOME/rdbms/admin) and see if there's an obvious bad choice of plan.

                    The second SQL statement takes half a second per execution - but looks like it is a query related to menu navigation - the users may well say the system is slow if they have to wait even 0.5 seconds between menu pages.

                    The fourth query takes 2 seconds per execution (of which 1 second CPU).
                    SELECT 
                            MRH_DT,MRH_FIRST_NAME,MRH_MIDDLE_NAME,MRH_SUR_NAME,MRH_STATUS   
                    FROM 
                            OM_MEM_REG_HEAD  
                    WHERE 
                            DECODE(:b1,1,MRH_MRN,MRH_MFCH_TEMP_REF_NO) = :b2
                    Because of that decode() the optimizer HAS to use a tablescan - it can't even split this into two optional indexed access paths using concatentation. The front-end code HAS to be rewritten to fix this one.

                    The fifth query really looks as if there should be an index that could allow this one to run quickly - but it's taking one second per execution.
                    SELECT COUNT('X')
                    FROM OM_STUDENT_HEAD
                    WHERE STUD_REF_NO = :b1
                    AND STUD_REF_TXN_CODE = :b2
                    AND STUD_SYS_ID != :b3

                    And so it goes on - nothing individually looks a total disaster - but remember my comment about something hiding outside the report - but lots of things seem to be doing too much work.

                    One possible thought - if someone has been rebuilding indexes, have they also been recreating statistics - and is it possible that they've (say) recreated stats on indexes but not on tables. Something like that could make the optimizer ignore the indexes because they appear to be much bigger than the tables.

                    Bottom line - there are oddities in the SQL report, so go back to my earlier comments: check the SQL plans and look for consistent oddities; and check the indexes where I've mentioned specific examples: and check the consistent (last_analyzed) date for the stats.


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


                    "Science is more than a body of knowledge; it is a way of thinking"
                    Carl Sagan
                    • 7. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                      Jonathan Lewis
                      Absorbine Jr. wrote:
                      Earlier some DBA first rebuild all the indexes then the process would had been completed less than a hour
                      Smart move rebuilding the indexes so to run so much faster.
                      And the another thing is that I can't tunned the SQL coz the sql are running behind the applications.....
                      If you have too long sql then set optimizer_index_cost_adj to low value for index access fast responses:

                      http://www.lmgtfy.com/?q=oracle+optimizer_index_cost_adj+faster+performance
                      Dear Mr. Burleson,

                      I added the term "-Burleson" to your suggested search string (many savvy Oracle professionals do this to improve the efficiency of their searches on Google) and found the following link at number seven on the first page:
                      [The Sin of Band-Aids in Database Tuning|http://michaelcorey.ntirety.com/Blog/tabid/51101/Default.aspx?Tag=optimizer_index_cost_adj]. This points to [an article by Steve Karam|http://www.oraclealchemist.com/oracle/the-sin-of-band-aids/] explaining the dangers of setting things like the optimizer_index_cost_adj as a knee-jerk reaction to an immediate performance problem, and how this can only lead to more problems that become harder to solve as time passes.

                      I would not advise the OP to mess about with optimizer parameters that are poorly understood until he has a better idea of why the performance is bad.

                      Regards
                      Jonathan Lewis
                      http://jonathanlewis.wordpress.com
                      http://www.jlcomp.demon.co.uk
                      • 8. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                        701909
                        I added the term "-Burleson" to your suggested search string (many savvy Oracle professionals do this to improve the efficiency of their searches on Google)
                        Har thats what you hope they will do!!!!!

                        The internet is not broken the popular authors are where they should be.
                        I would not advise the OP to mess about with optimizer parameters
                        That is OK I did it for you.

                        Porcine slosh!!!!
                        • 9. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                          679637
                          Absorbine Jr. wrote:
                          I added the term "-Burleson" to your suggested search string (many savvy Oracle professionals do this to improve the efficiency of their searches on Google)
                          Har thats what you hope they will do!!!!!
                          I do it and know many others who also do it. Your sites contain mostly rubbish. Poor jobs of copy/paste. Rarely include explanations.

                          [*There are some great comments on how to do this with Firefox and Customizegoogle*|http://jonathanlewis.wordpress.com/2008/06/10/firefox/]
                          The internet is not broken the popular authors are where they should be.
                          HA! Clearly a case of quantity versus quality. Reminds me of all the email spam I get. Just because it shows up all the time in my inbox, certainly does not mean it is worth reading.
                          I would not advise the OP to mess about with optimizer parameters
                          That is OK I did it for you.
                          Wow, aren't you cute and funny...
                          • 10. Re: what should be the value of Optimizer_index_cost_adj  in oracle 9i
                            user00726
                            HI all,

                            Thanks all for your valuable suggestion....

                            Mr.Jonathanlewis Sir,

                            We have a two CPUs

                            and the applicationa are running on CITRIX server......

                            Although the data is only 5Mb in size....but I am really not getting......Why it is taking lot of time....


                            I know i might be asking some stupid question but i am new to performance tunning........

                            Actually ealier DBA rebuild the indexes and it hardly took at that time half an hour......but i have also read article of Mr.Richard that it is really a myth that rebuilding the indexes improves perofrmance....that's really good article....

                            so pls suggest me....what shoul di do to improve the same....
                            • 11. Re: what should be the value of Optimizer_index_cost_adj  in oracle 9i
                              Jonathan Lewis
                              user00726 wrote:

                              We have a two CPUs

                              Although the data is only 5Mb in size....but I am really not getting......Why it is taking lot of time....
                              The statspack report tells us that you've got at least one table fo 72MB - since you say the data is only 5MB should we assume that this is the size of the text file you are producing ?

                              >
                              Actually ealier DBA rebuild the indexes and it hardly took at that time half an hour
                              I told you that your original statement could be read in two completely opposite ways, and gave you a choice of two interpretations. You've responded with a sentence that could be interpreted in three ways (a) it took half an hour to rebuild the indexes, (b) the job took half an hour BEFORE the index rebuild (c) the job took half an hour immediately AFTER the index rebuild.

                              I will assume you did not mean (a). I think I know what you did mean - but I wrote out the two options very carefully last time around so that I could be absolutely sure. If you do not describe your problem carefully you cannot hope to get good advice. So please read (b) and (c) carefully, and then tell me which one you intended.


                              Back to basics:
                              You have optimizer_mode = choose. Do you know whether you have statistics collected for the data, and how those statistics are collected, and when those statistics are collected, as CHOOSE means that you may have some SQL using rule-based optimisation, and some using cost-based optimisation - and a mixture of missing statistics, old statistics, and current statistics will often lead to extremely inefficient execution plans.

                              For the SCHEMA owner, try running the following SQL:
                              break on report
                              compute sum of count(*) on report
                              
                              select 
                                   trunc(last_analyzed), 
                                   count(*) 
                              from 
                                   user_tables
                              group by
                                   trunc(last_analyzed)
                              order by
                                   trunc(last_analyzed)
                              ;
                              
                              select 
                                   trunc(last_analyzed), 
                                   count(*) 
                              from 
                                   user_indexes
                              group by
                                   trunc(last_analyzed)
                              order by
                                   trunc(last_analyzed)
                              ;
                              Regards
                              Jonathan Lewis
                              http://jonathanlewis.wordpress.com
                              http://www.jlcomp.demon.co.uk


                              "Be very, very careful what you put into that head because you will never, ever get it out."
                              Cardinal Wolsey
                              • 12. Re: what should be the value of Optimizer_index_cost_adj  in oracle 9i
                                user00726
                                Mr.Johanthan Says
                                
                                I told you that your original statement could be read in two completely opposite ways, and gave you a choice of two interpretations. You've responded with a sentence that could be interpreted in three ways (a) it took half an hour to rebuild the indexes, (b) the job took half an hour BEFORE the index rebuild (c) the job took half an hour immediately AFTER the index rebuild. 
                                
                                I will assume you did not mean (a). I think I know what you did mean - but I wrote out the two options very carefully last time around so that I could be absolutely sure. If you do not describe your problem carefully you cannot hope to get good advice. So please read (b) and (c) carefully, and then tell me which one you intended.
                                My question was related to your c) point 
                                SQL> select
                                        trunc(last_analyzed),
                                        count(*)
                                from
                                        user_tables
                                group by
                                        trunc(last_analyzed)
                                order by
                                        trunc(last_analyzed)
                                ;
                                  2    3    4    5    6    7    8    9   10
                                TRUNC(LAS   COUNT(*)
                                --------- ----------
                                09-OCT-05        300
                                                  31
                                
                                SQL> select
                                        trunc(last_analyzed),
                                        count(*)
                                from
                                        user_indexes
                                group by
                                        trunc(last_analyzed)
                                order by
                                        trunc(last_analyzed)
                                ;
                                  2    3    4    5    6    7    8    9   10
                                TRUNC(LAS   COUNT(*)
                                --------- ----------
                                09-OCT-05         10
                                                 329
                                
                                
                                SQL> select
                                        trunc(last_analyzed),
                                        count(*)
                                from
                                        dba_tables where owner='DATA3'
                                group by
                                        trunc(last_analyzed)
                                order by
                                        trunc(last_analyzed)
                                ;
                                  2    3    4    5    6    7    8    9   10
                                
                                TRUNC(LAS   COUNT(*)
                                --------- ----------
                                08-JAN-09          7
                                09-JAN-09         10
                                12-MAR-09       1831
                                01-MAY-09          4
                                                  16
                                
                                SQL> SQL>
                                
                                
                                 select
                                        trunc(last_analyzed),
                                        count(*)
                                from
                                        dba_indexes where owner='DATA3'
                                group by
                                        trunc(last_analyzed)
                                order by
                                        trunc(last_analyzed)
                                
                                SQL>  select
                                        trunc(last_analyzed),
                                        count(*)
                                from
                                        dba_indexes where owner='DATA3'
                                group by
                                        trunc(last_analyzed)
                                order by
                                        trunc(last_analyzed)  2    3    4    5    6    7    8    9
                                 10  ;
                                
                                TRUNC(LAS   COUNT(*)
                                --------- ----------
                                12-MAR-09       1583
                                01-MAY-09         21
                                                  27
                                Edited by: user00726 on Jun 11, 2009 3:41 AM
                                • 13. Re: what should be the value of Optimizer_index_cost_adj  in oracle 9i
                                  Charles Hooper
                                  user00726 wrote:
                                  HI all,

                                  Thanks all for your valuable suggestion....

                                  Mr.Jonathanlewis Sir,

                                  We have a two CPUs

                                  and the applicationa are running on CITRIX server......

                                  Although the data is only 5Mb in size....but I am really not getting......Why it is taking lot of time....


                                  I know i might be asking some stupid question but i am new to performance tunning........

                                  Actually ealier DBA rebuild the indexes and it hardly took at that time half an hour......but i have also read article of Mr.Richard that it is really a myth that rebuilding the indexes improves perofrmance....that's really good article....

                                  so pls suggest me....what shoul di do to improve the same....
                                  I do not wish to confuse things, just a couple observations from the portions of the Statspack reports that you provided:
                                  Other than CPU utilization, the average resource utilization in the instance do not seem to be too significant.

                                  The ending sessions statistic appears as ####### (too large to print) in first instance, and 3,353 in the second instance. Each session requires some of the server's memory on connection (for reference purposes, by default on Windows with dedicated connections, each session requires at least 1MB just for the session to connect). Is it possible that due to the number of connected sessions, the allocated memory of the first instance is exceeding the server's physical memory thus requiring excessive page file swapping? It might be that there is additional competition for the CPU from another process running on the server, which causes the Oracle processes to spend a lot of time sitting in the CPU run queue.

                                  This SQL statement only appears for the first instance in the portions of the Statspack reports that you provided, it is at the top of the buffer gets, has the highest total CPU consumption, 97% of the execution time is on the CPU, and each execution requires just 1.07 seconds:
                                  SELECT A.FH_HO_REGION_CODE,DECODE(NVL(MCNT.M_CNT,0),1,2,1) FH_TY
                                  PE,D.CITY_NAME,A.FH_FRN FH_FRN_M,A.FH_FIRM_NAME FIRM_NAME_M,'1|'
                                   RECTYPE,C.MRH_MRN MRH_MRN_M,C.MRH_FIRST_NAME || ' '  || C.MRH_M
                                  IDDLE_NAME  || ' '  || C.MRH_SUR_NAME  MEM_NAME,O_GET_COP_STATUS
                                  (C.MRH_MRN,:b1) MRH_COP_STATUS,FCNT.F_CNT   FROM OM_FIRM_HEAD A,
                                  This SQL statement, while only executed 3 times in the first instance, required an average of 5.37 seconds per execution for what appears to be a simple SQL statements (it might be that the first execution required 15 seconds, and the final two each required a half second), and 66% of the execution time is on the CPU:
                                  SELECT LTRIM(RTRIM(DECODE(TIT_NAME,'MR.','CA.','MS.','CA.','MRS.
                                  ','CA.') || ' '  || MRH_FIRST_NAME  || ' '  || MRH_MIDDLE_NAME
                                  || ' '  || MRH_SUR_NAME  || ' '  || DECODE(MRH_APPR_UID, NULL ,
                                  NULL ,DECODE(MRH_MEM_STATUS,2, NULL ,DECODE(MRH_FELLOW_STATUS_YN
                                  ,'Y','FCA','ACA')))  || DECODE(MRH_RESI_STATUS,'A','
                                  It would be interesting to look at the execution plans for the SQL statements. You would want to look at the explain plans stored in memory, and there might be multiple plans (more correctly, multiple child cursors with potentially different plans) for each SQL statement. An example of displaying the execution plans in memory on Oracle 9.2.x may be found here:
                                  http://blog.tanelpoder.com/2009/05/26/scripts-for-showing-execution-plans-via-plain-sql-and-also-in-oracle-9i/

                                  It might be interesting to look at the initialization parameters and the remaining portions of the Statspack reports.

                                  Charles Hooper
                                  IT Manager/Oracle DBA
                                  K&M Machine-Fabricating, Inc.
                                  • 14. Re: what should be the value of Optimizer_index_cost_adj  in oracle 9i
                                    701909
                                    Earlier some DBA first rebuild all the indexes then the process would had been completed less than a hour
                                    And when some DBA rebuilded your indexes the process ran very fast, yes?
                                    but i have also read article of Mr.Richard that it is really a myth that rebuilding the indexes improves perofrmance
                                    But this myth was not true in your case?
                                    I know i might be asking some stupid question but i am new to performance tunning........
                                    If they think your question is stupid you will become accused of DKB!
                                    what shoul di do to improve the same....
                                    Try again what worked before perhaps.
                                    1 2 3 Previous Next