4 Replies Latest reply: Apr 13, 2011 8:32 AM by 855033 RSS

    PHP OCI performance VS. TOAD

    mackrispi
      Hi,

      My customer has a problem because web page that is done in PHP 5.2.x and uses Oracle OCI8 driver, is performing very very slow .... At first we tested sql statements and explain plan is looking ok ...

      But then we ran the same sql using Toad .... and the performance was 1:1000 ..... What took PHP 5minutes, took Toad 5 seconds .... Database has over 7mil records ... and takes 72Gb on disk ...

      Can anyone explain why ? Is there any known problem with OCI driver that i'm not aware of ?

      We run IIS on Windows 2008 64bit Server .... Oracle is 10.1.0.2.0 ... and table on which sql statement is executed contains CLOB columns .... and yes we search inside CLOBs using INSTR ..... but still why so big performance difference ??

      What is also wierd is that sometimes very simple sql statement takes ages, but the complex one is fast .....

      I have attached our DB setting below, if someone could take a look , if there is someting that is not ok .... or should be increased .... If yes, then please describe how to perform ALTER of some parameter that will efect database(tablespace if so) and not just current session. ..

      Thank you.


      NAME          VALUE
      active_instance_count          
      aq_tm_processes          0
      archive_lag_target          0
      asm_diskgroups          
      asm_diskstring          
      asm_power_limit          1
      audit_file_dest          /home/orabase/OraHome_1/rdbms/audit
      audit_sys_operations          FALSE
      audit_trail          NONE
      background_core_dump          partial
      background_dump_dest          /home/orabase/admin/presclip/bdump
      backup_tape_io_slaves          FALSE
      bitmap_merge_area_size          1048576
      blank_trimming          FALSE
      buffer_pool_keep          
      buffer_pool_recycle          
      circuits          
      cluster_database          FALSE
      cluster_database_instances          1
      cluster_interconnects          
      commit_point_strength          1
      compatible          10.1.0.2.0
      control_file_record_keep_time          7
      control_files          /home/orabase/OraHome_1/oradata/presclip/control01.ctl, /home/orabase/OraHome_1/oradata/presclip/control02.ctl, /home/orabase/OraHome_1/oradata/presclip/control03.ctl
      core_dump_dest          /home/orabase/admin/presclip/cdump
      cpu_count          2
      create_bitmap_area_size          8388608
      create_stored_outlines          
      cursor_sharing          EXACT
      cursor_space_for_time          FALSE
      db_block_buffers          0
      db_block_checking          FALSE
      db_block_checksum          TRUE
      db_block_size          8192
      db_cache_advice          ON
      __db_cache_size          469762048
      db_cache_size          0
      db_create_file_dest          
      db_create_online_log_dest_1          
      db_create_online_log_dest_2          
      db_create_online_log_dest_3          
      db_create_online_log_dest_4          
      db_create_online_log_dest_5          
      db_domain          pressclip.si
      db_file_multiblock_read_count          16
      db_file_name_convert          
      db_files          200
      db_flashback_retention_target          1440
      db_keep_cache_size          0
      db_name          presclip
      db_recovery_file_dest          /home/orabase/flash_recovery_area
      db_recovery_file_dest_size          2147483648
      db_recycle_cache_size          0
      db_unique_name          presclip
      dbwr_io_slaves          0
      db_writer_processes          1
      db_16k_cache_size          0
      db_2k_cache_size          0
      db_32k_cache_size          0
      db_4k_cache_size          0
      db_8k_cache_size          0
      ddl_wait_for_locks          FALSE
      dg_broker_config_file1          /home/orabase/OraHome_1/dbs/dr1presclip.dat
      dg_broker_config_file2          /home/orabase/OraHome_1/dbs/dr2presclip.dat
      dg_broker_start          FALSE
      disk_asynch_io          TRUE
      dispatchers          (PROTOCOL=TCP) (SERVICE=presclipXDB)
      distributed_lock_timeout          60
      dml_locks          748
      drs_start          FALSE
      enqueue_resources          968
      event          
      fal_client          
      fal_server          
      fast_start_io_target          0
      fast_start_mttr_target          0
      fast_start_parallel_rollback          LOW
      fileio_network_adapters          
      file_mapping          FALSE
      filesystemio_options          none
      fixed_date          
      gc_files_to_locks          
      gcs_server_processes          0
      global_context_pool_size          
      global_names          FALSE
      hash_area_size          131072
      hi_shared_memory_address          0
      hs_autoregister          TRUE
      ifile          
      instance_groups          
      instance_name          presclip
      instance_number          0
      instance_type          RDBMS
      java_max_sessionspace_size          0
      __java_pool_size          8388608
      java_pool_size          0
      java_soft_sessionspace_limit          0
      job_queue_processes          10
      __large_pool_size          8388608
      large_pool_size          0
      ldap_directory_access          NONE
      license_max_sessions          0
      license_max_users          0
      license_sessions_warning          0
      local_listener          
      lock_name_space          
      lock_sga          FALSE
      log_archive_config          
      log_archive_dest          
      log_archive_dest_state_1          enable
      log_archive_dest_state_10          enable
      log_archive_dest_state_2          enable
      log_archive_dest_state_3          enable
      log_archive_dest_state_4          enable
      log_archive_dest_state_5          enable
      log_archive_dest_state_6          enable
      log_archive_dest_state_7          enable
      log_archive_dest_state_8          enable
      log_archive_dest_state_9          enable
      log_archive_dest_1          
      log_archive_dest_10          
      log_archive_dest_2          
      log_archive_dest_3          
      log_archive_dest_4          
      log_archive_dest_5          
      log_archive_dest_6          
      log_archive_dest_7          
      log_archive_dest_8          
      log_archive_dest_9          
      log_archive_duplex_dest          
      log_archive_format          %t_%s_%r.dbf
      log_archive_local_first          TRUE
      log_archive_max_processes          2
      log_archive_min_succeed_dest          1
      log_archive_start          FALSE
      log_archive_trace          0
      log_buffer          524288
      log_checkpoint_interval          0
      log_checkpoints_to_alert          FALSE
      log_checkpoint_timeout          1800
      log_file_name_convert          
      logmnr_max_persistent_sessions          1
      max_commit_propagation_delay          700
      max_dispatchers          
      max_dump_file_size          UNLIMITED
      max_enabled_roles          150
      max_shared_servers          
      object_cache_max_size_percent          10
      object_cache_optimal_size          102400
      olap_page_pool_size          0
      open_cursors          1500
      open_links          4
      open_links_per_instance          4
      optimizer_dynamic_sampling          2
      optimizer_features_enable          10.1.0.3
      optimizer_index_caching          0
      optimizer_index_cost_adj          100
      optimizer_mode          ALL_ROWS
      os_authent_prefix          ops$
      os_roles          FALSE
      O7_DICTIONARY_ACCESSIBILITY          FALSE
      parallel_adaptive_multi_user          TRUE
      parallel_automatic_tuning          FALSE
      parallel_execution_message_size          2148
      parallel_instance_group          
      parallel_max_servers          40
      parallel_min_percent          0
      parallel_min_servers          0
      parallel_server          FALSE
      parallel_server_instances          1
      parallel_threads_per_cpu          2
      pga_aggregate_target          209715200
      plsql_code_type          INTERPRETED
      plsql_compiler_flags          INTERPRETED, NON_DEBUG
      plsql_debug          FALSE
      plsql_native_library_dir          
      plsql_native_library_subdir_count          0
      plsql_optimize_level          2
      plsql_v2_compatibility          FALSE
      plsql_warnings          DISABLE:ALL
      pre_page_sga          FALSE
      processes          150
      query_rewrite_enabled          TRUE
      query_rewrite_integrity          enforced
      rdbms_server_dn          
      read_only_open_delayed          FALSE
      recovery_parallelism          0
      remote_archive_enable          true
      remote_dependencies_mode          TIMESTAMP
      remote_listener          
      remote_login_passwordfile          EXCLUSIVE
      remote_os_authent          FALSE
      remote_os_roles          FALSE
      replication_dependency_tracking          TRUE
      resource_limit          FALSE
      resource_manager_plan          
      resumable_timeout          0
      rollback_segments          
      serial_reuse          disable
      service_names          presclip
      session_cached_cursors          0
      session_max_open_files          10
      sessions          170
      sga_max_size          1044381696
      sga_target          1044381696
      shadow_core_dump          partial
      shared_memory_address          0
      shared_pool_reserved_size          27682406
      __shared_pool_size          553648128
      shared_pool_size          255852544
      shared_servers          1
      shared_server_sessions          
      skip_unusable_indexes          TRUE
      smtp_out_server          
      sort_area_retained_size          0
      sort_area_size          65536
      spfile          /home/orabase/OraHome_1/dbs/spfilepresclip.ora
      sp_name          presclip
      sql_trace          FALSE
      sqltune_category          DEFAULT
      sql_version          NATIVE
      sql92_security          FALSE
      standby_archive_dest          ?/dbs/arch
      standby_file_management          MANUAL
      star_transformation_enabled          FALSE
      statistics_level          TYPICAL
      streams_pool_size          0
      tape_asynch_io          TRUE
      thread          0
      timed_os_statistics          0
      timed_statistics          TRUE
      trace_enabled          TRUE
      tracefile_identifier          
      transactions          187
      transactions_per_rollback_segment          5
      undo_management          AUTO
      undo_retention          900
      undo_tablespace          UNDOTBS1
      use_indirect_data_buffers          FALSE
      user_dump_dest          /home/orabase/admin/presclip/udump
      utl_file_dir          
      workarea_size_policy          AUTO
        • 1. Re: PHP OCI performance VS. TOAD
          727763
          Have you ever tried comparing performance of a PHP OCI application vs. Shadowfax, the racing horse? Comparing a PHP application with a GUI tool makes no sense whatsoever. As for the application tuning, I can only direct you to several worthy manuscripts which will teach you the proper process. Here are the books that you should read, in that order:

          Optimizing Oracle Performance by Cary Millsap and Jeffrey Holt
          Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions by Thomas Kyte
          Effective Oracle by Design by Thomas Kyte
          Troubleshooting Oracle Performance by Christian Antognini
          Oracle Insights: Tales of the Oak Table
          Citizen In Space by Robert Sheckley
          The Hitchhiker's Guide to the Galaxy by Douglas Adams

          The last two books are a bit off topic but will teach you how to think out of the box. There are, of course, many more books. I can wholeheartedly recommend authors like K. Gopalakrishnan, Gaja Vaidayanatha, Jonathan Lewis (a bit hard to read but well worth it) and Tim Gorman. In other words, tuning an application is a process which requires a fairly extensive knowledge.
          • 2. Re: PHP OCI performance VS. TOAD
            cj
            What monitoring capabilities have you built into your application (this is a hint) to identify bottlenecks?

            Check AWR reports to see how efficient your application is.

            Look at some of the tips in the Underground PHP & Oracle Manual (and other fine PHP books like Mladen's http://www.amazon.com/Easy-Oracle-PHP-Create-Dynamic/dp/0976157306) and make sure all the basics (persistent connections, binding, prefetching etc) are being done.
            • 3. Re: PHP OCI performance VS. TOAD
              448765
              It depends what you are doing !!

              Always keep in mind that Toad is dispalying only a bunch of data
              while fetching and transport a huge recordset in PHP (f.e. and copy it into an array) may take a very long time !!
              • 4. Re: PHP OCI performance VS. TOAD
                855033
                I have similar problem with php_oci8 but what is strange everything works fine when i was changed connection string:

                $db = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)))(CONNECT_DATA=(SID=mysid)))";
                oci_connect($dbuser, $dbpass, $db, 'UTF8');

                Old (slow) version:
                oci_connect($dbuser,$dbpass, 'mysid'); -> connection like this take more than 1s (IIS works on same host as database).

                Edited by: user13793617 on 2011-04-13 06:31