Long running Oracle SQL ..Implement stats in DataStage job

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Developer9
Premium Member
Premium Member
Posts: 187
Joined: Thu Apr 14, 2011 5:10 pm

Long running Oracle SQL ..Implement stats in DataStage job

Post by Developer9 »

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.
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Developer9
Premium Member
Premium Member
Posts: 187
Joined: Thu Apr 14, 2011 5:10 pm

Post by Developer9 »

@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 .
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply