6 Replies Latest reply on Jun 3, 2011 12:23 PM by Srini Chavali-Oracle

    Oracle 11gR2 (11.2.0.1) Install on Sun Solaris SPARC T3 running slow

    vikramrathour
      H/W Configuration:
      Operating System : Solaris 5.10/08
      Sun SPARC T3 -1 Server
      CPU : SPARC T3 16-Core 1.65 GHz Processor
      RAM : 16 GB DDR3
      HDD : 4*300 GB= 1200 TB

      Oracle:
      SQL> select * from v$version;
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE    11.2.0.1.0      Production
      TNS for Solaris: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      
      
      shmmax = 10G
      SQL> show parameters sga
      
      NAME                                 TYPE                             VALUE
      ------------------------------------ -------------------------------- ------------------------------
      sga_max_size                         big integer                      6352M
      sga_target                           big integer                      0
      
      SQL> show parameters mem
      
      NAME                                 TYPE                             VALUE
      ------------------------------------ -------------------------------- ------------------------------
      hi_shared_memory_address             integer                          0
      memory_max_target                    big integer                      6352M
      memory_target                        big integer                      6352M
      shared_memory_address                integer                          0
      
      SQL> show parameters io
      
      NAME                                 TYPE                             VALUE
      ------------------------------------ -------------------------------- ------------------------------
      O7_DICTIONARY_ACCESSIBILITY          boolean                          FALSE
      audit_sys_operations                 boolean                          FALSE
      backup_tape_io_slaves                boolean                          FALSE
      cell_offload_compaction              string                           ADAPTIVE
      cell_offload_decryption              boolean                          TRUE
      cell_partition_large_extents         string                           DEFAULT
      db_flashback_retention_target        integer                          1440
      dbwr_io_slaves                       integer                          0
      deferred_segment_creation            boolean                          FALSE
      disk_asynch_io                       boolean                          TRUE
      fast_start_io_target                 integer                          0
      fileio_network_adapters              string
      filesystemio_options                 string                           SETALL
      java_max_sessionspace_size           integer                          0
      java_soft_sessionspace_limit         integer                          0
      license_max_sessions                 integer                          0
      license_sessions_warning             integer                          0
      parallel_execution_message_size      integer                          16384
      parallel_io_cap_enabled              boolean                          FALSE
      replication_dependency_tracking      boolean                          TRUE
      resource_manager_cpu_allocation      integer                          128
      result_cache_remote_expiration       integer                          0
      sec_protocol_error_further_action    string                           CONTINUE
      sec_protocol_error_trace_action      string                           TRACE
      session_cached_cursors               integer                          50
      session_max_open_files               integer                          10
      sessions                             integer                          1408
      shared_server_sessions               integer
      star_transformation_enabled          string                           FALSE
      tape_asynch_io                       boolean                          TRUE
      transactions                         integer                          1100
      transactions_per_rollback_segment    integer                          5
      undo_retention                       integer                          900
      Am I missing something obvious.
      We have also taken a trace of a couple of queries and that does not highlight anything significant.

      Edited by: vikramrathour on Jun 2, 2011 12:03 AM
        • 1. Re: Oracle 11gR2 (11.2.0.1) Install on Sun Solaris SPARC T3 running slow
          Helios-GunesEROL
          Hi;

          What is the issue here? How you decide your db is slow? What is your source to can say this?

          Regard
          Helios
          • 2. Re: Oracle 11gR2 (11.2.0.1) Install on Sun Solaris SPARC T3 running slow
            Srini Chavali-Oracle
            I believe this is a known issue. See MOS Doc 781763.1 (Migration from fast single threaded CPU machine to CMT UltraSPARC T1 and T2 results in increased CPU reporting and diminished performance)

            HTH
            Srini
            1 person found this helpful
            • 3. Re: Oracle 11gR2 (11.2.0.1) Install on Sun Solaris SPARC T3 running slow
              suvesh kumar - oracle
              from the heading it seems it's a new installation.

              you can check the long running sqls, and also operating system resources utilization.
              • 4. Re: Oracle 11gR2 (11.2.0.1) Install on Sun Solaris SPARC T3 running slow
                Srini Chavali-Oracle
                Suvi wrote:
                from the heading it seems it's a new installation.
                This does not change the fact that OP is using T3 processors - see MOS Doc mentioned above.

                you can check the long running sqls, and also operating system resources utilization.
                Srini
                • 5. Re: Oracle 11gR2 (11.2.0.1) Install on Sun Solaris SPARC T3 running slow
                  vikramrathour
                  Hello All,

                  Thanks for the responses, but I have managed to solve the issue, but am now completely confused. Here is what I did

                  The way I had done the new Installation was

                  1/ Fresh Install Oracle 11gR2
                  2/ Create New Instance using dbca
                  3/ Manually create the new Schema
                  4/ Import (IMPDP) using the EXPDP from another database. This source DB was on a Sun SPARC T5140 machine.

                  This lead to poor performance. So we then copied over the database files and cloned this old DB on the new SPARC T3 and the result was amazing. Everything is running is very smooth. Now this is very very surprising. I definately need to know / understand what is going on.

                  In the meanwhile, I used TRCANLYZR and TKPROF and have zeroed in on the exact portion of the code that is causing performance bottleneck.

                  In our application we use VIEWS to access all data. Therefore if I run a query joining the base tables it runs very fast. However, If I query the view then it gives me issues.
                  So a query like:
                    SELECT *
                  FROM   ADD_RPT_SELECTED_I I,
                         ADD_RPT_SELECTED_V V1,
                         ADD_RPT_SELECTED_V V2
                  WHERE  V1.ADD_RPT_SELECTED_ID = I.ADD_RPT_SELECTED_ID
                  AND    V2.ADD_RPT_SELECTED_ID = I.ADD_RPT_SELECTED_ID;
                  
                  runs good.
                  
                  But, in the views we make a call to a DB package.function, so the view query is:
                  
                    SELECT *
                  FROM   ADD_RPT_SELECTED_I I,
                         ADD_RPT_SELECTED_V V1,
                         ADD_RPT_SELECTED_V V2
                  WHERE  V1.ADD_RPT_SELECTED_ID = I.ADD_RPT_SELECTED_ID
                  AND    V2.ADD_RPT_SELECTED_ID = I.ADD_RPT_SELECTED_ID
                  AND    V1.version_id = get_version_id.ADD_RPT_SELECTED(V1.ADD_RPT_SELECTED_ID, 'NEAREST')
                  AND    V2.version_id = get_version_id.ADD_RPT_SELECTED(V2.ADD_RPT_SELECTED_ID, 'KNOWLEDGE');
                  
                  GET_VERSION_ID.ADD_RPT_SELECTED is the package procedure. The package executes the below query
                  CURSOR c_version_asc (cp_bus_date VARCHAR2, cp_know_timestamp TIMESTAMP) IS
                        SELECT  v.version_id
                        FROM    add_rpt_selected_v v
                        WHERE   v.add_rpt_selected_id = p_add_rpt_selected_id
                        AND     v.version_valid_from <= cp_bus_date
                        AND     v.cre_datim          <= cp_know_timestamp
                        AND     v.deletes_version_id IS NULL
                        AND     NOT EXISTS (SELECT NULL
                                            FROM   add_rpt_selected_v v2
                                            WHERE  v.version_id = v2.deletes_version_id
                                            AND    v2.version_valid_from <= cp_bus_date
                                            AND    v2.cre_datim <= cp_know_timestamp)
                        ORDER BY v.version_valid_from ASC, v.cre_datim DESC;
                  
                  and just returns the first ROW in this cursor. As there is an order by :
                  SQL ID: cv4dua23db5q4
                  Plan Hash: 3649325462
                  SELECT V.VERSION_ID 
                  FROM
                   RD_DATA_ENTRY_V V WHERE V.RD_TYPE_CODE = :B3 AND V.VERSION_VALID_FROM <= :B2 
                    AND V.CRE_DATIM <= :B1 AND V.DELETES_VERSION_ID IS NULL AND NOT EXISTS 
                    (SELECT NULL FROM RD_DATA_ENTRY_V V2 WHERE V.VERSION_ID = 
                    V2.DELETES_VERSION_ID AND V2.VERSION_VALID_FROM <= :B2 AND V2.CRE_DATIM <= 
                    :B1 ) ORDER BY V.VERSION_VALID_FROM DESC, V.CRE_DATIM DESC
                  
                  
                  call     count       cpu    elapsed       disk      query    current        rows
                  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                  Parse        1      0.00       0.00          0          0          0           0
                  Execute   7999     31.45      31.70          0          0          0           0
                  Fetch     7999     69.24      69.12          0     255570          0        7999
                  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                  total    15999    100.69     100.83          0     255570          0        7999
                  But, my question is: How come it runs fast in other environments?

                  Edited by: vikramrathour on Jun 2, 2011 8:50 PM
                  • 6. Re: Oracle 11gR2 (11.2.0.1) Install on Sun Solaris SPARC T3 running slow
                    Srini Chavali-Oracle
                    Were statistics gathered after IMPDP completed ?

                    Recommendations for Gathering Optimizer Statistics on 11g (Doc ID 749227.1)

                    Pl see these threads on how to post a tuning request

                    HOW TO: Post a SQL statement tuning request - template posting

                    When your query takes too long ...

                    HTH
                    Srini