Yii 1.1:increasing AR performance in connections with Oracle

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

An approach currently presented into COCISCHEMA-I.E. Basing on All_objects, All_tab_columns and All_cons_columns are Probab Ly 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 m Ay run more than one minute (up to even twenty minutes at a very separate cases), which causes for example Gii to-up p ermanently with timeout error message, if PHP script execution. This situation is presented for example in this post.

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

One of possible solutions is to introduce new set of, that'll hold information on tables and views used in a Proje Ct. This is a kind to caching on database side.

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

This looks pretty a much-like using Schemacachingduration property, but has some advantages:in most situations, caching in The database is faster than using caching component ("The" "," the ". Results at"), using schemacachingduration not always ORACLE-I.E. Does not provides noticeable performance upgrade, on some slow servers using proposed solutions enables Usin G Gii, while without it this tool are unusable (here). Building Caching Tables

Here are a example SQL statements that builds up these tables:

create table Www_tab_columns (column_id number is NOT NULL, table_name VARCHAR2 is not NULL, COLUMN_NAME VARCHAR2 is not NULL, data_t
Ype VARCHAR2 (MB) not NULL, nullable char (1), Data_default VARCHAR2 (m), key char (1));
Comment on column www_tab_columns.nullable is ' y/n ';
Comment on column www_tab_columns.key is ' p/null ';
 
ALTER TABLE WWW_TAB_COLUMNS ADD constraint Pk_www_tab_columns PRIMARY KEY (table_name, COLUMN_NAME);
CREATE TABLE Www_tables (table_name VARCHAR2 () not NULL);
 
ALTER TABLE Www_tables ADD constraint Pk_www_tables PRIMARY KEY (table_name); CREATE TABLE Www_tab_cons (table_name VARCHAR2 () NOT NULL, COLUMN_NAME VARCHAR2 (x) not NULL, Positi On number is not NULL, r_constraint_name VARCHAR2 is not NULL,