Performance issues on Sun - solved
Thanks to Cary, Jared, Ferenc whose inputs helped a lot in solving the problem. Ferenc's document did the trick... Once we converted the files to use Qio, the performance was much much better... Thanks a lot... Babu _ This e-mail transmission and any attachments to it are intended solely for the use of the individual or entity to whom it is addressed and may contain confidential and privileged information. If you are not the intended recipient, your use, forwarding, printing, storing, disseminating, distribution, or copying of this communication is prohibited. If you received this communication in error, please notify the sender immediately by replying to this message and delete it from your computer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: performance issues on sun
My bad. I should also have posted the 10046 trace. I did a 10046 trace and also a STATSPACK report. Of the total time, more than 50% of the time is spent waiting on DIRECT PATH WRITE and around 40% of the time it waits on DIRECT PATH READ. This is what prompted me to do a truss and see what it is doing.. From one of the Veritas whitepapers - I found this Quick I/O allows databases to access regular files on a VxFS file system as raw character devices, improving throughput for Oracle databases. Unlike raw devices, Quick I/O files can still be managed as regular UNIX files. There are three requirements to use Quick I/O: 1. Quick I/O driver (VRTSqio) must be loaded before a regular file can be accessed through the Quick I/O interface. 2. Files must be preallocated on a VxFS file system. The file must be preallocated because the file cannot be extended through writes via its Quick I/O interface. This preallocation can be done using the qiomkfile command. This command ensures that the file is created as a single large extent, or as a chain of direct extents. Using direct extents is inherently faster than using indirect extents. 3. The file must be accessed via its Quick I/O name extension (::cdev:vxfs:). In a VxFS file system, a file can be accessed using two types of interface: regular file and device file. The device file interface allows a regular file to be accessed as a raw character device. This is achieved by using the Quick I/O naming extension of ::cdev:vxfs: while accessing a regular file. For example, a file named system.dbf can be accessed as a raw character device when the name system.dbf::cdev:vxfs: is used for database access. Converting Oracle Database Files on VERITAS File System to use Quick/IO The scripts getdbfiles.sh and mkqio.sh are provided to easily change Oracle database files to use Quick I/O. The database files must be on VxFS file systems before they can be converted. The getdbfiles.sh script is a shell script that can be run by the Oracle DBA (with appropriate user ID) while the database instance is up and running. This script extracts the filenames from the system tables of the database and stores the filenames in a file called mkqio.dat. Alternatively, you can manually create the mkqio.dat file containing the Oracle database filenames to convert for use with Quick I/O. The mkqio.sh script processes a list of filenames in the file mkqio.dat and converts them to use Quick I/O. This conversion process should be performed while the database is closed. The mkqio.sh script must also be run by the Oracle user of the database instance to avoid any permission problems. I think Quick IO is needed on VxFS to perform KAIO calls. Have anyone done this before? Any input is greatly appreciated. Babu Cary Millsap [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] sos.com cc: Sent by: Subject: RE: performance issues on sun [EMAIL PROTECTED] 02/25/03 05:23 PM Please respond to ORACLE-L I wish now that I hadn't deleted what I composed this morning... It was this: People probably get sick of seeing me say the same thing over and over and over... You have some interesting information from the truss that you've done. But you can't tell how long
RE: performance issues on sun
The failed KAIO calls normally happen very quickly and have little impact on your system. I did run into some buggy Hitachi/Solaris/Vxfs configuration once that took a very long time to fail the KAIO calls. You could always just set disk_asynch_io=false in init.ora. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/26/2003 04:44 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: performance issues on sun My bad. I should also have posted the 10046 trace. I did a 10046 trace and also a STATSPACK report. Of the total time, more than 50% of the time is spent waiting on DIRECT PATH WRITE and around 40% of the time it waits on DIRECT PATH READ. This is what prompted me to do a truss and see what it is doing.. From one of the Veritas whitepapers - I found this Quick I/O allows databases to access regular files on a VxFS file system as raw character devices, improving throughput for Oracle databases. Unlike raw devices, Quick I/O files can still be managed as regular UNIX files. There are three requirements to use Quick I/O: 1. Quick I/O driver (VRTSqio) must be loaded before a regular file can be accessed through the Quick I/O interface. 2. Files must be preallocated on a VxFS file system. The file must be preallocated because the file cannot be extended through writes via its Quick I/O interface. This preallocation can be done using the qiomkfile command. This command ensures that the file is created as a single large extent, or as a chain of direct extents. Using direct extents is inherently faster than using indirect extents. 3. The file must be accessed via its Quick I/O name extension (::cdev:vxfs:). In a VxFS file system, a file can be accessed using two types of interface: regular file and device file. The device file interface allows a regular file to be accessed as a raw character device. This is achieved by using the Quick I/O naming extension of ::cdev:vxfs: while accessing a regular file. For example, a file named system.dbf can be accessed as a raw character device when the name system.dbf::cdev:vxfs: is used for database access. Converting Oracle Database Files on VERITAS File System to use Quick/IO The scripts getdbfiles.sh and mkqio.sh are provided to easily change Oracle database files to use Quick I/O. The database files must be on VxFS file systems before they can be converted. The getdbfiles.sh script is a shell script that can be run by the Oracle DBA (with appropriate user ID) while the database instance is up and running. This script extracts the filenames from the system tables of the database and stores the filenames in a file called mkqio.dat. Alternatively, you can manually create the mkqio.dat file containing the Oracle database filenames to convert for use with Quick I/O. The mkqio.sh script processes a list of filenames in the file mkqio.dat and converts them to use Quick I/O. This conversion process should be performed while the database is closed. The mkqio.sh script must also be run by the Oracle user of the database instance to avoid any permission problems. I think Quick IO is needed on VxFS to perform KAIO calls. Have anyone done this before? Any input is greatly appreciated. Babu Cary Millsap [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] sos.com cc: Sent by: Subject: RE: performance issues on sun [EMAIL PROTECTED] 02/25/03 05:23 PM Please respond to ORACLE-L I wish now that I hadn't deleted what I composed this morning... It was this: People probably get sick of seeing me say the same thing over and over and over... You have some interesting information from the truss that you've done. But you can't tell how long something took by counting how many times it happened. The easiest way to determine what's consuming the most *time* is to use extended SQL trace (event 10046 level 8). The resulting trace file will tell you exactly where your time has gone, and it will enable you to determine whether your performance problem is a result of the kaio calls or not. You'll probably find that the system is doing what you suspect: issuing an async write call, failing, and then calling a synchronous write call. However, without knowing the impact of this behavior upon response time, it's hard to know whether the time you invest into checking stuff and fixing stuff is worth anything. The worst feeling is to invest your time into fixing something, succeeding, and then finding you've made no impact because the thing you fixed accounts for only a small amount of response time. ...Find out what activity is consuming the largest chunk of your response time
RE: performance issues on sun
Babu, Most people assume that the only way to fix something like this is to improve the per-call latency time. But response time equals number of calls (N) times the average latency per call (C): R = N x C. Your goal is to reduce R. Reducing N often provides far superior leverage in meeting this goal. I've forgotten whether you said why your system was doing the direct path operations. Often these operations are the result of sorting. If this is your case, there are several things to consider, including (and in no particular order): - Does the business function really require the sort? - Is the execution plan you're using actually optimal for your query? For example, maybe you're doing a sort-merge join in a case where a hash join might be more efficient? - Can a composite index's columns be rearranged to match the ORDER BY clause, to avoid the sort entirely? - Can the sort be performed using fewer merge runs by using techniques that Jonathan Lewis and Steve Adams describe on their web sites? - Can the sort to disk be avoided entirely by careful manipulation of the SESSION's sort_area_size? - There are probably at least fifteen more that I'm not thinking of at the moment... Hope this helps... Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 6:44 AM To: Multiple recipients of list ORACLE-L My bad. I should also have posted the 10046 trace. I did a 10046 trace and also a STATSPACK report. Of the total time, more than 50% of the time is spent waiting on DIRECT PATH WRITE and around 40% of the time it waits on DIRECT PATH READ. This is what prompted me to do a truss and see what it is doing.. From one of the Veritas whitepapers - I found this Quick I/O allows databases to access regular files on a VxFS file system as raw character devices, improving throughput for Oracle databases. Unlike raw devices, Quick I/O files can still be managed as regular UNIX files. There are three requirements to use Quick I/O: 1. Quick I/O driver (VRTSqio) must be loaded before a regular file can be accessed through the Quick I/O interface. 2. Files must be preallocated on a VxFS file system. The file must be preallocated because the file cannot be extended through writes via its Quick I/O interface. This preallocation can be done using the qiomkfile command. This command ensures that the file is created as a single large extent, or as a chain of direct extents. Using direct extents is inherently faster than using indirect extents. 3. The file must be accessed via its Quick I/O name extension (::cdev:vxfs:). In a VxFS file system, a file can be accessed using two types of interface: regular file and device file. The device file interface allows a regular file to be accessed as a raw character device. This is achieved by using the Quick I/O naming extension of ::cdev:vxfs: while accessing a regular file. For example, a file named system.dbf can be accessed as a raw character device when the name system.dbf::cdev:vxfs: is used for database access. Converting Oracle Database Files on VERITAS File System to use Quick/IO The scripts getdbfiles.sh and mkqio.sh are provided to easily change Oracle database files to use Quick I/O. The database files must be on VxFS file systems before they can be converted. The getdbfiles.sh script is a shell script that can be run by the Oracle DBA (with appropriate user ID) while the database instance is up and running. This script extracts the filenames from the system tables of the database and stores the filenames in a file called mkqio.dat. Alternatively, you can manually create the mkqio.dat file containing the Oracle database filenames to convert for use with Quick I/O. The mkqio.sh script processes a list of filenames in the file mkqio.dat and converts them to use Quick I/O. This conversion process should be performed while the database is closed. The mkqio.sh script must also be run by the Oracle user of the database instance to avoid any permission problems. I think Quick IO is needed on VxFS to perform KAIO calls. Have anyone done this before? Any input is greatly appreciated. Babu Cary Millsap [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] sos.com cc: Sent by: Subject: RE: performance issues on sun [EMAIL PROTECTED] 02/25/03 05:23 PM Please respond to ORACLE-L I wish now that I hadn't deleted what I composed this morning... It was this: People probably get sick of seeing me say the same thing over and over and over... You have some interesting information from the truss that you've done. But you can't
Re: performance issues on sun
Agree with Jared. However, if you have determined that the synchronous IO and all that is slowing you down, and you want to proceed to direct IO, and Qio is what you want, I would suggest that you get Veritas to help you. It will take about 1 - 2 hours to set up for a 100 GB DB. I have a document outlining all the steps I took to do this (as root), I will forward to you from my other email). You will have to drop and recreate your TEMP tablespace as outlined in the doc. Cheers : Ferenc Mantfeld Dreaming costs you nothing. Not dreaming costs you everything. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, February 27, 2003 5:21 AM The failed KAIO calls normally happen very quickly and have little impact on your system. I did run into some buggy Hitachi/Solaris/Vxfs configuration once that took a very long time to fail the KAIO calls. You could always just set disk_asynch_io=false in init.ora. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/26/2003 04:44 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: performance issues on sun My bad. I should also have posted the 10046 trace. I did a 10046 trace and also a STATSPACK report. Of the total time, more than 50% of the time is spent waiting on DIRECT PATH WRITE and around 40% of the time it waits on DIRECT PATH READ. This is what prompted me to do a truss and see what it is doing.. From one of the Veritas whitepapers - I found this Quick I/O allows databases to access regular files on a VxFS file system as raw character devices, improving throughput for Oracle databases. Unlike raw devices, Quick I/O files can still be managed as regular UNIX files. There are three requirements to use Quick I/O: 1. Quick I/O driver (VRTSqio) must be loaded before a regular file can be accessed through the Quick I/O interface. 2. Files must be preallocated on a VxFS file system. The file must be preallocated because the file cannot be extended through writes via its Quick I/O interface. This preallocation can be done using the qiomkfile command. This command ensures that the file is created as a single large extent, or as a chain of direct extents. Using direct extents is inherently faster than using indirect extents. 3. The file must be accessed via its Quick I/O name extension (::cdev:vxfs:). In a VxFS file system, a file can be accessed using two types of interface: regular file and device file. The device file interface allows a regular file to be accessed as a raw character device. This is achieved by using the Quick I/O naming extension of ::cdev:vxfs: while accessing a regular file. For example, a file named system.dbf can be accessed as a raw character device when the name system.dbf::cdev:vxfs: is used for database access. Converting Oracle Database Files on VERITAS File System to use Quick/IO The scripts getdbfiles.sh and mkqio.sh are provided to easily change Oracle database files to use Quick I/O. The database files must be on VxFS file systems before they can be converted. The getdbfiles.sh script is a shell script that can be run by the Oracle DBA (with appropriate user ID) while the database instance is up and running. This script extracts the filenames from the system tables of the database and stores the filenames in a file called mkqio.dat. Alternatively, you can manually create the mkqio.dat file containing the Oracle database filenames to convert for use with Quick I/O. The mkqio.sh script processes a list of filenames in the file mkqio.dat and converts them to use Quick I/O. This conversion process should be performed while the database is closed. The mkqio.sh script must also be run by the Oracle user of the database instance to avoid any permission problems. I think Quick IO is needed on VxFS to perform KAIO calls. Have anyone done this before? Any input is greatly appreciated. Babu Cary Millsap [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] sos.com cc: Sent by: Subject: RE: performance issues on sun [EMAIL PROTECTED] 02/25/03 05:23 PM Please respond to ORACLE-L I wish now that I hadn't deleted what I composed this morning... It was this: People probably get sick of seeing me say the same thing over and over and over... You have some interesting information from the truss that you've done. But you can't tell how long something took by counting how many times it happened. The easiest way to determine what's consuming the most *time* is to use extended SQL trace (event 10046 level 8). The resulting trace file
Re: performance issues on sun
Ferenc Thanks for the document. I am working with the SAs to try to implement it. Will update the results once complete.. Thanks a lot... Babu Ferenc Mantfeld [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] us.net.au cc: Sent by: Subject: Re: performance issues on sun [EMAIL PROTECTED] 02/26/03 02:14 PM Please respond to ORACLE-L Agree with Jared. However, if you have determined that the synchronous IO and all that is slowing you down, and you want to proceed to direct IO, and Qio is what you want, I would suggest that you get Veritas to help you. It will take about 1 - 2 hours to set up for a 100 GB DB. I have a document outlining all the steps I took to do this (as root), I will forward to you from my other email). You will have to drop and recreate your TEMP tablespace as outlined in the doc. Cheers : Ferenc Mantfeld Dreaming costs you nothing. Not dreaming costs you everything. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, February 27, 2003 5:21 AM The failed KAIO calls normally happen very quickly and have little impact on your system. I did run into some buggy Hitachi/Solaris/Vxfs configuration once that took a very long time to fail the KAIO calls. You could always just set disk_asynch_io=false in init.ora. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/26/2003 04:44 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: performance issues on sun My bad. I should also have posted the 10046 trace. I did a 10046 trace and also a STATSPACK report. Of the total time, more than 50% of the time is spent waiting on DIRECT PATH WRITE and around 40% of the time it waits on DIRECT PATH READ. This is what prompted me to do a truss and see what it is doing.. From one of the Veritas whitepapers - I found this Quick I/O allows databases to access regular files on a VxFS file system as raw character devices, improving throughput for Oracle databases. Unlike raw devices, Quick I/O files can still be managed as regular UNIX files. There are three requirements to use Quick I/O: 1. Quick I/O driver (VRTSqio) must be loaded before a regular file can be accessed through the Quick I/O interface. 2. Files must be preallocated on a VxFS file system. The file must be preallocated because the file cannot be extended through writes via its Quick I/O interface. This preallocation can be done using the qiomkfile command. This command ensures that the file is created as a single large extent, or as a chain of direct extents. Using direct extents is inherently faster than using indirect extents. 3. The file must be accessed via its Quick I/O name extension (::cdev:vxfs:). In a VxFS file system, a file can be accessed using two types of interface: regular file and device file. The device file interface allows a regular file to be accessed as a raw character device. This is achieved by using the Quick I/O naming extension of ::cdev:vxfs: while accessing a regular file. For example, a file named system.dbf can be accessed as a raw character device when the name
performance issues on sun
All We are attempting to move some applications off Compaq T64 into Sun Solaris 8 and running into performance issues. I am trying to rebuild an index which is taking more than 3 1/2 hours while it used to take 20 min on T64. I find most of the waits on DIRECT PATH READS and DIRECT PATH WRITES. The index tablespace and the temporary tablespace are on separate mountpoints which reside on separate disks. I am doing a truss on the session and see that its doing the following kaio(AIOWAIT, 0x) Err#22 EINVAL pread(364, \b02\0\0\v\099E1 f h ECB.., 1048576, 0x26784000) = 1048576 kaio(AIOWAIT, 0x) Err#22 EINVAL lwp_cond_wait(0x7CED7F70, 0x7CED7F80, 0x) = 0 pwrite(408, 0602\0\0\nC41007 f h SDD.., 49152, 0x10401C000) = 49152 pwrite(408, 0602\0\0\nC410\n f h SDD.., 49152, 0x104028000) = 49152 pwrite(408, 0602\0\0\nC410\r f h SDD.., 49152, 0x104034000) = 49152 pwrite(408, 0602\0\0\nC41010 f h SDD.., 49152, 0x10404) = 49152 pwrite(408, 0602\0\0\nC41013 f h SDD.., 49152, 0x10404C000) = 49152 pwrite(408, 0602\0\0\nC41016 f h SDD.., 49152, 0x104058000) = 49152 pwrite(408, 0602\0\0\nC41019 f h SDD.., 49152, 0x104064000) = 49152 pwrite(408, 0602\0\0\nC4101C f h SDD.., 49152, 0x10407) = 49152 pwrite(408, 0602\0\0\nC4101F f h SDD.., 49152, 0x10407C000) = 49152 pwrite(408, 0602\0\0\nC410 f h SDD.., 49152, 0x104088000) = 49152 pwrite(408, 0602\0\0\nC410 % f h SDD.., 49152, 0x104094000) = 49152 pwrite(408, 0602\0\0\nC410 ( f h SDD.., 49152, 0x1040A) = 49152 pwrite(408, 0602\0\0\nC410 + f h SDD.., 49152, 0x1040AC000) = 49152 pwrite(408, 0602\0\0\nC410 . f h SDD.., 49152, 0x1040B8000) = 49152 pwrite(408, 0602\0\0\nC410 1 f h SDD.., 49152, 0x1040C4000) = 49152 pwrite(408, 0602\0\0\nC410 4 f h SDD.., 49152, 0x1040D) = 49152 fdsync(408, O_RDONLY|O_SYNC)= 0 pwrite(408, 0602\0\0\nC410 7 f h SDE.., 49152, 0x1040DC000) = 49152 pwrite(408, 0602\0\0\nC410 : f h SDE.., 49152, 0x1040E8000) = 49152 pwrite(408, 0602\0\0\nC410 = f h SDE.., 49152, 0x1040F4000) = 49152 pwrite(408, 0602\0\0\nC410 @ f h SDE.., 49152, 0x10410) = 49152 pwrite(408, 0602\0\0\nC410 C f h SDE.., 49152, 0x10410C000) = 49152 lwp_cond_wait(0x7CF0DF70, 0x7CF0DF80, 0x) = 0 lwp_cond_signal(0x7CF0DF70) = 0 pread(364, \b02\0\0\v\09A ! f h ECB.., 16384, 0x26884000) = 16384 I think it is trying to do a KAIO call and failing. Then it attempts a synchronous PWRITE call. But our SAs are not able to help us to confirm this. Have any of you seen this issue? Any inputs would be greatly appreciated. I'll gladly provide you with addl info if you need. Thanks in advance Babu _ This e-mail transmission and any attachments to it are intended solely for the use of the individual or entity to whom it is addressed and may contain confidential and privileged information. If you are not the intended recipient, your use, forwarding, printing, storing, disseminating, distribution, or copying of this communication is prohibited. If you received this communication in error, please notify the sender immediately by replying to this message and delete it from your computer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: performance issues on sun
Babu - I would recommend checking: - Where is the tablespace of the table? Shared disk with temp or index tablespace? - Check your SORT_AREA_SIZE on both systems. - Do some basic disk I/O tests. On both the Compaq and Solaris, move a large file from one drive to another, just to see what time it takes. If the Solaris system is significantly slower, discuss this with your system administrators. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, February 25, 2003 8:11 AM To: Multiple recipients of list ORACLE-L All We are attempting to move some applications off Compaq T64 into Sun Solaris 8 and running into performance issues. I am trying to rebuild an index which is taking more than 3 1/2 hours while it used to take 20 min on T64. I find most of the waits on DIRECT PATH READS and DIRECT PATH WRITES. The index tablespace and the temporary tablespace are on separate mountpoints which reside on separate disks. I am doing a truss on the session and see that its doing the following kaio(AIOWAIT, 0x) Err#22 EINVAL pread(364, \b02\0\0\v\099E1 f h ECB.., 1048576, 0x26784000) = 1048576 kaio(AIOWAIT, 0x) Err#22 EINVAL lwp_cond_wait(0x7CED7F70, 0x7CED7F80, 0x) = 0 pwrite(408, 0602\0\0\nC41007 f h SDD.., 49152, 0x10401C000) = 49152 pwrite(408, 0602\0\0\nC410\n f h SDD.., 49152, 0x104028000) = 49152 pwrite(408, 0602\0\0\nC410\r f h SDD.., 49152, 0x104034000) = 49152 pwrite(408, 0602\0\0\nC41010 f h SDD.., 49152, 0x10404) = 49152 pwrite(408, 0602\0\0\nC41013 f h SDD.., 49152, 0x10404C000) = 49152 pwrite(408, 0602\0\0\nC41016 f h SDD.., 49152, 0x104058000) = 49152 pwrite(408, 0602\0\0\nC41019 f h SDD.., 49152, 0x104064000) = 49152 pwrite(408, 0602\0\0\nC4101C f h SDD.., 49152, 0x10407) = 49152 pwrite(408, 0602\0\0\nC4101F f h SDD.., 49152, 0x10407C000) = 49152 pwrite(408, 0602\0\0\nC410 f h SDD.., 49152, 0x104088000) = 49152 pwrite(408, 0602\0\0\nC410 % f h SDD.., 49152, 0x104094000) = 49152 pwrite(408, 0602\0\0\nC410 ( f h SDD.., 49152, 0x1040A) = 49152 pwrite(408, 0602\0\0\nC410 + f h SDD.., 49152, 0x1040AC000) = 49152 pwrite(408, 0602\0\0\nC410 . f h SDD.., 49152, 0x1040B8000) = 49152 pwrite(408, 0602\0\0\nC410 1 f h SDD.., 49152, 0x1040C4000) = 49152 pwrite(408, 0602\0\0\nC410 4 f h SDD.., 49152, 0x1040D) = 49152 fdsync(408, O_RDONLY|O_SYNC)= 0 pwrite(408, 0602\0\0\nC410 7 f h SDE.., 49152, 0x1040DC000) = 49152 pwrite(408, 0602\0\0\nC410 : f h SDE.., 49152, 0x1040E8000) = 49152 pwrite(408, 0602\0\0\nC410 = f h SDE.., 49152, 0x1040F4000) = 49152 pwrite(408, 0602\0\0\nC410 @ f h SDE.., 49152, 0x10410) = 49152 pwrite(408, 0602\0\0\nC410 C f h SDE.., 49152, 0x10410C000) = 49152 lwp_cond_wait(0x7CF0DF70, 0x7CF0DF80, 0x) = 0 lwp_cond_signal(0x7CF0DF70) = 0 pread(364, \b02\0\0\v\09A ! f h ECB.., 16384, 0x26884000) = 16384 I think it is trying to do a KAIO call and failing. Then it attempts a synchronous PWRITE call. But our SAs are not able to help us to confirm this. Have any of you seen this issue? Any inputs would be greatly appreciated. I'll gladly provide you with addl info if you need. Thanks in advance Babu _ This e-mail transmission and any attachments to it are intended solely for the use of the individual or entity to whom it is addressed and may contain confidential and privileged information. If you are not the intended recipient, your use, forwarding, printing, storing, disseminating, distribution, or copying of this communication is prohibited. If you received this communication in error, please notify the sender immediately by replying to this message and delete it from your computer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of
RE: performance issues on sun
- Where is the tablespace of the table? Shared disk with temp or index tablespace? Its on a different mountpoint (and disk) from the index or the temp tablespaces - Check your SORT_AREA_SIZE on both systems. Its 1 Mb. I have done the tests with as much as 250M but same results - Do some basic disk I/O tests. On both the Compaq and Solaris, move a large file from one drive to another, just to see what time it takes. If the Solaris system is significantly slower, discuss this with your system administrators. If I do a simple cp, I am not able to see any big performance difference Thanks a lot... Babu DENNIS WILLIAMS [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] UCH.COM cc: Sent by: Subject: RE: performance issues on sun [EMAIL PROTECTED] 02/25/03 10:14 AM Please respond to ORACLE-L Babu - I would recommend checking: - Where is the tablespace of the table? Shared disk with temp or index tablespace? - Check your SORT_AREA_SIZE on both systems. - Do some basic disk I/O tests. On both the Compaq and Solaris, move a large file from one drive to another, just to see what time it takes. If the Solaris system is significantly slower, discuss this with your system administrators. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, February 25, 2003 8:11 AM To: Multiple recipients of list ORACLE-L All We are attempting to move some applications off Compaq T64 into Sun Solaris 8 and running into performance issues. I am trying to rebuild an index which is taking more than 3 1/2 hours while it used to take 20 min on T64. I find most of the waits on DIRECT PATH READS and DIRECT PATH WRITES. The index tablespace and the temporary tablespace are on separate mountpoints which reside on separate disks. I am doing a truss on the session and see that its doing the following kaio(AIOWAIT, 0x) Err#22 EINVAL pread(364, \b02\0\0\v\099E1 f h ECB.., 1048576, 0x26784000) = 1048576 kaio(AIOWAIT, 0x) Err#22 EINVAL lwp_cond_wait(0x7CED7F70, 0x7CED7F80, 0x) = 0 pwrite(408, 0602\0\0\nC41007 f h SDD.., 49152, 0x10401C000) = 49152 pwrite(408, 0602\0\0\nC410\n f h SDD.., 49152, 0x104028000) = 49152 pwrite(408, 0602\0\0\nC410\r f h SDD.., 49152, 0x104034000) = 49152 pwrite(408, 0602\0\0\nC41010 f h SDD.., 49152, 0x10404) = 49152 pwrite(408, 0602\0\0\nC41013 f h SDD.., 49152, 0x10404C000) = 49152 pwrite(408, 0602\0\0\nC41016 f h SDD.., 49152, 0x104058000) = 49152 pwrite(408, 0602\0\0\nC41019 f h SDD.., 49152, 0x104064000) = 49152 pwrite(408, 0602\0\0\nC4101C f h SDD.., 49152, 0x10407) = 49152 pwrite(408, 0602\0\0\nC4101F f h SDD.., 49152, 0x10407C000) = 49152 pwrite(408, 0602\0\0\nC410 f h SDD.., 49152, 0x104088000) = 49152 pwrite(408, 0602\0\0\nC410 % f h SDD.., 49152, 0x104094000) = 49152 pwrite(408, 0602\0\0\nC410 ( f h SDD.., 49152, 0x1040A) = 49152 pwrite(408, 0602\0\0\nC410 + f h SDD.., 49152, 0x1040AC000) = 49152 pwrite(408, 0602\0\0\nC410 . f h SDD.., 49152, 0x1040B8000) = 49152 pwrite(408, 0602\0\0\nC410 1 f h SDD.., 49152, 0x1040C4000) = 49152 pwrite(408, 0602\0\0\nC410 4 f h SDD.., 49152, 0x1040D
RE: performance issues on sun
Babu, I think it is trying to do a KAIO call and failing. Then it attempts a synchronous PWRITE call. But our SAs are not able to help us to confirm this. Have any of you seen this issue? I think you have hit the nail on the head. By default, the Oracle port on Solaris sets 'disk_async_io' to TRUE. Set this to FALSE by introducing such an entry in init.ora. Let us know if tihis solves your issue... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 I don't know what the future holds for me, but I do know who holds my future! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: performance issues on sun
The Solaris kernel has to have asynchronous IO enabled. If you are running with at least Solaris 2.8, this should not be a problem. If you have your files on VxFS, then I would VERY strongly suggest looking into Veritas DB Edition, particularly, Quick IO (writes) and cached qio (reads). This gives you true DIRECT IO on cooked journalled file systems (VxFS). I have seen performance gains on the IO of up to 400% just from turning this on. Moreover, if you are looking at waits, where I installed this at a previous client, I saw my idle CPU time go from 0% - 5% range, into the 70% range, and the iowait% reduce on average from 50% - 90% range to single-digit figures. Also the load on the machine was greatly reduced. There was also a management issue . See if you can understand this logic : They were using an A1000 with RAID5 and 8 MB write cache (I told them how RAID5 hurts redo log writes, TEMP and RBS writes until I was blue in the face, but they kept showing me that RAID5 allowed them to configure more logical space than RAID 10, duh !) with wait for it SCSI UW2, yep 2, that means a full 40MBPS throughput, woohoo !. So they were willing to fork out the 30 grand it cost for Qio, than to replace the A1000 with a new Adaptec Durastor 7220 SS which would have given fibre speed, and about 4 times the amount of logical space, and they would have gotten change from 20 grand, and there was no annual software license maintenance fee. Don't get me wrong, I think Veritas has some FANTASTIC products, a lot is dependent on the support person they assign to your account (it took me about 10 support calls to realize that the reason my reads were not going any faster was because I needed to configure cached-quick-IO, which was NOT in any of the marketing stuff, only upon re-reading the technical guide for the 4th time, did I spot the 3 line entry about it and decided to ask questions. Prior to Solaris 2.8, asynch IO on Solaris was not considered safe (by the SA's anyway), but as of 2.8, one can enable asycnh IO on Solaris for cooked file systems, if you can convince the SA that Oracle has its own backup and recovery mechanism. Then the next thing you need to worry about is stripe size and getting it just right. Oh, and if you can ditch RAID5 in favour of RAID 10, please do so as early as possible. I have just been reading through my complimentary copy of Gaja and Kirti's book, and Gaja does a great job of describing stripe sizes (Gaja, you did not mention cached quick IO only QIO, tut tut ! ). I also disagree with Gaja about the folklore on HAVING to separate indexes and their tables into separate tablespaces, that depends on where the volumes are physically mapped to and unless you can see this information, there is no basis for making this claim either way. But that is another story, for another thread. Hope this has helped you out. Regards : Ferenc Mantfeld The pain of regret is far worse than the pain of discipline !. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 1:11 AM All We are attempting to move some applications off Compaq T64 into Sun Solaris 8 and running into performance issues. I am trying to rebuild an index which is taking more than 3 1/2 hours while it used to take 20 min on T64. I find most of the waits on DIRECT PATH READS and DIRECT PATH WRITES. The index tablespace and the temporary tablespace are on separate mountpoints which reside on separate disks. I am doing a truss on the session and see that its doing the following kaio(AIOWAIT, 0x) Err#22 EINVAL pread(364, \b02\0\0\v\099E1 f h ECB.., 1048576, 0x26784000) = 1048576 kaio(AIOWAIT, 0x) Err#22 EINVAL lwp_cond_wait(0x7CED7F70, 0x7CED7F80, 0x) = 0 pwrite(408, 0602\0\0\nC41007 f h SDD.., 49152, 0x10401C000) = 49152 pwrite(408, 0602\0\0\nC410\n f h SDD.., 49152, 0x104028000) = 49152 pwrite(408, 0602\0\0\nC410\r f h SDD.., 49152, 0x104034000) = 49152 pwrite(408, 0602\0\0\nC41010 f h SDD.., 49152, 0x10404) = 49152 pwrite(408, 0602\0\0\nC41013 f h SDD.., 49152, 0x10404C000) = 49152 pwrite(408, 0602\0\0\nC41016 f h SDD.., 49152, 0x104058000) = 49152 pwrite(408, 0602\0\0\nC41019 f h SDD.., 49152, 0x104064000) = 49152 pwrite(408, 0602\0\0\nC4101C f h SDD.., 49152, 0x10407) = 49152 pwrite(408, 0602\0\0\nC4101F f h SDD.., 49152, 0x10407C000) = 49152 pwrite(408, 0602\0\0\nC410 f h SDD.., 49152, 0x104088000) = 49152 pwrite(408, 0602\0\0\nC410 % f h SDD.., 49152, 0x104094000) = 49152 pwrite(408, 0602\0\0\nC410 ( f h SDD.., 49152, 0x1040A) = 49152 pwrite(408, 0602\0\0\nC410 + f h SDD.., 49152, 0x1040AC000) = 49152 pwrite(408, 0602\0\0\nC410 . f h SDD.., 49152, 0x1040B8000) = 49152 pwrite(408, 0602\0\0\nC410 1 f h SDD.., 49152, 0x1040C4000) = 49152 pwrite(408, 0602\0\0\nC410 4 f h SDD.., 49152, 0x1040D) = 49152 fdsync(408, O_RDONLY|O_SYNC)
RE: performance issues on sun
I did this and its taking the same amount of time. The difference this time is that it does not do the KAIO call. But the time has not improved. Its still doing pwrite calls. TIA Babu John Kanagaraj [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ds.com cc: Sent by: Subject: RE: performance issues on sun [EMAIL PROTECTED] 02/25/03 01:04 PM Please respond to ORACLE-L Babu, I think it is trying to do a KAIO call and failing. Then it attempts a synchronous PWRITE call. But our SAs are not able to help us to confirm this. Have any of you seen this issue? I think you have hit the nail on the head. By default, the Oracle port on Solaris sets 'disk_async_io' to TRUE. Set this to FALSE by introducing such an entry in init.ora. Let us know if tihis solves your issue... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 I don't know what the future holds for me, but I do know who holds my future! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ This e-mail transmission and any attachments to it are intended solely for the use of the individual or entity to whom it is addressed and may contain confidential and privileged information. If you are not the intended recipient, your use, forwarding, printing, storing, disseminating, distribution, or copying of this communication is prohibited. If you received this communication in error, please notify the sender immediately by replying to this message and delete it from your computer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: performance issues on sun
I wish now that I hadn't deleted what I composed this morning... It was this: People probably get sick of seeing me say the same thing over and over and over... You have some interesting information from the truss that you've done. But you can't tell how long something took by counting how many times it happened. The easiest way to determine what's consuming the most *time* is to use extended SQL trace (event 10046 level 8). The resulting trace file will tell you exactly where your time has gone, and it will enable you to determine whether your performance problem is a result of the kaio calls or not. You'll probably find that the system is doing what you suspect: issuing an async write call, failing, and then calling a synchronous write call. However, without knowing the impact of this behavior upon response time, it's hard to know whether the time you invest into checking stuff and fixing stuff is worth anything. The worst feeling is to invest your time into fixing something, succeeding, and then finding you've made no impact because the thing you fixed accounts for only a small amount of response time. ...Find out what activity is consuming the largest chunk of your response time, and then try to figure out how to do that thing less. The cheapest, fastest, most error-free way that I know to do that is to collect the 10046 level-8 data. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- [EMAIL PROTECTED] Sent: Tuesday, February 25, 2003 2:45 PM To: Multiple recipients of list ORACLE-L I did this and its taking the same amount of time. The difference this time is that it does not do the KAIO call. But the time has not improved. Its still doing pwrite calls. TIA Babu John Kanagaraj [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ds.com cc: Sent by: Subject: RE: performance issues on sun [EMAIL PROTECTED] 02/25/03 01:04 PM Please respond to ORACLE-L Babu, I think it is trying to do a KAIO call and failing. Then it attempts a synchronous PWRITE call. But our SAs are not able to help us to confirm this. Have any of you seen this issue? I think you have hit the nail on the head. By default, the Oracle port on Solaris sets 'disk_async_io' to TRUE. Set this to FALSE by introducing such an entry in init.ora. Let us know if tihis solves your issue... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 I don't know what the future holds for me, but I do know who holds my future! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ This e-mail transmission and any attachments to it are intended solely for the use of the individual or entity to whom it is addressed and may contain confidential and privileged information. If you are not the intended recipient, your use, forwarding, printing, storing, disseminating, distribution, or copying of this communication is prohibited. If you received this communication in error, please notify the sender immediately by replying to this message and delete it from your computer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
