10 Replies Latest reply: Dec 30, 2012 9:59 AM by Osama_Mustafa RSS

    instance is very slow

    941949
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        yes,i will close that post and now i want to sga tuning.will u help?
                        thanks.
                        • 9. Re: instance is very slow
                          JohnWatson
                          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
                            Read
                            http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ