Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jul 2004
    Posts
    102

    Unanswered: Oracle Performance Tuning

    Hi,

    I'm using Oracle 8.0.5, The database response very slow. I have only 30 users. My total data size is 250 MB. There are some critical report for top mamagement. These reports ar full of formula column. When we rum those report then, database takes too much time for a simple update or Insert from client end.

    My basic Initora config is below :

    DB_files = 1024
    DB_BLOCK_BUFFERS = 8192
    SHARED_POOL_SIZE = 300000000
    PROCESS = 59
    LOG_BUFFER = 262144
    DB_BLOCK_SIZE = 2048

    My OS :

    Win2000 SVR
    RAM 512 MB
    Processor 2.4Gh

    Can anyone help me figureout the best parameter values for my database or how can i calculate those parameter values are perfect for my database.

    Please....................

    Please ignore any spelling mistake.

    Thanks in advance for your help.
    Working Together...

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I would start with tuning the tables/indexes before tuning system parameters. Do a search for tuning sql.

    Alan

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I agree ... find out the worst performing SQL statements, then start
    to look at getting those corrected ... (trace or tkprof)
    Here's a script to find out poor performing sql

    SELECT disk_reads,executions,sql_text
    FROM v$sqlarea
    WHERE disk_reads > 10000
    ORDER BY disk_reads desc
    ;

    ----- and -----

    SELECT buffer_gets, sql_text
    FROM v$sqlarea
    WHERE buffer_gets > 10000
    ORDER BY buffer_gets desc
    ;

    HTH
    Gregg

  4. #4
    Join Date
    Jul 2004
    Posts
    102
    Thanks Gregg.

    Could you please inform me which sql statement is very poor of the folloing :

    DISK_READS BUFFER_GETS SQL_TEXT
    ------------ ------------- --------------------------
    74768 2857677 select ALL f.sl,a.mbm_order, substr(a.fno,3,3) fno, a.byr, a.br1, a.qcat, a.qty, d.add_pct tcqty, b.cqty, d.add_pct-b.cqty bcqty, nvl(c.sqty,0) sqty, d.add_pct-nvl(c.sqty,0) bsqty, nvl(e.faqty,0) faqty, d.add_pct-nvl(e.faqty,0) bfaqty, d.sew_com from mbm_order a, v_cut_info b, v_sew_info c, v_sew_input e, cut_turget d, sew_order f WHERE ( a.mbm_order = b.mbm_order and nvl ( a.prod_status , ' ' ) <> 'C' and a.mbm_order = c.mbm_order (+) and a.mbm_order = e.mbm_order (+) and a.mbm_order = d.mbm_order (+) and a.mbm_order = f.mbm_order ) AND ( L1 = f.sl) order by sl

    162310 1295044 SELECT ROUND(SUM(QUANTITY)) FROM QCAT_EXPORT_NEW A,V_B_Q_M_Y B WHERE A.MBM_ORDER = B.MBM_ORDER AND DECODE(QCAT_STATUS,'G','NQ',DECODE(SUBSTR(A.QCAT,1 ,3),'640','340','348','347','239','NQ','335','NQ', '641','NQ',SUBSTR(A.QCAT,1,3))) = DECODE(QCAT_STATUS,'G','NQ',B.QCAT) AND DECODE(QCAT_STATUS,'G','NQ',DECODE(SUBSTR(A.QCAT,1 ,3),'640','340','348','347','239','NQ','335','NQ', '641','NQ',SUBSTR(A.QCAT,1,3))) = :b1 AND A.BYR = :b2 AND UPPER(MON) = :b3 AND YR = :b4 AND EX_FTY IS NOT NULL

    563464 620685 SELECT dinfo.pid, DINFO.WD, decode(DINFO.PS,'A','Absent','Present') ps, DINFO.IN_TIME, DINFO.OUT_TIME out,days.days FROM HR.DINFO, days WHERE days.days=to_char(HR.DINFO.wd(+),'dd') and dinfo.pid(+) like :mpid and to_char(dinfo.wd(+),'yyyymm')=:mym order by days.days

    551618 552801 SELECT distinct dinfo.pid, DINFO.WD, decode(DINFO.PS,'A','Absent','Present') ps, DINFO.IN_TIME, DINFO.OUT_TIME out, days.days FROM HR.DINFO, days WHERE days.days=to_char(HR.DINFO.wd(+),'dd') and dinfo.pid(+) like :mpid and to_char(dinfo.wd(+),'yyyymm')=:mym order by days.days

    361 429621 SELECT COUNT(A.PID) FROM ENW A,DINFO B WHERE A.SD = :b1 AND A.ACTIVE = 'A' AND A.PID NOT IN (SELECT PID FROM LEFT_SEP ) AND A.PID = B.PID AND B.PS = 'P' AND B.WD = :b2

    Also please informe me how can I improve the statements.

    Thanks in advance.
    Working Together...

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    Does your V$SQL or V$SQLAREA have the column ROWS_PROCESSED (I don't have a v8 db anymore, so I don't know)? If a query returns a million rows (for a report, suppose), then the high reads and gets are necessarily a problem. Same with GROUP BY's - if a query does a group by to return 1 row, it still may need to process a million rows to get there.

    You should run each of the statements below through explain, and see what they're doing. Maybe you need more indexes (can't tell from here).

    Check what optimizer mode you're using. In 8.0.5 rule is as good as cost - each one does some things better than the other. If you're using CHOOSE mode (I believe the default at install) then it will always choose ALL_ROWS, which will prefer full table scans. Consider changing from CHOOSE to FIRST_ROWS. If you're using rule, try putting FIRST_ROWS hints into individual queries - it may speed them up, maybe not. If you change from cost to rule, or rule to cost, some things will speed up, but others will slow down (certainly some code runs well with the current settings), so at this point you're better off just tuning individual sql with hints.

    Some general guidelines:
    try to manipulate the bind variables with function, not the columns. This will help index availability. In general, change things like:
    days.days=to_char(HR.DINFO.wd(+),'dd') and to_char(dinfo.wd(+),'yyyymm')=:mym
    to
    to_Date(:mym || days.days, 'YYYYMMDD') = hr.dinfo.wd(+)

    Consider alternative SQL. If a query with a sub-select runs poorly, try changing it to a correlate sub-query:
    AND A.PID NOT IN (SELECT PID FROM LEFT_SEP )
    becomes:
    AND NOT EXISTS (select null from LEFT_SEP where pid = a.PID)

    A NOT IN subquery can run very poorly, especially if the sub-query returns a lot of rows (in higher versions, oracle can automatically rewrite these as anti-joins, regardless of rule/cost mode).
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    one more thing. all data from v$sql (or v$sqlarea) should be looked at with the value of EXECUTIONS. A statement might be very efficient, but it's been run 10 million times, so the DISK_READS and BUFFER_GETS will be high as these are total values for the life of that query in the shared pool.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    For the reasons outlined above you can use these queries which calculate the ratio of buffer_gets to executions and rows processed.

    Alan

    Code:
    select trunc(buffer_gets/(executions+1)) "BUFF-EXEC", trunc(buffer_gets/(rows_processed+1)) "BUFF-ROWS",first_load_time, executions, parse_calls, disk_reads, buffer_gets, rows_processed, sql_text, sorts , u.username 
    from v$sqlarea a, dba_users u 
    where parsing_user_id!=0 --and upper(sql_text) like '%JOB%'
    and u.user_id = parsing_user_id 
    order by executions desc, buffer_gets desc
    
    select trunc(buffer_gets/(executions+1)) "BUFF-EXEC", trunc(buffer_gets/(rows_processed+1)) "BUFF-ROWS",first_load_time, executions, parse_calls, disk_reads, buffer_gets, rows_processed, sql_text, sorts , u.username 
    from v$sqlarea a, dba_users u 
    where parsing_user_id!=0 --and upper(sql_text) like 'UPDATE%FINANCIAL%'
    and u.user_id = parsing_user_id 
    order by buffer_gets desc

  8. #8
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Regardless of what is and isn't performing well, your DB_BLOCK_BUFFERS looks alarmingly low to me.

    DB_BLOCK_BUFFERS = 8192
    DB_BLOCK_SIZE = 2048

    8192 * 2048 = 16Mb or so.,

    The advice you are being offered is generally quite correct, tune the SQL before you tune the database. However, 16Mb is alarmingly small.

    The last time I was that alarmed by a low number, it was on a pay cheque ;-)

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  9. #9
    Join Date
    Jul 2004
    Posts
    102
    Thanks Billm,

    I'm trying to improve my SQL query by all of your help.

    Quote Originally Posted by billm
    Hi,

    Regardless of what is and isn't performing well, your DB_BLOCK_BUFFERS looks alarmingly low to me.

    DB_BLOCK_BUFFERS = 8192
    DB_BLOCK_SIZE = 2048

    8192 * 2048 = 16Mb or so.,

    However, 16Mb is alarmingly small.
    What should be the DB_BLOCK_BUFFERS size for my database you think.
    Working Together...

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I would set it to something like 20-40% of available RAM. The other thing to note though in your case is that you only have 512M which is a bit tight for oracle. I would also suggest your reduce your shared pool a bit (say 100M max) if you can. The key thing though is too make sure Windows+Oracle < 512M otherwise youll get swapping to disk which will really slow things down.

    Alan

  11. #11
    Join Date
    Jul 2004
    Posts
    102
    Quote Originally Posted by AlanP
    The key thing though is too make sure Windows+Oracle < 512M otherwise youll get swapping to disk which will really slow things down.

    Alan
    Thanks. But how i know, how much memory uses by Windows+Oracle. Your sugs hardly needed.

    Again many many thanks.
    Working Together...

  12. #12
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Use task manager and the performance tab to see how much total memory is being used.

    Alan

  13. #13
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I believe a win2k server will use approx 100Mb just to get started up. If there are no other apps/processes on it then verify this by starting it without Oracle and checking memory usage in the task manager. Adding to what Alan has already said, make sure you add the line

    PRE_PAGE_SGA=YES

    to your init.ora.

    This will force Oracle to preallocate all configured buffers in their entirety at startup, rather than allocating and freeing as needed. (There are some exceptions to this, Extproc calls will allocate a new process per connection - about 2.5Mb and running in dedicated server mode will add approx 4mb per connection).

    Now start the database and check Oracle and other memory usage again.

    On a 512Mb machine, I would start somewhere around...
    100Mb for OS
    100Mb Free
    300Mb for Oracle

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  14. #14
    Join Date
    Jul 2004
    Posts
    102
    Quote Originally Posted by AlanP
    Use task manager and the performance tab to see how much total memory is being used.

    Alan

    I already see it and my available memory is 250MB at this tim. So what is youe sugs.............................?
    Working Together...

  15. #15
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try for starters

    shared_pool_size=80M
    db_block_buffers=15000

    and then change it according to how much free memory you have under max load.

    Alan

Posting Permissions

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