Things I learned at the Oracle talk

It’s not complete notes from the session, but here’s a few interesting things I caught during Christopher Jones’ talk:

  • Oracle is introducing connection pooling for Oracle->PHP scripts (to be included with version 11g) – database resident connection pooling
  • you don’t need to redo the entire prepare/reparse of the oracle statement just because you change a value of a bind variable. You can just oci_execute and move on…
  • You get better performance if the table statistics are up to date
  • Check the oci8.statement_cache_size php.ini setting to tweak your fetching performance
  • Use oci_set_prefetch function (PHP5) to get a bit permance increase
  • ‘set autotrace on explain’ can give you more stats on your query and how it’s performing
  • Look into using TKPROF and STATSPACK (admin)

Looks like a bright future ahead for Oracle and PHP – especially with the new connection pooling. I can’t wait to give it a shot…

Category: PHP 2 comments »

2 Responses to “Things I learned at the Oracle talk”

  1. Ivo Jansch

    Point 2 is actually the whole thought behind the ‘binding’ feature. It is funny how often this is not used in PHP scripts that connect to Oracle. I guess that Oracle guys that are moving to PHP are used to this feature, whereas PHP guys moving from MySQL to PHP will not be familiar with it.

    From experience, I can say that it can save quite some performance.

    On Point 3 (table statistics), if you are looking for a way to do this easily from the sql prompt instead of from Oracle’s enterprise manager, use this statement:

    exec dbms_stats.gather_schema_stats(‘schemaname’, options=>’gather’);

    This will analyze all tables in a schema. If you trust the oracle optimization process enough, you can put this in a cron job to run, for example, one time a week. (In theory, some queries might run slower from an optimization, but in practice, I’ve never encountered this.)

  2. Ivo Jansch

    I meant ‘from mysql to oracle’ in the above post, obviously. ;-)


Leave a Reply



Back to top