FALLOUT FROM DELETING OWNER

[CBK]

I have an Oracle 8i database and recently had someone delete an owner using a variety of select statements. Since then I have been having extreme performance problems running reports.

Since learning of the incident I have examined the following:
report sql
sort_shared_size
shared pool
indexes
views

I am out of ideas and would greatly appreciate any hints or clues.

Thanks in advance

 

[dbtoo2001]

What is the SID's optimizer mode parameter?
Did you update statistics after the delete?

 

[CBK]

Preface: I am really a programmer who has been saddled with DBA duties.

Now, I am not sure what you are asking, any chance of dumbing it down a bit?

 

[hubud]

OPtimiser mode can be found by querying V$parameter where name = optimizer_mode. this is advised to be set as choose unless you are running a db older than 8.0 (i think).

Following this you can then ensure that statistics are up to date by running;

analyze <object_type> <object_name> compute statistics (or estimate statistics if table is big);

on each object in your db e.g. tables indexes. If your using toad you can do this very easily.

YOu can check when they were last analysed by selecting last_analyzed from dba_tables. This will give you the last date statistics were gathered. Statistics help the optimizer choose the best way to get information.

If your statistics are old then it might choose roots to getting information that are not optimal.



cheers

simmo

 

[SantaMufasa]

CBK,

Before we charge off to slay the &quot;Optimizer/Statistics Dragon&quot;, let's ensure that we readers understand what you say happened: &quot;...had someone delete an owner using a variety of select statements...&quot; Please clarify what this all means: What do you mean by &quot;owner&quot;? How many rows actually disappeared from how many tables? What kind for performance loss did you encounter? Did anything else change in your environment? What do you mean by, &quot;...a variety of select statements...&quot;?

Dave
Sandy, Utah, USA @ 18:07 GMT, 11:07 Mountain Time

 

[CBK]

I have taken a look at the tables and they appear as though they never have been analyzed.

Does this have anything to do with the fact they have indexes?

Any ideas?

 

[CBK]

SantaMufasa,

I work with a multi-user database that contains several owners contained within a tablespace. Each owner then owns several tables with that tablespace.

I was on vaction and we received an ora-01653 error, so one of my coworkers decided to drop an owner using drop table statements in place of drop owner cascade.

I return from vacation to a firestorm. Now some of our reports that were running fine hang and have to be cancelled. I went a ahead and completed dropping the owner using drop owner cascade to ensure I took care of everything.

Since it is not all reports, but rather reports querying our most populated view and tables I am at a loss. All reports that do not run use order by, group by and unions. We also have a high number of sorts that are being completed on disk rather than memory. I have so far adjusted the sort_area_size and extents on temp tablespace but they will not take effect until tomorrow morning (changed in sort_area_size in init.ora file) and we do a back-up each evening

Let me know if you need more data

Craig

 

[SantaMufasa]

CBK,

When you said, &quot;...recently had someone delete an owner...&quot;, I was worried that you meant an Oracle owner, and that is apparently what happened. I am appalled (as you must have been) to hear that someone started dropping users (business-critical information assets) just because they encountered an &quot;unable to extend table...&quot; error. (That's akin to shooting passengers because there aren't enough seats on an airplane!).

If you had someone willie-nillie dropping tables and users, then you have faaaaaar bigger problems than performance and statistics gathering. How do you plan to recover the dropped information assets? I'd work with recovering vital data first, then let's look at performance. Or am I missing something?

Dave
Sandy, Utah, USA @ 19:02 GMT, 12:02 Mountain Time

 

[CBK]

That particular owner is no longer around and data is stored on tape so recovery not applicable.

Any other thoughts?

 

[sem]

Dave, I think that if dropping user just worsened performance, that user schema probably contained only INDEXES on tables he didn't own, so those tables are probably untouched and no really VITAL DATA was lost.

Craig, you should perform common sql tuning: investigate execution plans of the worst queries and probably create missing indexes. Use statspack to catch most greedy statements. Don't forget to gather statistics on newly created indexes.



Regards, Dima

 

[CBK]

sem and santamufasa

Continuing with the problem, as I mentioned earlier my database has several owners who use the same reports and forms. The owner with the most populated tables is the one in which the reports hang.

I can run the reports for the other with no problem. I have increased the sort_area_size and although reduced the number of disk sorts, I am still having these reports hang.

still looking for clues

 

[sem]

I suppose that the one and the only clue is to find out what it really tries to do while hanging. Or are you suppose that somebody may answer like create index foo on table bar???

Regards, Dima