Friday, August 27, 2010

Stale Oracle Statistics? Cached Execution Plan?

Today I had a very interesting issue. My Datastage developer had a job that ran forever and eventually out of temp tablespace and the job was aborted. The job was doing a single select query by joining 3 tables. I took the query out to sql*plus and it ran instantly.

By examing the query execution plan when running from the Datastage job and when running from sql*plus, they were different. How could that be? The two queries were exactly the same, why the execation plan were different. Were the execution plan cached somewhere when running from DataStage.

Anyway, updating the statisitcs on the table with 'analyze table ... compute statistics' resolved the issue.

Tuesday, August 10, 2010

Retrieve OID ORASSO Password

The user is orasso in the OID repository database, run ldapsearch from oid server, replacing password with the actual password of orcladmin, and db_service_name with the actual database service name, e.g. orcl.oracle.com
---------------------------------------------------------------
ldapsearch -p 389 -h laoid -D "cn=orcladmin" -w  password -b "orclresourcename=orasso, orclreferencename=db_service_name, cn=IAS Infrastructure Databases,cn=IAS,cn=Products,cn=OracleContext" -s base "objectclass=*" orclpasswordattribute