Please, note. This article is entirely based on a work done by me colleague developer, who hasn't got account on this webpage and also hasn't got time for writing this article. I'm writing it for him. I will try to forward him any comments that may appear, but can't assure if/when he'll be able to answer. I'm far, far less experienced in Oracle therefore I won't probably be able to help myself.

An approach currently presented in COciSchema - i.e. basing on all_objects, ALL_TAB_COLUMNS and
ALL_CONS_COLUMNS is probably correct solution, but very, very slow, when dealing with Oracle databases. Experiments proved that in some rare situations (very slow, not localhost server), schema analysing query based on above may run more than one minute (up to even twenty minutes in a very separate cases), which causes for example Gii to end up permanently with timeout error message, if PHP script execution time is to low. This situation was presented for example in this post.

This approach works very well in other RDBMS (like MySQL) as they are properly optimised for such queries. Unfortunately, fails on Oracle setup on a slow server.

One of possible solutions is to introduce new set of tables that will hold information on tables and views used in a project. This is a kind of caching on database side.

The idea is following. We are taking original SQL query from original COciSchema, which is used to get tables schema. It is fired upon each AR use and since it is one of slowest queries in Oracle, it makes AR nearly unusable. Instead of calling this slow query every time, we are firing it only when real table schema changes. And instead of returning results, we are putting them into our set of tables, used for caching real tables schema. Then we change COciSchema so it reads tables schema from that so called technical-tables. This speeds up whole process significantly as Active Record is querying normal table, which is done very fast.

After creating proper tables and modifying COCiSchema class definition, you can insert any table schema into created set of tables. Here is an exemplary SQL statement for this. As it was stated eariler, it is pretty much the same as the one originally used in COciSchema.

This step is essential as base of speeding up Active Record with presented solution is to change it, so it will read table schema from newly created set of tables, instead of querying Oracle each time for table schema (this is one of the slowest operations in Oracle). Therefore, any change in real table schema must be reflected in above set of tables.

This is one of possible solution. It was designed for our own project and therefore might not satisfy other developers needs or may need some slight changes. But, on the other hand, we were able to speed up Active Record ten times. For example selection of 300 records (160 columns) took 0,24 second, while before (on original Yii-build in COciSchema) the same query was taking around 2,45 second. Both test without using any caching component.

Total 10 comments

You can use the following query to insert the into the caching tables. Just change the <YOUR_TABLE_NAME> into your table. This SQL Script will automatically insert all the data needed for the caching tables.

@yktoo - you could create github ticket and even push change request with your patch...

@trejder - about param binding in PDO OCI - there is a difference between named parameters which are passed to Oracle engine with OCIBindByName internally and query without params. Also Oracle query runner can cache query execution plans with named params and reuse them which should speed up running similar queries. So using params is rather recommended...

I use CFileCache. It works well in both Windows and Linux. I still can delete the cache files when I think necessary.

Using many queries and retrieving many data is not a problem. The metadata querie is just made once per table.

Caching metadata is always a good idea. But not caching resultsets. Yes, when your data changes very often, caching is unnecessary and slow down performance, as you told. But this will happen even on MySQl or Postgres.

@rickgrana: First of all -- I'm not working on a project, where I used Oracle, any more. I'm not pretty sure right now, but the problem was that we're not able to find a good cache component for PHP running under Windows and Linux (we were developing a localhost application to be deployed on many customers' servers).

Second of all -- Using cache was not a good idea at all in my project. It was meant to handle hundreds (if not thousands) of small SQL queries, ex-changing millions of a small chunks of data per second. Data changing very dynamically every second. If I'm not mistaken, using a cache in such project would only slow down performance.

I was told many times that cache isn't a best solution always, in every project.

@sx9: Do you ask, if just adding parameters binding will speed up your queries (not using above solution at all) or you we tested our solution with parameters binding?

Answer to the second question is that this solution should speed up any query passed to Oracle database? Parameters binding is done on PHP side (in either Yii or PDO) and the result is a query quite similar to the one not using parameters biding. I.e. If you are using it or not, it should not have influence for speed, if using our solution.

For first question: I can't answer, if using parameters biding is faster than not using it, because I haven't got opportunity to test it and because this is quite not related to this article! :)