Re: ADDM advices Oracle's application logic

On Jan 18, 8:04 am, Helma <helma.vi..._at_hotmail.com> wrote:
> 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

I have made some monitorings with iostat on Linux, with ASM and i've seen something beetween 95 MBytes/second and 160 MBytes/second. 8 minutes for 16 GBytes means about 33 Mbyte / second: it is not a lot, i've seen about 100 MByte /Second on a test server with a SATA disk. I've not data about creation of datafiles on filesystem.

Regards,

 Cristian Cudizio
http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com Received on Fri Jan 18 2008 - 03:59:37 CST