Re: Table Storage Estimates
I think you can partition the table. It would not change the logical database design (so, your application will not see the difference). Based on you numbers, it is very possible that row chaining and migration exist. Follow the steps I passed to you earlier. My suggestion: 1. Recreate database with larger database block size, if it is possible, or at least 2. Reduce the PCTFREE from its default. 3. Partition table if possible 4. Follows these common practices: - Separate tables and their indexes on different disks. - Separate online redo log files and archived log files on separate disks to avoid contention between LGWR and ARCn. - Separate online redo log files and datafiles on separate disks to avoid contention between LGWR and DBWn. - Locally-managed tablespaces can reduce fragmentation (for other than SYSTEM tablespace) - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, March 01, 2001 12:46 AM > Thanks all for the solution. I too thought of having a 1 : 1 relationship by > separating the long raw column from the table. But I believe I am not > permitted to do that (Strange). Since this is a third party product, I > am not suppose to change any of the design issues and also the datatype. > > I believe row chaining is obvious in this situation. Also the average row > length is 10M. The size of the database block size is 8. > > When I queried the dba_extents to find the number of blocks that have been > occupied, it showed 16620 and the number of rows are 113. > > I am totally confused now. Any help greatly appreciated. > > Sanjay > > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Wednesday, February 28, 2001 12:46 AM > > > Your estimate comes to around 1 row per block. > What is the average row lenght once data is in the db > ? Do you have row chaining ? > > What is your block size ? Can you increase it ? > > If a lot of queries do not need the long raw column > you can put it in another table with a 1 to 1 > relationship with the parent table. > > If your planning to have 200 rows of 1 block each, I > do not understand why you are adding datafiles as the > data should not occupied more than 3200K if you > blocksize is 16K. Increase the size of the initial > datafile instead of adding new ones. > > --- Sanjay Kumar <[EMAIL PROTECTED]> a écrit : > > Hi, > > > > I have a table which has a structure similar to the > > following. > > > > MID NOT NULL > > VARCHAR2(24) > > NAME > > VARCHAR2(512) > > PRIORITY > > NUMBER(38) > > PERSISTENT > > CHAR(1) > > EXP_DATE > > DATE > > BODY > > LONG RAW > > > > > > I estimated the table storage for this table using > > the formula given in the Oracle documentation. It > > comes to around 1 rows per block. > > > > It is estimated that the number of rows for this > > table will not exceed 200. > > > > My questions are > > > > 1. Doesnt this lead to fragmentation.? > > 2. I have added three datafiles to this tablespace > > as of now and it seems like this might require more > > space. How do I effectively redesign this > > table to avoid contention and chaining? > > > > 3. Moreover, initially when there were 105 rows > > inserted, I issued a count(*) and it took about 5 > > minutes? How do I reduce the time taken to execute > > the query? > > > > > > I would highly appreciate if someone comes forward > > to help me on this. > > > > I am using Oracle 8.1.6 on Solaris > > > > Sanjay > > > > > > > > > = > Stephane Paquette > DBA Oracle > [EMAIL PROTECTED] > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: =?iso-8859-1?q?paquette=20stephane?= > 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: Sanjay Kumar > 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: UN
Re: Table Storage Estimates
Thanks all for the solution. I too thought of having a 1 : 1 relationship by separating the long raw column from the table. But I believe I am not permitted to do that (Strange). Since this is a third party product, I am not suppose to change any of the design issues and also the datatype. I believe row chaining is obvious in this situation. Also the average row length is 10M. The size of the database block size is 8. When I queried the dba_extents to find the number of blocks that have been occupied, it showed 16620 and the number of rows are 113. I am totally confused now. Any help greatly appreciated. Sanjay - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, February 28, 2001 12:46 AM Your estimate comes to around 1 row per block. What is the average row lenght once data is in the db ? Do you have row chaining ? What is your block size ? Can you increase it ? If a lot of queries do not need the long raw column you can put it in another table with a 1 to 1 relationship with the parent table. If your planning to have 200 rows of 1 block each, I do not understand why you are adding datafiles as the data should not occupied more than 3200K if you blocksize is 16K. Increase the size of the initial datafile instead of adding new ones. --- Sanjay Kumar <[EMAIL PROTECTED]> a écrit : > Hi, > > I have a table which has a structure similar to the > following. > > MID NOT NULL > VARCHAR2(24) > NAME > VARCHAR2(512) > PRIORITY > NUMBER(38) > PERSISTENT > CHAR(1) > EXP_DATE > DATE > BODY > LONG RAW > > > I estimated the table storage for this table using > the formula given in the Oracle documentation. It > comes to around 1 rows per block. > > It is estimated that the number of rows for this > table will not exceed 200. > > My questions are > > 1. Doesnt this lead to fragmentation.? > 2. I have added three datafiles to this tablespace > as of now and it seems like this might require more > space. How do I effectively redesign this > table to avoid contention and chaining? > > 3. Moreover, initially when there were 105 rows > inserted, I issued a count(*) and it took about 5 > minutes? How do I reduce the time taken to execute > the query? > > > I would highly appreciate if someone comes forward > to help me on this. > > I am using Oracle 8.1.6 on Solaris > > Sanjay > > > = Stephane Paquette DBA Oracle [EMAIL PROTECTED] __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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: Sanjay Kumar 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: Table Storage Estimates
Your estimate comes to around 1 row per block. What is the average row lenght once data is in the db ? Do you have row chaining ? What is your block size ? Can you increase it ? If a lot of queries do not need the long raw column you can put it in another table with a 1 to 1 relationship with the parent table. If your planning to have 200 rows of 1 block each, I do not understand why you are adding datafiles as the data should not occupied more than 3200K if you blocksize is 16K. Increase the size of the initial datafile instead of adding new ones. --- Sanjay Kumar <[EMAIL PROTECTED]> a écrit : > Hi, > > I have a table which has a structure similar to the > following. > > MID NOT NULL > VARCHAR2(24) > NAME > VARCHAR2(512) > PRIORITY > NUMBER(38) > PERSISTENT > CHAR(1) > EXP_DATE > DATE > BODY > LONG RAW > > > I estimated the table storage for this table using > the formula given in the Oracle documentation. It > comes to around 1 rows per block. > > It is estimated that the number of rows for this > table will not exceed 200. > > My questions are > > 1. Doesnt this lead to fragmentation.? > 2. I have added three datafiles to this tablespace > as of now and it seems like this might require more > space. How do I effectively redesign this > table to avoid contention and chaining? > > 3. Moreover, initially when there were 105 rows > inserted, I issued a count(*) and it took about 5 > minutes? How do I reduce the time taken to execute > the query? > > > I would highly appreciate if someone comes forward > to help me on this. > > I am using Oracle 8.1.6 on Solaris > > Sanjay > > > = Stephane Paquette DBA Oracle [EMAIL PROTECTED] __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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: Table Storage Estimates
1. Fragmentation depends on how the application use / manipulate the data. The important parameter is PCTFREE and PCTUSED; - Frequent inserts & deletes will not cause fragmentation if PCTFREE 0 and PCTUSED high enough: I assumes your calculation is correct that a row (max size) fit to about 1 database block and initial insert is about that size. - Frequent updates will cause fragmentation if initial insert is less than half size of database block and updated later on. 2. Of course, addidng datafiles will consume more space, because it is allocated to those datafiles (which may still partly empty). Contention happens when there is possibility of many users accessing the same database resources. For example, if 2 tables will be updated frequently by many different users, separate it on different hard disk or configure RAID properly. A common pratice is to separate tables and their indexes. (I ask others to complete this with others common practices. Speak up, Guys!). If your row size is more than the size of a database block, it will have row chaining. To avoid it, set larger database block (I am sorry, you need to recreate the database). 3. I think you can avoid the column BODY in your query; SELECT COUNT(mandatory_column) FROM table_name; Or, if you do not have a mandatory column, try SELECT COUNT(NVL(any_column,'0')) FROM table_name; HTH - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, February 28, 2001 7:30 AM Hi, I have a table which has a structure similar to the following. MID NOT NULL VARCHAR2(24) NAME VARCHAR2(512) PRIORITY NUMBER(38) PERSISTENT CHAR(1) EXP_DATE DATE BODYLONG RAW I estimated the table storage for this table using the formula given in the Oracle documentation. It comes to around 1 rows per block. It is estimated that the number of rows for this table will not exceed 200. My questions are 1. Doesnt this lead to fragmentation.? 2. I have added three datafiles to this tablespace as of now and it seems like this might require more space. How do I effectively redesign this table to avoid contention and chaining? 3. Moreover, initially when there were 105 rows inserted, I issued a count(*) and it took about 5 minutes? How do I reduce the time taken to execute the query? I would highly appreciate if someone comes forward to help me on this. I am using Oracle 8.1.6 on Solaris Sanjay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yustiono 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).