5 Replies Latest reply on May 19, 2017 4:12 PM by mdtaylor

    slower database



      i have problem in my oracle database (e-suite under linux Oracle Database 10g Enterprise Edition Release - Production)

      it is slowe i try to make another server to check the problem (hardware or database) but the same problem in new server

      i check the trace files and i found in trace file many note like these note

      Media recovery not enabled or manual archival only 0x10000

      Maximum redo generation record size = 392192 bytes

      Maximum redo generation change vector size = 379056 bytes

      AUTO SGA: Not free 0x85f9f0f0, 1, 1, 1

      unable to schedule MMON Slave, error 5


      KCRP: blocks claimed = 717, eliminated = 0

      ----- Recovery Hash Table Statistics ---------

      Hash table buckets = 32768

      Longest hash chain = 2

      Average hash chain = 717/710 = 1.0

      Max compares per lookup = 1

      Avg compares per lookup = 7/717 = 0.0


      i execute the command (select sid,start_time,last_update_time,message from v$session_longops;) when the server is slower the command return many rows like

            1154 18-MAY-17 18-MAY-17 Sort Output:  : 45275 out of 45275 Blocks done

            1082 18-MAY-17 18-MAY-17 Gather Table Partition Statistics: Table WRH$_PARAMETER : 1 out of 1 Partitions done

      Gather Table's Index Statistics: Table WRH$_PARAMETER : 1 out of 1 Indexes done

      Gather Table Partition Statistics: Table WRH$_ACTIVE_SESSION_HISTOR : 1 out of 1  Partitions done

      and others

      please how can you help me to solve this problem

      i have little information in this field

        • 1. Re: slower database

          Provide your server configuration.  Also provide screen shot of Unix "top" command when the database is slow. also attach init.ora

          • 2. Re: slower database

            If you are not licensed for Tuning and Diagnostics Packs, you should probably uninstall AWR and setup Statspack again.  Otherwise, I would look at bde_chk_cbo,sql and ensure your init parameters are all setup OK, and you actually have enough RAM on the database server.  Your sga_target should be no more than 50% of physical RAM.  You need free RAM for UGA/PGA per session memory outside of SGA.  I prefer to not use AMM (memory_target) with EBS and rely on the sga_target advised in bde_chk_cbo.sql.


            Solutions for possible AWR Library Cache Latch Contention Issues in Oracle 10g (Doc ID 296765.1)

            MMON Trace generated with -- Sga Policy: Cache Below Reserve And Cant Get Memory (Doc ID 422954.1)

            bde_chk_cbo.sql - EBS initialization parameters - Healthcheck (Doc ID 174605.1)

            Disabling and Uninstalling AWR (Doc ID 1909073.1)

            FAQ - Commonly asked questions pertaining to Automatic Statistics Collection procedures in 10G (Doc ID 341839.1)




            • 3. Re: slower database


              i would like to introduce my thanks for all participant

              i make the next commands

              SQL> show parameter sga;

              NAME                                 TYPE        VALUE

              ------------------------------------ ----------- ------------------------------

              lock_sga                             boolean     FALSE

              pre_page_sga                         boolean     FALSE

              sga_max_size                         big integer 2304M

              sga_target                           big integer 2304M


              SQL> select * from v$sgastat where name like '%free%';


              POOL         NAME                            BYTES

              ------------ -------------------------- ----------

              shared pool  ksuloi: long op free list         192

              shared pool  message pool freequeue         698460

              shared pool  kghx free lists                 41328

              shared pool  free memory                 144258752

              shared pool  kglsim free obj list              204

              shared pool  sim kghx free lists                 4

              shared pool  ksr message pool free que         460

              shared pool  kglsim free heap list             204

              large pool   free memory                  16467844

              java pool    free memory                  26554880


              i make these command to help me to check  the values of sga is true or not

              i have little information in this field and i need to help me

              streams pool free memory                  16784708

              • 4. Re: slower database

                hello mr.Mdtylor

                i have not account on metalink so i can not get your doc you refer to

                • 5. Re: slower database

                  Ask your DBA to ssh to the database server and provide the init.ora and any ifile used in $ORACLE_HOME/dbs.


                  Also type free -m


                  What is the operating system and version of database server?


                  Does anyone at the client have a support contract for Oracle?