ORACLE SQL 基础与性能调整

 

ORACLE SQL 基础知识

1．    SQL基本语法

SQL语句可以分为6大类，如下表所列。详细请参考oracle sql 手册。

类    别	示例 SQL 语句
数据操纵语言	SELECT INSERT UPDATE DELETE SET TRANSACION EXPLAIN PLAN
数据定义语言	DROP CREAT ALTER GRANT REVOKE
事务控制	COMMIT ROLLBACK SAVEPOINT
会话控制	ALTER SESSION SET ROLE
系统控制	ALTER SYSTEM
嵌入式 SQL	CONNECT DECLARE CURSOR ALLOCATE

表 1 SQL 语句分类和示例

 

2．    ANSI SQL92 与ORACLE SQL 的异同

表格连接

表格连接的类型有几种： LEFT，RIGHT，FULL OUTER，INNER和CROSS

例： ANSI SQL92 ：

SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2
ON table1.column1 = table2.column1;

ORACLE SQL:

(1) SELECT table1.column1, table2.column2 FROM table1, table2, table3 WHERE table1.column1 = table2.column1 AND table1.column1 = table3.column1;

(2) SELECT table1.column1, table2.column2 FROM table1, table2, table3 WHERE table1.column1 = table2.column1 (+) AND table1.column1 = table3.column1 (+);

 

3．    SELECT 语句（ORACLE）

SELECT [/*+ optimizer_hitnts */]

[ALL | DISTINCT] select _list

[FROM

{table_name | view_name | select_statement}]

[WHERE clause]

[GROUP BY group_by_expression]

[HAVING search_condition]

[START WITH … CONNECT BY]

[{UNINON |UNION ALL | INTERSECT| MINUS} SELECT …]

[ORDER BY clause]

[FOR UPDATE]

 

4 ORACLE 函数：字符，数学，日期，转换函数等。

5 子查询和运算符：子查询概念；运算符：IN, NOT IN, EXIST, NOT EXIST。

6 统计计算函数：SUM,AVG,MIN,MAX,COUNT.注意：非单一统计时，要添加GROUP BY 子句

 

SQL性能优化基础知识

1 SQL 调整的步骤

定位 →生成执行计划→重新书写SQL→检验SQL执行效果→调整持久化

2 ORACLE 优化器：基于规则（RBO）和基于成本(CBO)。

优化器模式： RULE 模式，CHOOSE 模式，FIRST_ROWS模式, ALL_ROWS模式。

3 调整措施

（ 1） 调整SQL表访问方式

l         表访问方式：全表扫描，索引扫描（唯一 /非唯一），快速完全索引扫描

l         ORACLE 存储参数和表访问性能

l         避免或改善全表扫描：添加索引来避免全表扫描；全表扫描和并行查询；

（ 2）排序

l         排序的分类

l         排序的初始化参数

l         通过添加所引来避免排序

（ 3）调整表连接

l         表连接类型及评估：等连接，外部连接，自连接，反连接，半连接

l         ORACLE 表连接方法及评估：嵌套循环，散列，排序合并，星型

l         评估表连接顺序： CBO和RBO的差异

（ 4）调整子查询：关联子查询与非关联子查询。目标：尽量转化成标准表连接

（ 5）使用提示来进行调整

l         优化器提示： all_rows, first_rows, rule

l         表连接提示： use_hash, use_merge, use_nl, star

l         表反连接提示： merge_aj, hash_aj

l         Index提示：index_join, and_equal（合并索引）, index_asc, index_desc, no_index, index_combine（位图）, index_ffs（快速完全索引扫描）, use_concat

l         并行提示： parallel, pq_distribute,noparallel

l         表访问提示： full, hash, cluster, no_expand, nocache, ordered, orderd_predicates (指定布尔条件的顺序), push_subq

l         子查询中的提示

 

4. RBO的分级和代价

1）ROWID读取；

2）使用簇连接的单独纪录；

3）使用散列连接的单独纪录；

4）使用主健的单独纪录；

5）簇连接

6）散列簇主健

7）索引簇主健

8）复合主健

9）单字段索引

10）索引字段的结合范围查找

11）索引字段的非结合范围查找

12）排序合并连接

13）索引字段上的MAX或MIN

14) 索引字段上的ORDER BY

15) 全表扫描

 

5． 基于规则优化器（ RBO）的调整：可以预测，但是无法了解表中每个索引的可选择性

特性：总是使用索引；从驱动表开始；最坏的情况才使用全表扫描；任何索引都可以用；

I.              三种调用方法：

①   设置 init.ora参数 optimizer_mode=rule

②   会话层中使用 alter session set optimizer_goal=rule

③   为基于成本的 SQL添加rule 提示：/*+ rule */

II.           驱动表（执行计划中的第一个表）位置与表基数

RBO和CBO 相反：CBO中在指定顺序的情况下，第一个表是驱动表。RBO中驱动表是FROM子句中的最后一个表。

RBO中，经过WHERE子句的过滤后，返回最少的记录的表应该作为驱动表，即放在表列的最后。如果使用的表都包含索引，驱动表应该由所引来决定。

III.       调整步骤

①     调整表顺序：经过 WHERE子句的过滤后，返回最少的记录的表应该作为驱动表。

②     调整布尔条件的顺序：以 AND 分开，限制性最强的布尔条件放在最底层。有多个OR条件时，用use_concat提示。

③     添加基于成本的提示：忽略基于规则的默认优化器模式。

④     评估和调整表连接方法：必须处理嵌套循环连接。不能使用或避免使用散列连接

7．基于成本的优化器模式（CBO）:需要了解表和索引的细节

表数据；记录数目；物理快的数目；索引数据；索引中唯一值的数目；索引中值的分布；索引的可选择性；索引簇因素。字段矩形图数据。

6. SQL评价指标：

SQL_TEXT	VARCHAR2
SHARABLE_MEM	NUMBER
PERSISTENT_MEM	NUMBER
RUNTIME_MEM	NUMBER
SORTS	NUMBER
VERSION_COUNT	NUMBER
OPEN_VERSIONS	NUMBER
USERS_OPENING	NUMBER
EXECUTIONS	NUMBER
USERS_EXECUTING	NUMBER
LOADS	NUMBER
DISK_READS	NUMBER
ROWS_PROCESSED	NUMBER
OPTIMIZER_MODE	VARCHAR2