Thread: Table Creation Parameters
03-31-04, 07:30 #1Registered User
- Join Date
- Mar 2004
Unanswered: Table Creation Parameters
I have a peculiar problem. We have a our Database objects installed in Oracle V7.3, 8i and 9i. There are tables which has over 1 lakh of record and grows by a lakh every month for a financial year.
Same set of index, procedure, function, table definition is same across all versions. The problem is some procedures in 9i and 7.3 takes 20 - 25 minutes to complete, whereas it is taking more than 2 hours 30 minutes in 8i. (Actually we stopped the process at this time, we don't know how long will it take exactly...)
Now some basic questions (I am new to DB)
1. Is there any thumb rule for PCTFREE, PCTUSED specifications ?
2. Is it advisible to have many index having same column in it at different position or same position with different combination ? Will this affect in Insert operation ? (Because, 1000's of insert will happen continously)
3. Thumb rules for Tablespace sizing ?
I will be happy, even if someone chips in with some tips.
03-31-04, 08:37 #2Registered User
- Join Date
- Oct 2003
If you're using the Cost Based Optimizer (CBO) each SQL statement will probably optimize differently in each database (DB). Read general info about how the CBO works and you'll clearly see this. For examle, the CBO takes into its decision process the amount of pool memory, number and speed of processors, amount of anticipated I/O, etc.
"1. Is there any thumb rule for PCTFREE, PCTUSED specifications ?"
There aren't any ROTs! However, there are Defaults! Each case is different. For rows that increase in size due to Updates PCTFREE will probably need to be higher than the Default. For tables where the data rarely changes, use lower PCTFREE and higher PCTUSED. In very high concurrency OLTP environments you want to sparsely populate a data block with rwos to spread contention, thus high PCTUSED.
Once you understand how the CBO works you'll be able to answer your question #2.
Finally, to answer #3 and gain tremendous knowledge in the process go to http://asktom.oracle.com/pls/ask/f?p=4950:1:Oracle - DB2 - MS Access -