Archive

Posts Tagged ‘orac’

Invalid DBMS_SQLPA package and poor database performance.

Recently, when I delivered Oracle 10gR2 (10.2.0.4) production database to the users, they started to claim that application is working very slowly. I’ve checked server and noticed unusual CPU overload. And then started to dig deeper. Finally, I noticed that I have a few invalid objects in database (what was strange because I could swear that everything was perfect just few hours before).
Anyway, I tried to recompile invalid object and left with one that I couldn’t recompile – DBMS_SQLPA. In the Metalink I found something about it:
– note 565600.1 ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA
– bug 6893073 – DBMS_SQLPA IS INVALID ORA-904: “OTHER_XML”: INVALID IDENTIFIER

And above could happen after upgrade to Oracle 10.2.0.4 – but the problem is that I didn’t upgrade to this version (at least not at that time). So, to make long story short, below you have the solution to recompile invalid DBMS_SQLPA package. Log in to the sqlplus (SYS AS SYSDBA) and execute the following commands:

drop table plan_table;
@ORACLE_HOME\rdbms\admin\utlxplan.sql
@ORACLE_HOME\rdbms\admin\prvtspao.plb
@ORACLE_HOME\rdbms\admin\utlrp.sql

To verify if everything is valid execute the following command:

select substr(comp_id,1,15) comp_id, substr(comp_name,1,30) comp_name,
substr(version,1,10) version, status from dba_registry order by modified;
Advertisements
Categories: Oracle, RDBMS Tags: , ,