DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Syetem Slow

  1. #1
    Join Date
    Apr 2002
    Posts
    86
    Hi,

    User is complaing system is slow.
    How to start identify the problem..?

    Thanks,
    Harry

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Look for your bottleneck. Typical bottlenecks are CPU, I/O, network, Inadequate Indexing, etc.

    Check v$waitstat
    Jeff Hunter

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Oh, and 80% of performance problems are poorly written queries.
    Jeff Hunter

  4. #4
    Join Date
    Apr 2002
    Posts
    86
    SQL> /

    CLASS COUNT TIME
    ------------------ ---------- ----------
    data block 5158 0
    sort block 0 0
    save undo block 0 0
    segment header 7 0
    save undo header 0 0
    free list 0 0
    extent map 0 0
    bitmap block 0 0
    bitmap index block 0 0
    unused 0 0
    system undo header 0 0

    CLASS COUNT TIME
    ------------------ ---------- ----------
    system undo block 0 0
    undo header 38 0
    undo block 5 0

    How can I move from here..?

    Thanks
    Harry

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    data block 5158 0

    indicates you probably have some heavy I/O. I would check a couple things:
    1. make sure your physical devices (disks) are not swamped. On solaris, use iostat -xdM

    2. Make sure your top queries are using indexes. Look in v$sqltext, v$session, and v$process to find out your top offenders.
    Jeff Hunter

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by marist89
    Oh, and 80% of performance problems are poorly written queries.
    I agree 100% on this one with Jeff!
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Apr 2002
    Posts
    86
    Which kind of queries will tell me that which particular User or application or query causing the problem.

    I am new born DBA.

    Thanks,
    Harry

  8. #8
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Another possible problem is:

    undo header 38 0
    # of rollback segments or # of extents of rbs.
    u have to check it and increase # of rbs.

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by harrygulls
    Which kind of queries will tell me that which particular User or application or query causing the problem.

    I am new born DBA.

    Thanks,
    Harry
    Code:
    select b.username, a.buffer_gets reads,
    a.executions exec, a.buffer_gets / decode
    (a.executions, 0, 1, a.executions) rds_exec_ratio,
    a.command_type, a.sql_text Statement
    from v$sqlarea a, dba_users b
    where a.parsing_user_id = b.user_id
    and a.buffer_gets > 100000
    order by a.buffer_gets desc;
    
    select b.username, a.disk_reads reads,
    a.executions exec, a.disk_reads / decode
    (a.executions, 0, 1, a.executions) rds_exec_ratio,
    a.command_type, a.sql_text Statement
    from v$sqlarea a, dba_users b
    where a.parsing_user_id = b.user_id
    and a.disk_reads > 100000
    order by a.disk_reads desc;
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Apr 2002
    Posts
    86
    Thanks
    But how I am gonna apply the results..?
    How to analyze from these results.

    Thanks
    Harry

Posting Permissions

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


Click Here to Expand Forum to Full Width