Couple of DBA questions, please advice

[farrokhp]

I just start to take care of a new production database and I'd like you to give me some advice about these questions :


1- When I query V$SESSION I saw about 20 INACTIVE sessions and 1 or active one. Is it good idea to kill these session or find out what it this or just ignore!!


2- I have freespace fragmentations as below

DATA Tablespace about 126 total count
Rollback Tablespace about 2600 total count
TMP Tablespace about 10023 total count

Is that too bad and what's the best way to make it better


3- Their DB is about 20 GIG and not too busy only every months they have a massive email brodcast for 1000000 member in table which I guess need a huge rollback segment
do I need to tune Rollback seg or not ? How ?


Thanks Guys , please guide!!

[grjohnson]

1). INACTIVE sessions, are sessions that are connected but arn't processing at that moment. e.g. If you log into SQL/PLUS and then walk away from your computer to get a drink of water, your session is INACTIVE. Wouldn't you be annoyed if you can back and your session had been killed?

2). Freespace fragmentation - You could have freespace holes in your tablespaces, this mightn't be a problem if the fragmentated spaces are all standard sizes. i.e. RBS tablespace, the fragmentation sizes should be the size of the extents of the RBS segments (Assuming all RBS segs are the same extent sizes). TEMP should be the same, freespace frag should be the same size of SORT_AREA_SIZE variable. YOur DATA tablespace would probably be of highest concern. What you might want to look at is create 3 Tablespaces ie..

DATA_SML (INITIAL 1M NEXT 1M)
DATA_MED (INITIAL 10M NEXT 10M)
DATA_LGE (INITIAL 50M NEXT 50M)

(Alternatively use LMT with Uniform extents)

(Just an example). And then keep associated sized tables (indexes in another three tablespaces), in each tablepace. The idea is to keep ALL object in an associated tablespace to have ALL the same INITIAL and NEXT extent, thereby enabling objects to reuse extents previously occupied by other segments' extents). Oh, also, make sure you temp tablespaec is on type TEMPORARY.

3). Not sure what you mean by this, or how the email broadcast use Rollback segments.

Cheers,

[farrokhp]

Thnaks a lot grjohnson for your good response, I need to ask you alittle bit more again

1- How can I see how much time the session is INACTIVE ? May be it INACTIVE for Weeks!!! Script you have?

2- I looked at the DATA Tablespace freespace frags is not equal to size of extent, what is the best I can do now? Coalse TS resolve the frags completely or not?

3- Sorry When we broadcast 1000000 emails the batch job also insert about 500000 records in bunch of tables, so it is a very DML intensive process, my question is what I have to be careful?? Having a big RB, Having Several big Redo log, and what else can blow out when huge insert happen ??

Thanks Buddy

[grjohnson]

Damn, I typed the incorrect password and lost my whole post... jeez!.

Anyway,

1 - select username, status, logon_time from v$session
where username is not null
order by logon_time desc;

2 - Coalecse will help, but possibly on marginlly, remeber COALESCE only merges contiguous free extents. Smaller individual extents between objects in a tablespaces won't be coeaseced and therefore may never be used by a segment again. But, give the COALESCE a go for sure.

3 - Rather than a jumbo RBS segment, try breaking hte process up with a number of commits. I assume you are porcessing via a loop. Put in a counter and commit every 100,000 rows process or something. It makes the management of the task alot easier.

Cheers,