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
How Oracle Enterprise stage reformats SQL with /*+rowid(p)*/
Moderators: chulett, rschirm, roy
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 7
- Joined: Thu Nov 17, 2005 7:22 am
-
- Premium Member
- Posts: 224
- Joined: Tue Sep 24, 2002 7:32 am
- Location: Denver, CO USA
Re: How Oracle Enterprise stage reformats SQL with /*+rowid(
Snapshot too old error!!
See if Tom's Article
http://asktom.oracle.com/pls/asktom/f?p ... 5215756923
helps you.
See if Tom's Article
http://asktom.oracle.com/pls/asktom/f?p ... 5215756923
helps you.
~Kris