| Dropping User takes 1 hour [message #133601] |
Fri, 19 August 2005 22:49  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I have a lot of old schemas left from previous tests. Some of them need to be reused for some new tests. The tests scrip suppose to drop them and then to recreate them. But here's the problem - to drop one of them takes about 1 hour... the size is about 2-5M - around 31 tables, 43 indexes, 31 sequences, 31 PK, 12 FK.
What will be the way to troubleshoot this problem?
After 1 hour dropping the old user, the new schema is created for another 1 hour instead of less then 2 min.
This use to be an easy process - we never had a problem with that. There's no errors, warnings, when opening EM console, there's no fidings... Everything looks perfect, but it's not as this happen with a single user on the box... Imagine that there are 50-60 users... And how they would be affected!!!
Any idea is greatly apprecited.
Thanks,mj
|
|
|
|
|
|
| Re: Dropping User takes 1 hour [message #133644 is a reply to message #133601] |
Sat, 20 August 2005 09:57  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I saw this case yesterday, but there's nothing I could use...
The dropping is done throught SQLPLUS with the command:
drop user user_name cascade;
Ora10.1.0.4, AIX5.2 all latest patches for both, LMT.
In the moment I issue the command for some logins, not for every one, the CPU jumps to 99 and the memory start swapping.
And I'm the only person on the box - sqlplus locally on the box, from the other machines as well.
Truncating and then dropping table by table will not work, I need to drop the whole schema. The goal for testing is to run the testing scripts. They are run locally on the box and call SQLPLUs from inside.
I tried this from my sqlplus as well. Same. It's consistent: for some users- drops them for a second, for some others - hours or usually hangs... I can get a pattern which ones...
It was working with no problems a few days ago with over 20 test scripts running on the box.
As long as I'm aware there have not been any changes on the box.
There's no errors in the logs. Bouncing the instance didn't help. Flashing the buffer, purging the recycle bin - it took 2h 52 min to purge the dba_recyclebin...
I did a temp fix creating a new tablespace for this users and the test scripts start working, but till the first drop of some of them, then start freezing again.
I need to make sure by Monday that the testing can keep going ...
Thanks a lot for the help.mj
|
|
|
|
|
|
| Re: Dropping User takes 1 hour [message #133731 is a reply to message #133645] |
Sun, 21 August 2005 23:58 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
Hi, and thanks a lot for the help.
I had similar experience with Ora 9.2.0.1.0. Oracle's advise was to bounce the instance and to apply 9.2.0.6 patch. Rebooting the instance helped. On Friday, bouncing the instance did not help in 10g. the CPU was again till the top - 99 and the memory usage was 98... There were only Oracle processes running on the box. Except that, why there's no errors or warnings anywhere? Even EM Console has reported no findings!!!
How can I check for sql_true? It could not be altered for the session as I did not see that in the script.
If this is a known issue, please, provide a solution. I still need to have the box fixed...
Thanks again, mj
|
|
|
|
|
|
|
|
|
|
| Re: Dropping User takes 1 hour [message #133870 is a reply to message #133601] |
Mon, 22 August 2005 08:31 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Just speculating...but you mentioned memory swapping. Is your shared pool memory size really small, or at least really small when compared with the task of dropping a user and all of their associated objects? Are you in automatic shared memory mode of 10g? Perhaps it has tuned your shared pool size for normal work, but when you do the large scale drop you use more resources than normal and oracle hasn't had time to catch up and adjust the parameters? Perhaps you can adjust the size yourself right before running the drop to see if that helps?
As far as the dba_recyclebin, I've had my purge command take some time on my test pc as well. Suggestion for that is to add the word purge to your drop statements so that the objects don't go to the recyclebin in the first place. Doing this on your creation and drop scripts should be fine, especially if you test it first. The recyclebin in my mind is more for recovering from ad hoc drop statements. Other option is to not do a dba_recyclebin purge, but just do a regular user recyclebin purge of the user owning your objects in question.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
