Re: Unindexed foreign key
Terrible expecially on inserts into the database, it may have to do a full table scan to insert one record. Don't do it. >From: "Shishir Kumar Mishra" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Unindexed foreign key >Date: Thu, 11 Jul 2002 04:13:36 -0800 > >Hi Gurus! >How does the unindexed foreign key affect the performance ? > >regards >Shishir Kumar Mishra >Agni Software (P) Ltd. >www.agnisoft.com >-- >Vidya Dadaati Viniyam >-- > > Over and out Basher 59 _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: basher 59 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: storage parameters
No. I can think of several reason when you would not wan them the save. An example: Think of load a lot of data into a table, and then load on a very limited basis. This tells me to create a large first extent that everything can fit into. Example: Say 100M and then you may want to make next extents 5M. Based on the fact that you won't be reaching the second extent for a while. Also the temp tablespace is used to sort. I had an instance where the sorts were taking a long time. I looked at the temp tablespace and found out that my current sort was using 300 extents of a very small size. I increase my extent size to a large number, where the sort only took 4 extents and it really speeded up my sort. To optimize everything I look at the majority of my sorts and tried to make my first entent give me 90 % of these sorts. Which was somewhat small, and then I made my next extents large enough so my largest sort did not use more than about (5 to 10) extents. MN >From: "Malik, Fawzia" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: storage parameters >Date: Tue, 28 May 2002 08:18:37 -0800 > > >Hi, > >Does Oracle reccommend having the initial and next extents set at the same >values??? >I am tidying up a current schema and the values are non uniform all the way >through- any comments would be very much appreciated > >Rgds > >Fawzia > > >** >Information in this email is confidential and may be privileged. >It is intended for the addressee only. If you have received it in error, >please notify the sender immediately and delete it from your system. >You should not otherwise copy it, retransmit it or use or disclose its >contents to anyone. >Thank you for your co-operation. >** > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Malik, Fawzia > 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). _____ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: basher 59 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: SORT
Sort area size is used to sort, but so is the temporary table space. Can increase your sort area size? Both sort_area_size and sort_area_retained_size need to be looked into. sort_area_retained_size specifies in bytes the maximum amount of the user global area (UGA) memory retained after a sort. SORT_AREA_SIZE specifies in bytes the maximum amount of memory Oracle will use for a sort. So sort_area_retained_size is the min and sort_area_size is the max. You will also use the temporary table space to do sorts. Also as a note not everything will be sorted in memory. Sometimes our sorts go to disk. Here is a big key when trying to increase the speed of sorts. Check the paramers of the temporary table space. What is the initial extent size and what is the next extent size. Some time our sorts have 100's of extents. This is bad , try and minimize this by increase the extent sizes. BYE. MN >From: Remacle Jean <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: SORT >Date: Thu, 23 May 2002 08:28:38 -0800 > >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). _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: basher 59 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: Partitoned Table Insert Performance
Two years ago when partitioning came out we ran some tests. We found out that oracle had some coding problems if you were using 8.0.5. Everything ran okay, but it was slow. The problems was with how the determined which partition to put data into. If you had 200 partition and data was being loaded into the the last partition, it had to do 200 compares and then insert. They have fixed it and now the use a hash algorythem to determine which partition to insert data into. It is really fast. However you will find your biggest pay backs will be in doing selects. Did you know that partitioning takes advantage of pararrel processing, so your selects can be hitting several partitions at the same time. This really speeds everything up. You will not want to partition every table. Only those table that fit the partitioning model. Also the way that you partition data can make or break your application. You need to really think out how you are going to partition a table. It really makes a big difference in performance. Try different options and do some testing. Ask this question, "how are my users going to access the data?" This is the starting question. Also you can also see how partitioning is going to effect backups and system/database management. How many disks are you going to put data on. Which drives are used. Where are my indexes and data? All good question. So it's not just a partitioning question. >From: "Toepke, Kevin M" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: Partitoned Table Insert Performance >Date: Thu, 02 May 2002 09:48:31 -0800 > >Way back in the days of Oracle 8.0.5 I did some performance testing of bulk >inserts/sqlldr of range partitioned tables v.s. non-partitioned tables. I >don't have the benchmarks on hand, but here's what I found. All tests were >done using the direct path inserts (sqlldr direct=true or /*+ APPEND */) > >If the table had no indexes, then there was no noticable difference when >inserting approx 8 million rows. > >If the partitioned table had only local indexes, then the inserts into the >tables were slightly faster. The difference was in seconds for my 8million >row test. > >If the partitioned table had global partitioned indexes, then the inserts >were generally slower. Again, the diference was in seconds. > >If the partitioned table had a global non-partitioned index there was no >noticable diffence in time. > >In sort, range partitioning a table does not effect performance. The >indexing of the range-partitioned table does. > >HTH >Caver > >-Original Message- >Sent: Thursday, May 02, 2002 12:45 PM >To: Multiple recipients of list ORACLE-L > > >I am in the process of implementing partitioning on some existing tables. I >have been asked by management to evaluate the performance impacts of the >changes. I am aware of many of the performance advantages of partitioning: >partition pruning, partition-wise joins and parallel data loads. What I am >concerned about is the additional overhead of inserting data into a >partitioned table. What sort of overhead is associated with partitioned >table inserts? Does determining the correct partition slow insertions? We >are utilizing only range partitions, so hash value computations should not >be a factor. Our application is very insertion intensive. One of the >operations that performs insertions does so synchronously, so any decrease >in insertion performance would be quite visible. Do you have any thoughts >or >experiences with this issue. Any performance tests on two similar tables, >one partitioned and the other not? Any input would be appreciated. Thanks. >Erik > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Toepke, Kevin M > 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). _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: basher 59 INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, Califor
Re: SIMPLE QUESTION
stop the listener, then rename or delete it. (It won't matter.) Then restart the listener and you will have a new log. >From: Hamid Alavi <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: SIMPLE QUESTION >Date: Wed, 01 May 2002 09:18:23 -0800 > >HI AGAIN, > >JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO >RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY >ORACLE. >HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? >THANKS FOR HELP. > > > > >Hamid Alavi >Office 818 737-0526 >Cell818 402-1987 > > > > > > >=== Confidentiality Statement === >The information contained in this message and any attachments is >intended only for the use of the individual or entity to which it is >addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL >and exempt from disclosure under applicable law. If you have received >this message in error, you are prohibited from copying, distributing, or >using the information. Please contact the sender immediately by return >e-mail and delete the original message from your system. >= End Confidentiality Statement = > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Hamid Alavi > 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). _________ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: basher 59 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).
