In Schema class file - ALL_TAB_COLUMNS query sucks all my DB CPU

Hello Memebers,

I am using the following query in the Schema Class file, which is getting executed more than 3,000,000 times in Production DB and sucks most of the DB CPU which cause performance degradation

SELECT a.column_name, a.data_type ||


    when data_precision is not null

        then '(' || a.data_precision ||

                case when a.data_scale > 0 then ',' || a.data_scale else '' end

            || ')'

    when data_type = 'DATE' then ''

    when data_type = 'NUMBER' then ''

    else '(' || to_char(a.data_length) || ')'

end as data_type,

a.nullable, a.data_default,

(   SELECT D.constraint_type


    inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name


       and C.table_name = B.object_name

       and C.column_name = A.column_name

       and D.constraint_type = 'P') as Key,

com.comments as column_comment


inner join ALL_OBJECTS B ON b.owner = a.owner and ltrim(B.OBJECT_NAME) = ltrim(A.TABLE_NAME)

LEFT JOIN user_col_comments com ON (A.table_name = com.table_name AND A.column_name = com.column_name)


a.owner = '{$schemaName}'

and (b.object_type = 'TABLE' or b.object_type = 'VIEW')

and b.object_name = '{$tableName}'

ORDER by a.column_id;

I want to know why this query is executed for every time for every query. In production, per day one time the framework checks the schema is enough. Kindly give me some ideas to solve this issue.

An ActiveRecord class needs to query db schema in order to specify its column-based attributes.

And it can be (and should be) cached in production environment.


Thanks for the solving my problem. We have almost 200 tables in oracle database. I need one more help

Which cache is best to increase the performance, like dbcache, memcache, filecache, rediscache. How to refresh daily only once?

I don’t know for sure. But I think some memory-based cache is suitable for schema cache.

Personally I’m using APC for it.

Set ‘schemaCacheDuration’. One day is 60 x 60 * 24 seconds.


I too got the same problem. I used PDO_OCI extension of Yii2

And there is no need of cache yii2 works perfect with Oracle.

Hi Arokiya Johnson,

Thanks for your advice.

Kindly confirm, through PDO_OCI, it will call the schema details or not.

I want to avoid executing the schema query every time it executes the actual query as I said earlier.



Hi Siva,

It will call the schema but did not take much time. Because I have override the default OCI driver of Yii2 with the PDO_OCI, It is working perfect.

Even this didn’t work then you can make use of the following links

Hi Johnson,

I am trying to insert a record in oracle db table using seq.nextval as follows

$nextVal = new CDbExpression(‘T_ACTION_SEQ.NEXTVAL’);

$stpaModel = new T_ACTION();

$stpaModel->TA_ID = $nextVal;

$stpaModel->LAN = ‘PHR12345’;

$stpaModel->TOKEN_ID = $pregenratedTokenNumber;

$stpaModel->CREATED_AT = date(‘d-M-y h:i A’);


I want to return last inserted TA_ID (i.e $stpaModel->TA_ID)

How to achieve this. I tried using lastinsertid(), but no use. Can help me



Hi Siva,

May be u can make use of the beforeSave from your model for example. I hope this is straight way rather than cdbexpression as well will work fine.


     $pk = Yii::app()->db->createCommand() ->select('T_ACTION_SEQ.NEXTVAL PK') ->from('dual')->queryRow(); //Your DB Command

     $stpaModel->TA_ID = $pk['PK']; 



I executed as you said. But it shows error like select "T_ACTION_SEQ.NEXTVAL" as PK from dual. invalid identifier. We should not send quotes for T_ACTION_SEQ.NEXTVAL.

So i used CDbExpression as follows. Now also I am getting the same error.

$nextVal = new CDbExpression(‘T_ACTION_SEQ.NEXTVAL’);

$pk = Yii::app()->db->createCommand()->select->($nextval.’ PK’)->from(‘dual’)->queryRow(); //Your DB Command

$this->TQMS_ID = $pk[‘PK’];

Kindly help me



Hi Siva,

Go your way of simplifying …

Yii::app()->db->createCommand() ->createCommand('SELECT T_ACTION_SEQ.NEXTVAL PK FROM dual')->queryRow();

I hope this will work

Which oci PDO emulation layer works well? We may hint that in docs.

Hi Samdark,

I don’t remember which file I have modified Since the project was moved to production 2 years ago. I have attached the db folder as zip file. for your reference.

Note: This zip file may not have the updated yii2 core files. But you can use it for references. As well the PHP version also bit older.

The app is in production and working well! Before these files changes, The application was very slow in the performance.

  • We use <TABLE_NAME>_SEQ for sequences.

Hi Arokiya Johnson,


Your code worked well and solved my issue. But only thing is I want to insert a record and return the last insereted ID. Id is generated using seq.nextval.

Now using your code, I am fetching the nextval first into a variable and that value is passed to the id field in the table.

Its like to insert a record, I am hitting the db two times.

Kindly give me idea to solve this



Hi Siva,

Please make use of the

Hi Arokia Johnson,

Thanks for your help. Issue resolve