Couple of DBA questions, please advice
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Couple of DBA questions, please advice

  1. #1
    Join Date
    Nov 2000
    Posts
    416
    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!!

    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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,
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Nov 2000
    Posts
    416
    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

    An ounce of prevention is worth a pound of cure

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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,
    OCP 8i, 9i DBA
    Brisbane Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width