Interesting little snipets of information I have come accross from Oracle forums, Oracle presentations and SIG's…
Optimizer
Bind Peaking
it means when the query is first hard parsed, the optimizer
will peek at the binds in order to determine how to optimize the query. Stress
the word hard there, it is not every parse, only on the hard parse.
Truncate Affect on Query Plans
If you truncate a partition, then a statement [will get] invalidated - even though it used partition-specific code, and we truncated a different partition.
Full Table Scan
If you are going to select more that 25% of the table then Oracle will do a full table scan
Not exactly, its not 25% — it is much more complex then that. 25% is ROT (rule of thumb). It is actually a function of the estimated cardinality, the clustering factor, the row widths, the db_file_multi_block_read_count, etc etc etc.
Init.ora Parameters
Compatibility
Compatible only affects "disk based structures" really — it is there so that if
Oracle come up with some new format for persisting data — it won't be used (Of course
you cannot downgrade if we did).
I/O
Sequential and Scattered Reads
A db file sequential read is an event that shows a wait for a foreground process while doing a sequential read from the database. This is an o/s operation, most commonly used for single block reads. Single block reads are mostly commonly seen for index block access or table block access by a rowid (Eg: to access a table block after an index entry has been seen)
This can also be a multiblock read. Then it will usually be a read from a SORT (TEMPORARY) segment as multiblock reads for table scans (and index fast full scans) usually show up as waiting on "db file scattered read"
A db file scattered read is the same type of event as "db file sequential read", except that Oracle will read multiple data blocks. Multi-block reads are typically used on full table scans. The name "scattered read" may seem misleading but it refers to the fact that multiple blocks are read into DB block buffers that are 'scattered' throughout memory.
