For the next few weeks I am going to be going over some of the basics of what you need to know to be a good Oracle DBA. Feel Free to speak up if there is something you think I should cover. I should let you know fast is not always cheap and cheap is not always fast. Think of cheap as Oracle is going to use as few resources as possible to satisfy the query. But if you are using parallel query, then Oracle sets the goal to finish the query as fast as possible and to heck with the resources. Now be carful here; you don’t want to be running an OLTP application with 70+ thousand people hitting it and all of them running parallel query. If you do, you are going to starve the machine of CPU. I’ve seen it happen, it’s not pretty.
Yes Virginia there is a Maria Colgan who is always happy to explain the difference between cheap and fast. If you don’t know Maria, she is one of the smartest people in the Oracle community and heads up the Oracle Optimizer Group. Her blog is at: https://blogs.oracle.com/optimizer/
For my first set of installments I am going to go over access paths (how does Oracle get to the data you want.)
Full Table Scan otherwise know as “Dang it; why won’t oracle use my indexes” There are a number of factors for the optimizer consider to select the best access path to the data.
What predicates have been passed in the query?
What indexes exists?
What degree of parallelism is being used?
What is db_multi_block_read_count?
What hints have been put on the query?
Cardinality – you are going to be reading a lot about that, so start studying.
The Oracle cost based optimizer determines the fastest or most efficient way to access data, sometimes the optimizer will select the full table scan. If you pass a predicate in the query that is not indexed then Oracle has no choice then to do a full table scan. If the cost based optimizer decides it is cheaper to do a full table scan over using an index then Oracle will use a full table scan. I know this is hard to believe but it’s true. Here is a simple example, if Oracle needs to read 100 blocks of an index then do another 100 IOs’ to the table to get your data as apposed doing a full table scan of 150 blocks; the cost based optimizer may decide it would just be cheaper to do a full table scan.
| SQL> create table t1 as select
* from dba_objects; Table created. SQL> analyze table t1 compute statistics; Table analyzed. SQL> create index t1_idx on t1(object_type); Index created. SQL> analyze index t1_idx compute statistics; Index analyzed. SQL> set autotrace on SQL> select owner, object_name 2 from t1 3 where object_type in (‘TABLE’,’INDEX’); ————snip a lot————- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ————————————————————————– | 0 | SELECT STATEMENT | | 2440 | 90280 | 109 (34)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 2440 | 90280 | 109 (34)| 00:00:01 | ————————————————————————– Predicate Information (identified by operation id): ————————————————— 1 – filter(“OBJECT_TYPE”=’INDEX’ OR “OBJECT_TYPE”=’TABLE’) |
|
Hay Maria, Oracle forgot that I had an index on T1. Waaaaaaaa. Oh, it’s cheaper to just read the whole table. |
|
select /*+ index (t1 t1_idx) */ owner, object_name from t1 where object_type in (‘TABLE’,’INDEX’); ————————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ————————————————————————————— | 0 | SELECT STATEMENT | | 2440 | 90280 | 121 (3)| 00:00:01 | | 1 | INLIST ITERATOR | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 2440 | 90280 | 121 (3)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T1_IDX | 2440 | | 10 (10)| 00:00:01 | ————————————————————————————— |
| Hay Maria, I got Oracle to use my index but it cost more. Waaaaaaa, Oh I had to do more IOs’ to get my data. |
As db_multi_block_read_count increases the cost of a full table scan decreases. As parallelism increases, Oracle makes the determination of executing the query as fast as possible as apposed to a cheap as possible. If you put hints into your sql query then those hints will be used as apposed to what the Oracle optimizer believes would be the best way to access the data.
When oracle does a full table scan, blocks are read into the buffer cache and rows that don’t meet the predicate are rejected.