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 ||
case
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
FROM ALL_CONS_COLUMNS C
inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
WHERE C.OWNER = B.OWNER
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
FROM ALL_TAB_COLUMNS A
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)
WHERE
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.
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
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.
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.
