DSXchange

Posted: **Wed Jul 20, 2016 10:19 am**

Hi,

I have a production (multi instance) extract job which is running couple of hours daily for only one deployment data other deployments are running fine,While testing in QA for the root cause took only about 10-15 minutes when DBA collects stats before job execution.

Code: Select all

Oracle connector ---->extract.ds

SQL query is having multiple joins on tables , co-related sub -queries and also in and Not in statments in where clause

I am testing this following before sql statment in oracle connector stage

Code: Select all

EXEC DBMS_STATS.GATHER_TABLE_STATS('sysadm','pch_hours');

Director log :

Code: Select all

db_balance: The OCI function executeDirect returned status -1. Error code: 20,000, Error message: ORA-20000: Unable to analyze TABLE "SYSADM"."PCH_HOURS", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 23818
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1. (CC_OraUtils::handleBeforeAfterSQL, file CC_OraUtils.cpp, line 5,098)

I believe this is an access issue with application id for executing this stats but would like have input on the approach I am testing for Stats.

Thanks for the suggestions.

Posted: **Wed Jul 20, 2016 11:36 am**

On your approach, doing it before a read operation makes little sense.

Best would be to do it immediately after an operation that significantly alters data content (load/insert/update/delete).

Next best would be to have a scheduled DBA task.

Mike

Posted: **Sun Jul 24, 2016 6:52 pm**

You job's running user will certainly NOT have grants to analyze anything owned by sysadm... that is strictly DBA Only territory. If that is a "user" table, get it out of that schema.

Posted: **Thu Jul 28, 2016 9:49 am**

@Mike/Chulett,

We are looking out for a option of scheduled DBA task. I came to know that application ID doesn't have access privileges

Thank you .

Posted: **Thu Jul 28, 2016 12:06 pm**

A typical solution (and what we do here) is have a DBA created procedure to do the actual task which our functional ETL user is then granted the privleges needed to execute.

DSXchange

Long running Oracle SQL ..Implement stats in DataStage job

Long running Oracle SQL ..Implement stats in DataStage job