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
71
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
