This discussion is archived
10 Replies Latest reply: Dec 30, 2012 7:59 AM by Osama_Mustafa RSS

instance is very slow

941949 Explorer
Currently Being Moderated
hii everybody,
i m a newbie,my instance is slow,how can i make it fast?my db is 10.2.0.3 on linux platform redhat 5.7 . how can i check sga,memory of cpu,.....your help is higly appreciated.
thanks and regards,
xyz.
  • 1. Re: instance is very slow
    P.Forstmann Guru
    Currently Being Moderated
    At Linux level you can use top/vmstat to check CPU usage and vmstat/free to check RAM usage.

    At database level:

    If you have the right license for AWR and ADDM try to get a AWR report: http://docs.oracle.com/cd/E11882_01/server.112/e16638/autostat.htm#PFGRF94203 and an ADDM report:
    http://www.oracle-base.com/articles/10g/automatic-database-diagnostic-monitor-10g.php. If you don't have the right license for AWR and ADDM try to install Statspack using <ORACLE_HOME>/rdbms/admin/spdoc.txt and run a Statspack report. In AWR or Statspack report look at the top SQL statements in the different "SQL ordered by ..." sections and try to analyze these SQL statements.

    If you can identify slow database sessions, you can also try to enable SQL trace and to use TKPROF for this database session: http://www.oracle-base.com/articles/10g/sql-trace-10046-trcsess-and-tkprof-10g.php. TKPROF will give run time statistics for all traced SQL statements: SQL statements that take most time should be analyzed.

    Edited by: P. Forstmann on 30 déc. 2012 09:24
  • 2. Re: instance is very slow
    941949 Explorer
    Currently Being Moderated
    thanks for the quick response.can u please telll me what are the other linux command i must run on linux to check the performance of the system ?.how to check sga,pga at db level.ur help is highly appreciated thanks.
    regards,
  • 3. Re: instance is very slow
    P.Forstmann Guru
    Currently Being Moderated
    At Linux level you can use top/vmstat to check CPU usage and vmstat/free to check RAM usage.


    To check SGA:
    select * from v$sgainfo;
    select * from v$sgastat;
    To check PGA:
    select * from v$pgastat;
    However it is unlikely that only checking/changing SGA/PGA settings you will solve your performance issue. As already said you need to use more advanced tools like AWR/Staspack or SQL/trace/TKPROF.
  • 4. Re: instance is very slow
    941949 Explorer
    Currently Being Moderated
    can you please tell me,how can i tune SGA which includes
    1)tuning of buffer cache,
    2)tunning of shared pool,
    3)tuning of library cache,
    4)tuning of dictionary cache,
    5)tunning the redo log buffer and
    6) tuning the sort area.
    what should be the minimum and maximum values of each? when to increase the size?

    thanks and regards,
    xyz
  • 5. Re: instance is very slow
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl check the documentation first - http://docs.oracle.com/cd/B19306_01/server.102/b14211/memory.htm#i53187

    What parameters/data have you used to determine that your instance is "slow" ? Why do you think this slowness is caused by parameters related to the SGA ?

    HTH
    Srini
  • 6. Re: instance is very slow
    941949 Explorer
    Currently Being Moderated
    hi srini,my team lead checked that instance is slow and i dnt know which parameters he used and telling that system is slow.could you please tell me the parameters which are used to check whether the system is slow or not?. Slowness might not caused by sga parameter but my team lead asked me to check the sga parameters performance,so i want to check it.
    thanks,
    srini

    Edited by: 938946 on Dec 30, 2012 4:40 AM
  • 7. Re: instance is very slow
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    How did you team lead decide the instance was "slow" ?

    Is this post related to your other post ? how to check the performance of the server in oracle applications 11i
  • 8. Re: instance is very slow
    941949 Explorer
    Currently Being Moderated
    yes,i will close that post and now i want to sga tuning.will u help?
    thanks.
  • 9. Re: instance is very slow
    JohnWatson Guru
    Currently Being Moderated
    938946 wrote:
    can you please tell me,how can i tune SGA which includes
    1)tuning of buffer cache,
    2)tunning of shared pool,
    3)tuning of library cache,
    4)tuning of dictionary cache,
    5)tunning the redo log buffer and
    6) tuning the sort area.
    what should be the minimum and maximum values of each? when to increase the size?

    thanks and regards,
    xyz
    Dear xyz, if it were possible to answer these questions in a forum message, no-one would need to hire database administrators. A good starting point would be to complete your introductory training (the OCP curriculum) and only when you a are comfortable with that, move on to tuning.
    This is serious advice. Do not attempt to ruyn before you can walk.
  • 10. Re: instance is very slow
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Read
    http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points