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…

2 comments

  1. 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.)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>