Drop USER CASCADE TOO SLOW

Problem:  The command “drop user hrtest cascade; ” is slow and probably dropping 10 objects per second.

Tried:

alter session set optimizer_mode = CHOOSE; — no good

exec dbms_stats.gather_database_stats;   — take too long to complete

Solution from Tom Kyte

- http://www.oracle.com/technology/oramag/oracle/06-jul/o46asktom.html

We have an application that creates users and places private synonyms in users’ accounts. On occasion a security administrator drops multiple users who no longer need access to the database. It can take about two minutes to drop a user that contains about a thousand synonyms. I’m getting complaints that it takes too long to remove the obsolete accounts. Apart from transitioning the system to using public synonyms, do you have any suggestions on improving the performance of the DROP USER command?

It is all about perception. Whenever I have a long-running process, I think about how I can “background” it so an unlucky end user never has to wait for it to complete. If the end user doesn’t have to wait for it, it will seem instantaneous.

So, move long-running processes into the background, and the end users think, “Wow, this is really fast!” What I recommend is to turn the process of DROP USER USERNAME CASCADE into the following:

1. ALTER USER USERNAME LOCK; (The account is disabled, so the “secure goal” is achieved.)
2. dbms_job.submit( l_job, 'execute immediate ''drop user a cascade'';' );
3. commit;

And give the user a message immediately that says “OK.” The locking of the account will achieve the “goal” of dropping the user (by removing access), and the actual dropping of the user schema—which might take a bit of time—will happen shortly after the COMMIT, in the background, without making the end user wait. As far as the end user is concerned, the act of dropping the user is instantaneous and the response time is always the same. Consistency is important to end users.

I do this with lots of apparently slow things—hide the real work in the background and let the end users continue, and they think the application is much faster than it is.

So,

1.

declare
l_job binary_integer;
begin
dbms_job.submit( l_job, ‘execute immediate ”drop user hrtest cascade”;’ );
commit;
dbms_output.put_line(l_job);
end;
/

Execution result:

SYS@SPRT> declare
2  l_job binary_integer;
3  begin
4  dbms_job.submit( l_job, ‘execute immediate ”drop user hrtest cascade”;’ );
5  commit;
6  dbms_output.put_line(l_job);
7  end;
8  /
179824

PL/SQL procedure successfully completed.

SYS@SPRT>

2. select TOTAL_TIME, failures from dba_jobs where job = 179824;

Execution result:

SYSTEM@SPRT> select TOTAL_TIME, failures from dba_jobs where job = 179824;

TOTAL_TIME   FAILURES
———- ———-
54

SYSTEM@SPRT>

Advertisement