4 Replies Latest reply: May 14, 2008 3:42 AM by Jonathan Lewis RSS

    performance related question....

    user00726
      can anybody tell me my database performace is good or not....
      if not then what i have to do to increase the performance........

      as many users are complaining thaT they are getting data very late...
      pls suggest me.....

      SQL> select (1- (sum(decode(a.name,'physical reads',value,0)))/
      2 (sum(decode(a.name,'db block gets',value,0)) +
      3 sum(decode(a.name,'consistent gets',value,0)))) * 100 pct
      4 from v$sysstat a;

      PCT
      ----------
      78.5615887

      SQL> select sum(pins) "executions",sum(reloads) "cache Misses",sum(reloads)/sum(
      pins)*100 "Ratio" from v$librarycache;

      executions cache Misses Ratio
      ---------- ------------ ----------
      24234139 143361 .591566302

      SQL>
      SQL>
      SQL> select (sum(getmisses)/sum(gets)) * 100 "Hit Ratio" from v$rowcache;

      Hit Ratio
      ----------
      .315087345SQL> select disk_reads,sql_text from v$sqlarea where disk_reads>10000 and rownum
      < 11 order by disk_reads desc;

      DISK_READS
      ----------
      SQL_TEXT
      --------------------------------------------------------------------------------

      71436
      select * from DoctorVisit where Date_r >= to_date('04/05/2008','dd/mm/yyyy') and

      Date_r <= to_date('14/05/2008','dd/mm/yyyy')

      41415
      Select * From Requisitions Where ReqDt>=to_date('11/05/2008','fmDD/MM/YYYY') AND

      PATIENTTYPE = 1 And ReqDt<=to_date('13/05/2008','fmDD/MM/YYYY') Order by ReqDT,

      Reqtm


      DISK_READS
      ----------
      SQL_TEXT
      --------------------------------------------------------------------------------

      18920
      SELECT mrBILLNO as billno, MONEYRECIEPT.REGNO, SOURCEFLG, REMARKS, PATNAME, RCA

      MT, rcdt,usernm,MONEYRECIEPT.terminalid FROM MONEYRECIEPT,users,patientsregistr

      ation where patientsregistration.regid=moneyreciept.patid and patientsregistrati

      on.refertype=2 and MONEYRECIEPT.userid=users.userid and MONEYRECIEPT.generalopd
      = 0 and sourceflg <> 3 and Moneyreciept.rcdt>=to_date('14/05/2008','dd/MM/yyyy'

      ) And MoneyReciept.RcDt<=to_date('14/05/2008','dd/MM/yyyy')

      15417

      DISK_READS
      ----------
      SQL_TEXT
      --------------------------------------------------------------------------------

      Select * from track where tablenm = 'FRMREPORTING' and primkeyid = 'R205466TS000

      002' order by trackdate,tracktime

      14136
      Select * From Requisitions Where ReqDt>=to_date('12/05/2008','fmDD/MM/YYYY') AND

      PATIENTTYPE = 1 And ReqDt<=to_date('14/05/2008','fmDD/MM/YYYY') Order by ReqDT,

      Reqtm

      12839

      DISK_READS
      ----------
      SQL_TEXT
      --------------------------------------------------------------------------------

      select discount,discamount from billpharmacy where patientid='PAT09013'

      11213
      Select * from track where tablenm = 'FRMREPORTING' and primkeyid = 'R205466TS000

      001' order by trackdate,tracktime


      7 rows selected.

      SQL>
      SQL> select * from sys.v_$recover_file;

      no rows selected

      SQL> select owner, object_type, substr(object_name,1,30) object_name
      2 from sys.dba_objects
      3 where status='INVALID'
      4 order by object_type;

      OWNER OBJECT_TYPE OBJECT_NAME
      ------------------------------ ------------------ ------------------------------

      ADMIN VIEW ADMINMR
      ADMIN VIEW DGMR
      ADMIN VIEW IPMR
      ADMIN VIEW PCASHHANDOVER
      IP VIEW ADVANCE
      IP VIEW ADVANCEVIEW1
      IP VIEW DEPTBILL
      IP VIEW FINALBILLDTLS
      IP VIEW FINALBILLGWFLG
      IP VIEW FINALBILLREPORT
      IP VIEW FINALBILLREPORTS

      OWNER OBJECT_TYPE OBJECT_NAME
      ------------------------------ ------------------ ------------------------------

      IP VIEW INVBILL
      INVENTORY VIEW ITEMLEDGER
      INVENTORY VIEW LPURCH
      INVENTORY VIEW PCASHHANDOVER
      DIAGNOTECH VIEW CONSDRVIEW
      DIAGNOTECH VIEW F3SEARCH1921685117
      DIAGNOTECH VIEW F3SEARCH1921685148
      DIAGNOTECH VIEW F3SEARCH1921685156
      DIAGNOTECH VIEW F3SEARCH1921685163
      DIAGNOTECH VIEW F3SEARCH1921685167
      DIAGNOTECH VIEW F3SEARCH1921685212

      OWNER OBJECT_TYPE OBJECT_NAME
      ------------------------------ ------------------ ------------------------------

      DIAGNOTECH VIEW F3SEARCH1921685220
      DIAGNOTECH VIEW F3SEARCH192168535
      DIAGNOTECH VIEW F3SEARCH192168556
      DIAGNOTECH VIEW F3SEARCH192168586
      DIAGNOTECH VIEW F3SEARCH192168591
      DIAGNOTECH VIEW F3SEARCH192168593
      DIAGNOTECH VIEW PERFDRVIEW
      DIAGNOTECH VIEW REFDRVIEW
      DIAGNOTECH VIEW TESTWISEDOCPAYMENT

      31 rows selected.
        • 1. Re: performance related question....
          585179
          What is your Oracle version?

          post explain plan for slow query that is complained


          Cheers
          • 2. Re: performance related question....
            user00726
            u can see the hit ratio.....

            and my db version is oracle 9.2.....


            as i already mention the top 10 long ruinning queries....
            so now suggest me what ai have to do now.....
            • 3. Re: performance related question....
              Aman....
              So you think that just because you are getting hit ratio to a good % , you got an issue or slow database?
              Tell you what,get the book , Oracle wait interface by Richmond Shee.It has an excellent example of a doctor who checks the patients only by measuring their pulse.If pulse is slow than only according to him,there is an issue.Now imagine, a person goes to him with a broken arm and doctor says you are fine dude,your pulse is normal! So what would you say about the doctor?
              Please generate a statspack report for the time period which you think is having issues and post.It will reveal the facts and also as suggested, get the slowest query according to the users and do 10046 trace over it to find the issue.Hit ratio methodology is discarded since Oracle 8 if I remember correctly.
              Aman....
              • 4. Re: performance related question....
                Jonathan Lewis
                lokesh_aggarwal,

                It is possible to make a few reasonable guesses from the information you've supplied.

                DISK_READS
                ----------
                SQL_TEXT
                --------------------------------------------------------------------------------
                11213
                Select * from track where tablenm = 'FRMREPORTING' and primkeyid = 'R205466TS000001' order by trackdate,tracktime


                First - there are two statements that look like this - so your coding strategy seems to be "literal string" construction, which is generally undesirable for something that looks (from the other queries) as if it's meant to be more of an OLTP system.

                Second - a query that has "primarykeyid = constant" looks like a query that is supposed to find one row very quickly. The fact that this takes 11,213 reads of blocks from disk suggests that you haven't created the thing that appears to be the primary key index. If you've missed an index of this type on this query, then perhaps you've missed a lot of other precision indexes on other queries.

                Third - you need to examine the queries for meaning, work done, and data returned to decide which queries are (a) unreasonable or (b) doing too much work for the data returned. Amongst other things, you need to see not just disk_reads, but also executions, rows_processed, buffer_gets, sorts, cpu_time, elapsed_time to get a better idea of which queries are significant.

                As another poster suggests - start using Statspack and look at the 'SQL order by ..." sections. However, this may not help you very much in the early stages of your work, as you may find that you have a very large number of very similar queries (thanks to the literal string approach) which help to hide the issue of which queries are having the worst impact.

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