Re: ADDM advices Oracle's application logic

On 17 jan, 16:35, Arun Mathur <themath..._at_gmail.com> wrote:
> 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

Hello *,

Thank you for your thoughts. The problem didn't reproduce when i created an other 16Gb file with the 10046 trace on. What happend is the following: I saw that the 0+1raid disks had 2 partitions. I preferred to undo this split in logical + physical drives, so i combined the 2 partitions into one big one. But i didn't rebuild the filesystem (with the mkfs3 ), and now i saw afterwards that the new partition shows only half the space.
After i recreated the filesystem and put the databack on it, i ran the command for an extra datafile with the 10046 trace, and it took 8 minutes, which seems acceptable for 16Gb. ( how fast are yours?)

Thanks again,
Helma Received on Fri Jan 18 2008 - 01:04:49 CST