Oracle uses rule-based or cost-based optimisation.
Rule-based optimisation uses a predefined set of parsing rules used to create query execution plans. Rule-based optimisation is used when statistics for an object are not present. Rule based optimisation can make use of stored outlines in order to preserve previously generated execution plans. Thus these execution plans do not require regeneration by the optimiser on re-execution of the statements involved.
Cost-based optimisation selects the lowest-cost path approach and is used when statistics are created for an object. Note that these statistics must be kept up-to-date. Some new features are only available when utilising cost-based optimisation. The OPTIMZER_MODE parameter must be set to CHOOSE to enable cost-based optimisation.
Statistics can be collected using the ANALYZE command.
ANALYZE table | index | cluster <object name> COMPUTE | ESTIMATE STATISTICS [SAMPLE ROWS | <percentage>];
The cost based approach by default optimises for batch applications in terms of throughput. To optimise for response time in interactive systems. To change the goal of the cost based optimisation to response time execute the following command.
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS | ALL_ROWS;
Histograms can be stored and used as a map to unevenly distributed data which can assist statisitics in cost based optimisation. Like statistics histograms require maintenance. Do not generate histograms for data columns without extremely skewed distributions of data values. Create histograms on columns used often in WHERE clauses, ie. columns used in indexes. Use DBMS_STATS.GETHER_TABLE_STATS ([table[, table ...]) to generate histograms. Histogram sampling rates can also be varied, defaulted at 75.
Statistics should be generated regularly for all tables, clsuters and indexes. Out-of-date statistics will affect performance. Statistics can be generated as an estimated percentage through random samples of records, an exact computation or a user-defined methodology. Exact computations of all records will give the best results. The DBMS_STATS package includes GATHER_INDEX, TABLE, SCHEMA and DATABASE_STATS procedures. These procedures will perform the same function as the ANALYZE object ... command. Note that indexes should always be analysed first since table analysis will utilise the newly created index statistics. Note that when statisitics are gathered on a table all parsed SQL code and code blocks are invalidated and require reparsing and reloading into memory. Therefore pinned code should really require bouncing the database and re-pinning on completion of statistics gathering in order to not have defragmented memory pinned objects.
Statistics can be gathered automatically for tables only using the MONITORING clause of the CREATE and ALTER TABLE statements. This can only be useful in very large databases. Since indexes are used for statistics gathering on tables small databases will not benefit. Also what occurs to parsed code blocks previosly pinned into memory. Generally large databases are not really amenable to being restarted so automated statisitics gathering is a bit dubious as to it's value. The state of table statistics can also be a controlling factor in terms of stale and non-existent statisitics.
Set the TIMED_STATISITICS = TRUE to enable the retention of timed statisitcs in the X$ tables. These timed statistics are available through the X$ overlays, ie. the V$ views.
select name ,value ,to_char(sysdate,'ssss') "Secs" ,(value-&value) "Change" ,(to_char(sysdate,'sssss') - &secs) "Change" ,(value - &value) / (to_char(sysdate,'sssss') - &secs) "Rate" from v$sysstat order by name; NAME VALUE Change Change Rate ------------------------------ --------------- --------- --------- ---------- CPU used by this session 0 0 76913 0.0 CPU used when call started 0 0 76913 0.0 CR blocks created 0 0 76913 0.0 Cached Commit SCN referenced 0 0 76913 0.0 Commit SCN cached 0 0 76913 0.0 DBWR buffers scanned 270 270 76913 0.0 DBWR checkpoint buffers writte 0 0 76913 0.0 DBWR checkpoints 0 0 76913 0.0 DBWR forced writes 0 0 76913 0.0 DBWR free buffers found 265 265 76913 0.0 DBWR lru scans 3 3 76913 0.0 DBWR make free requests 3 3 76913 0.0 DBWR revisited being-written b 0 0 76913 0.0 DBWR summed scan depth 386 386 76913 0.0 DBWR transaction table writes 0 0 76913 0.0 DBWR undo block writes 3 3 76913 0.0 DDL statements parallelized 0 0 76913 0.0 DFO trees parallelized 0 0 76913 0.0 DML statements parallelized 0 0 76913 0.0 PX local messages recv'd 0 0 76913 0.0 PX local messages sent 0 0 76913 0.0 PX remote messages recv'd 0 0 76913 0.0 PX remote messages sent 0 0 76913 0.0 Parallel operations downgraded 0 0 76913 0.0 Parallel operations downgraded 0 0 76913 0.0 Parallel operations downgraded 0 0 76913 0.0 Parallel operations downgraded 0 0 76913 0.0 Parallel operations downgraded 0 0 76913 0.0 Parallel operations not downgr 0 0 76913 0.0 SQL*Net roundtrips to/from cli 136 136 76913 0.0 SQL*Net roundtrips to/from dbl 0 0 76913 0.0 Unnecesary process cleanup for 0 0 76913 0.0 background checkpoints complet 0 0 76913 0.0 background checkpoints started 0 0 76913 0.0 background timeouts 1,438 1438 76913 0.0 branch node splits 0 0 76913 0.0 buffer is not pinned count 15,438 15438 76913 0.2 buffer is pinned count 27,131 27131 76913 0.4 bytes received via SQL*Net fro 15,503 15503 76913 0.2 bytes received via SQL*Net fro 0 0 76913 0.0 bytes sent via SQL*Net to clie 93,839 93839 76913 1.2 bytes sent via SQL*Net to dbli 0 0 76913 0.0 calls to get snapshot scn: kcm 724 724 76913 0.0 calls to kcmgas 14 14 76913 0.0 calls to kcmgcs 5 5 76913 0.0 calls to kcmgrs 0 0 76913 0.0 change write time 0 0 76913 0.0 cleanouts and rollbacks - cons 0 0 76913 0.0 cleanouts only - consistent re 10 10 76913 0.0 cluster key scan block gets 812 812 76913 0.0 cluster key scans 443 443 76913 0.0 cold recycle reads 0 0 76913 0.0 commit cleanout failures: bloc 0 0 76913 0.0 commit cleanout failures: buff 0 0 76913 0.0 commit cleanout failures: call 0 0 76913 0.0 commit cleanout failures: cann 0 0 76913 0.0 commit cleanout failures: hot 0 0 76913 0.0 commit cleanout failures: writ 0 0 76913 0.0 commit cleanouts 9 9 76913 0.0 commit cleanouts successfully 9 9 76913 0.0 consistent changes 10 10 76913 0.0 consistent gets 27,419 27419 76913 0.4 current blocks converted for C 10 10 76913 0.0 cursor authentications 35 35 76913 0.0 data blocks consistent reads - 0 0 76913 0.0 db block changes 55 55 76913 0.0 db block gets 418 418 76913 0.0 deferred (CURRENT) block clean 9 9 76913 0.0 dirty buffers inspected 1 1 76913 0.0 enqueue conversions 0 0 76913 0.0 enqueue deadlocks 0 0 76913 0.0 enqueue releases 342 342 76913 0.0 enqueue requests 349 349 76913 0.0 enqueue timeouts 0 0 76913 0.0 enqueue waits 0 0 76913 0.0 exchange deadlocks 0 0 76913 0.0 execute count 640 640 76913 0.0 free buffer inspected 2 2 76913 0.0 free buffer requested 2,435 2435 76913 0.0 global cache convert time 0 0 76913 0.0 global cache convert timeouts 0 0 76913 0.0 global cache converts 0 0 76913 0.0 global cache cr block receive 0 0 76913 0.0 global cache cr blocks receive 0 0 76913 0.0 global cache cr requests block 0 0 76913 0.0 global cache cr timeouts 0 0 76913 0.0 global cache defers 0 0 76913 0.0 global cache freelist waits 0 0 76913 0.0 global cache get time 0 0 76913 0.0 global cache gets 0 0 76913 0.0 global lock async converts 0 0 76913 0.0 global lock async gets 0 0 76913 0.0 global lock convert time 0 0 76913 0.0 global lock get time 0 0 76913 0.0 global lock releases 0 0 76913 0.0 global lock sync converts 0 0 76913 0.0 global lock sync gets 0 0 76913 0.0 hot buffers moved to head of L 463 463 76913 0.0 immediate (CR) block cleanout 10 10 76913 0.0 immediate (CURRENT) block clea 0 0 76913 0.0 index fast full scans (direct 0 0 76913 0.0 index fast full scans (full) 0 0 76913 0.0 index fast full scans (rowid r 0 0 76913 0.0 instance recovery database fre 0 0 76913 0.0 kcmccs called get current scn 0 0 76913 0.0 kcmgss read scn without going 0 0 76913 0.0 kcmgss waited for batching 0 0 76913 0.0 leaf node splits 0 0 76913 0.0 logons cumulative 8 8 76913 0.0 logons current 7 7 76913 0.0 messages received 26 26 76913 0.0 messages sent 26 26 76913 0.0 native hash arithmetic execute 0 0 76913 0.0 native hash arithmetic fail 0 0 76913 0.0 next scns gotten without going 0 0 76913 0.0 no buffer to keep pinned count 1 1 76913 0.0 no work - consistent read gets 13,076 13076 76913 0.2 opened cursors cumulative 303 303 76913 0.0 opened cursors current 1 1 76913 0.0 opens of replaced files 0 0 76913 0.0 opens requiring cache replacem 0 0 76913 0.0 parse count (hard) 137 137 76913 0.0 parse count (total) 398 398 76913 0.0 parse time cpu 0 0 76913 0.0 parse time elapsed 0 0 76913 0.0 physical reads 2,434 2434 76913 0.0 physical reads direct 0 0 76913 0.0 physical writes 4 4 76913 0.0 physical writes direct 0 0 76913 0.0 physical writes non checkpoint 4 4 76913 0.0 pinned buffers inspected 1 1 76913 0.0 prefetched blocks 1,180 1180 76913 0.0 prefetched blocks aged out bef 0 0 76913 0.0 process last non-idle time 0 0 76913 0.0 queries parallelized 0 0 76913 0.0 recovery array read time 0 0 76913 0.0 recovery array reads 0 0 76913 0.0 recovery blocks read 0 0 76913 0.0 recursive calls 8,593 8593 76913 0.1 recursive cpu usage 0 0 76913 0.0 redo blocks written 23 23 76913 0.0 redo buffer allocation retries 0 0 76913 0.0 redo entries 34 34 76913 0.0 redo log space requests 0 0 76913 0.0 redo log space wait time 0 0 76913 0.0 redo log switch interrupts 0 0 76913 0.0 redo ordering marks 0 0 76913 0.0 redo size 7,468 7468 76913 0.1 redo synch time 0 0 76913 0.0 redo synch writes 2 2 76913 0.0 redo wastage 3,940 3940 76913 0.1 redo write time 0 0 76913 0.0 redo writer latching time 0 0 76913 0.0 redo writes 14 14 76913 0.0 remote instance undo block wri 0 0 76913 0.0 remote instance undo header wr 0 0 76913 0.0 rollback changes - undo record 0 0 76913 0.0 rollbacks only - consistent re 0 0 76913 0.0 rows fetched via callback 1,245 1245 76913 0.0 serializable aborts 0 0 76913 0.0 session connect time 0 0 76913 0.0 session cursor cache count 0 0 76913 0.0 session cursor cache hits 0 0 76913 0.0 session logical reads 27,837 27837 76913 0.4 session pga memory 1,559,588 1559588 76913 20.3 session pga memory max 1,559,588 1559588 76913 20.3 session stored procedure space 0 0 76913 0.0 session uga memory 187,976 187976 76913 2.4 session uga memory max 247,132 247132 76913 3.2 sorts (disk) 0 0 76913 0.0 sorts (memory) 105 105 76913 0.0 sorts (rows) 1,418 1418 76913 0.0 summed dirty queue length 0 0 76913 0.0 switch current to new buffer 0 0 76913 0.0 table fetch by rowid 13,498 13498 76913 0.2 table fetch continued row 109 109 76913 0.0 table scan blocks gotten 2,163 2163 76913 0.0 table scan rows gotten 40,093 40093 76913 0.5 table scans (cache partitions) 0 0 76913 0.0 table scans (direct read) 0 0 76913 0.0 table scans (long tables) 35 35 76913 0.0 table scans (rowid ranges) 0 0 76913 0.0 table scans (short tables) 31 31 76913 0.0 total file opens 49 49 76913 0.0 transaction lock background ge 0 0 76913 0.0 transaction lock background ge 0 0 76913 0.0 transaction lock foreground re 0 0 76913 0.0 transaction lock foreground wa 0 0 76913 0.0 transaction rollbacks 0 0 76913 0.0 transaction tables consistent 0 0 76913 0.0 transaction tables consistent 0 0 76913 0.0 user calls 151 151 76913 0.0 user commits 0 0 76913 0.0 user rollbacks 0 0 76913 0.0 write clones created in backgr 0 0 76913 0.0 write clones created in foregr 0 0 76913 0.0