November 3, 2011 at 9:12 am
that can be done in Oracle.
run a relatively simple query that takes 16 + hours.
November 3, 2011 at 9:13 am
i have seen code that would disagree with this.
November 3, 2011 at 10:15 am
Not in my databases.
November 4, 2011 at 2:30 am
foxjazzG (11/3/2011)
Not in my databases.
The same could be said by many Oracle DBAs.
November 4, 2011 at 12:37 pm
foxjazzG (11/3/2011)
that can be done in Oracle.run a relatively simple query that takes 16 + hours.
I've seen that before - we call it extremelly poorly written code running in extremelly poorly designed database.
Have you traced it to figure out where all the time is spent? 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 4, 2011 at 1:39 pm
Actually, I have seen cases where simple looking queries would run for days at a time.
Heck, I had a rollback command once that took over a week to finish. On a command that had been running for about 30 seconds.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2011 at 2:30 pm
GSquared (11/4/2011)
... I had a rollback command once that took over a week to finish. On a command that had been running for about 30 seconds.
Most probably something blocked the rollback operation for about ((over-a-week) - (one minute)) 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 8, 2011 at 6:30 am
PaulB-TheOneAndOnly (11/5/2011)
GSquared (11/4/2011)
... I had a rollback command once that took over a week to finish. On a command that had been running for about 30 seconds.Most probably something blocked the rollback operation for about ((over-a-week) - (one minute)) 😀
No other activity in that database during that time. The rollback was listed as blocking everything else I tried to do, and not being blocked by anything. Nobody else was using the database at all (was in single-user mode).
Ended up having to rebuild the database from backups (easy) to a point in time before that transaction, after a forced reboot of the server.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2011 at 7:05 am
GSquared (11/8/2011)
PaulB-TheOneAndOnly (11/5/2011)
GSquared (11/4/2011)
... I had a rollback command once that took over a week to finish. On a command that had been running for about 30 seconds.Most probably something blocked the rollback operation for about ((over-a-week) - (one minute)) 😀
No other activity in that database during that time. The rollback was listed as blocking everything else I tried to do, and not being blocked by anything. Nobody else was using the database at all (was in single-user mode).
Do you mean "restricted session" at database level? That may explain it all, when set to restricted session Oracle allows only one transaction at a time to get a lock on a specific table therefore if one transaction (in this case a rolling back transaction) has a lock on the target table no other DML is allowed on it - it will show the rollback transaction blocking "everything" else.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 9, 2011 at 6:43 am
Restricted Session and Single-User aren't the same thing. Single-User just blocks anyone else from using the database at the same time. Doesn't restrict the number of transactions, et al.
Can block access, especially if some automatic task (like asynchronous stat rebuilds) is the "single user" and just keeps running, but if you're the only user (like I was), it just keeps others out of the database till you disconnect.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2011 at 12:43 pm
GSquared (11/9/2011)
Restricted Session and Single-User aren't the same thing.
mmmhh... are we still talking about Oracle here?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 10, 2011 at 11:19 am
powellstark (11/10/2011)
Reported as SPAM.
November 10, 2011 at 11:32 am
PaulB-TheOneAndOnly (11/9/2011)
GSquared (11/9/2011)
Restricted Session and Single-User aren't the same thing.mmmhh... are we still talking about Oracle here?
Sort of.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2011 at 11:21 am
GSquared (11/10/2011)
PaulB-TheOneAndOnly (11/9/2011)
GSquared (11/9/2011)
Restricted Session and Single-User aren't the same thing.mmmhh... are we still talking about Oracle here?
Sort of.
Good! because as far as I remember we only have "restricted session" in Oracle.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 15, 2011 at 2:43 am
It's never about how simple the query is, it's about how efficient the query is. It's like putting a V8 into a chevy spark and hoping for good fuel consumption because it's a small car
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply