1. Parallel inserts (Increases the use of multiple CPUs)

If I an inserting from only one process (i.e only one session is inserting these rows, am I able to parralel insert. And if there is ref integrity, would parrallel be affected.

Answer: This is based on my program that uses my database. This database is primarily populated by SQL*Loader. I noticed that 1 SQL*loader was running and the program was lagging behind, becuase the SQL*Loader process was tied to one CPU. This restriction to one CPU was killing total insert speed. So, I recommended parallel SQL*Loaders, this worked great and we keep up with the load. I have a 4 CPU Sun box with 2GB of RAM passing our bigger box with 8 CPUs and 10GB of RAM. So, a single process maybe restricted to a single CPU, so try parallel, if needed.


Also, watch out for locking, like:
1. The insert order
2. Direct inserts
3. Self deadlocking (interesting one! Has to do with INITTRANS, so set it high enough, so it never has to increase as needed)
4. If using partitions, watchout for DDL operation while inserting.


4. PCT_USED to 0 (This gets rid of freelists)

Not quite sure the benefit of this, I ned to reuse the blocks space when it come available, if there are no freelist, how does Oracle know that the bloack is available for NEW INSERTS?

Answer: Your freelists can become a problem if not managed properly. By setting PCT_FREE to 0 you get rid of freelists, but those blocks are never reused once they are over the PCT_FREE setting. This great for partitioned tables, where a rolling window is applied, which is my case. Becuase I drop partitions as needed to make new ones, which means I never do major deletes from a partition segment. So, I do not need the headache of freelists, but you may if you are concerned about space and large deletes. If you are going to use freelists, learn how to keep them maintained.