1 2 3 Previous Next 33 Replies Latest reply: May 18, 2008 9:31 PM by Aman.... RSS

    performance(statspack) related problem

    user00726
      Anybodyu can suggest me how to improve the database performance as many uiser are complaining that they are getting data very slow........

      i have also used statspack analyzer.......


      STATSPACK report for

      DB Name DB Id Instance Inst Num Release Cluster Host
      ------------ ----------- ------------ -------- ----------- ------- ------------
      NEOSOFT 399087012 neosoft 1 9.2.0.7.0 NO APP

      Snap Id Snap Time Sessions Curs/Sess Comment
      --------- ------------------ -------- --------- -------------------
      Begin Snap: 1 16-May-08 10:20:15 435 2.2
      End Snap: 3 16-May-08 10:24:12 442 2.2
      Elapsed: 3.95 (mins)

      Cache Sizes (end)
      ~~~~~~~~~~~~~~~~~
      Buffer Cache: 352M Std Block Size: 8K
      Shared Pool Size: 200M Log Buffer: 1,024K

      Load Profile
      ~~~~~~~~~~~~ Per Second Per Transaction
      --------------- ---------------
      Redo size: 23,125.82 7,966.31
      Logical reads: 6,050.15 2,084.14
      Block changes: 96.53 33.25
      Physical reads: 1,103.40 380.10
      Physical writes: 16.60 5.72
      User calls: 189.40 65.24
      Parses: 29.92 10.31
      Hard parses: 8.69 2.99
      Sorts: 6.97 2.40
      Logons: 0.11 0.04
      Executes: 29.25 10.08
      Transactions: 2.90

      % Blocks changed per Read: 1.60 Recursive Call %: 31.70
      Rollback per transaction %: 0.29 Rows per Sort: 472.41

      Instance Efficiency Percentages (Target 100%)
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      Buffer Nowait %: 100.00 Redo NoWait %: 100.00
      Buffer Hit %: 82.01 In-memory Sort %: 99.21
      Library Hit %: 89.76 Soft Parse %: 70.95
      Execute to Parse %: -2.29 Latch Hit %: 99.95
      Parse CPU to Parse Elapsd %: 100.93 % Non-Parse CPU: 84.62

      Shared Pool Statistics Begin End
      ------ ------
      Memory Usage %: 94.72 94.24
      % SQL with executions>1: 39.68 38.62
      % Memory for SQL w/exec>1: 39.34 38.76

      Top 5 Timed Events
      ~~~~~~~~~~~~~~~~~~ % Total
      Event Waits Time (s) Ela Time
      -------------------------------------------- ------------ ----------- --------
      db file scattered read 39,162 38 39.49
      CPU time 28 29.48
      db file sequential read 42,173 28 29.24
      direct path read 1,193 1 .59
      SQL*Net more data to client 31,629 1 .53
      -------------------------------------------------------------
      Wait Events for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> 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 scattered read 39,162 0 38 1 56.9
      db file sequential read 42,173 0 28 1 61.3
      direct path read 1,193 0 1 0 1.7
      SQL*Net more data to client 31,629 0 1 0 46.0
      log file sync 683 0 0 0 1.0
      control file sequential read 184 0 0 1 0.3
      db file parallel write 61 0 0 1 0.1
      control file parallel write 78 0 0 0 0.1
      log file parallel write 732 0 0 0 1.1
      direct path write 30 0 0 0 0.0
      SQL*Net break/reset to clien 10 0 0 0 0.0
      LGWR wait for redo copy 3 0 0 0 0.0
      SQL*Net message from client 44,675 0 577 13 64.9
      virtual circuit status 47,524 4 145 3 69.1
      SQL*Net message to client 44,674 0 0 0 64.9
      SQL*Net more data from clien 260 0 0 0 0.4
      -------------------------------------------------------------
      Background Wait Events for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> 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 80 0 0 2 0.1
      db file parallel write 61 0 0 1 0.1
      control file parallel write 78 0 0 0 0.1
      log file parallel write 732 0 0 0 1.1
      LGWR wait for redo copy 3 0 0 0 0.0
      rdbms ipc message 993 242 2,054 2069 1.4
      pmon timer 79 79 234 2962 0.1
      smon timer 13 0 228 17520 0.0
      -------------------------------------------------------------
      SQL ordered by Gets for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> 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
      --------------- ------------ -------------- ------ -------- --------- ----------
      92,160 36 2,560.0 6.4 0.41 0.37 1007205397
      Module: Pharmacy 2.0.174.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      75,042 33 2,274.0 5.2 0.23 0.29 3052506495
      Module: In Patient 2.1.152.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      68,310 27 2,530.0 4.8 0.27 0.27 2037438344
      Module: Pharmacy 2.0.174.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      48,617 31 1,568.3 3.4 0.16 0.22 1186386010
      Module: In Patient 2.1.152.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      45,060 20 2,253.0 3.1 0.17 0.18 3400961035
      Module: In Patient 2.1.152.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      42,976 17 2,528.0 3.0 0.14 0.17 3744553745
      Module: Pharmacy 2.0.174.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      36,114 78 463.0 2.5 0.13 0.11 4262998487
      Module: In Patient 2.1.152.exe
      SQL ordered by Gets for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> 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 to_char(null), ac.owner, ac.table_name, acc.column_name,
      acc.position, ac.constraint_name from all_constraints ac, all_co
      ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
      'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
      e 'IP' escape '\' and ac.table_name like 'DOCTORVISIT' escape '

      25,102 11 2,282.0 1.8 0.06 0.10 812077776
      Module: In Patient 2.1.152.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      24,984 36 694.0 1.7 0.09 0.07 3956499023
      Module: Pharmacy 2.0.174.exe
      select to_char(null), ac.owner, ac.table_name, acc.column_name,
      acc.position, ac.constraint_name from all_constraints ac, all_co
      ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
      'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
      e 'INVENTORY' escape '\' and ac.table_name like 'DMEDMAST' esca

      23,213 1 23,213.0 1.6 1.59 2.83 2435229694
      Module: SQL*Plus
      BEGIN statspack.snap(i_snap_level=>7); END;

      22,930 10 2,293.0 1.6 0.14 0.09 3466778893
      Module: In Patient 2.1.152.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      22,680 10 2,268.0 1.6 0.08 0.09 3786189544
      Module: In Patient 2.1.152.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      20,340 9 2,260.0 1.4 0.13 0.08 3649693145
      Module: In Patient 2.1.152.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      SQL ordered by Gets for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> 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
      --------------- ------------ -------------- ------ -------- --------- ----------
      19,460 42 463.3 1.4 0.09 0.06 3858576624
      Module: In Patient 2.1.152.exe
      select to_char(null), ac.owner, ac.table_name, acc.column_name,
      acc.position, ac.constraint_name from all_constraints ac, all_co
      ns_columns acc where ac.owner=acc.owner and ac.constraint_type=

      -------------------------------------------------------------
      SQL ordered by Reads for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> End Disk Reads Threshold: 1000

      CPU Elapsd
      Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
      --------------- ------------ -------------- ------ -------- --------- ----------
      8,084 4 2,021.0 3.1 0.14 1.08 4198744706
      Module: Out Patient 2.1.41.exe
      Select Max(BillNo) as Bno From PatientsRegistration Where regdt
      = to_date('01/04/2008','dd/mm/yyyy') and regdt <= to_date('31/0
      3/2009','dd/mm/yyyy')

      7,302 1 7,302.0 2.8 0.13 0.51 2116660478
      Module: In Patient 2.1.157.exe
      select discount,discamount from billpharmacy where patientid='PA
      T09212'

      7,302 1 7,302.0 2.8 0.06 0.41 3612969128
      Module: In Patient 2.1.152.exe
      select discount,discamount from billpharmacy where patientid='PA
      T09037'

      7,302 1 7,302.0 2.8 0.19 0.51 3981413559
      Module: In Patient 2.1.152.exe
      sELECT * FROM BillPharmacy WHERE BillPharmacy.pATIENtid= 'PAT090
      37' order by to_number((issueno))

      7,300 1 7,300.0 2.8 0.14 0.69 136284863
      Module: In Patient 2.1.152.exe
      delete from billpharmacy where patientid='PAT09037'

      7,299 1 7,299.0 2.8 0.20 0.77 2137420337
      Module: In Patient 2.1.152.exe
      delete from billpharmacy where patientid='PAT09154'

      7,298 1 7,298.0 2.8 0.13 0.43 1007690124
      Module: In Patient 2.1.152.exe
      select discount,discamount from billpharmacy where patientid='PA
      T08301'

      7,298 1 7,298.0 2.8 0.13 0.58 3087447381
      Module: In Patient 2.1.152.exe
      select discount,discamount from billpharmacy where patientid='PA
      T09154'

      7,061 1 7,061.0 2.7 0.13 0.97 2993688253
      Module: In Patient 2.1.152.exe
      select discount,discamount from billpharmacy where patientid='PA
      T09146'

      7,058 1 7,058.0 2.7 0.39 0.76 1202603169
      Module: In Patient 2.1.152.exe
      SELECT BILLHDR.BILLNO, BILLHDR.BILLDT, BILLHDR.BALANCE, BILLHDR.
      TOTAL, BILLHDR.NETAMOUNT,IPINFO.type as orgtype, BILLHDR.PREVAD
      V , IPInfo.IpNO, IPInfo.REGDT,IPInfo.REGTM, IPInfo.Name, IPInfo.
      ADDRESS1,IPINFO.relativenm ,Rooms.roomno , Beds.BEDNO, ROOMTYPES
      , decode(IPINFO.sex,1,'Male','Female')sex,IPINFO.AGE ||' '||dec

      6,640 1 6,640.0 2.5 0.13 1.06 796742944
      Module: In Patient 2.1.152.exe
      SQL ordered by Reads for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> End Disk Reads Threshold: 1000

      CPU Elapsd
      Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
      --------------- ------------ -------------- ------ -------- --------- ----------
      select discount,discamount from billpharmacy where patientid='PA
      T09123'

      4,617 4 1,154.3 1.8 0.19 0.94 2042905630
      Module: Out Patient 2.1.41.exe
      SELECT NVL(Max(MoneyReciept.mrBillNo),0) AS MaxOfBillNob FROM Mo
      neyReciept where generalopd = 0

      4,496 3 1,498.7 1.7 0.06 0.57 2718453880
      Module: Diagnotech 2.2.49.exe
      Select * from track where tablenm = 'FRMREPORTING' and primkeyid
      = 'R208970TS000002' order by trackdate,tracktime

      4,494 3 1,498.0 1.7 0.13 0.74 3290276624
      Module: Diagnotech 2.2.49.exe
      Select * from track where tablenm = 'FRMREPORTING' and primkeyid
      = 'R207032T0000351' order by trackdate,tracktime

      4,451 3 1,483.7 1.7 0.20 0.99 768943678
      Module: Diagnotech 2.2.49.exe
      Select * from track where tablenm = 'FRMREPORTING' and primkeyid
      = 'R208613TS000002' order by trackdate,tracktime

      4,091 4 1,022.8 1.6 0.19 1.62 266509601
      Module: In Patient 2.1.152.exe
      SELECT IPInfo.IPNo ,IPInfo.name,doctorvisit.date_r ,unithdr.drna
      me , doctorvisit.visitid FROM Doctorvisit , IPInfo,unithdr wher
      e Doctorvisit.Patid = IPInfo.PatID and Doctorvisit.doctorid=unit
      hdr.unitid and doctorvisit.date_r >=to_date('06/05/2008','fmdd/m
      m/yyyy') order by IPInfo.IPNo

      3,933 5 786.6 1.5 0.14 1.29 2546712787
      Module: Diagnotech 2.2.49.exe
      Select max(BillNo) as Bno From MoneyReciept where rcdt >= to_dat
      e('01/04/2008','dd/mm/yyyy') and rcdt <= to_date('31/03/2009','d
      d/mm/yyyy')

      3,439 3 1,146.3 1.3 0.25 1.89 2816864569
      Module: Diagnotech 2.2.49.exe
      Select * from track where tablenm = 'FRMREPORTING' and primkeyid
      = 'R207242T0000104' order by trackdate,tracktime

      3,129 2 1,564.5 1.2 0.06 0.88 1803349496
      Module: Diagnotech 2.2.49.exe
      Select * from track where tablenm = 'FRMREPORTING' and primkeyid
      = 'R208962TS000002' order by trackdate,tracktime

      3,065 4 766.3 1.2 0.17 1.30 54282346
      Module: In Patient 2.1.157.exe
      Select * from track where tablenm = 'FRMDOCTORVISIT' and primkey
      id = 'VI048354' order by trackdate,tracktime

      2,834 3 944.7 1.1 0.17 1.57 165470279
      Module: Diagnotech 2.2.49.exe
      SQL ordered by Reads for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> End Disk Reads Threshold: 1000

      CPU Elapsd
      Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
      --------------- ------------ -------------- ------ -------- --------- ----------
      Select * from track where tablenm = 'FRMREPORTING' and primkeyid
      = 'R208926TS000002' order by trackdate,tracktime

      2,769 3 923.0 1.1 0.13 1.28 3812904405
      Module: Diagnotech 2.2.49.exe
      Select * from track where tablenm = 'FRMREPORTING' and primkeyid
      = 'R208892TS000002' order by trackdate,tracktime

      2,730 3 910.0 1.0 0.19 1.21 680462328
      Module: In Patient 2.1.157.exe
      select * from DoctorVisit where Date_r >= to_date('06/05/2008','
      dd/mm/yyyy') and Date_r <= to_date('16/05/2008','dd/mm/yyyy')

      2,526 2 1,263.0 1.0 0.58 1.14 4043595143
      Module: SQL*Plus
      INSERT INTO STATS$SQL_SUMMARY ( SNAP_ID , DBID , INSTANCE_NUMBER
      , TEXT_SUBSET , SHARABLE_MEM , SORTS , MODULE , LOADED_VERSIONS
      , FETCHES , EXECUTIONS , LOADS , INVALIDATIONS , PARSE_CALLS ,
      DISK_READS , BUFFER_GETS , ROWS_PROCESSED , COMMAND_TYPE , ADDRE
      SS , HASH_VALUE , VERSION_COUNT , CPU_TIME , ELAPSED_TIME , OUTL

      2,484 1 2,484.0 0.9 0.19 0.91 33028691
      Module: Diagnotech 2.2.49.exe
      select * from requisitions where upper(reqno) = '180800'

      2,483 1 2,483.0 0.9 0.13 0.93 2943168498
      Module: Diagnotech 2.2.49.exe
      Update Requisitions Set Collected = 0 Where TRIM(ReqID) ='R20987
      8'

      2,482 1 2,482.0 0.9 0.11 0.74 2336722822
      Module: Diagnotech 2.2.49.exe
      Update Requisitions Set Collected = 0 Where TRIM(ReqID) ='R20987
      7'

      2,480 1 2,480.0 0.9 0.06 0.67 650704065
      Module: In Patient 2.1.152.exe
      select sum(can.returnamt) refamt from diagnotech.cancellationhd
      r can,diagnotech.requisitions req where can.reqid = req.reqid an

      -------------------------------------------------------------
      SQL ordered by Executions for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> End Executions Threshold: 100

      CPU per Elap per
      Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
      ------------ --------------- ---------------- ----------- ---------- ----------
      283 283 1.0 0.00 0.00 1283498366
      Module: Diagnotech 2.2.49.exe
      Select to_char(sysdate,'fmdd/mm/yyyy HH24:mi:ss am') as abc from
      OPTIONS

      238 76 0.3 0.00 0.00 4274598960
      select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
      estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
      density, col#, spare1, spare2, avgcln from hist_head$ where obj#
      =:1 and intcol#=:2

      126 126 1.0 0.00 0.00 2168100061
      Module: Diagnotech 2.2.49.exe
      select * from departments where deptid = 'DEP00004'

      114 0 0.0 0.00 0.00 4073347083
      select grantor#, grantee#, privilege#, sequence#, nvl(option$,0)
      from objauth$ where obj#=:1 and col#=:2

      112 112 1.0 0.00 0.00 3687727603
      insert into col$(obj#,name,intcol#,segcol#,type#,length,precisio
      n#,scale,null$,offset,fixedstorage,segcollength,deflength,defaul
      t$,col#,property,charsetid,charsetform,spare1,spare2,spare3)valu
      es(:1,:2,:3,:4,:5,:6,decode(:7,0,null,:7),decode(:5,2,decode(:8,
      -127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,

      99 99 1.0 0.00 0.00 3468666020
      select text from view$ where rowid=:1

      84 84 1.0 0.00 0.00 2997854589
      insert into access$(d_obj#,order#,columns,types) values (:1,:2,:
      3,:4)

      84 84 1.0 0.00 0.00 3951809012
      insert into dependency$(d_obj#,d_timestamp,order#,p_obj#,p_times
      tamp,d_owner#, property)values (:1,:2,:3,:4,:5,:6, :7)

      78 0 0.0 0.00 0.00 3867936055
      Module: Out Patient 2.1.41.exe
      commit

      78 78 1.0 0.00 0.00 4262998487
      Module: In Patient 2.1.152.exe
      select to_char(null), ac.owner, ac.table_name, acc.column_name,
      acc.position, ac.constraint_name from all_constraints ac, all_co
      ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
      'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
      e 'IP' escape '\' and ac.table_name like 'DOCTORVISIT' escape '

      44 44 1.0 0.00 0.00 3216099004
      Module: Diagnotech 2.2.49.exe
      select * from options

      42 42 1.0 0.00 0.00 3858576624
      SQL ordered by Executions for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> End Executions Threshold: 100

      CPU per Elap per
      Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
      ------------ --------------- ---------------- ----------- ---------- ----------
      Module: In Patient 2.1.152.exe
      select to_char(null), ac.owner, ac.table_name, acc.column_name,
      acc.position, ac.constraint_name from all_constraints ac, all_co
      ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
      'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
      e 'IP' escape '\' and ac.table_name like 'IPINFO' escape '\'

      36 540 15.0 0.01 0.01 1007205397
      Module: Pharmacy 2.0.174.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      36 36 1.0 0.00 0.00 3956499023
      Module: Pharmacy 2.0.174.exe
      select to_char(null), ac.owner, ac.table_name, acc.column_name,
      acc.position, ac.constraint_name from all_constraints ac, all_co
      ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
      'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
      e 'INVENTORY' escape '\' and ac.table_name like 'DMEDMAST' esca

      34 34 1.0 0.00 0.00 857250417
      Module: Diagnotech 2.2.46.exe
      select * from departments where deptid = 'DEP00031'

      33 43,442 1,316.4 0.04 0.05 502973950
      Module: Pharmacy 2.0.174.exe
      SELECT substr(UPPER(MedMast.MedNm),1,50) MedNm , MedSubCat.MSCOD
      E ,MEDMAST.POTENCY ,substr(genericnm,1,50) genericnm, sum(dmeddt
      ls.currqty) stock, MedMast.medid FROM MedMast , MedSubCat,dmedd
      tls where UPPER(MEDMAST.MEDNM) LIKE '%%' and medmast.medid = d
      meddtls.medid and MEDMAST.SubCatID = MedSubCat.MSCatID and dmed

      33 132 4.0 0.01 0.01 3052506495
      Module: In Patient 2.1.152.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      31 124 4.0 0.01 0.01 1186386010
      Module: In Patient 2.1.152.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      31 31 1.0 0.00 0.00 3993731530
      Module: In Patient 2.1.152.exe
      select to_char(null), ac.owner, ac.table_name, acc.column_name,
      SQL ordered by Executions for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> End Executions Threshold: 100

      CPU per Elap per
      Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
      ------------ --------------- ---------------- ----------- ---------- ----------
      acc.position, ac.constraint_name from all_constraints ac, all_co
      ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
      'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
      e 'OTS1' escape '\' and ac.table_name like 'DOCTORINFO' escape

      28 28 1.0 0.00 0.00 737183288
      Module: Diagnotech 2.2.49.exe
      Select * From CReqno

      27 0 0.0 0.00 0.00 246231567
      Module: Pharmacy 2.0.174.exe
      select to_char(null), ac.owner, ac.table_name, acc.column_name,

      -------------------------------------------------------------
      SQL ordered by Parse Calls for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> End Parse Calls Threshold: 1000

      % Total
      Parse Calls Executions Parses Hash Value
      ------------ ------------ -------- ----------
      283 283 3.99 1283498366
      Module: Diagnotech 2.2.49.exe
      Select to_char(sysdate,'fmdd/mm/yyyy HH24:mi:ss am') as abc from
      OPTIONS

      126 126 1.78 2168100061
      Module: Diagnotech 2.2.49.exe
      select * from departments where deptid = 'DEP00004'

      99 99 1.40 3468666020
      select text from view$ where rowid=:1

      78 78 1.10 3867936055
      Module: Out Patient 2.1.41.exe
      commit

      78 78 1.10 4262998487
      Module: In Patient 2.1.152.exe
      select to_char(null), ac.owner, ac.table_name, acc.column_name,
      acc.position, ac.constraint_name from all_constraints ac, all_co
      ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
      'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
      e 'IP' escape '\' and ac.table_name like 'DOCTORVISIT' escape '

      44 44 0.62 3216099004
      Module: Diagnotech 2.2.49.exe
      select * from options

      42 42 0.59 3858576624
      Module: In Patient 2.1.152.exe
      select to_char(null), ac.owner, ac.table_name, acc.column_name,
      acc.position, ac.constraint_name from all_constraints ac, all_co
      ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
      'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
      e 'IP' escape '\' and ac.table_name like 'IPINFO' escape '\'

      36 36 0.51 1007205397
      Module: Pharmacy 2.0.174.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      36 36 0.51 3956499023
      Module: Pharmacy 2.0.174.exe
      select to_char(null), ac.owner, ac.table_name, acc.column_name,
      acc.position, ac.constraint_name from all_constraints ac, all_co
      ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
      'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
      e 'INVENTORY' escape '\' and ac.table_name like 'DMEDMAST' esca

      34 34 0.48 857250417
      Module: Diagnotech 2.2.46.exe
      SQL ordered by Parse Calls for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> End Parse Calls Threshold: 1000

      % Total
      Parse Calls Executions Parses Hash Value
      ------------ ------------ -------- ----------
      select * from departments where deptid = 'DEP00031'

      33 33 0.47 502973950
      Module: Pharmacy 2.0.174.exe
      SELECT substr(UPPER(MedMast.MedNm),1,50) MedNm , MedSubCat.MSCOD
      E ,MEDMAST.POTENCY ,substr(genericnm,1,50) genericnm, sum(dmeddt
      ls.currqty) stock, MedMast.medid FROM MedMast , MedSubCat,dmedd
      tls where UPPER(MEDMAST.MEDNM) LIKE '%%' and medmast.medid = d
      meddtls.medid and MEDMAST.SubCatID = MedSubCat.MSCatID and dmed

      33 33 0.47 3052506495
      Module: In Patient 2.1.152.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      31 31 0.44 1186386010
      Module: In Patient 2.1.152.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      31 31 0.44 3993731530
      Module: In Patient 2.1.152.exe
      select to_char(null), ac.owner, ac.table_name, acc.column_name,
      acc.position, ac.constraint_name from all_constraints ac, all_co
      ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
      'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
      e 'OTS1' escape '\' and ac.table_name like 'DOCTORINFO' escape

      30 15 0.42 3067006941
      delete from dependency$ where d_obj#=:1

      28 28 0.39 737183288
      Module: Diagnotech 2.2.49.exe
      Select * From CReqno

      27 27 0.38 246231567
      Module: Pharmacy 2.0.174.exe
      select to_char(null), ac.owner, ac.table_name, acc.column_name,
      acc.position, ac.constraint_name from all_constraints ac, all_co
      ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
      'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
      e 'INVENTORY' escape '\' and ac.table_name like 'DISSUEDTLSP' e

      27 27 0.38 445844524
      Module: In Patient 2.1.152.exe
      select nvl(tarrifflg,0) as tf from sourcehdr where sourceid = ''


      SQL ordered by Parse Calls for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> End Parse Calls Threshold: 1000

      % Total
      Parse Calls Executions Parses Hash Value
      ------------ ------------ -------- ----------
      27 27 0.38 2037438344
      Module: Pharmacy 2.0.174.exe
      select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
      ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
      'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
      ll_ind_columns c where i.owner = c.index_owner and i.index_name
      = c.index_name and i.table_owner = c.table_owner and i.table_nam

      22 22 0.31 3073477137
      select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled
      from obj$ o,user$ u,trigger$ t where t.baseobject=:1 and t.ob
      j#=o.obj# and o.owner#=u.user# order by o.obj#

      -------------------------------------------------------------
      Instance Activity Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3

      Statistic Total per Second per Trans
      --------------------------------- ------------------ -------------- ------------
      CPU used by this session 2,821 11.9 4.1
      CPU used when call started 2,826 11.9 4.1
      CR blocks created 15 0.1 0.0
      DBWR buffers scanned 5,873 24.8 8.5
      DBWR checkpoint buffers written 167 0.7 0.2
      DBWR checkpoints 0 0.0 0.0
      DBWR free buffers found 5,653 23.9 8.2
      DBWR lru scans 17 0.1 0.0
      DBWR make free requests 17 0.1 0.0
      DBWR summed scan depth 5,873 24.8 8.5
      DBWR transaction table writes 1 0.0 0.0
      DBWR undo block writes 228 1.0 0.3
      SQL*Net roundtrips to/from client 89,026 375.6 129.4
      active txn count during cleanout 156 0.7 0.2
      background checkpoints completed 0 0.0 0.0
      background checkpoints started 0 0.0 0.0
      background timeouts 243 1.0 0.4
      branch node splits 0 0.0 0.0
      buffer is not pinned count 1,225,066 5,169.1 1,780.6
      buffer is pinned count 1,069,472 4,512.5 1,554.5
      bytes received via SQL*Net from c 4,334,045 18,287.1 6,299.5
      bytes sent via SQL*Net to client 79,310,906 334,645.2 115,277.5
      calls to get snapshot scn: kcmgss 14,976 63.2 21.8
      calls to kcmgas 942 4.0 1.4
      calls to kcmgcs 166 0.7 0.2
      change write time 19 0.1 0.0
      cleanout - number of ktugct calls 182 0.8 0.3
      cleanouts and rollbacks - consist 5 0.0 0.0
      cleanouts only - consistent read 18 0.1 0.0
      cluster key scan block gets 206,010 869.2 299.4
      cluster key scans 81,803 345.2 118.9
      commit cleanout failures: block l 0 0.0 0.0
      commit cleanout failures: callbac 11 0.1 0.0
      commit cleanout failures: cannot 0 0.0 0.0
      commit cleanouts 2,731 11.5 4.0
      commit cleanouts successfully com 2,720 11.5 4.0
      commit txn count during cleanout 87 0.4 0.1
      consistent changes 15 0.1 0.0
      consistent gets 1,412,564 5,960.2 2,053.2
      consistent gets - examination 572,440 2,415.4 832.0
      cursor authentications 531 2.2 0.8
      data blocks consistent reads - un 15 0.1 0.0
      db block changes 22,877 96.5 33.3
      db block gets 21,323 90.0 31.0
      deferred (CURRENT) block cleanout 1,548 6.5 2.3
      dirty buffers inspected 19 0.1 0.0
      enqueue conversions 52 0.2 0.1
      enqueue releases 4,639 19.6 6.7
      enqueue requests 4,640 19.6 6.7
      enqueue timeouts 0 0.0 0.0
      enqueue waits 0 0.0 0.0
      execute count 6,932 29.3 10.1
      free buffer inspected 19 0.1 0.0
      free buffer requested 258,870 1,092.3 376.3
      hot buffers moved to head of LRU 2,520 10.6 3.7
      immediate (CR) block cleanout app 23 0.1 0.0
      Instance Activity Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3

      Statistic Total per Second per Trans
      --------------------------------- ------------------ -------------- ------------
      immediate (CURRENT) block cleanou 502 2.1 0.7
      index fast full scans (full) 163 0.7 0.2
      index fetch by key 380,673 1,606.2 553.3
      index scans kdiixs1 86,708 365.9 126.0
      leaf node 90-10 splits 22 0.1 0.0
      leaf node splits 64 0.3 0.1
      logons cumulative 25 0.1 0.0
      messages received 806 3.4 1.2
      messages sent 806 3.4 1.2
      no buffer to keep pinned count 0 0.0 0.0
      no work - consistent read gets 779,017 3,287.0 1,132.3
      opened cursors cumulative 7,669 32.4 11.2
      parse count (failures) 2 0.0 0.0
      parse count (hard) 2,060 8.7 3.0
      parse count (total) 7,091 29.9 10.3
      parse time cpu 434 1.8 0.6
      parse time elapsed 430 1.8 0.6
      physical reads 261,506 1,103.4 380.1
      physical reads direct 3,529 14.9 5.1
      physical writes 3,935 16.6 5.7
      physical writes direct 3,529 14.9 5.1
      physical writes non checkpoint 3,839 16.2 5.6
      pinned buffers inspected 0 0.0 0.0
      prefetched blocks 175,966 742.5 255.8
      prefetched blocks aged out before 0 0.0 0.0
      process last non-idle time 270 1.1 0.4
      recovery blocks read 0 0.0 0.0
      recursive calls 20,832 87.9 30.3
      recursive cpu usage 296 1.3 0.4
      redo blocks written 11,309 47.7 16.4
      redo buffer allocation retries 0 0.0 0.0
      redo entries 12,097 51.0 17.6
      redo log space requests 0 0.0 0.0
      redo log space wait time 0 0.0 0.0
      redo ordering marks 1 0.0 0.0
      redo size 5,480,820 23,125.8 7,966.3
      redo synch time 30 0.1 0.0
      redo synch writes 689 2.9 1.0
      redo wastage 160,348 676.6 233.1
      redo write time 29 0.1 0.0
      redo writer latching time 0 0.0 0.0
      redo writes 732 3.1 1.1
      rollback changes - undo records a 2 0.0 0.0
      rollbacks only - consistent read 10 0.0 0.0
      rows fetched via callback 235,269 992.7 342.0
      session connect time 0 0.0 0.0
      session logical reads 1,433,886 6,050.2 2,084.1
      session pga memory 45,074,044 190,185.8 65,514.6
      session pga memory max 49,946,024 210,742.7 72,596.0
      session uga memory max 3,455,708 14,581.1 5,022.8
      shared hash latch upgrades - no w 54,598 230.4 79.4
      shared hash latch upgrades - wait 0 0.0 0.0
      sorts (disk) 13 0.1 0.0
      sorts (memory) 1,639 6.9 2.4
      sorts (rows) 780,425 3,292.9 1,134.3
      summed dirty queue length 239 1.0 0.4
      Instance Activity Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3

      Statistic Total per Second per Trans
      --------------------------------- ------------------ -------------- ------------
      switch current to new buffer 172 0.7 0.3
      table fetch by rowid 612,799 2,585.7 890.7
      table fetch continued row 626 2.6 0.9
      table scan blocks gotten 459,367 1,938.3 667.7
      table scan rows gotten 18,523,828 78,159.6 26,924.2
      table scans (long tables) 128 0.5 0.2
      table scans (short tables) 1,655 7.0 2.4
      transaction rollbacks 1 0.0 0.0
      transaction tables consistent rea 0 0.0 0.0
      transaction tables consistent rea 0 0.0 0.0
      user calls 44,888 189.4 65.2
      user commits 686 2.9 1.0
      user rollbacks 2 0.0 0.0
      workarea executions - multipass 0 0.0 0.0
      workarea executions - onepass 24 0.1 0.0
      workarea executions - optimal 2,433 10.3 3.5
      write clones created in foregroun 0 0.0 0.0
      -------------------------------------------------------------
      Tablespace IO Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      ->ordered by IOs (Reads + Writes) desc

      Tablespace
      ------------------------------
      Av Av Av Av Buffer Av Buf
      Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
      -------------- ------- ------ ------- ------------ -------- ---------- ------
      DIAGNOTECH
      61,945 261 0.7 1.9 64 0 0 0.0
      IP
      10,753 45 1.2 9.5 23 0 0 0.0
      SYSTEM
      5,668 24 1.2 3.2 22 0 0 0.0
      OTS1
      2,456 10 1.5 7.8 19 0 0 0.0
      TEMP
      1,429 6 0.6 2.5 739 3 0 0.0
      INVENTORY
      478 2 3.5 1.3 49 0 0 0.0
      UNDOTBS1
      0 0 0.0 229 1 0 0.0
      INDX
      15 0 2.0 1.0 0 0 0 0.0
      PAYROLL
      1 0 20.0 4.0 0 0 0 0.0
      -------------------------------------------------------------
      File IO Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      ->ordered by Tablespace, File

      Tablespace Filename
      ------------------------ ----------------------------------------------------
      Av Av Av Av Buffer Av Buf
      Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
      -------------- ------- ------ ------- ------------ -------- ---------- ------
      DIAGNOTECH F:\ORACLE\ORADATA\NEOSOFT\DIAGNOTECH1.ORA
      61,945 261 0.7 1.9 64 0 0

      INDX F:\ORACLE\ORADATA\NEOSOFT\INDX01.DBF
      15 0 2.0 1.0 0 0 0

      INVENTORY F:\ORACLE\ORADATA\NEOSOFT\INVENTORY1.ORA
      478 2 3.5 1.3 49 0 0

      IP F:\ORACLE\ORADATA\NEOSOFT\IP1.ORA
      10,753 45 1.2 9.5 23 0 0

      OTS1 F:\ORACLE\ORADATA\NEOSOFT\OTS11.ORA
      2,456 10 1.5 7.8 19 0 0

      PAYROLL F:\ORACLE\ORADATA\NEOSOFT\PAYROLL1.ORA
      1 0 20.0 4.0 0 0 0

      SYSTEM F:\ORACLE\ORADATA\NEOSOFT\SYSTEM01.DBF
      5,668 24 1.2 3.2 22 0 0

      TEMP F:\ORACLE\ORADATA\NEOSOFT\TEMP01.DBF
      1,429 6 0.6 2.5 739 3 0

      UNDOTBS1 F:\ORACLE\ORADATA\NEOSOFT\UNDOTBS01.DBF
      0 0 229 1 0

      -------------------------------------------------------------
      Buffer Pool Statistics for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> Standard block size Pools D: default, K: keep, R: recycle
      -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

      Free Write Buffer
      Number of Cache Buffer Physical Physical Buffer Complete Busy
      P Buffers Hit % Gets Reads Writes Waits Waits Waits
      --- ---------- ----- ----------- ----------- ---------- ------- -------- ------
      D 44,044 82.0 1,432,475 257,328 406 0 0 0
      -------------------------------------------------------------

      Instance Recovery Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> B: Begin snapshot, E: End snapshot

      Targt Estd Log File Log Ckpt Log Ckpt
      MTTR MTTR Recovery Actual Target Size Timeout Interval
      (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
      - ----- ----- ---------- ---------- ---------- ---------- ---------- ----------
      B 87 19 3628 65684 64450 184320 64450
      E 87 20 4063 69805 69805 184320 69805
      -------------------------------------------------------------

      Buffer Pool Advisory for DB: NEOSOFT Instance: neosoft End Snap: 3
      -> Only rows with estimated physical reads >0 are displayed
      -> ordered by Block Size, Buffers For Estimate (default block size first)

      Size for Size Buffers for Est Physical Estimated
      P Estimate (M) Factr Estimate Read Factor Physical Reads
      --- ------------ ----- ---------------- ------------- ------------------
      D 32 .1 4,004 67.25 19,260,129,810
      D 64 .2 8,008 42.95 12,300,910,895
      D 96 .3 12,012 32.00 9,165,118,680
      D 128 .4 16,016 25.62 7,338,010,607
      D 160 .5 20,020 17.54 5,022,366,182
      D 192 .5 24,024 11.74 3,361,228,965
      D 224 .6 28,028 7.05 2,020,169,694
      D 256 .7 32,032 3.94 1,127,930,012
      D 288 .8 36,036 2.32 663,835,521
      D 320 .9 40,040 1.44 413,674,540
      D 352 1.0 44,044 1.00 286,377,525
      D 384 1.1 48,048 0.75 215,831,108
      D 416 1.2 52,052 0.60 171,670,371
      D 448 1.3 56,056 0.51 145,708,247
      D 480 1.4 60,060 0.45 128,439,943
      D 512 1.5 64,064 0.40 114,053,332
      D 544 1.5 68,068 0.37 104,584,674
      D 576 1.6 72,072 0.34 96,792,591
      D 608 1.7 76,076 0.32 92,596,531
      D 640 1.8 80,080 0.31 89,929,979
      -------------------------------------------------------------
      PGA Aggr Target Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> B: Begin snap E: End snap (rows dentified with B or E contain data
      which is absolute i.e. not diffed over the interval)
      -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
      -> Auto PGA Target - actual workarea memory target
      -> W/A PGA Used - amount of memory used for all Workareas (manual + auto)
      -> %PGA W/A Mem - percentage of PGA memory allocated to workareas
      -> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
      -> %Man W/A Mem - percentage of workarea memory under manual control

      PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
      --------------- ---------------- -------------------------
      88.2 213 29

      %PGA %Auto %Man
      PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
      Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
      - --------- --------- ---------- ---------- ------ ------ ------ ----------
      B 24 7 24.4 0.4 1.6 .0 100.0 1,228
      E 24 8 23.8 0.0 .0 .0 .0 1,228
      -------------------------------------------------------------

      PGA Aggr Target Histogram for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> Optimal Executions are purely in-memory operations

      Low High
      Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
      ------- ------- -------------- ------------- ------------ ------------
      8K 16K 2,000 2,000 0 0
      16K 32K 55 55 0 0
      32K 64K 28 28 0 0
      64K 128K 50 50 0 0
      128K 256K 110 110 0 0
      256K 512K 26 26 0 0
      512K 1024K 176 158 18 0
      1M 2M 8 4 4 0
      4M 8M 2 0 2 0
      -------------------------------------------------------------

      PGA Memory Advisory for DB: NEOSOFT Instance: neosoft End Snap: 3
      -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
      where Estd PGA Overalloc Count is 0

      Estd Extra Estd PGA Estd PGA
      PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
      Est (MB) Factr Processed Written to Disk Hit % Count
      ---------- ------- ---------------- ---------------- -------- ----------
      12 0.5 417.1 207.2 67.0 1
      18 0.8 417.1 89.3 82.0 0
      24 1.0 417.1 46.4 90.0 0
      29 1.2 417.1 46.4 90.0 0
      34 1.4 417.1 46.4 90.0 0
      38 1.6 417.1 13.2 97.0 0
      43 1.8 417.1 11.1 97.0 0
      48 2.0 417.1 11.1 97.0 0
      72 3.0 417.1 11.1 97.0 0
      96 4.0 417.1 11.1 97.0 0
      144 6.0 417.1 0.0 100.0 0
      192 8.0 417.1 0.0 100.0 0
      -------------------------------------------------------------
      Rollback Segment Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      ->A high value for "Pct Waits" suggests more rollback segments may be required
      ->RBS stats may not be accurate between begin and end snaps when using Auto Undo
      managment, as RBS may be dynamically created and dropped as needed

      Trans Table Pct Undo Bytes
      RBS No Gets Waits Written Wraps Shrinks Extends
      ------ -------------- ------- --------------- -------- -------- --------
      0 15.0 0.00 0 0 0 0
      1 412.0 0.00 831,990 1 0 1
      2 48.0 0.00 61,500 0 0 0
      3 87.0 0.00 132,404 0 0 0
      4 79.0 0.00 66,626 0 0 0
      5 71.0 0.00 91,284 0 0 0
      6 40.0 0.00 15,474 0 0 0
      7 660.0 0.00 547,536 1 0 1
      8 168.0 0.00 21,762 0 0 0
      9 86.0 0.00 10,108 0 0 0
      10 295.0 0.00 156,468 0 0 0
      -------------------------------------------------------------
      Rollback Segment Storage for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      ->Optimal Size should be larger than Avg Active

      RBS No Segment Size Avg Active Optimal Size Maximum Size
      ------ --------------- --------------- --------------- ---------------
      0 385,024 0 385,024
      1 9,625,600 806,999 16,900,096
      2 5,365,760 711,094 8,511,488
      3 5,365,760 664,381 9,560,064
      4 5,365,760 763,735 8,511,488
      5 5,365,760 672,648 9,625,600
      6 5,365,760 677,906 9,560,064
      7 6,414,336 647,093 8,511,488
      8 5,365,760 628,652 10,608,640
      9 5,365,760 769,325 9,560,064
      10 5,365,760 622,015 9,560,064
      -------------------------------------------------------------
      Undo Segment Summary for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> Undo segment block stats:
      -> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
      -> eS - expired Stolen, eR - expired Released, eU - expired reUsed

      Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/
      TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU
      ---- -------------- ---------- -------- ---------- -------- ------ -------------
      1 695 1,032,965 12 1 0 0 0/0/0/0/0/0
      -------------------------------------------------------------


      Undo Segment Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> ordered by Time desc

      Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/
      End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU
      ------------ ------------ -------- ------- -------- ------- ------ -------------
      16-May 10:22 695 ######## 12 1 0 0 0/0/0/0/0/0
      -------------------------------------------------------------
      Latch Activity for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
      willing-to-wait latch get requests
      ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
      ->"Pct Misses" for both should be very close to 0.0

      Pct Avg Wait Pct
      Get Get Slps Time NoWait NoWait
      Latch Requests Miss /Miss (s) Requests Miss
      ------------------------ -------------- ------ ------ ------ ------------ ------
      Consistent RBA 732 0.0 0 0
      FAL request queue 1 0.0 0 0
      FIB s.o chain latch 12 0.0 0 0
      FOB s.o list latch 27 0.0 0 0
      SQL memory manager latch 2 0.0 0 79 0.0
      SQL memory manager worka 7,245 0.0 0 0
      active checkpoint queue 169 0.0 0 0
      archive control 10 0.0 0 0
      archive process latch 7 0.0 0 0
      cache buffer handles 12,078 0.0 0 0
      cache buffers chains 2,602,999 0.0 0.0 0 506,781 0.0
      cache buffers lru chain 858 0.0 0 615,593 0.0
      channel handle pool latc 37 0.0 0 0
      channel operations paren 265 0.0 0 0
      checkpoint queue latch 30,545 0.0 0 893 0.0
      child cursor hash table 16,079 0.0 0 0
      dml lock allocation 2,674 0.0 0 0
      dummy allocation 43 0.0 0 0
      enqueue hash chains 9,313 0.0 0 0
      enqueues 9,405 0.0 0 0
      event group latch 5 0.0 0 0
      hash table column usage 0 0 55,565 0.0
      hash table modification 1 0.0 0 0
      kmcptab latch 78 0.0 0 0
      kmcpvec latch 0 0 78 0.0
      ktm global data 13 0.0 0 0
      lgwr LWN SCN 742 0.0 0 0
      library cache 193,957 0.0 0.0 0 6,959 0.4
      library cache load lock 110 0.0 0 0
      library cache pin 63,420 0.0 0 0
      library cache pin alloca 43,208 0.0 0 0
      list of block allocation 49 0.0 0 0
      loader state object free 34 0.0 0 0
      message pool operations 26 0.0 0 0
      messages 2,866 0.0 0 0
      mostly latch-free SCN 742 0.0 0 0
      multiblock read objects 164,572 0.0 0.0 0 0
      ncodef allocation latch 3 0.0 0 0
      object stats modificatio 1,669 0.0 0 0
      post/wait queue 1,029 0.0 0 683 0.0
      process allocation 11 0.0 0 5 0.0
      process group creation 11 0.0 0 0
      redo allocation 13,617 0.0 0.0 0 0
      redo copy 0 0 12,145 0.0
      redo writing 2,533 0.0 0 0
      row cache enqueue latch 215,027 0.0 0.0 0 0
      row cache objects 218,418 0.0 0.0 0 472 0.0
      sequence cache 99 0.0 0 0
      session allocation 8,683 0.0 0 0
      session idle bit 94,091 0.0 0 0
      Latch Activity for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
      willing-to-wait latch get requests
      ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
      ->"Pct Misses" for both should be very close to 0.0

      Pct Avg Wait Pct
      Get Get Slps Time NoWait NoWait
      Latch Requests Miss /Miss (s) Requests Miss
      ------------------------ -------------- ------ ------ ------ ------------ ------
      session switching 3 0.0 0 0
      session timer 80 0.0 0 0
      shared pool 160,825 0.0 0.0 0 0
      sim partition latch 0 0 35 0.0
      simulator hash latch 63,337 0.0 0 0
      simulator lru latch 4,437 0.0 0 16,447 0.0
      sort extent pool 129 0.0 0 0
      transaction allocation 47 0.0 0 0
      transaction branch alloc 3 0.0 0 0
      undo global data 3,091 0.0 0 0
      user lock 114 0.0 0 0
      virtual circuit buffers 540,157 0.4 0.0 0 0
      virtual circuit queues 256,196 0.0 0.0 0 0
      virtual circuits 89,002 0.0 0 0
      -------------------------------------------------------------
      Top 5 Logical Reads per Segment for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> End Segment Logical Reads Threshold: 10000

      Subobject Obj. Logical
      Owner Tablespace Object Name Name Type Reads %Total
      ---------- ---------- -------------------- ---------- ----- ------------ -------
      DIAGNOTECH DIAGNOTECH REQUISITIONS TABLE 162,064 11.66
      SYS SYSTEM I_OBJ1 INDEX 115,328 8.30
      SYS SYSTEM OBJ$ TABLE 113,328 8.15
      SYS SYSTEM I_IND1 INDEX 109,584 7.88
      IP IP BILLPHARMACY TABLE 89,328 6.43
      -------------------------------------------------------------


      Top 5 Physical Reads per Segment for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> End Segment Physical Reads Threshold: 1000

      Subobject Obj. Physical
      Owner Tablespace Object Name Name Type Reads %Total
      ---------- ---------- -------------------- ---------- ----- ------------ -------
      IP IP BILLPHARMACY TABLE 86,212 34.43
      DIAGNOTECH DIAGNOTECH REQUISITIONS TABLE 79,487 31.74
      DIAGNOTECH DIAGNOTECH TRACK TABLE 33,193 13.25
      IP SYSTEM TRACK TABLE 17,926 7.16
      OTS1 OTS1 MONEYRECIEPT TABLE 10,036 4.01
      -------------------------------------------------------------


      Top 5 Row Lock Waits per Segment for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      -> End Segment Row Lock Waits Threshold: 100

      Row
      Subobject Obj. Lock
      Owner Tablespace Object Name Name Type Waits %Total
      ---------- ---------- -------------------- ---------- ----- ------------ -------
      IP IP BILLID4064 INDEX 1 100.00
      -------------------------------------------------------------
      Dictionary Cache Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      ->"Pct Misses" should be very low (< 2% in most cases)
      ->"Cache Usage" is the number of cache entries being used
      ->"Pct SGA" is the ratio of usage to allocated size for that cache

      Get Pct Scan Pct Mod Final
      Cache Requests Miss Reqs Miss Reqs Usage
      ------------------------- ------------ ------ ------- ----- -------- ----------
      dc_histogram_defs 22,135 1.1 0 0 3,348
      dc_object_ids 26,251 0.0 0 0 625
      dc_objects 4,895 0.1 0 15 960
      dc_profiles 21 0.0 0 0 1
      dc_rollback_segments 273 0.0 0 0 12
      dc_segments 11,239 0.1 0 0 604
      dc_sequences 3 33.3 0 3 6
      dc_tablespaces 19,449 0.0 0 0 5
      dc_user_grants 2,814 0.0 0 0 17
      dc_usernames 886 0.0 0 0 15
      dc_users 22,542 0.0 0 0 20
      -------------------------------------------------------------


      Library Cache Activity for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
      ->"Pct Misses" should be very low

      Get Pct Pin Pct Invali-
      Namespace Requests Miss Requests Miss Reloads dations
      --------------- ------------ ------ -------------- ------ ---------- --------
      BODY 445 0.0 445 0.0 0 0
      CLUSTER 397 0.0 121 0.0 0 0
      SQL AREA 7,041 28.0 24,030 16.9 55 23
      TABLE/PROCEDURE 8,324 0.0 15,082 0.6 18 0
      TRIGGER 953 0.0 953 0.0 0 0
      -------------------------------------------------------------
      Shared Pool Advisory for DB: NEOSOFT Instance: neosoft End Snap: 3
      -> Note there is often a 1:Many correlation between a single logical object
      in the Library Cache, and the physical number of memory objects associated
      with it. Therefore comparing the number of Lib Cache objects (e.g. in
      v$librarycache), with the number of Lib Cache Memory Objects is invalid

      Estd
      Shared Pool SP Estd Estd Estd Lib LC Time
      Size for Size Lib Cache Lib Cache Cache Time Saved Estd Lib Cache
      Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem Obj Hits
      ----------- ----- ---------- ------------ ------------ ------- ---------------
      104 .5 97 21,986 47,321 1.0 22,910,514
      128 .6 120 27,657 47,381 1.0 22,988,994
      152 .8 143 32,965 47,422 1.0 23,048,924
      176 .9 166 38,101 47,451 1.0 23,094,145
      200 1.0 189 42,541 47,474 1.0 23,126,910
      224 1.1 212 47,258 47,492 1.0 23,155,651
      248 1.2 235 52,101 47,508 1.0 23,180,673
      272 1.4 258 56,800 47,521 1.0 23,202,200
      296 1.5 283 61,512 47,532 1.0 23,220,564
      320 1.6 306 65,892 47,540 1.0 23,235,720
      344 1.7 329 70,532 47,547 1.0 23,246,826
      368 1.8 352 75,208 47,553 1.0 23,257,346
      392 2.0 375 81,494 47,559 1.0 23,268,801
      416 2.1 411 88,685 47,565 1.0 23,279,344
      -------------------------------------------------------------
      SGA Memory Summary for DB: NEOSOFT Instance: neosoft Snaps: 1 -3

      SGA regions Size in Bytes
      ------------------------------ ----------------
      Database Buffers 369,098,752
      Fixed Size 455,784
      Redo Buffers 1,191,936
      Variable Size 436,207,616
      ----------------
      sum 806,954,088
      -------------------------------------------------------------


      SGA breakdown difference for DB: NEOSOFT Instance: neosoft Snaps: 1 -3

      Pool Name Begin value End value % Diff
      ------ ------------------------------ ---------------- ---------------- -------
      java free memory 83,886,080 83,886,080 0.00
      large free memory 63,891,136 64,350,992 0.72
      large session heap 19,994,944 19,535,088 -2.30
      shared 1M buffer 2,098,176 2,098,176 0.00
      shared Checkpoint queue 1,129,216 1,129,216 0.00
      shared DML lock 1,028,764 1,028,764 0.00
      shared FileOpenBlock 6,564,072 6,564,072 0.00
      shared KGK heap 3,756 3,756 0.00
      shared KGLS heap 2,584,540 2,431,584 -5.92
      shared KQR M PO 2,844,672 2,768,896 -2.66
      shared KQR S PO 249,880 250,652 0.31
      shared KQR S SO 7,936 7,936 0.00
      shared KSXR pending messages que 841,036 841,036 0.00
      shared KSXR receive buffers 1,033,000 1,033,000 0.00
      shared MTTR advisory 59,288 59,288 0.00
      shared PL/SQL DIANA 3,179,412 3,015,708 -5.15
      shared PL/SQL MPCODE 388,924 241,096 -38.01
      shared PLS non-lib hp 3,688 3,688 0.00
      shared VIRTUAL CIRCUITS 2,564,620 2,564,620 0.00
      shared db_handles 1,080,000 1,080,000 0.00
      shared dictionary cache 2,137,216 2,137,216 0.00
      shared enqueue 1,676,824 1,676,824 0.00
      shared event statistics per sess 18,675,020 18,675,020 0.00
      shared fixed allocation callback 388 388 0.00
      shared free memory 14,167,016 15,470,156 9.20
      shared joxs heap init 4,220 4,220 0.00
      shared ktlbk state objects 778,960 778,960 0.00
      shared library cache 43,962,768 43,051,344 -2.07
      shared message pool freequeue 665,792 665,792 0.00
      shared miscellaneous 41,742,904 42,080,748 0.81
      shared parameters 3,224 9,272 187.59
      shared processes 1,500,000 1,500,000 0.00
      shared sessions 3,998,480 3,998,480 0.00
      shared sim memory hea 195,212 195,212 0.00
      shared sql area 111,345,832 111,177,960 -0.15
      shared table definiti 16,576 15,240 -8.06
      shared transaction 1,852,092 1,852,092 0.00
      shared trigger defini 26,236 24,148 -7.96
      shared trigger inform 592 592 0.00
      shared trigger source 304 304 0.00
      buffer_cache 369,098,752 369,098,752 0.00
      fixed_sga 455,784 455,784 0.00
      log_buffer 1,180,672 1,180,672 0.00
      -------------------------------------------------------------
      init.ora Parameters for DB: NEOSOFT Instance: neosoft Snaps: 1 -3

      End value
      Parameter Name Begin value (if different)
      ----------------------------- --------------------------------- --------------
      background_dump_dest F:\oracle\admin\neosoft\bdump
      compatible 9.2.0.0.0
      control_files F:\oracle\oradata\neosoft\control
      core_dump_dest F:\oracle\admin\neosoft\cdump
      db_block_size 8192
      db_cache_size 369098752
      db_domain
      db_file_multiblock_read_count 16
      db_name neosoft
      dispatchers (PROTOCOL=TCP)
      fast_start_mttr_target 300
      hash_join_enabled TRUE
      instance_name neosoft
      java_pool_size 83886080
      large_pool_size 83886080
      open_cursors 300
      pga_aggregate_target 25165824
      processes 1500
      query_rewrite_enabled FALSE
      remote_login_passwordfile EXCLUSIVE
      shared_pool_size 209715200
      sort_area_size 524288
      star_transformation_enabled FALSE
      timed_statistics TRUE
      undo_management AUTO
      undo_retention 10800
      undo_tablespace UNDOTBS1
      user_dump_dest F:\oracle\admin\neosoft\udump
      -------------------------------------------------------------

      End of Report
        • 1. Re: performance(statspack) related problem
          ViragSharma
          1) Snap shot for 3.95 (mins) is not enough to make any conclusion about DB
          2) Report looks very unformatted , please upload file on some side and give link.
          so It should be more readable
          3) Advisory suggest , if you double, Buffer Pool physical I/O will go down significantly. Check advisory section for more details.
          • 2. Re: performance(statspack) related problem
            584650
            The snapshot period is 3 minutes, is this long enough? Rather than posting a lengthy unreadable report on the forum with possible private information on what it looks pharmaceuticals and health care try http://www.txmemsys.com/statspack-reg.htm
            • 3. Re: performance(statspack) related problem
              Jonathan Lewis
              This is a follow-up to: performance related question.... and you need only read my response to that thread to see that this report supports my comments.

              Your are doing a lot of "db file scattered read" - yet the SQL sections suggest that this is an OLTP system.

              7,300 blocks read from disk for:
              select discount,discamount from billpharmacy where patientid='PAT09037'

              4,500 blocks read from disk for:
              Select * from track where tablenm = 'FRMREPORTING' and primkeyid = 'R208970TS000002' order by trackdate,tracktime

              4,400 blocks read from disk for
              Update Requisitions Set Collected = 0 Where TRIM(ReqID) ='R209878'


              These all look like high-precision statements that are doing too much work because of indexing problems. (The last one looks like it needs a function-based index, the others look as if they need ordinary indexes on the 'id' column).

              Your statspack report shows that the highest component of time being (a) multi-block disc reads - which show a very slow average time, and (b) CPU - which often appears as a side effect of excessive tablescans and index fast full scans. The three queries shown are examples of where this time is going.


              Regards
              Jonathan Lewis
              http://jonathanlewis.wordpress.com
              http://www.jlcomp.demon.co.uk
              • 4. Re: performance(statspack) related problem
                user00726
                i can't tune the query ,all these queries are running behind the applications......

                sp suggest me how to increase the db performance......

                we are facing DB perfomance related problem generally aroung 10-12 am.........
                • 5. Re: performance(statspack) related problem
                  Aman....
                  Lokesh,
                  Please understand what JL has mentioned.There is nothing you can do in the database for example , setting makedb_fast=true sort of parameters in the parameter file when the base, the query itself is wrong. JL has shown you 3 queries hitting your db and generating scattered read wait event.You have to go for the queries before doing anything else in the db.And no matter what you do in the db, it wont last for long in terms of giving you the benefit as the query itself is the issue.
                  Check with the developer who wrote the queries and ask them to do some thing about it.Try to create tehmentioned indexes on the columns as he has suggestted.
                  HTH
                  Aman....
                  • 6. Re: performance(statspack) related problem
                    Jonathan Lewis
                    You may not be able to change the text of the query, but that doesn't stop you from creating indexes in the database - at least on a test system to prove the point that there are critical indexes missing.

                    Having said that, it is possible that indexes on the relevant columns exist, but the environment has been set with some "national language" (NLS) settings that make the existing indexes unusable. (In which case you can still solve the obvious performance issues by adding some function-based indexes).

                    What do you get when you use dbms_xplan to generate an execution plan on the three statements I listed ? Please read the FAQ (near top right) to see how to use the "pre" tag to make the output readable when you paste it into the forum.

                    Regards
                    Jonathan Lewis
                    http://jonathanlewis.wordpress.com
                    http://www.jlcomp.demon.co.uk
                    • 7. Re: performance(statspack) related problem
                      user00726
                      Thankx yes both of you are right......

                      select owner,table_name from all_tables where taSQL> Select * from diagnotech.track where tablenm = 'FRMREPORTING' and pri
                      = 'R208970TS000002' order by trackdate,tracktime;
                      ♀TRACKDATE TRACKTIME USERNAME
                      --------- --------- --------------------------------------------------
                      TERMINALID
                      --------------------------------------------------
                      TABLENM
                      --------------------------------------------------
                      PRIMKEYID EDITFLAG
                      -------------------------------------------------- ----------
                      15-MAY-08 01-MAY-08 VIVEK
                      LAB-MICRO
                      FRMREPORTING
                      R208970TS000002 0


                      Execution Plan
                      ----------------------------------------------------------
                      0
                      SELECT STATEMENT Optimizer=CHOOSE (Cost=136 Card=1 Bytes=61)

                      1 0
                      SORT (ORDER BY) (Cost=136 Card=1 Bytes=61)

                      2 1
                      TABLE ACCESS (FULL) OF 'TRACK' (Cost=134 Card=1 Bytes=61)



                      SQL> select discount,discamount from billpharmacy where patientid='PAT0903
                      2
                      SQL> select discount,discamount from ip.billpharmacy where patientid='PAT0


                      no rows selected


                      Execution Plan
                      ----------------------------------------------------------
                      0
                      SELECT STATEMENT Optimizer=CHOOSE (Cost=547 Card=59 Bytes=767)

                      1 0
                      TABLE ACCESS (FULL) OF 'BILLPHARMACY' (Cost=547 Card=59 Bytes=767)

                      ble_name='TRACK';
                      • 8. Re: performance(statspack) related problem
                        Jonathan Lewis
                        Please note what I said in my previous post:
                        What do you get when you use dbms_xplan to generate an execution
                        plan on the three statements I listed ? Please read the FAQ (near top right) to
                        see how to use the "pre" tag to make the output readable when you
                        paste it into the forum.
                        See this note on my blog about using dbms_xplan - and why it's so important in this particular case.

                        Regards
                        Jonathan Lewis
                        http://jonathanlewis.wordpress.com
                        http://www.jlcomp.demon.co.uk
                        • 9. Re: performance(statspack) related problem
                          Charles Hooper
                          It is surprising how much easier it is to read the statspack report with a fixed width font. You mentioned that you used Statspack Analyzer. Out of curiosity, what did it tell you?

                          With the suggestions that Jonathan has provided to you regarding indexes, note the SQL statements that look like the following in the section of the statspack report titled "SQL ordered by Reads":
                          select discount,discamount from billpharmacy where patientid=
                          sELECT * FROM BillPharmacy WHERE BillPharmacy.pATIENtid=
                          delete from billpharmacy where patientid=
                          The duration and number of blocks read for those types of SQL statements with PATIENTID in the WHERE clause likely contributing to the db file scattered read waits:
                          Seconds Blocks Read
                          0.51    7,302
                          0.41    7,302
                          0.51    7,302
                          0.69    7,300
                          0.77    7,299
                          0.43    7,298
                          0.58    7,298
                          0.97    7,061
                          -------------
                          4.87 seconds (possibly more) of the 38 seconds total for db file scattered read waits
                          This suggests that an index like this might help:
                          CREATE INDEX IND_BP_PATIENTID ON BILLPHARMACY(PATIENTID);
                          There is another possibility to recover at least 1.08 seconds of the db file scattered read waits with an index like this:
                          CREATE INDEX IND_PR_PATIENTID ON PATIENTSREGISTRATION(REGDT);
                          When was the last time DBMS_STATS was used with the CASCADE option to gather table and index stats?

                          At the end of the statspack run there were 442 sessions. You are allocating (a minimum of) roughly 25MB to the pga_aggregate_target, which is shared among the 442 sessions. This may be too small of a value, but did not appear to cause too many problems in this time interval.

                          The java_pool_size and large_pool_size are each set to roughly 83MB - is there a reason why these are set to 83MB? If not, you may be able to decrease those values to permit more memory to be used for the buffer cache or the pga aggregate target.

                          Charles Hooper
                          IT Manager/Oracle DBA
                          K&M Machine-Fabricating, Inc.
                          • 10. Re: performance(statspack) related problem
                            user00726
                            How did you calculated all these ratios............


                            and on what basis you one should have to find out which query causing the problem....

                            i also wanted to learn how to read the statspack.........
                            • 11. Re: performance(statspack) related problem
                              Aman....
                              How did you calculated all these ratios............
                              I didnt understand which ratios?These are all the timins and reads from your report only.
                              what basis you one should have to find out which query causing the problem....
                              Charles will give a better answer than me for this.
                              Aman....
                              • 12. Re: performance(statspack) related problem
                                Charles Hooper
                                How did you calculated all these ratios............
                                I looked through the formatted output of the Statspack report, specifically the setion "SQL ordered by reads for DB" and found these SQL statements which are performing a large number of physical reads. Most of the SQL statements have similar WHERE clauses, except for the constants to the right of the = :
                                Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
                                --------------- ------------ -------------- ------ -------- --------- ----------
                                          8,084            4        2,021.0    3.1     0.14      1.08 4198744706
                                Module: Out Patient 2.1.41.exe
                                Select Max(BillNo) as Bno From PatientsRegistration Where regdt
                                = to_date('01/04/2008','dd/mm/yyyy') and regdt <= to_date('31/0
                                3/2009','dd/mm/yyyy')

                                          7,302            1        7,302.0    2.8     0.13      0.51 2116660478
                                Module: In Patient 2.1.157.exe
                                select discount,discamount from billpharmacy where patientid='PAT09212'

                                          7,302            1        7,302.0    2.8     0.06      0.41 3612969128
                                Module: In Patient 2.1.152.exe
                                select discount,discamount from billpharmacy where patientid='PAT09037'

                                          7,302            1        7,302.0    2.8     0.19      0.51 3981413559
                                Module: In Patient 2.1.152.exe
                                sELECT * FROM BillPharmacy WHERE BillPharmacy.pATIENtid= 'PAT09037' order by to_number((issueno))

                                          7,300            1        7,300.0    2.8     0.14      0.69  136284863
                                Module: In Patient 2.1.152.exe
                                delete from billpharmacy where patientid='PAT09037'

                                          7,299            1        7,299.0    2.8     0.20      0.77 2137420337
                                Module: In Patient 2.1.152.exe
                                delete from billpharmacy where patientid='PAT09154'

                                          7,298            1        7,298.0    2.8     0.13      0.43 1007690124
                                Module: In Patient 2.1.152.exe
                                select discount,discamount from billpharmacy where patientid='PAT08301'

                                          7,298            1        7,298.0    2.8     0.13      0.58 3087447381
                                Module: In Patient 2.1.152.exe
                                select discount,discamount from billpharmacy where patientid='PAT09154'

                                          7,061            1        7,061.0    2.7     0.13      0.97 2993688253
                                Module: In Patient 2.1.152.exe
                                select discount,discamount from billpharmacy where patientid='PAT09146'
                                and on what basis you one should have to find out
                                which query causing the problem....

                                i also wanted to learn how to read the
                                statspack.........
                                I personally do not like Statspack reports, but there are several very good sources for understanding Statspack reports:
                                http://jonathanlewis.wordpress.com/2006/11/29/analysing-statspack-pt1/
                                http://jonathanlewis.wordpress.com/statspack-examples/
                                Metalink Note:232443.1 (How to Identify Resource Intensive SQL for Tuning)
                                Metalink Note:390374.1 (Oracle Performance Diagnostic Guide (OPDG))
                                Metalink Note:228913.1 (Systemwide Tuning using STATSPACK Reports)

                                Charles Hooper
                                IT Manager/Oracle DBA
                                K&M Machine-Fabricating, Inc.
                                • 13. Re: performance(statspack) related problem
                                  user00726
                                  Thankx for a link and a gud explanation.....


                                  as you suggested me to create an index on Billpharamcy table....
                                  i have checked out that index has already been there........
                                  SQL> select table_name,column_name,table_owner from dba_ind_columns where table_
                                  name='BILLPHARMACY';
                                  TABLE_NAME            COLUMN_NAME         TABLE_OWNER

                                  BILLPHARMACY          BILLID                         IP
                                  BILLPHARMACY          PATIENTID                   IP
                                  • 14. Re: performance(statspack) related problem
                                    Aman....
                                    Are the stats for the table and indexes are refreshed?
                                    Aman....
                                    1 2 3 Previous Next