RE: SORT_AREA_SIZE
There are two nice papers about it on Steve Adams' site. http://www.ixora.com.au http://www.ixora.com.au You can find it in the newsletter section. One for daily oltp : Basically it says to measure the daily disk sort size used, dived it by the sort count and you have your average size = sort_area_size. By the way the script is also on the site: disk_sorts.sql http://www.ixora.com.au/scripts/sql/disk_sorts.sql . This script shows also the maximum concurrent sorts, which will allow you to know the maximum memory allocated for sorts. For batch jobs Here he uses event 10032 that will output a trace for sorts during batch processing. From there you can compute the best sort_area_size to allow a one pass sort. It means that you will only need one merge phase. I wrote a summary of this if you want I can send it to you. Jean Remacle -Original Message- From: Manavendra Gupta [mailto:[EMAIL PROTECTED]] Sent: jeudi 8 août 2002 19:59 To: Multiple recipients of list ORACLE-L Subject:SORT_AREA_SIZE What should be the optimal SORT_AREA_SIZE? What parameters does it vary on? Regards, Manav. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Manavendra Gupta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Remacle Jean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Looking for book contributors
Hi there, Where I am working we will order the TSM module pretty soon. I can write the whole installation process on how to interface RMAN with TSM. Jean Remacle -Original Message- From: Freeman, Robert [mailto:[EMAIL PROTECTED]] Sent: lundi 8 juillet 2002 23:04 To: Multiple recipients of list ORACLE-L Subject:Looking for book contributors First of all, apologies to those I have talked to about helping with our new RMAN book. I've been searching for email addresses that I thought I had tucked away, and only found one. I know there were 2 - 3 other people who expressed a desire to help with our RMAN book project. So now, out goes the call!! We need someone (or 2 someone's) to contribute about 10 pages to two different RMAN MML topics. We need someone to do 10 pages on configuring Tivoli Storage manager and Legato Networker for use with RMAN, and then interfacing with them with RMAN commands. If you would be interested in doing this, please EMAIL me and let me know. I can't offer any pay for this small assignment, but I can offer you a big thank you mention in the acknowledgements and a really cool thing to add to your resume! Robert G. Freeman - Oracle8i OCP Oracle Database Architect CSX Midtier Database Administration Author: Oracle9i New Features Mastering Oracle8i Clark Griswold: Eddie, has anyone ever told you that you're bad luck? Cousin Eddie: Those were my mother's dying words. But I guess if your body's covered in third degree burns, and your foot's caught in a bear trap, you tend to start talkin' crazy. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Remacle Jean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: rows values in tkprof
Ravi, The rows value comes from the execution plan stored in the trace file at runtime. The execution plan is only dumped to trace file when the cursor is closed. So, if you take another try with sql*plus for instance and quit sql*plus before closing the trace, then you will see the rows values printed in your trace. Or if you commit the transaction even if it is a select then you will see the rows count. That's a matter of fact there is a tkprof option to add the explain plan into the output. All the option does is connect to the database when you issue the tkprof command line and compute a explain plan. I see two quirks here first you do not have a real image of what happened at runtime and second you don't have the rows count. By the way if your cursor is closed ( you have stats info in your trace file) and you issue a tkprof command with the explain option you will see two execution plan in the output, the stored one and the newly computed one. Jean Remacle -Original Message- From: Nalla Ravi [mailto:[EMAIL PROTECTED]] Sent: lundi 24 juin 2002 21:08 To: Multiple recipients of list ORACLE-L Subject:rows values in tkprof Dear All, In my tkprof output rows values under explain plan section are not getting printed, is there any parameter to be set? Thanks, Ravi __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Remacle Jean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
SORT
Hi all, I did some testing on sorts. I sorted a table of 269 MB. I did set the sort_area_size to a minimum value of 10k. I set event 10032 to level 1 and looked after the initial runs to be able to calculate a good value for sort_area_size. Once this done, I did run the statement with event 10053 set to level 1 and discovered that initial runs did not match. With 10032 I get 6 as initial runs and sort_area_size is 385024 where under 10053 I get 1069 initial runs and an area_size of 264192. So if you make the maths you discover that the sorted size is about (6*385024) 2.2 MB for 10032 and is (1069*264192) 269 MB for 10053. I guess that event 10053 reports just a simulation and it divides the table size by its area size to get the number of initial runs. Event 10032 reports the real numbers of what happened. But then my question is how can it sort 269 MB of data in 2.2 MB of sort space? My guess is that it only needs space for sorted rows and this is a group by. So, the number of rows to sort is 3884794 times 73 bytes (avg row length) is 269 MB. The number of output rows is 19839 times 73 is 1.4 MB it will fit in 2.2 MB. Another question is why that SORT_SIZE is reported instead of SORT_AREA_SIZE? Here is the statement and execution plan: SELECT trunc(collecttime, 'HH24'), expname, ipaddr, hostname, instance, sum(floatvalue), sum(stopticks - startticks) / 10 cnt FROM coldata group by expname, ipaddr, hostname, instance, trunc(collecttime, 'HH24'); Execution Plan -- 0SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=95599 Card=3866581 Bytes=220395117) 10 SORT (GROUP BY) (Cost=95599 Card=3866581 Bytes=220395117) 21 TABLE ACCESS (FULL) OF 'COLDATA' (Cost=2294 Card=3866581 Bytes=220395117) Jean Remacle -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Remacle Jean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
