oracle第五天

sql调优

在sql references 的那本书里面。最前面的部分可以看到一些，Hints oracle提出的针对性能的一些东西。DBA主要的任务是维护，所以性能的提升主要是oracle和sql的责任。让oracle 选择最优的执行计划，因为我们知道什么样的执行计划是最优的，所以我们可以强制oracle选择我们的执行计划，而不是自己选择，这样来达到调优的作用。

访问路径：

以什么样的模式，oracle以什么样的方式来访问表。

1、 索引访问 只需要访问，扫描极少的符合条件的数据块

2、 全表扫描 table full access

要分析走表与走索引的效率，需要测试一下。索引：适合大河捞小鱼，表：大河捞大鱼。
Hints可以强迫执行计划的选择。

让一列顺序往下排；

update t set id =rownum;

set autot trace

set timing on

当用create table t as select * from t2;的时候，t中会自动填充数据；

update 后直接加表名，而不用table参数、

consistent gets逻辑读Consistent Gets（数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块）

physical reads物理读

full

SELECT /*+ FULL(e) */ employee_id, last_name

FROM hr.employees e要用别名

WHERE last_name LIKE :b1

强迫使用全表扫描

执行计划中，access是直接、取数据，filter是过滤后取数据。

select /*+ FULL(e) */ from test where id >=1 and id<=1000

注意书写格式

ERROR at line 1:

ORA-00936: missing expression

Elapsed: 00:00:00.12

1000 rows selected.

Elapsed: 00:00:00.16

Execution Plan

----------------------------------------------------------

Plan hash value: 1603273050

--------------------------------------------------------------------------------

----

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

|

--------------------------------------------------------------------------------

----

| 0 | SELECT STATEMENT | | 1000 | 137K| 18 (0)| 00:00:

01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1000 | 137K| 18 (0)| 00:00:

01 |

|* 2 | INDEX RANGE SCAN | TEID | 1000 | | 4 (0)| 00:00:

01 |有*的地方代表有条件。缩进最大的地方先执行

--------------------------------------------------------------------------------

----

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID">=1 AND "ID"<=1000)

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

202 recursive calls

0 db block gets

258 consistent gets

0 physical reads

0 redo size

99394 bytes sent via SQL*Net to client

1111 bytes received via SQL*Net from client

68 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

1000 rows processed

多运行几次可以看到稳定的效

full scan 以及range scan

index fast full scan

full scan不把索引当索引，不在需要链表的支持

http://download.oracle.com/docs

SELECT /*+ INDEX (employees emp_department_ix)*/

employee_id, department_id

FROM employees

WHERE department_id > 50;

HASH HINT首先读一个表，读到自己PGA，然后构建hash表，然后读第二表，计算hash值，放到表中。直接找到数据。

优势：每个表只读一遍。select * from t a,t1 b where a.id=b.id;

SQL> create table test2 as select * from test;

Table created.

Elapsed: 00:00:01.75

SQL> create index test2id on test2(id);

Index created.

Elapsed: 00:00:00.21

SQL> select * from test,test2 where test.id=test2.id;

9689 rows selected.

Elapsed: 00:00:00.56

Execution Plan

----------------------------------------------------------

Plan hash value: 1156410790

--------------------------------------------------------------------------------

----

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time

|

--------------------------------------------------------------------------------

----

| 0 | SELECT STATEMENT | | 8985 | 2474K| | 197 (1)| 00:00:

03 |

|* 1 | HASH JOIN | | 8985 | 2474K| 1344K| 197 (1)| 00:00:

03 |

| 2 | TABLE ACCESS FULL| TEST | 8985 | 1237K| | 31 (0)| 00:00:

01 |//离hash近的会创建hash表，然后第二个表会扫表，生成hash value

| 3 | TABLE ACCESS FULL| TEST2 | 9285 | 1278K| | 32 (0)| 00:00:

01 |

--------------------------------------------------------------------------------

----

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("TEST"."ID"="TEST2"."ID")直接取

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

2 recursive calls

0 db block gets

893 consistent gets

75 physical reads

0 redo size

831466 bytes sent via SQL*Net to client

7480 bytes received via SQL*Net from client

647 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

9689 rows processed

SQL> select /*+ USE_NL(a b) */ * from test a,test2 b where a.id=b.id;另外一种

嵌套循环

Elapsed: 00:00:00.39

Execution Plan

----------------------------------------------------------

Plan hash value: 853234687

--------------------------------------------------------------------------------

-------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim

e |

--------------------------------------------------------------------------------

-------

| 0 | SELECT STATEMENT | | 8985 | 2474K| 18012 (1)| 00:

03:37 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 141 | 2 (0)| 00:

00:01 |

| 2 | NESTED LOOPS | | 8985 | 2474K| 18012 (1)| 00:

03:37 |

| 3 | TABLE ACCESS FULL | TEST | 8985 | 1237K| 31 (0)| 00:

00:01 |

|* 4 | INDEX RANGE SCAN | TEST2ID | 1 | | 1 (0)| 00:

00:01 |

--------------------------------------------------------------------------------

-------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("A"."ID"="B"."ID")

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

12524 consistent gets

0 physical reads

0 redo size

831466 bytes sent via SQL*Net to client

7480 bytes received via SQL*Net from client

647 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

9689 rows processed

做连接的时候，驱动表的概念：驱动了你的nest loop循环。驱动表会被读进内存，来创建hash、表或者其他表或者有其他作用。反正就是会被读进内存。

不重复列做驱动表会更好一些；

索引

B树索引

位图索引的创建结构。位图索引使用与列中重复值比较多的情况。

对每个列都有自己的位图。这样由于有很多的列值是 相同的，所以这样位图就有很多不用创建了，重复利用。有大并发地修改的话，不适合用位图。索引是很小的。

创建位图的时候有一个参数，create_bitmap_area_size,如果想更快。分布着pga，可以调高。

用户权限管理

创建用户

create user username identified by password

default tablesapce tablesp

temporary tablesapce tablesp

quota integer k|m |unlimited on tablespace配额

password expire 密码过期

account lock|unlock

profile

这些都可以通过alter来改变的

for example

create user u3 identified by abcde default tablespace users quota 10M on users;

配额允许使用的表空间的大小

没有配额的时候是创建表的时候是会出现错误的。

这个时候还不能够连接，因为新用户还没有赋予该有的权限。

select * from DBA_SYS_PRIVS where grantee='gx';

create session //connect to the database

alter session /issue alter session statements

SQL> select * from DBA_SYS_PRIVS where grantee='U2';

GRANTEE PRIVILEGE ADM

------------------------------ ---------------------------------------- ---

U2 CREATE TABLE NO

U2 UNLIMITED TABLESPACE NO

U2 CREATE SESSION NO

Elapsed: 00:00:00.11

grant select gx.t to u2;

show parameter o7;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

O7_DICTIONARY_ACCESSIBILITY boolean FALSE

alter system set O7_DICTIONARY_ACCESSIBILITY = true scop = profile;

这样当期用户就可以访问数据字典表了

角色

和权限相关的。角色一般没密码

create role role_u1 identified by abc;

create role u1 not identified;

先创建较色，然后赋给它一定的权限，grant drop any table to role_u1;

权限的生效要断开连接后生效。oracle中有很多默认的角色。

撤销revoke。

public

将权限设为public，这样用户就会都默认有这些权限；

grant /

相关视图 dba_sys_privs

dba_role_privs

dba_clo_privs

配置文件

alter user 用户名profile 配置文件

create profile 配置文件名 limit

口令管理

failed_login_attempts N;用户登录多少次仍没成功，锁定

create profile pro2 limit failed_login_attempsts 3 password_lock_time 1;锁定一天

alter user profile pro2;

alter user gx account unlock;解锁

还有password_life_time口令有效期

password_grace_time密码宽限期。

创建密码校验函数

create profile pro3 limit

session _per_user

cpu_per_session