How Oracle Enterprise stage reformats SQL with /*+rowid(p)*/

[shin0066]

Hi,

One of my SQL Query to select records from Oracle table causing an issues to retrieve the data from table. Job aborted after running about 10 hrs saying that "Snap shot too old".

We verified the Sql at the database side - query is reformatted at the database side by adding /*+rowid(p)*/ as hint and it is adding row id clause in the where condition, DBA's are saying that by adding the rowid hint is causing the job to abort after running long time.

SQL query - is just a join between 2 table joining on where Key/Indexed columns are, but still it is causing an issue.

I am just trying to understand why the query is reformatted to add hint and where condition? my guess is as we are running in Oracle Enterprise stage - it is splitting the query to run in multiple way (px), but how it is calculating the proper number of rowid's in where condition?

I am not agreeing with my DBA as they are pointing that it is DataStage's fault by adding the hint and rowid's. (As if i change the query to join on other non indexed column it is working fine).

If any one has any information please share how this Oracle EE Stage works and it is common in PX area to add this extra hint.

Any information is appreciated.

Thanks

[ArndW]

I think if you make your output Oracle stage run in sequential mode, or just use a 1-node configuration, then the hint will not be used. I don't know why your DBAs should be so certain that this is the cause of your snapshot performance issue, though.

[Ramona Reed]

We run 95 percent of our loads using Oracle Stage, and we have never seen it add a HINT on our queries. What is your SQL like?

[ds_developer]

I suspect it is the optimizer adding the hint and not DataStage. Do you see the hint when you look at the SQL in the log file?

John

[kris]

Snapshot too old error!!

See if Tom's Article
http://asktom.oracle.com/pls/asktom/f?p ... 5215756923
helps you.