Re: ADDM advices Oracle's application logic

On Jan 16, 1:29 pm, Helma <helma.vi..._at_hotmail.com> wrote:
> On 16 jan, 18:31, Cristian Cudizio <cristian.cudi..._at_yahoo.it> wrote:
>
>
>
>
>
> > On Jan 16, 5:30 pm, Arun Mathur <themath..._at_gmail.com> wrote:
>
> > > On Jan 16, 9:37 am, Helma <helma.vi..._at_hotmail.com> wrote:
>
> > > > Hello *,
>
> > > > I'm new to performance tuning and linux , so any pointers to speed up
> > > > my investigation is much appreciated.
>
> > > > I've just installed a 10.2.0.3 on a 64bit Linux AS. I created a few
> > > > TBS, with datafiles of 16GB each. The first file took about 3 minutes,
> > > > the last file took about 1 hour to create. My experience on other
> > > > machines is just a few minutes.
> > > > I'm looking at the ADDM advice:
>
> > > > "Individual database segments responsible for significant user I/O
> > > > wait were found."
>
> > > > Investigate application logic involving I/O on database object with id
> > > > 440.
> > > >         Database ObjectSYS.I_RLS2
>
> > > > where I_RLS2 is:
>
> > > > Name            I_RLS2
> > > > Schema          SYS
> > > > Tablespace      SYSTEM
> > > > Index Type      Normal
> > > > Status          VALID
>
> > > > Has anyone an idea what happened or where to look? What other elements
> > > > are needed for a proper diagnoses? Again, i am alone on the machine,
> > > > not other processes, users etc. Physical writing was the paramount i/o
> > > > activity, as expected.
>
> > > > Thanks!
>
> > > Hello,
>
> > > Two questions:
>
> > > 1) What commands did you issue when creating the tablespace(s) and
> > > associated datafile(s)?
> > > 2) Do the datafiles need to be 16G in size due to given requirements?
>
> > > Regards,
> > > Arun
>
> > I'm just curious to know also if your datafile are on a filesystem, a
> > raw device or ASM.
> > On my linux installations i use ASM and i've never got a similar
> > issue: i've noticed that creation
> > of the datafiles writes the entire space occupied by datafiles
> > monitoring disks usage with iostat.
> > However the question seems to be that you have created tablespace
> > dictionary managed, but it
> > is not the default so i think it not probable.
>
> > Regards,
>
> >  Cristian Cudiziohttp://oracledb.wordpress.comhttp://cristiancudizio.wordpress.com
>
> 1) What commands did you issue when creating the tablespace(s) and
> associated datafile(s)?
>
> CREATE TABLESPACE "prod_index" LOGGING
> DATAFILE '/oradata01/xxxxxxx/prod_index01.dbf' SIZE 16384M
> REUSE AUTOEXTEND OFF
> EXTENT MANAGEMENT LOCAL
> SEGMENT SPACE MANAGEMENT AUTO;
>
> 2) Do the datafiles need to be 16G in size due to given requirements?
>
> yes
>
> 3) I'm just curious to know also if your datafile are on a filesystem.
>
> yes- Hide quoted text -
>
> - Show quoted text -

Thanks. It may not hurt to trace the process that's creating the tablespace:

1. connect as a sysdba via SQL*Plus
2. run dbms_system_set.ev(<sid of process that is creating tablespace>,<serial# of process that is creating tablespace>, 10046,12,'');
3. tail the .trc file in your udump directory named with your process id. You should see the wait events associated with the tasks your process is currently running.

This is a good question. Good luck, and keep us posted.

Regards,
Arun Received on Thu Jan 17 2008 - 09:35:27 CST