SQL优化(ORACLE)数据库优化技巧

1.oracle 如何得到一个很大的表
在 Oracle 默认的数据库中最大的系统表非 dba_objects 莫属了。 我们可以利用 CTAS 这个表来创建一个很大的表。很快就可以得到几千万的数据量，对测试 提供了很好的环境。 还有一种就是笛卡尔积这个表，得到的数据惊人。 SQL> create table awen.ob_bak as select * from dba_objects; 表已创建。 SQL> select count(*) from awen.ob_bak;
SQL> create index ob_index on awen.ob_bak(owner);
索引已创建。
SQL> set autotrace traceonly; SQL> show parameter sql_trace; SQL> show parameter sql; SQL> select count(*) from awen.ob_bak where owner='awen';
| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
目录
1.oracle 如何得到一个很大的表.........................................................3 2. loop insert 实例.........................................................................5 3. autotrace 验证索引的性能到底有多大？ ..........................................6 4. EXPLAIN 验证 SQL 是否走索引 ......................................................8 5. 结合 autotrace 创建并验证函数索引 ...............................................8 6. sql trace 分析工具——TKPROF 详细讲解........................................ 11 7. V$SQL 视图详解加几个实例........................................................ 15 8. autotrace 验证压缩表性能.......................................................... 17 9. AUTOTRACE 验证消除子查询后的性能.......................................... 20 10. 基于成本的优化 CBO............................................................... 23 11. 如何统计数据库数据 ............................................................... 26 12. oracle 如何统计操作系统数据.................................................... 28 13. /*+parallel(t,4)*/在大表查询性能的体现..................................... 29 14. CRAS 和 create insert 的性能测试............................................... 30 15. 增加字段时指定 default 值和先增加再 update 哪个性能好？ ............. 31 16. 分区表实例讲解 ..................................................................... 33 17. 分区索引实例讲解 .................................................................. 39 18. 分区表管理实例，以及常见的错误.............................................. 40
COUNT(*) ----------
50429 SQL> insert into awen.ob_bak select * from dba_objects; 已创建 50428 行。 SQL> insert into awen.ob_bak select * from dba_objects; 已创建 50428 行。 SQL> insert into awen.ob_bak select * from dba_objects; 已创建 50428 行。 SQL> insert into awen.ob_bak select * from dba_objects; 已创建 50428 行。 SQL> insert into awen.ob_bak select * from dba_objects; 已创建 50428 行。 SQL> insert into awen.ob_bak select * from dba_objects; 已创建 50428 行。 SQL> / 已创建 50428 行。 SQL> /
执行计划 ---------------------------------------------------------Plan hash value: 3442547862
------------------------------------------------------------------------------
COUNT(*) ----------
19364384
还有一种就是笛卡尔积，这种可以翻倍增长。 还有下面用 PL/SQL 也可以实现。
2. loop insert 实例
下面是一个简单的 loop insert 的实例。
SQL> desc over_com;
名称
是否为空? 类型
----------------------------------------- -------- ---------------------------
PL/SQL 过程已成功完成。
SQL> select count(*) from over_com;
COUNT(*) ----------
1000010
3. autotrace 验证索引的性能到底有多大？
下面是通过例子来验证索引的性能到底有多大，一个只要 2 秒，一个要 6 分 22 秒。 看到下面的例子，大家就会意识到索引的重要性了。
------------------------------------------------------------------------------
| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
前面一个是建索引的，后面 的是没有索引的。 先对几个指标解释一下：
recursive calls 递归调用的次数 db block gets 读数据块的数量 consistent gets 总的逻辑 I/O physical reads 物理 I/O redo size 重做数量 bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) 内存排序统计 sorts (disk) 磁盘排序统计 rows processed 被检索的行数
Biblioteka Baidu
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("OWNER"='awen')
统计信息 ----------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 6 | 314K (1)| 00:06:22 |
| 1 | SORT AGGREGATE |
| 1| 6|
| 1 | 6 | 1115 (4)| 00:00:02 |
| 1 | SORT AGGREGATE |
| 1| 6|
|
|
|* 2 | INDEX RANGE SCAN| OB_INDEX | 463K| 2717K| 1115 (4)| 00:00:02 |
------------------------------------------------------------------------------
-
ID
NUMBER(9)
SQL> select * from over_com;
ID ----------
1 1 1 1 1 1 1 1 1 1
已选择 10 行。
SQL> begin 2 for i in 1 .. 1000000 3 loop 4 insert into over_com values(i); 5 end loop; 6 end; 7/
189 recursive calls 0 db block gets 27 consistent gets 2 physical reads 0 redo size
407 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 1 rows processed
SQL> select count(*) from awen.ob_bak2 where owner='awen';
执行计划 ---------------------------------------------------------Plan hash value: 2227305659
已创建 50428 行。 SQL> / 已创建 50428 行。 SQL> / 已创建 50428 行。 SQL> / 已创建 50428 行。 SQL> insert into awen.ob_bak select * from awen.ob_bak; 已创建 605137 行。 SQL> insert into awen.ob_bak select * from awen.ob_bak; 已创建 1210274 行。 SQL> insert into awen.ob_bak select * from awen.ob_bak; 已创建 2420548 行。 SQL> insert into awen.ob_bak select * from awen.ob_bak; 已创建 4841096 行。 SQL> / 已创建 9682192 行。 SQL> select count(*) from awen.ob_bak;