Database, Oracle performance training

Full table scan may be faster than using index access if query returns > 10% of table rows. Full scans take advantage from multi-block IO
To fully exploit machine CPU there should be a ratio of 10 process per processor. Only a few will be active at any time due to IO waits. Process should belong to different sessions to avoid waiting is synchronized
A good indication of execution plan performance : consistent get count * block size, if it is several time bigger than the table size then it may not be optimal
Know the application using the DB => OLTP should be optimized for querying and updating a small number of rows (selective and few indexes). Data mining should be optimized for complex queries returning lots of data and batch updates (bitmap indexes …)

RBO - rule based optimizer (deprecated)
CBO - cost based optimizer for sql queries, evaluates the cost of a request by estimating the number of consistent gets, IO and cpu it will consume. It relies heavily on statistics which allow oracle to infer the hidden structure of the data and adapt as it evolves over time. (a bit like machine learning ??)
Statistics consist of : the underlying machine characteristics (avg IO read/write, avg consistent read …), and data distribution (distinct values in columns, frequency of each value, table size …)
Bind variables may generate sub optimal execution plans. Ex: if an index contains key with high and low selectivity (data distribution is not even) then it may be worth only for some queries.
Queries using joins on N tables may produce N! different execution plans. The optimizer will only try a subset.
Parallelization can change the execution plan (// prefers full tables scans over index, nested loops for joins over hash joins …)
Histograms are more sensitive to sampling. If the sampled population is not representative, it may not reflect the truth distribution

Selectivity of an index determines table scan or not. However the exact selectivity of a key can only be known with an histogram with as many buckets as keys. In other scenarios oracle will estimate it
NULL values are not indexed in oracle b-tree indexes. If a request uses an indexed key only for an “order by” clause then the index will not be taken. Solve it by declaring the column not NULL
B-tree indexes are more effective when predicates on several indexes are combined using AND
Insertion in a b-tree index takes around 3 times more time than insertion in the table
Bitmap indexes work better with keys of low selectivity (~1%) and high cardinality => to be better than a full scan they have to be combined (logical operations on the bitmaps). Updates on bitmap indexed tables can lock several blocks at once.
When performing heavy updates it may be better to drop index and rebuild it at the end using //

Index on foreign keys speed up the update in parent tables ?? Updating indexed columns can side effect and lock other unrelated rows in table ?

Feature	Effect
Connection pooling	Avoids to establish a new connection for each statement
Cursors	Lazy retrieval, DB server may not have to calculate all results
Prepared statements	SQL statements can be compiled and cached on server side, execution plan already determined
Batch statements	Avoids useless round trips by packing statements into groups