1.“单纯的数据库层面的优化是没有前途的”,你怎么看?
比较同意.
确实现实中大多数技术都是业务驱动的,数据库也是一样,所以如果能够优化业务流程,精简掉不必要的步骤,自然能够带来最好的效果.比如一段SQL效率很差,如果从业务上分析,可以直接把这段SQL砍掉,自然比任何优化都好。
另一方面,数据库层面的优化也是必要的。不可能所有的业务流程都能被精简,因此总是有些过程存在优化的必要,无论是从业务上还是从技术上。如果需要从技术上进行优化,自然就涉及到了数据库层面的优化,不过并不是单纯的数据库层面优化,还是需要对业务有一定的理解,尤其是改写SQL。
--EOF--
2.当一个有性能问题的数据库摆在你的面前,作为责任人,你的处理思路是什么?
a.由宏观到微观: 可先从OS级检查是否有oracle进程占用过高的CPU/内存,然后根据进程找到session,再根据session定位到具体SQL。
b.由微观到宏观: 如果已经能定位到细粒度的问题,则无需再绕道上一个级别去调优。如果已知某SQL语句有问题,则无需到session级调优;已知session有问题,则无需到instance级调优。
--EOF--
3.依照课件上的示例,自己构思2条SQL用于完成相同的功能,并比较它们的性能(要给出SQL演示过程)。
本示例演示了索引可以包含NULL值的特性对min和max函数的影响,参照了《收获,不止Oracle》(梁敬彬,梁敬弘)书中示例。
SQL> drop table t;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
SQL> create index idx_t_objid on t(object_id);
Index created.
SQL> set linesize 200
SQL> set autotrace on
SQL> select min(object_id) min_id, max(object_id) max_id from t;
MIN_ID MAX_ID
---------- ----------
2 3321813
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1555 (2)| 00:00:19 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| T | 381K| 4848K| 1555 (2)| 00:00:19 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
5723 consistent gets
0 physical reads
0 redo size
391 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select (select min(object_id) from t) min_id,(select max(object_id) from t) max_id from dual;
MIN_ID MAX_ID
---------- ----------
2 3321813
Execution Plan
----------------------------------------------------------
Plan hash value: 338405576
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_T_OBJID | 381K| 4848K| 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 13 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| IDX_T_OBJID | 381K| 4848K| 3 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
208 consistent gets
4 physical reads
0 redo size
445 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
--EOF--