This discussion is archived
4 Replies Latest reply: Apr 13, 2011 6:32 AM by 855033 RSS

PHP OCI performance VS. TOAD

675093 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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