Oracle--优化

11g的内存分配通过memory_target参数控制
SYS@ db01> show parameter memory

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address         integer     0
memory_max_target             big integer 804M
memory_target                 big integer 804M
shared_memory_address             integer     0

计算当前的PGA大小：
SYS@ db01> select sum(PGA_USED_MEM)/1048576 from v$process;

SUM(PGA_USED_MEM)/1048576
-------------------------
           124.820364

计算当前的SGA大小：
select sum(BYTES)/1048576 sga_mb from v$sgainfo
where name in ('Fixed SGA Size',
               'Redo Buffers',
               'Buffer Cache Size',
               'Shared Pool Size',
               'Large Pool Size',
               'Java Pool Size',
               'Streams Pool Size');

    SGA_MB
----------
520.399376

查看SGA子内存池大小的参数
select name,value from v$parameter
where name in
('shared_pool_size',
'db_cache_size',
'large_pool_size',
'java_pool_size',
'streams_pool_size',
'log_buffer');
通过参数控制内存组件收缩的下限
alter system set db_cache_size=200m;
===================================================================================
--库缓冲区高速缓存命中率建议>90%
select sum(pinhits)/sum(pins) "Hit Ratio" from v$librarycache;
字典缓冲区高速缓存命中率建议>80%
select (1-(sum(getmisses)/sum(gets)))*100 "Hit Ratio" from v$rowcache;
共享池使用率75%~90%
select 1-(a.BYTES/b.BYTES) from v$sgastat a,v$sgainfo b where a.POOL='shared pool' and a.name='free memory' and b.NAME='Shared Pool Size';
数据缓冲区高速缓存命中率建议>90%
select NAME,1-(PHYSICAL_READS/(CONSISTENT_GETS+DB_BLOCK_GETS)) from v$buffer_pool_statistics;
===================================================================================
屏蔽11g的内存管理新特性
alter system reset memory_max_target scope=spfile sid='*';
alter system reset memory_target scope=spfile sid='*';

启用10g的内存管理手段
alter system set sga_target=804m scop=spfile;
startup force

pga单独管理：
show parameter pga_aggregate_target

确定user process的sid：为了找到对应的server process
SCOTT@ db01> select sid from v$mystat where rownum=1;

       SID
----------
       148

如何找到user process对应的server process
v$session.sid --> v$session.paddr = v$process.addr --> v$process.PGA_USED_MEM

select PGA_USED_MEM/1048576 from v$session s,v$process p where p.addr=s.paddr and s.sid=148;

查看pga中cursor info中的游标：
show parameter session_cached_cursors [50]
为快速软解析保存sql语句，缓解shared pool的压力
select sql_text from v$open_cursor where sql_text like 'select * from scott.emp%';

pga中的排序区如何管理
show parameter workarea_size_policy [auto]
[auto]由oracle服务器在pga_aggregate_target所指定的范围之内自动调整pga的大小

查看pga分配建议：
select PGA_TARGET_FOR_ESTIMATE/1048576,ESTD_PGA_CACHE_HIT_PERCENTAGE from v$pga_target_advice;

OLTP： 80% total memory
20% --> pga
80% --> sga

OLAP:80% total memory
50% --> pga
50% --> sga

实验：
[auto]
select sid from v$mystat where rownum=1;
select * from ob1 order by 5,4,3,2,1;
select sid from v$mystat where rownum=1;
[MANUAL]
SCOTT@ db01> alter session set workarea_size_policy=manual;
SCOTT@ db01> set autot trace exp
SCOTT@ db01> select * from ob1 order by 5,4,3,2,1;
SCOTT@ db01> alter session set sort_area_size=1048576;
SCOTT@ db01> select * from ob1 order by 5,4,3,2,1;

sga单独管理:
show parameter sga
sga内存页面预分配：
alter system set pre_page_sga=true scope=spfile;
sga不做磁盘页面交换：锁定在物理内存当中
alter system set lock_sga=true scope=spfile;

vi /etc/security/limits.conf
-----------------------------
oracle soft memlock unlimited
oracle hard memlock unlimited
-----------------------------

在自动管理模式下控制子内存池收缩的下限：
select name,value from v$parameter
where name in
('shared_pool_size',
'db_cache_size',
'large_pool_size',
'java_pool_size',
'streams_pool_size');

查看sga分配的建议：
select SGA_SIZE,ESTD_DB_TIME from v$sga_target_advice;

共享池：保存sql和pl/sql语句块，加速sql解析，减少sql语句重解析
查看共享池中缓存的内容
select sql_text from v$sqlarea where sql_text like 'select * from scott.emp%';

为了提高共享池的使用效率，推荐使用绑定变量！

什么样的sql适合使用绑定变量改写？
sql主文本相同，只有where条件取值不同！
表中键值分布尽量均衡！
（表中键值分布状态极度倾斜，不适合使用绑定变量）
解释绑定变量
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
create table scott.t01 (id int,name varchar2(10));

begin
  for i in 1..10000 loop
    insert into scott.t01 values (i,dbms_random.string('u',10));
  end loop;
  commit;
end;
/
使用字符条件做查询的效率：
declare
  type ctype is ref cursor;
  c ctype;
  r scott.t01%rowtype;
begin
  for i in 1..10000 loop
    open c for 'select * from scott.t01 where id='||i;
    fetch c into r;
--    dbms_output.put_line(r.id||' '||r.name);
    close c;
  end loop;
end;
/

使用绑定变量做条件查询的效率：
declare
  type ctype is ref cursor;
  c ctype;
  r scott.t01%rowtype;
begin
  for i in 1..10000 loop
    open c for 'select * from scott.t01 where id=:1' using i;
    fetch c into r;
--    dbms_output.put_line(r.id||' '||r.name);
    close c;
  end loop;
end;
/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
数据缓冲区高速缓存命中率建议>90%
select NAME,1-(PHYSICAL_READS/(CONSISTENT_GETS+DB_BLOCK_GETS)) from v$buffer_pool_statistics;

alter system set db_cache_size=60m;
alter system set db_keep_cache_size=120m;
alter system set db_recycle_cache_size=20m;

alter table dept storage (buffer_pool keep);
alter table ob1 storage (buffer_pool recycle);
===================================================================================
oracle 8i：
PGA：手工管理
session_cached_cursors=50
bitmap_merge_area_size        
create_bitmap_area_size     
hash_area_size            
sort_area_size

oracle 8i：
SGA：手工管理
shared_pool_size --> 共享池的大小 K M G
db_block_buffers --> 以oracle块为单位指定数据库缓冲区告诉缓存的大小
java_pool_size -->
large_pool_size -->
log_buffer --> 只能使用byte作为单位，不能使用K M G

oracle 9i：
PGA：自动管理,带有_area_size的参数全报废
workarea_size_policy = AUTO | manual
pga_aggregate_target=100m

oracle 9i：不重新启动实例的情况下，内存组件的大小可以调整，log_buffer除外！
SGA：手工管理 sga_max_size = 900m  (Granule=4m & 8m sga<128m sga>128m)
shared_pool_size
db_cache_size
java_pool_size -->
large_pool_size -->
log_buffer --> 只能使用byte作为单位，不能使用K M G
不需要重新启动实例，内存可以调整（log_buffer除外），上限是sga_max_size！

oracle 10g：
PGA：自动管理

oracle 10g：
SGA：自动管理 sga_max_size = 900m >= sga_target = 900m (MMAN MMON MMNL)
shared_pool_size
db_cache_size
java_pool_size -->
large_pool_size -->
streams_pool_size  -->
*log_buffer -->

查询进程的pga使用情况
select PGA_USED_MEM/1048576 from v$process
where addr=(select paddr from v$session where sid=&sid);
        
PGA+SGA = 80% TOTAL MEMORY
OLTP : pga 20% sga 80%
OLAP : pga 50% sga 50%

oracle 11g：/dev/shm
PGA+SGA自动管理
memory_max_target = 使用内存的上限
memory_target = 自动管理的上限
------------------------------------------------------------
确定memory_target的分配是否合理：
select MEMORY_SIZE,ESTD_DB_TIME from v$memory_target_advice;
自动管理模式下如果使用了手工管理的参数，参数值将作为收缩下限
alter system set shared_pool_size=200m;
alter system set db_cache_size=400m;
large_pool_size
java_pool_size
streams_pool_size
------------------------------------------------------------
alter system reset memory_max_target scope=spfile sid='*';
alter system reset memory_target scope=spfile sid='*';
alter system reset shared_pool_size scope=spfile sid='*';
alter system reset db_cache_size scope=spfile sid='*';
===============================================================================
确定scott的sid:
SCOTT@ orcl> select sid from v$mystat where rownum=1;
使用排序消耗pga
SCOTT@ orcl> select * from ob1 order by 5,4,3,2,1;
观察pga的增长：
SYS@ orcl> select PGA_USED_MEM/1048576 from v$process where ADDR=(select paddr from v$session where sid=21);
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
使用变量消耗pga内存
定义对象类型
CREATE OR REPLACE TYPE pga_type AS OBJECT
(
id NUMBER,
name char(2000)
);
/

定义表类型
create or replace type t_pga_table as table of pga_type;
/

向表类型变量持续填充数据
create or replace procedure proc_test_pga as
v_pga t_pga_table := t_pga_table();
begin
loop
  v_pga.extend();
  v_pga(v_pga.count) := pga_type(1, 'pga_test');
end loop;
end;
/

执行procedure

SQL>exec proc_test_pga;

跟踪会话的pga使用：
select sid from v$mystat where rownum=1;
sid
----
133

select PGA_USED_MEM/1048576 from v$process
where addr=(select paddr from v$session where sid=133);
================================================================================
PGA中缓存的游标数量：快速软解析,降低shared_pool的压力
session_cached_cursors = 50
select sql_text from v$open_cursor where sql_text like 'select * from scott.emp%';
================================================================================
pga的手工管理：
alter session set workarea_size_policy=manual;

管理的排序区的参数：
bitmap_merge_area_size--为位图索引创建的内存区域
create_bitmap_area_size--创建位图索引时使用的内存区域
hash_area_size--表做哈希连接的时候保存内存哈希表的区域
sort_area_size--排序区

set autot trace exp
select * from ob1 order by 5,4,3,2,1;
alter session set sort_area_size=131072;
select * from ob1 order by 5,4,3,2,1;

pga的自动管理：
show parameter pga_aggregate_target

select PGA_TARGET_FOR_ESTIMATE/1048576,ESTD_PGA_CACHE_HIT_PERCENTAGE from v$pga_target_advice;

select * from v$pgastat;

PGA:进程全局区
 UGA：用户全局区（独占模式时存在于pga，共享模式时存在于SGA，没有large pool时存放到shared pool）
 session info
 cursor info
 sort area
 打开的游标
 java会话信息
 激活的角色
 NLS参数
 打开的dblink

 CGA:调用全局区
 运行时的sql语句（解析、执行的sql）
================================================================================
SGA的管理：
alter system set sga_target=800m scope=spfile;
startup force

查看sga分配建议：
select SGA_SIZE,ESTD_DB_TIME from v$sga_target_advice;

自动管理模式下如果使用了手工管理的参数，参数值将作为收缩下限
alter system set shared_pool_size=200m;
alter system set db_cache_size=400m;
查看sga各个组件的大小
select * from v$sgainfo;

SGA手工管理：
alter system set sga_target=0;
评估sga_target是否合理：
select SGA_SIZE,ESTD_PHYSICAL_READS from v$sga_target_advice;

找到重复的相似sql
select substr(sql_text,1,30),count(*) from v$sqlarea group by substr(sql_text,1,30) having count(*)>10;

使用绑定变量替换字符条件，提高sql语句命中率
select * from scott.emp where empno=:b1;

找到版本计数高的sql
select sql_text,version_count from v$sqlarea where version_count>3;

select addr from v$process where spid=4131;
--addr=paddr
select SQL_HASH_VALUE from v$session where paddr='00000000894CB320';
--SQL_HASH_VALUE=hash_value
select sql_text from v$sqlarea where hash_value=52081782;
捕获即时SQL：
select sql_text from v$sqlarea where hash_value in
(select SQL_HASH_VALUE from v$session s,v$process p where s.paddr=p.addr and p.spid=&pid);

查看等待事件
select event,p1,p2,p3 from v$session where uSername='SCOTT';
查看等待事件类别
select distinct WAIT_CLASS#,WAIT_CLASS from v$event_name order by 1;
查看等待事件的参数含义
select parameter1,parameter2,parameter3 from v$event_name where name='db file scattered read';
================================================================================
SGA手工管理：sga_target=0
col name for a30
select name,value/1048576 from v$parameter
where name in ('shared_pool_size',
'db_cache_size',
'java_pool_size',
'large_pool_size',
'log_buffer',
'streams_pool_size');

评估共享池的分配是否合理：
shared_pool_size=264m
shared_pool_reserved_size=20M

select SHARED_POOL_SIZE_FOR_ESTIMATE,ESTD_LC_MEMORY_OBJECT_HITS from v$shared_pool_advice;

转储共享池的信息：查看共享池的空间分配单位
alter session set events 'immediate trace name heapdump level 10';

cat orcl_ora_3735.trc | grep Bucket

Bucket 0 size=32
Bucket 1 size=40
Bucket 2 size=48

查看共享池保留区的空间分配单位
select indx,ksppinm from x$ksppi where ksppinm like '%shared_pool%';
182 _shared_pool_reserved_min_alloc

select KSPPSTDVL from x$ksppcv where indx=182;
KSPPSTDVL
---------
4400

评估数据库缓冲区高速缓存的分配是否合理：
select SIZE_FOR_ESTIMATE,ESTD_PHYSICAL_READS from v$db_cache_advice;

select * from (select EVENT,TOTAL_WAITS,TIME_WAITED,AVERAGE_WAIT,WAIT_CLASS# from v$system_event order by 3 desc) where rownum<11;

select SID,EVENT,P1,P2,P3 from v$session_wait where sid=18;

查看数据库缓冲区高速缓存的命中率：
SELECT name, 1 - (physical_reads /
(db_block_gets + consistent_gets)) "HIT_RATIO"
FROM v$buffer_pool_statistics
WHERE db_block_gets + consistent_gets > 0;

db_keep_cache_size --> keep池
db_cache_size --> default池
db_recycle_cache_size --> recycle池

查看对象使用哪一个内存池：
select table_name,buffer_pool from user_tables;
修改对象使用的内存池：
alter table dept storage (buffer_pool keep);
alter table dept storage (buffer_pool default);
alter table ob1 storage (buffer_pool recycle);

非标准块大小的内存池：db_block_size=8192  --> db_8k_cache_size
db_16k_cache_size
db_2k_cache_size    
db_32k_cache_size
db_4k_cache_size    

大块提高吞吐量，但是争用严重，适合保存宽的表
小块IO频繁，但是争用不严重！适合保存窄的表

非标准块大小的表空间：
select tablespace_name,block_size from dba_tablespaces;
alter system set db_16k_cache_size=8m;
create tablespace tbs16 datafile '/u01/app/oracle/oradata/orcl/tbs16.dbf' size 10m blocksize 16k;
================================================================================
memory_max_target
mamory_target
sga_target
sga_max_size
shared_pool_size
db_cache_size
db_16k_cache_size
db_2k_cache_size    
db_32k_cache_size
db_4k_cache_size
db_keep_cache_size
db_recycle_cache_size
java_pool_size
larget_pool_size
streams_pool_size
log_buffer
================================================================================

declare
 v_job varchar2(10);
begin
  for i in 1..500 loop
    dbms_lock.sleep(0.01);
    execute immediate 'alter system checkpoint';
  end loop;
end;
/

select sid,event,p1,p2,p3 from v$session where username='SYS';

SID EVENT                  P1         P2     P3
--- ------------------------------ ---------- ---------- ----------
125 SQL*Net message to client      1650815232          1      0
141 rdbms ipc reply              12   21474836      0

select parameter1,parameter2,parameter3 from v$event_name where name='rdbms ipc reply';

SYS> select ADDR from v$process where PID=12;

ADDR
----------------
0000000090CC4430

Elapsed: 00:00:00.00
SYS> select name from v$bgprocess where paddr='0000000090CC4430';

NAME
-----
CKPT

top --> system pid --> v$proccess.spid : addr --> v$session.paddr:sql_hash_value
--> v$sqlarea.hash_value:sql_text

================================================================================
查看系统下的快照：
select SNAP_ID,STARTUP_TIME from dba_hist_snapshot order by 1;

手工生成快照
exec dbms_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

生成 AWR 报告：
SYS> @?/rdbms/admin/awrrpt

对问题sql生成报告：AWR SQL RPT
@?/rdbms/admin/awrsqrpt
7ptx8v9nhakbc

查看快照保留策略：
select * from dba_hist_wr_control;
查看基线保留时间长度
select dbid,baseline_name,baseline_type,moving_window_size from dba_hist_baseline;
修改基线保留时间长度
exec dbms_workload_repository.modify_baseline_window_size(2);
修改快照保留策略
exec dbms_workload_repository.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60,RETENTION=>2880);

生成 AWR 报告可能会出现的问题：
select event,p1,p2,p3 from v$session where username='SYS';
select segment_name,segment_type,owner from dba_extents where 9402 between block_id and block_id+blocks-1 and file_id=2;

导出AWR快照
SYS> @?/rdbms/admin/awrextr
导入AWR快照
SYS> @?/rdbms/admin/awrload
生成AWR 报告：
SYS> @?/rdbms/admin/awrrpti
同时对比两份报告
SYS> @?/rdbms/admin/awrddrpi
================================================================================
SYS@ orcl> select * from v$diag_info;
adrci
adrci> show homes
adrci> set home diag/rdbms/orcl/orcl
adrci> show alert -tail
adrci> show alert -tail 20
adrci> show alert -tail -f
adrci> show alert -tail -p "MESSAGE_TEXT like 'ORA-%'"
adrci> show tracefile -t
adrci> purge -age 2880 -type trace
adrci> show problem
adrci> show incident
adrci> ips pack incident 24181
adrci> ips show package
adrci> ips unpack file /home/oracle/ORA4030_20170711153016_COM_1.zip into /home/oracle
================================================================================
调整IO：减少IO & 分散IO

如何减少IO：最常用的手段使用索引

通过查看SQL语句的执行计划来跟踪索引使用
SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

如果打开SQL跟踪报错：
SQL> set AUTOT ON
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
解决办法：
SQL> conn / as sysdba
SQL> @?/sqlplus/admin/plustrce
SQL> grant plustrace to scott;
SQL> conn scott/tiger
SQL> set AUTOT ON  结果集、执行计划、统计信息
SQL> set AUTOT OFF

SQL> set AUTOT TRACE 执行计划、统计信息
SQL> set AUTOT OFF

SQL> set AUTOT TRACE EXP 执行计划
SQL> set AUTOT OFF

SQL> set AUTOT TRACE STAT 统计信息
SQL> set AUTOT OFF

索引分类：
逻辑分类
 单列索引 & 复合列索引
   create index i_emp_ename on e01 (ename);
   create index i_emp on emp (deptno,ename);
 非唯一键索引 & 唯一键索引
   create unique index i_e01_ename on e01 (ename);
 基于函数索引
   create index i_e01_ename on e01 (lower(ename));
 应用程序域索引
   全文检索

物理分类
  非分区索引 & 分区索引 (分散索引的IO，通常时配合分区表在使用)
create index i_e01_ename on e01 (deptno,ename) global partition by range (deptno)
(partition i10 values less than (20) tablespace tbs1,
partition i20 values less than (30) tablespace tbs2,
partition i30 values less than (40) tablespace tbs3,
partition idef values less than (maxvalue) tablespace tbs4);
 
select index_name,partition_name,tablespace_name from user_ind_partitions;

避免索引右侧叶子的分裂产生争用：
create index i_e01_ename on e01 (deptno,ename) global partition by hash (deptno) partitions 4;

B*Tree索引:不记录空值(null)
  常规B-Tree
    create index i_e01_ename on e01 (ename)
    分析索引结构有效性
    analyze index i_e01_ename validate structure;
    查看索引架构
    select NAME,HEIGHT,BLOCKS,BR_BLKS,BR_ROWS,LF_BLKS,LF_ROWS from index_stats;
    回收叶子的碎片：不能降低二元高度
    alter index I_E01_ENAME coalesce;
    索引重建:使用老的索引树建立新的索引，索引有坏块无法重建
    alter index I_E01_ENAME rebuild;
    alter index I_E01_ENAME rebuild tablespace tbs1;
    索引联机重建:使用表中关键字重新建立新索引，
    alter index I_E01_ENAME rebuild online;
    延迟主键：
    alter table e01 add constraint pk_e01_empno primary key
    (empno) initially deferred;
  反向键B-Tree（倒排索引）：
     create index i_e01_ename on e01 (ename) reverse;
位图索引：可以记录空值,适合创建在低基数（重复值多的列：性别）
    create bitmap index i_e02_empno on e02 (empno);
================================================================================
索引的扫描算法：
索引唯一键扫描:INDEX UNIQUE SCAN
select * from emp where empno=7788;

索引范围扫描:INDEX RANGE SCAN
select * from e01 where empno<200;

索引快速扫描:INDEX FAST FULL SCAN
select empno from e01;

索引全扫描:INDEX FULL SCAN
select empno from e01 order by 1;

索引跳跃扫描:INDEX SKIP SCAN 必须是符合列索引；前导列不出现在where字句；要拥有列的统计信息！
create index i_ob3 on ob3 (owner,object_type,object_name);
analyze table ob3 compute statistics for all indexed columns;
select owner,object_type,object_name from ob1 where object_type='RULE';
================================================================================
影响索引使用的因素：
隐式数据类型转换影响索引使用：
create table t01 (id varchar2(13),name varchar2(20));
SCOTT@ orcl> insert into t01 values (1,'tom');
SCOTT@ orcl> insert into t01 values (2,'jerry');
SCOTT@ orcl> commit;
SCOTT@ orcl> create index i_t01_id on t01 (id);
SCOTT@ orcl> select * from t01 where id=1;
SCOTT@ orcl> select * from t01 where id='1';

查询返回的数据量影响索引使用：
create table ob1 as select * from dba_objects;
create index i_ob1_id on ob1 (object_id);
analyze table ob1 compute statistics;

select * from ob1 where object_id<20209; --> .232311389

初始化参数影响索引使用：
索引权重：区值越小索引越重要
optimizer_index_cost_adj=100 [1%~10000%]
全表的成本 VS 索引访问的成本*optimizer_index_cost_adj
alter session set optimizer_index_cost_adj=80;

最佳优化模式：
optimizer_mode = ALL_ROWS | first_rows_n | first_rows_100
alter session set optimizer_mode = first_rows_100;



索引的聚簇因子：顺序访问索引键值而在表块上面跳转的次数

update e02 set empno=round(dbms_random.value(1,229376));
create index i_e01_empno on e01 (empno);

create table e03 as select * from e02 order by empno;
create index i_e02_empno on e02 (empno);

analyze table e01 compute statistics;
analyze table e02 compute statistics;

select * from e03 where empno<46571;
select * from e02 where empno<376;

[5000]:1315
select dbms_rowid.rowid_block_number(rowid),count(*)
from e02
where empno<5000
group by dbms_rowid.rowid_block_number(rowid);

SQL> select blocks from user_tables where table_name='E01';

    BLOCKS
----------
       751

单块读取722 和批量读取751
select * from aux_stats$;

IOSEEKTIM=10(IO探查时间10毫秒)
IOTFRSPEED=4096(IO传输速度每毫秒4k) --> 8个8K

select indx,ksppinm from x$ksppi where ksppinm like '%db_file%';
select KSPPSTDVL from x$ksppcv where INDX=1156;

select 12*722 from dual;
select (751/8)*26 from dual;

[5000]:27
select dbms_rowid.rowid_block_number(rowid),count(*)
from e03
where empno<5000
group by dbms_rowid.rowid_block_number(rowid);
=======================================================================
create table t01 (x int);
insert into t01 values (4);
insert into t01 values (8);
insert into t01 values (9);
insert into t01 values (16);
create table t02 (x int);
insert into t02 values (2);
insert into t02 values (5);
insert into t02 values (10);
insert into t02 values (15);
create table t03 (x int);
insert into t03 values (1);
insert into t03 values (7);
insert into t03 values (11);
insert into t03 values (14);
create table t04 (x int);
insert into t04 values (3);
insert into t04 values (6);
insert into t04 values (12);
insert into t04 values (13);
create table t05 (x int);
insert /*+append*/ into t05 select * from t01;
commit;
insert /*+append*/ into t05 select * from t02;
commit;
insert /*+append*/ into t05 select * from t03;
commit;
insert /*+append*/ into t05 select * from t04;
commit;
create index i_t05_x on t05 (x);
select clustering_factor from user_indexes where index_name='I_T05_X';

select dbms_rowid.rowid_block_number(rowid),count(*)
from T05
group by dbms_rowid.rowid_block_number(rowid);

倒排索引会使聚簇因子增高：
alter index I_E02_EMPNO rebuild reverse;
select table_name,index_name,clustering_factor from user_indexes where table_name in ('E01','E02');

================================================================================
11g索引新特性：
透明索引：没有真正的创建索引
create table ob1 as select * from dba_objects;
analyze table ob1 compute statistics;
create index i_ob1_id on ob1 (object_id) nosegment;
set autot trace exp
select * from ob1 where object_id<5000;
alter session set "_use_nosegment_indexes"=true;

使用参数控制不可见索引的使用-->有针对性的选择索引对哪些SQL语句可用
create index I_OB1_ID on ob1 (object_id) INVISIBLE;
set autot trace exp
select * from ob1 where object_id<5000;
alter session set optimizer_use_invisible_indexes=true;--在要使用索引的sql语句前将该开关就可以打开
alter index I_OB1_ID VISIBLE;
alter index I_OB1_ID invisible;
================================================================================
转储索引块数据
oradebug setmypid
alter session set events 'immediate trace name treedump level 92380';
oradebug tracefile_name

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- begin tree dump
branch: 0x1008003 16809987 (0: nrow: 21, level: 1) --分枝块地址
   leaf: 0x1008004 16809988 (-1: nrow: 485 rrow: 485)
   leaf: 0x1008005 16809989 (0: nrow: 479 rrow: 479)
   leaf: 0x1008006 16809990 (1: nrow: 479 rrow: 479)
   leaf: 0x1008007 16809991 (2: nrow: 479 rrow: 479)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SYS@ orcl> select to_number('8003','xxxx') from dual;

TO_NUMBER('8003','XXXX')
------------------------
           32771

dump分枝块32771数据
alter system dump datafile 4 block 32771;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
kdxbrlmc 16809988=0x1008004 -- 32772 --> 28084 -->指向第一个叶子的指针
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8047] dba: 16809989=0x1008005 --32773 --> 28084 & 28085
col 0; len 3; (3):  c2 05 57 --486
col 1; TERM
row#1[8038] dba: 16809990=0x1008006 --32774 --> 28084 & ??
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

dump叶子块32772的数据
alter system dump datafile 4 block 32772;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
kdxconro 485
kdxcofbo 1006=0x3ee
kdxcofeo 1830=0x726
kdxcoavs 824
kdxlespl 0
kdxlende 0
kdxlenxt 16809989=0x1008005
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 02 --4号文件，28084号块
col 1; len 6; (6):  01 00 6d b4 00 00
row#1[8008] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 00 6d b4 00 01
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
通过索引rowid计算行所在的数据块编号
SYS@ orcl> select to_number('6db4','xxxx') from dual;

TO_NUMBER('6DB4','XXXX')
------------------------
           28084

SYS@ orcl> select dump(486) from dual;

DUMP(486)
---------------------
Typ=2 Len=3: 194,5,87

SYS@ orcl> select to_char(194,'xxx'),to_char('5','xx'),to_char('87','xx') from dual;

TO_C TO_ TO_
---- --- ---
  c2   5  57

dump函数的反向函数：
SYS@ orcl> select utl_raw.cast_to_number(replace('c2 05 57',' ')) from dual;

UTL_RAW.CAST_TO_NUMBER(REPLACE('C20557',''))
--------------------------------------------
                     486

SYS@ orcl> select object_id from dba_objects where object_name='I_T01_ID';

 OBJECT_ID
----------
     92380

================================================================================
全文检索实验：
野蛮修改数据库字符集
select userenv('language') from dual;
shutdown immediate
startup mount
alter system enable restricted session;--使数据库运行在受限访问模式下
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
alter database open;

alter database character set internal_use zhs16gbk;
alter system set job_queue_processes=1000;
alter system set aq_tm_processes=1;
alter system disable restricted session;

SELECT USERNAME FROM DBA_USERS ORDER BY 1;
--如果没有ctxsys用户，说明没有安装全文检索

安装全文检索：
@?/ctx/admin/catctx
Enter value for 1: oracle (ctxsys用户的口令)
Enter value for 2: users (ctxsys用户的默认表空间)
Enter value for 3: temp (ctxsys用户的临时表空间)
Enter value for 4: nolock (ctxsys用户解锁)
卸载全文检索：
--@?/ctx/admin/catnoctx

设置词法分析器(lexer)
conn / as sysdba
select userenv('language') from dual;
USERENV('LANGUAGE')
-------------------------------------
AMERICAN_AMERICA.ZHS16GBK

grant execute on ctxsys.ctx_ddl to public;
conn scott/tiger
create table scott.t1 (id number(4) constraint pk_shi2_id primary key,
author varchar2(20),
title varchar2(30),
writing varchar2(200));

INSERT INTO t1 VALUES (1,'李白','静夜思 ','床前明月光疑是地上霜举头望明月低头思故乡 ');

INSERT INTO t1 VALUES (2,'陈子昂 ','登幽州古台 ' ,'前不见古人后不见来者念天地之悠悠独怆然而涕下 ');
commit;

exec ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');
--exec ctx_ddl.DROP_PREFERENCE('my_lexer');--删除语法分析器

CREATE INDEX scott.I_FULLTEXT_t1 ON scott.t1(writing) indextype is ctxsys.context parameters('lexer my_lexer');--创建全文索引

---------------------------------------------------------------------------------
报错：
CREATE INDEX scott.I_FULLTEXT_t1 ON scott.t1(writing) indextype is ctxsys.context parameters('lexer my_lexer')
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_WORDLIST
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

grep -r DEFAULT_WORDLIST /u01/app/oracle/product/11.2.0/db_1/ctx/admin

/u01/app/oracle/product/11.2.0/db_1/ctx/admin/defaults/drdefptb.sql:  CTX_DDL.create_preference('DEFAULT_WORDLIST','BASIC_WORDLIST');

conn ctxsys/oracle
@/u01/app/oracle/product/11.2.0/db_1/ctx/admin/defaults/drdefptb.sql
---------------------------------------------------------------------------------
测试全文索引
set autot trace exp
select writing from scott.t1 where contains(writing,'明月')>0;

================================================================================
优化全表扫描：减少无关的块的访问，减少IO的次数
（3种方法）
识别全表扫描：
col OBJECT_NAME for a15
col OPERATION for a20
col OPTIONS for a10
select HASH_VALUE,OBJECT_NAME,OPERATION,OPTIONS from v$sql_plan where lower(OPTIONS) like '%full%' and OBJECT_OWNER='SCOTT';
通过hash值找到执行全表扫描的sql语句
select sql_text from v$sqlarea where hash_value=709788671;

1.查看表的碎片情况:降低高水位,减少无关的块的访问
set serverout on  --打开标准输入输出
declare      --查看碎片情况
v_UNFORMATTED_BLOCKS number;
v_UNFORMATTED_BYTES NUMBER;
v_FS1_BLOCKS NUMBER;
v_FS1_BYTES NUMBER;
v_FS2_BLOCKS NUMBER;
v_FS2_BYTES NUMBER;
v_FS3_BLOCKS NUMBER;
v_FS3_BYTES NUMBER;
v_FS4_BLOCKS NUMBER;
v_FS4_BYTES NUMBER;
v_FULL_BLOCKS NUMBER;
v_FULL_BYTES NUMBER;
begin
dbms_space.SPACE_USAGE(
'SCOTT','OB1','TABLE',
v_UNFORMATTED_BLOCKS,
v_UNFORMATTED_BYTES,    
v_FS1_BLOCKS,
v_FS1_BYTES,        
v_FS2_BLOCKS,        
v_FS2_BYTES,        
v_FS3_BLOCKS,        
v_FS3_BYTES,        
v_FS4_BLOCKS,        
v_FS4_BYTES,        
v_FULL_BLOCKS,        
v_FULL_BYTES);
dbms_output.put_line('UNFORMATTED_BLOCKS: '||v_UNFORMATTED_BLOCKS);
dbms_output.put_line('FULL_BLOCKS: '||v_FULL_BLOCKS);
dbms_output.put_line('0~25%: '||v_FS1_BLOCKS);
dbms_output.put_line('25~50%: '||v_FS2_BLOCKS);
dbms_output.put_line('50~75%: '||v_FS3_BLOCKS);
dbms_output.put_line('75~100%: '||v_FS4_BLOCKS);
end;
/
如果碎片严重，则回收碎片/move表到一个新的表空间（索引要重新建）
alter table scott.ob1 enable row movement;
alter table scott.ob1 shrink space;--回收空闲空间
alter table scott.ob1 move tablespace users; --将表move到一个新的表空间


2.提高IO吞吐量：减少IO的次数(128K是Linux操作系统i/o上限)
（表空间占用不连续不要盲目放大IO吞吐量）
11g之前
db_file_multiblock_read_count             --控制I/O吞吐量参数（default值是8）
11g：
_db_file_optimizer_read_count             --（隐含参数）

查看隐藏参数（_以下划线为前缀的参数是oracle自己作调试的参数）
select indx,ksppinm from x$ksppi where ksppinm like '%db_file%';
查看隐含参数的值
desc x$ksppcv
select KSPPSTDVL from x$ksppcv where INDX=1156;

使用后台事件跟踪sql语句优化器的参数选择，找到控制IO吞吐量的参数
declare
  errm varchar2(1000);
begin
  for errno in 10000..11000 loop
    errm:=sqlerrm(-errno);
    dbms_output. put_line(errm);
  end loop;
end;
/

使用事件跟踪sql语句执行时的优化器参数：
alter session set events '10053 trace name context forever ,level 1';
select * from scott.emp;       --10053事件可以把sql语句的行为脱机到跟踪文件
alter session set events '10053 trace name context off'; --关闭10053事件

打开跟踪文件搜索MBRC（multiblock read count）= 8
select indx,ksppinm from x$ksppi where ksppinm like '%db_file%';
_db_file_optimizer_read_count

SQL> select KSPPSTDVL from X$KSPPCV where INDX=1156;

KSPPSTDVL
----------
8

alter session set "_db_file_optimizer_read_count"=16;  --修改I/O吞吐量
alter session set "_db_file_optimizer_read_count"=32;

3.使用并行查询（并行：隐式使用多个session来执行同一条sql）
select /*+parallel(ob1 4)*/ * from ob1;    --野蛮霸占4个cpu来执行sql语句
select /*+parallel(ob1 4)*/ * from scott.ob1 order by object_id;

查看可用事件：
set serverout on
declare
   ora_mess varchar2(1000);
begin
   for i in 10200..10300 loop
      ora_mess:=sqlerrm(-i);
      dbms_output.put_line(ora_mess);
   end loop;
end;
/
使用10231事件全表扫描时跳过坏块
alter system set events '10231 trace name context forever,level 10';
================================================================================
消除行的迁移：（存在迁移的行会降低数据操作的效率）--减少I/O的一种手段
update操作会产生行的迁移；可变长度类型会产生行的迁移
drop table scott.t purge;
create table scott.t (x int,name varchar2(50)) tablespace tbs5;

begin
  for i in 1..1000 loop
    insert into scott.t values (i,'A');
  end loop;
  commit;
end;
/

analyze table scott.t compute statistics;--分析表
select chain_cnt from dba_tables where table_name='T01' and owner='SCOTT';  --查看行的迁移

update scott.t set name=rpad('A',50,'A');
commit;
analyze table scott.t compute statistics;
select chain_cnt from dba_tables where table_name='T' and owner='SCOTT';

move也可以消除行的迁移:开销大（适用于行迁移数据多的情况）
重新插入数据消除行迁移:（适用与海量数据中有少量行迁移的数据）
@?/rdbms/admin/utlchain      --使用脚本创建chained_rows表
desc chained_rows
analyze table t list chained rows into chained_rows; --分析表的时候将找到的行的迁移的rowid插入到表chained_rows中

create table tmp as select * from t where rowid in (select HEAD_ROWID from chained_rows);   --将行迁移的数据存放到临时表中

delete t where rowid in (select HEAD_ROWID from chained_rows);  --删除原表中的行迁移数据
insert into t select * from tmp;  --重新插入
commit;
analyze table t compute statistics;
select chain_cnt from user_tables where table_name='T';
=============================================================================

=============================================================================
优化INSERT：nologging
select force_logging from v$database;   --nologging所有操作都有日志产生（库级别force_logging级别最高）
alter database no force logging;

truncate table e02 reuse storage;

直接路径加载：不可恢复的操作（做完该操作记得备份）
insert /*+append*/ into e03 select * from e01 nologging;   --加速数据装载
------------------------------------------------------------------
(/*+append*/直接使用高水位指向的空白块插数据，不使用高水位以下的空闲空间，使用append做insert操作时不提交事务查询操作会失败；nologging 选项丢弃日志)
SCOTT@ db01> select * from e01;
select * from e01
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

insert /*+append*/ into e03 select * from e01;
insert into e03 select * from e01 nologging;
------------------------------------------------------------------
使用create代替update & delete：
create table e04 as select case EMPNO when EMPNO then rownum end empno,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from e02;
create index i_e04_empno on e04 (empno);


begin
dbms_parallel_execute.create_task('TEST PL');

dbms_parallel_execute.create_chunks_by_rowid(
task_name=>'TEST PL',
table_owner=>'SCOTT',
table_name=>'E03',
by_row=>true,
chunk_size=>20000);

dbms_parallel_execute.run_task(
task_name=>'TEST PL',
sql_stmt=>'update /*+ ROWID (dda) */ scott.E03 set empno=1 WHERE rowid BETWEEN :start_id AND :end_id',
language_flag=>DBMS_SQL.NATIVE,
parallel_level=>4
);
commit;
dbms_parallel_execute.drop_task('TEST PL');
end;
/
=============================================================================
使用条带化分散IO：
1.同一表空间下使用多个数据文件
create table t06 (x int) segment creation immediate storage (initial 15m) tablespace uplooking;   --建表时分配15M段空间

alter table t06 allocate extent (size 9m datafile '/u01/app/oracle/oradata/orcl/uplooking01.dbf');   --手工为表分配段空间

select file_id,block_id,blocks from dba_extents where segment_name='T06'; --查看表占用表空间情况

2.使用段的分区技术:空间占用2G以上或者行的数据量千万以上,表分区的数量1024k-1（100万个分区）(生产系统下大表一般都要做成分区表)
（分区表partition table:逻辑上是一个表，物理上由多个表组成,之前的那些表都叫堆表（heap table））
create tablespace tb1 datafile '/u01/app/oracle/oradata/db01/tb01.dbf' size 20m;
create tablespace tb2 datafile '/u01/app/oracle/oradata/db01/tb02.dbf' size 20m;
create tablespace tb3 datafile '/u01/app/oracle/oradata/db01/tb03.dbf' size 20m;
create tablespace tb4 datafile '/u01/app/oracle/oradata/db01/tb04.dbf' size 20m;
create tablespace tb5 datafile '/u01/app/oracle/oradata/db01/tb05.dbf' size 20m;
create tablespace tb6 datafile '/u01/app/oracle/oradata/db01/tb06.dbf' size 20m;
create tablespace tb7 datafile '/u01/app/oracle/oradata/db01/tb07.dbf' size 20m;
create tablespace tb8 datafile '/u01/app/oracle/oradata/db01/tb08.dbf' size 20m;

select * from (select owner,segment_name,blocks/128 from dba_segments order by 3 desc) where rownum<11;
范围分区：适合使用事件纬度作分区
单列的范围分区
create table t01
(empno number(4),
ename varchar2(10),
hiredate date)
partition by range (hiredate)  --分区键
(partition p01 values less than (to_date('2017-02-01','yyyy-mm_dd')) tablespace tb1,
partition p02 values less than (to_date('2017-03-01','yyyy-mm_dd'))  tablespace tb2,
partition p03 values less than (to_date('2017-04-01','yyyy-mm_dd')) tablespace tb3);
查看分区表信息：
select table_name,partition_name ,tablespace_name from user_table_partition;
移动表分区到指定表空间：
alter table t01 move partition p03 tablespace tbs4;  
增加分区：
alter table t01 add partition p04 values less than (to_date('2017-05-01','yyyy-mm_dd')) tablespace tbs4;
删除表分区：
alter table t01 drop partition p04;

insert into t01 values (1,'tom',to_date('2017-01-03','yyyy-mm_dd'));
insert into t01 values (2,'tom',to_date('2017-01-01','yyyy-mm_dd'));
insert into t01 values (3,'tom',to_date('2017-02-01','yyyy-mm_dd'));
insert into t01 values (4,'tom',to_date('2017-02-05','yyyy-mm_dd'));
insert into t01 values (5,'tom',to_date('2017-03-15','yyyy-mm_dd'));
insert into t01 values (6,'tom',to_date('2017-04-10','yyyy-mm_dd'));

复合列范围分区：（不常用）
create table t01
(empno number(4),
ename varchar2(10),
hiredate date)
partition by range (empno,hiredate)  --左侧分区键优先级别高
(partition p01 values less than (500,to_date('2017-02-01','yyyy-mm_dd')) tablespace tb1,
partition p02 values less than (1000,to_date('2017-03-01','yyyy-mm_dd')) tablespace tb2,
partition p03 values less than (1500,to_date('2017-04-01','yyyy-mm_dd')) tablespace tb2);

insert into t01 values (6,'tom',to_date('2017-04-05','yyyy-mm_dd'));

打开行移动：解决跨分区修改（修改分区键）（行移动的本质是将updata操作转换成delete+insert)
行移动出现的初始目的是给分区表操作的
alter table t01 enable row movement;  
分区表的两个查询的执行计划相同
select * from t01 partition (p01);
select * from t01 where hiredate<to_date('2017-02-01','yyyy-mm_dd');
------------------------------------------------------------------------------
分区表上的索引：
分区表上的全局索引：（很少出现)
create index_i_t01_empno on t01(empno);
本地分区索引：为每一个表分别创建索引,表分区增加时索引自动增加，索引的存储字句继承表分区存储子句
create index i_t01_hd on t01 (hiredate) local;
select index_name,partition_name,tablespace_name from user_ind_partitions；
全局分区索引：索引的分区规则和表的分区规则没有任何关系（表的分区键没有出现在where条件后，按照查询条件自己创建索引的分区规则），*如果表分区增加了，索引要重新创建
create index i_e01_ename on t01 (deptno,ename) global partition by range (empno)
(partition i10 values less than (20) tablespace tbs1,
partition i20 values less than (30) tablespace tbs2,
partition i30 values less than (40) tablespace tbs3,
partition idef values less than (maxvalue) tablespace tbs4);
------------------------------------------------------------------------------
LIST列表分区：适合使用地区纬度作分区
create table t02
(empno number(4),
name varchar2(10),
city varchar2(4))
partition by list (city)
(partition p010 values ('010') tablespace tbs1,
partition p021 values ('021') tablespace tbs2);
查看分区表的属性(high_value列存储分区最大值)
select table_name,partition_name,high_value from user_tab_partitions;

HASH分区：没有规则，通常不会作为主分区的分区手段，作为子分区的分区手段（只能起到分散IO的作用）
--hash分区的分区键要是高基列，分区数量最好时2的n次幂
create table e05 partition by hash (empno) partitions 4
store in (tbs1,tbs2,tbs3,tbs4)
as select * from e04;

create table e05 partition by hash (empno)
(partition p1 tablespace tbs1,
partition p2 tablespace tbs2,
partition p3 tablespace tbs3,
partition p4 tablespace tbs4)
as select * from e04;

select table_name,partition_name,tablespace_name from user_tab_partitions
where table_name='E05';

复合分区：
create table t03
(empno number(4),
ename varchar2(10),
deptno number(2))
partition by range (deptno)
SUBPARTITION BY HASH (empno)
subpartitions 4
STORE IN (tbs5,tbs6,tbs7,tbs8)
(partition p10 values less than (20),
partition p20 values less than (30) store in (tbs1,tbs2,tbs3,tbs4),
partition p30 values less than (40)
(subpartition p30_1 tablespace users,
subpartition p30_2 tablespace example)
);
查看带有复合分区的分区表信息
select table_name,partition_name,subpartition_name,tablespace_name from user_tab_subpartitions where table_name='T03';

获取创建分区表的元数据：
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);--屏蔽存储子句
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',false); --屏蔽表空间
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false); --屏蔽约束
select dbms_metadata.get_ddl('TABLE','E05') from dual;

select dbms_metadata.get_ddl('TABLE',u.table_name) from
(select table_name from user_tables) u;

合并分区
select PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME='T01';
alter table t01 merge partitions p01,p02 into PARTITION p02 tablespace tbs2;
撕裂分区(range)
alter table t01 split partition p02 at (to_date('2017-02-01','yyyy-mm_dd'))
into (partition p01 tablespace tbs1,partition p02 tablespace tbs2);

alter table t01 split partition p03 at (to_date('2017-03-15','yyyy-mm_dd'))
into (partition p31,partition p03);
---------------------------------------------------------------------------------
alter table t02 merge partitions p010,p021 into PARTITION p021;
撕裂分区(list)
alter table t02 split partition p021 values ('010')
into (partition p010,partition p022);

逻辑导出可以导出指定表分区
exp scott/tiger tables=t01:p02 file=p01.dmp
删除分区
alter table t01 drop partition p04;
移动分区
alter table t02 move partition p010 tablespace tbs5;
截断分区
alter table t02 truncate partition p010;
重命名分区
alter table t02 rename partition P022 to p021;

select table_name,partition_name from user_tab_partitions wherE table_name='T01';
----------------------------------------------------------------------------------
oracle 11g新特性：system分区
create table t01
(empno number(4),
ename varchar2(10),
hiredate date)
partition by system
(partition p01 tablespace tbs1,
partition p02 tablespace tbs2,
partition p03 tablespace tbs3);
插入时决定数据进入到哪一个分区：
insert into t01 partition (P01) select EMPNO,ENAME,HIREDATE from e01 where deptno=10;
insert into t01 partition (P02) select EMPNO,ENAME,HIREDATE from e01 where deptno=20;
insert into t01 partition (P02) select EMPNO,ENAME,HIREDATE from e01 where deptno=10;

oracle 11g新特性：自动扩展分区
create table t04
(empno number(4),
ename varchar2(10),
hire_date date)
partition by range (hire_date)
interval (numtodsinterval (1,'day'))
(partition p1 values less than (to_date('2017-01-01','yyyy-mm_dd')));

select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='T04';

insert into t04 values (1,'tom',to_date('2017-01-01','yyyy-mm_dd'));
insert into t04 values (2,'tom',to_date('2017-01-02','yyyy-mm_dd'));

select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='T04';
-----------------------------------------------------------------------------------
IOT表（索引组织表）：不占存储（无段空间占用）;查询速度快，insert慢;以索引树来保存数据
（IOT表适合在高效检索的时候用）
CREATE TABLE countries_demo
( country_id      CHAR(2) CONSTRAINT country_c_id_pk_demo PRIMARY KEY
,country_name    VARCHAR2(40)
,currency_name   VARCHAR2(25)
,currency_symbol VARCHAR2(3)
,region          VARCHAR2(15))
ORGANIZATION INDEX;

CREATE TABLE "SCOTT"."E06"
   (    "EMPNO" NUMBER(4,0),
    "ENAME" VARCHAR2(10),
    "JOB" VARCHAR2(9),
    "MGR" NUMBER(4,0),
    "HIREDATE" DATE,
    "SAL" NUMBER(7,2),
    "COMM" NUMBER(7,2),
    "DEPTNO" NUMBER(2,0),
     CONSTRAINT "PK_E06" PRIMARY KEY ("EMPNO"))
ORGANIZATION INDEX;
-----------------------------------------------------------------------------------
集簇表（cluster table）：物理上是一个段（cluster segment），该段存储多个表的信息
带有集簇键的表做表关联效率高
段的类型：temporary segment 、table segment 、rollback segment
   
CREATE CLUSTER partment (deptno number(2));  --创建集簇键
CREATE INDEX idx_partment ON CLUSTER partment;

CREATE TABLE c_dept
   CLUSTER partment (deptno)  --共享集簇键
   AS SELECT * FROM dept;

CREATE TABLE c_emp
   CLUSTER partment (deptno)  --共享集簇键
   AS SELECT * FROM emp;

select e.ename,d.loc from c_emp e,c_dept d where e.deptno=d.deptno and e.deptno=30;

SQL> select dbms_rowid.rowid_block_number(rowid),count(*) from c_dept group by dbms_rowid.rowid_block_number(rowid);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ----------
                4396          1
                4398          1
                4395          1
                4399          1

SQL> select dbms_rowid.rowid_block_number(rowid),count(*) from c_emp group by dbms_rowid.rowid_block_number(rowid);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ----------
                4398          6
                4395          5
                4399          3


system --> recyclebin
create table
drop table e01 [purge];
-------------------------------------------------------------------------------
堆表转换成其他类型的表：联机重定义（ETL）
ETL原理：开始重定义的时候在原表后建一张小表（快照日志snapshort log），开始重定义以后对原表的所有操作都存放在快照日志中，然后再将原表的大量数据导入到中间表中；同步数据的时候同步的是快照日志中的数据变化；结束重定义的时候在原表加锁（lock），然后交叉改名。
1.创建中间表
create table tmp
("EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0))
partition by range (deptno)
(partition p10 values less than (20) tablespace tb1,
partition p20 values less than (30) tablespace tb2,
partition p30 values less than (40) tablespace tb3
);

2.开始重定义：同步大量数据（生产库中常把234步骤放到一起做（一个begin end））
conn / as sysdba
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE (
'SCOTT','EMP','TMP',
'EMPNO EMPNO,
ENAME ENAME,
JOB JOB,
MGR MGR,
HIREDATE HIREDATE,
SAL SAL,
COMM COMM,
DEPTNO DEPTNO');
END;
/

3.同步数据：同步的是快照中的数据变化
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT','EMP','TMP');
END;
/

4.结束重定义
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT','EMP','TMP');
END;
/

5.如果重定义失败，强制结束联机重定义：
BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE('SCOTT','EMP','TMP');
END;
/
================================================================================
锁：lock，使用队列机制，保护磁盘数据结构

ddl lock : 字典锁！（保护对象结构）
 select table_name,table_lock from user_tables;
 alter table t01 enable table lock;
dml lock : 事务锁!持续到事务结束！0 - none
1 - null
2 - row-s     行共享
3 - row-x     行排它
4 - shared    共享锁
5 - s/row-x   共享行排它
6 - exclusive 排它锁（独占锁）

select TYPE,NAME,DESCRIPTION from V$LOCK_TYPE where type='KD' order by 1 desc;
-----------------------------------------------------------------------------
查看锁资源队列流程
查看当前会话sid
select sid from v$mystat where rownum=1;
查看锁队列的脚本（该脚本会生成一个报表）
@?/rdbms/admin/utllockt
查看锁定的对像ID
select object_id,locked_mode from v$locked_object;
通过对象号查看锁定对象的详细信息
select owner,object_name,object_type from dba_objects where object_id=;
查看产生锁的sql语句
select sql_hash_value,prev_hash_value from v$session where sid=26;
select sql_text from v$sqlarea where hash_value=0000;
通过会话的sid找到会话所在的终端
select machine from v$session where sid=26;
如果是批处理程序出现异常，可以杀掉会话
select sid,serial# from v$session where sid=26;
alter system kill session 'sid,serial#' immediate;
alter system kill session '26,47' immediate;
---------------------------------------------------------------------------------
insert /*+append*/ into e02 select * from e02;
会在表上加6级锁，后台是多个事务，如果append不commit，数据不可以读写！
select file_id,block_id,blocks from dba_extents where segment_name='E02';
select header_file,header_block from dba_segments where segment_name='E02';
dump出数据块研究
SYS@ db01> conn / as sysdba
SYS@ db01> alter system checkpoint;
SYS@ db01> alter system dump datafile 4 block 626;
SYS@ db01> oradebug setmypid
SYS@ db01> oradebug tracefile_name
---------------------------------------------------------------------------------
select * from v$lock where sid in (22,147);
分解v$lock.id1:
select trunc(327708/power(2,16)) usn,bitand(327708,to_number('ffff','xxxx')) slot from dual;

select sid,serial#,event,machine from v$session where sid=147;

通过事务的起点判断锁的起点：
select SES_ADDR,XIDUSN,XIDSLOT,XIDSQN,START_DATE from v$transaction;

alter system kill session '147,75' immediate;

create table t01 (x int,name varchar2(50)) pctfree 0;
begin
  for i in 1..800 loop
    insert into t01 values (i,'A');
  end loop;
  commit;
end;
/

insert操作入对，使用排除法：bitmap index,unique index,unique key,primary key

create table d tablespace tbs1 as select * from dept;
create table e tablespace tbs2 as select * from emp;
alter table d add constraint pk_d_deptno primary key (deptno);
alter table e add constraint fk_e_deptno foreign key (deptno) references d;
alter tablespace tbs2 offline;
delete d where deptno=40;
ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: '/u01/app/oracle/oradata/orcl/tbs02.dbf'

update e set deptno=10;
select SID,TYPE,ID1,ID2,LMODE,REQUEST,BLOCK from v$lock where sid in (22,125);

   SID TY     ID1        ID2      LMODE    REQUEST       BLOCK
------ -- ---------- ---------- ---------- ---------- ----------
   125 TM      88828          0      3        0           0
   125 TM      88829          0      3        0           0
   125 TX     262169        836      6        0           0

col OBJECT_NAME for a10
select object_id,object_name from user_objects where object_id in (88828,88829);

 OBJECT_ID OBJECT_NAM
---------- ----------
     88828 D
     88829 E

轻量级锁（闩）:公平竞争，有限共享，保护内存结构和内存对象
愿意等待型的闩：
SELECT * from (select NAME,GETS,MISSES,SLEEPS from v$latch order by GETS desc) where rownum<11;
SELECT * from (select NAME,GETS,MISSES,SLEEPS from v$latch order by MISSES desc) where rownum<11;
SELECT * from (select NAME,GETS,MISSES,SLEEPS from v$latch order by SLEEPS desc) where rownum<11;
立即型的闩：
select * from (select NAME,IMMEDIATE_GETS,IMMEDIATE_MISSES from v$latch order by IMMEDIATE_GETS desc) where rownum<11;
select * from (select NAME,IMMEDIATE_GETS,IMMEDIATE_MISSES from v$latch order by IMMEDIATE_MISSES desc) where rownum<11;
================================================================================
优化SQL:
1.识别高资源消耗的SQL
select * from (select sql_text,OPTIMIZER_COST from v$sqlarea order by 2 desc nulls last) where rownum<11;

select * from (select sql_text,DISK_READS from v$sqlarea order by 2 desc nulls last) where rownum<11;

select * from (select sql_text,BUFFER_GETS from v$sqlarea order by 2 desc nulls last) where rownum<11;

select * from (select sql_text,VERSION_COUNT from v$sqlarea order by 2 desc nulls last) where rownum<11;

select * from (select sql_text,EXECUTIONS from v$sqlarea order by 2 desc nulls last) where rownum<11;

活动会话历史
@?/rdbms/admin/ashrpt
自动数据库性能诊断
@?/rdbms/admin/addmrpt
@?/rdbms/admin/awrsqrpt

2.判断sql语句中设计的对象的统计信息是否是最新的
select table_name,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from user_tables;

3.查看sql语句的执行计划和【运行时的资源消耗】
awrrpt [6gvch1xu9ca3g] --> awrsqrpt -->

4.分析sql执行计划：【从里向外从上向下】
判断无用游标
索引的使用，索引扫描算法
表的连接算法
内嵌视图的引用，视图执行的顺序
谓词的推进
优先优化成本高的子模块
RBO & CBO
/*+rule*/
o7版本引入了对象的统计信息！在查询执行之前能够估算语句成本！根据成本筛选执行计划！

Cost=(读取单块的时间消耗+读取多块的时间消耗+CPU的时间消耗)/读取一个8K的时间消耗

create tablespace tbs9 datafile '/u01/app/oracle/oradata/orcl/tbs09.dbf' size 100m;
create table scott.t09 (x int,y varchar2(100)) pctfree 99 tablespace tbs9;
begin
  for i in 1..20000 loop
    insert into scott.t09 values (i,rpad('A',100,'A'));
  end loop;
  commit;
end;
/

analyze table t09 compute statistics;

Cost=(批量读取时间消耗+CPU的时间消耗)/读取一个8K的时间消耗
====================================================================
标准块大小：
db_block_size=8192
IO吞吐量：
_db_file_optimizer_read_count = 8
HWM：
select blocks from user_tables where table_name='T09';
20297
语句在cpu上循环的次数：
explain plan for select * from scott.t09;
select cpu_cost from plan_table;
147943868
====================================================================
select * from aux_stats$;
CPUSPEEDNW=3074.07407 --> CPU主频 Mhz/s
IOSEEKTIM=10 --> IO探查时间（毫秒）
IOTFRSPEED=4096 -->IO传输速度(4096byte/毫秒)
====================================================================
读取一个8K的时间消耗 = IO探查时间+db_block_size/IO传输速度 = 12毫秒
====================================================================
读取8个8K的时间消耗 = IO探查时间+db_block_size*8/IO传输速度 = 26毫秒
====================================================================
批量读取时间消耗=(HWM/_db_file_optimizer_read_count)*读取8个8K的时间消耗
             =(20297/8)*26
====================================================================
CPU的时间消耗 = 语句在cpu上循环的次数/CPU主频 = 147943868/3074.07407
====================================================================
Cost = ceil((((20297/8)*26)+(147943868/3074074.07))/12)+1 = 5503
====================================================================

收集表的统计信息：
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS
('SCOTT',
degree=>8,
options=>'GATHER');
END;
/

options=>'GATHER' 重新分析统计信息        
options=>'GATHER EMPTY' 只分析没有统计信息的表
options=>'GATHER STALE' 对数据变化超过10%的表进行分析
options=>'GATHER AUTO'  GATHER EMPT+GATHER STALE

将大表排除在GATHER_SCHEMA_STATS之外：锁定表的统计信息
BEGIN
DBMS_STATS.LOCK_TABLE_STATS('SCOTT','T09');
END;
/

BEGIN
DBMS_STATS.UNLOCK_TABLE_STATS('SCOTT','T09');
END;
/

针对大表单独做分析：
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
'SCOTT','OB2',
estimate_percent=>100,
degree=>8);
END;
/

删除统计信息
exec DBMS_STATS.delete_table_stats('SCOTT','DEPT');

导出统计信息
1.创建保存统计信息的表
exec DBMS_STATS.CREATE_STAT_TABLE ('SCOTT','TEST');

2.导出统计信息
BEGIN
DBMS_STATS.EXPORT_TABLE_STATS ('SCOTT','OB1',stattab=>'TEST');
END;
/

exp scott/tiger tables=test file=test.dmp
scp /home/oracle/test.dmp oracle@oracle5:/home/oracle/
imp scott/tiger file=test.dmp full=y

3.
BEGIN
DBMS_STATS.IMPORT_TABLE_STATS ('SCOTT','OB1',stattab=>'TEST');
END;
/

4.跨用户导入统计信息
imp system/uplooking file=test.dmp fromuser=scott touser=tom

UPDATE TEST SET C5='目标用户';
commit;

BEGIN
DBMS_STATS.IMPORT_TABLE_STATS ('TOM','OB1',stattab=>'TEST');
END;
/

设置统计信息
BEGIN
DBMS_STATS.SET_TABLE_STATS(
'SCOTT','OB1',
numrows=>5000000,
numblks=>50000);
END;
/

禁止统计信息发布：
select table_name from dict where table_name like 'DBA%STAT%';
EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'OB1', 'PUBLISH', 'FALSE');
使用未发布的统计信息测试
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;
新的统计信息产生的执行计划是正确的，那么发布统计信息
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SCOTT', 'OB1');
EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'OB1', 'PUBLISH', 'TRUE');
查看与统计信息相关的属性
select * from DBA_TAB_STAT_PREFS where table_name='OB1';

还原统计信息：
select table_name from dict where table_name like 'DBA%STAT%';
select OWNER,TABLE_NAME,STATS_UPDATE_TIME from DBA_TAB_STATS_HISTORY where table_name='OB1';
EXEC DBMS_STATS.RESTORE_TABLE_STATS('SCOTT','OB1','19-JUL-17 10.35.24.987917 AM +08:00');

5.增加强制或者改写sql
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
语句级别调整优化器：
/*+ optimizer_features_enable('11.2.0.1') */
/*+ optimizer_features_enable('10.2.0.5') */
/*+ optimizer_features_enable('10.2.0.4') */
/*+ optimizer_features_enable('10.2.0.1') */

强制使用RBO：
/*+rule*/

强制走索引
/*+ index(e i_e_ename)*/
强制不走索引
/*+ no_index(e i_e_ename)*/
强制走全表扫描
/*+ full(e)*/

改变表连接算法
/*+ use_nl(e,d)*/
/*+ use_merge(e,d)*/
/*+ use_hash(e,d)*/
改变第一驱动表
/*+ leading (d) use_nl(e,d)*/

为子查询命名
/*+ qb_name(tmp)*/
强制调度子查询（使子查询优先运行）
/*+push_subq(@tmp)*/

select ob1.object_name from
ob1,ob2
where ob1.object_name like '%EMP%'
and ob2.object_type=ob1.object_type
and 100 in
(SELECT ob3.object_id FROM ob3 WHERE ob1.object_id = ob3.object_id);

统计信息是否存在是否陈旧
缺失访问路径（少索引）

select /*+push_subq(@tmp)*/ ob1.object_name from
ob1,ob2
where ob1.object_name like '%EMP%'
and ob2.object_type=ob1.object_type
and 1000 in
(SELECT /*+qb_name(tmp)*/ ob3.object_id FROM ob3 WHERE ob1.object_id =ob3.object_id);
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
稳定概要：
select e.ename,d.loc
from emp e,dept d
where e.deptno=d.deptno;

select /*+index(e pk_emp)*/ e.ename,d.loc
from emp e,dept d
where e.deptno=d.deptno;

稳定概要：将执行计划存盘，交换硬盘上的执行计划
conn / as sysdba
打开outln用户：
alter user outln identified by outln account unlock;

相关的权限
grant create any outline to scott;
grant alter any outline to scott;
grant drop any outline to scott;

--在新的会话中起用存储概要类别demo
conn scott/tiger
alter session set create_stored_outlines = demo;
set autot trace exp

select ename,loc
from emp e,dept d
where e.deptno=d.deptno;

关掉创建概要，避免概要太多，乱
alter session set create_stored_outlines = false;
set autot off

存储的概要细节，得到概要名称，一会要通过名称来交换概要
col name for a30
col category for a10
col sql_text for a30
select name, category, used, sql_text
from user_outlines
where category = 'DEMO';

NAME                   CATEGORY   USED     SQL_TEXT
------------------------------ ---------- ------ -----------------------------
SYS_OUTLINE_17071914232018809  DEMO      UNUSED select ename,loc
                         from emp e,dept d
                         where e.deptno=d.deptno

在demo类中创建带有提示的概要，用来替换老的概要:
create or replace outline e_join_d_outline
for category demo on
select /*+ index(e pk_emp)*/ ename,loc
from emp e,dept d
where e.deptno=d.deptno;

交换两个概要:(在outln用户下做)
conn outln/outln

update outln.ol$hints
set ol_name =
decode(
      ol_name,
      'E_JOIN_D_OUTLINE','SYS_OUTLINE_17071914232018809',
      'SYS_OUTLINE_17071914232018809','E_JOIN_D_OUTLINE'
      )
where ol_name in ('SYS_OUTLINE_17071914232018809','E_JOIN_D_OUTLINE');

确保概要数量一致:
update outln.ol$ ol1
set hintcount = (
                select hintcount
                from outln.ol$ ol2
                where ol2.ol_name in ('SYS_OUTLINE_17071914232018809',
'E_JOIN_D_OUTLINE')
                and ol2.ol_name != ol1.ol_name
                )
where outln.ol1.ol_name in
('SYS_OUTLINE_17071914232018809','E_JOIN_D_OUTLINE');

验证概要交换是否成功：
conn scott/tiger
alter session set use_stored_outlines = demo;
set autot trace exp

select ename,loc
from emp e,dept d
where e.deptno=d.deptno;
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
使用SQL基线实现稳定计划：
conn / as sysdba
grant ADMINISTER SQL MANAGEMENT OBJECT to scott;

conn scott/tiger
set autot trace exp
select
e.ename,d.loc
from emp e,dept d
where e.deptno=d.deptno;

set autot off
获得语句sql_id:
select sql_id,sql_text from v$sqlarea where sql_text like '%e.deptno=d.deptno%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------
1120n10avzfrp
select e.ename,d.loc from emp e,dept d where e.deptno=d.deptno

将语句加入基线:
declare
  v_sql_id pls_integer;
begin
  v_sql_id:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'1120n10avzfrp');
end;
/

查看当前的SPM:
col SQL_HANDLE for a20
col SQL_TEXT for a30
col PLAN_NAME for a40
select sql_handle,sql_text,plan_name  from dba_sql_plan_baselines;

SQL_HANDLE         SQL_TEXT                PLAN_NAME
-------------------- ------------------------------ ----------------------------------------
SQL_055b8599084b5a63 select                SQL_PLAN_0aqw5m444qqm35ac47e2d
             e.ename,d.loc
             from emp e,dept d
             where e.deptno=d.deptno

加hint调整SQL语句:
select /*+index(e pk_emp)*/
e.ename,d.loc
from emp e,dept d
where e.deptno=d.deptno;

获得语句sql_id:
SQL> select sql_id,plan_hash_value,sql_text from v$sqlarea where sql_text like '%index(e pk_emp)%';

SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ------------------------------
5mmn45d5835g3       4161849850
select /*+index(e pk_emp)*/ e.ename,d.loc from emp e,dept d where e.deptno=d.deptno

将正确计划加入基线:
declare
  v_sql_id pls_integer;
begin
  v_sql_id:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'5mmn45d5835g3',plan_hash_value=>4161849850,sql_handle =>'SQL_055b8599084b5a63');
end;
/

查看基线:
验证计划正确性:
select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE('&sql_handle','&PLAN_NAME'));
删除原始计划:
var b1 number
exec :b1:=dbms_spm.DROP_SQL_PLAN_BASELINE('&SQL_HANDLE','&PLAN_NAME');

打开sql跟踪，运行sql语句验证计划被更换
set autot trace exp
select /*+ use_nl(e,d)*/
e.ename,d.loc
from emp e,dept d
where e.deptno=d.deptno;
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
SQL自动优化建议：
conn / as sysdba
grant ADVISOR to scott;

对指定SQL生成优化建议:
conn scott/tiger
DECLARE
    my_task_name VARCHAR2(30);
    my_sqltext CLOB;
BEGIN
    my_sqltext := 'select a.object_name,b.object_id from ob1 b, ob2 a';
    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_text => my_sqltext,
        user_name => 'SCOTT',
        scope => 'COMPREHENSIVE',
        time_limit => 60,
        task_name => 'TEST_sql_tuning_task',
        description => 'Task to tune a query on a specified PRODUCT');
    dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');
END;
/

查看优化建议
set long 10000
set linesize 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_sql_tuning_task') FROM DUAL;

删除优化建议
exec DBMS_SQLTUNE.DROP_TUNING_TASK('TEST_sql_tuning_task');
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
计算工资高于本部门平均工资10%的雇员有多少？
select count(*) from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno)*1.1;

select count(*) from
 emp e,
 (select deptno,avg(sal) avgsal from emp group by deptno) a
where e.deptno=a.deptno
and e.sal>a.avgsal*1.1;
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
查询重写:使用物化视图（实体化视图 & 快照）
计算月份销售额:
SELECT t.calendar_month_desc,sum(s.amount_sold) AS dollars
FROM sales s,times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

conn / as sysdba
grant CREATE MATERIALIZED VIEW to scott;

CREATE MATERIALIZED VIEW CAL_MONTH_SALES_MV
refresh on commit
ENABLE QUERY REWRITE
as
SELECT t.calendar_month_desc,sum(s.amount_sold) AS dollars
FROM sales s,times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

analyze table CAL_MONTH_SALES_MV compute statistics;

begin
DBMS_MVIEW.REFRESH
   ('CAL_MONTH_SALES_MV','cf');
end;
/

计算年度销售额:
SELECT t.CALENDAR_YEAR, sum(s.amount_sold) AS dollars
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY t.CALENDAR_YEAR;

CREATE DIMENSION TIMES_DIM
LEVEL "DAY" IS ("TIMES"."TIME_ID")
LEVEL "MONTH" IS ("TIMES"."CALENDAR_MONTH_DESC")
LEVEL "QUARTER" IS ("TIMES"."CALENDAR_QUARTER_DESC")
LEVEL "YEAR" IS ("TIMES"."CALENDAR_YEAR")
LEVEL "FIS_WEEK" IS ("TIMES"."WEEK_ENDING_DAY")
LEVEL "FIS_MONTH" IS ("TIMES"."FISCAL_MONTH_DESC")
LEVEL "FIS_QUARTER" IS ("TIMES"."FISCAL_QUARTER_DESC")
LEVEL "FIS_YEAR" IS ("TIMES"."FISCAL_YEAR")
HIERARCHY "CAL_ROLLUP" ("DAY" CHILD OF "MONTH" CHILD OF "QUARTER" CHILD OF
"YEAR")
HIERARCHY "FIS_ROLLUP" ("DAY" CHILD OF "FIS_WEEK" CHILD OF "FIS_MONTH" CHILD
OF "FIS_QUARTER" CHILD OF "FIS_YEAR")
ATTRIBUTE "DAY" DETERMINES "TIMES"."DAY_NUMBER_IN_WEEK"
ATTRIBUTE "DAY" DETERMINES "TIMES"."CALENDAR_WEEK_NUMBER"
ATTRIBUTE "DAY" DETERMINES "TIMES"."DAY_NUMBER_IN_MONTH"
ATTRIBUTE "DAY" DETERMINES "TIMES"."DAY_NAME"
ATTRIBUTE "MONTH" DETERMINES "TIMES"."CALENDAR_MONTH_DESC"
ATTRIBUTE "MONTH" DETERMINES "TIMES"."END_OF_CAL_MONTH"
ATTRIBUTE "MONTH" DETERMINES "TIMES"."DAYS_IN_CAL_MONTH"
ATTRIBUTE "MONTH" DETERMINES "TIMES"."CALENDAR_MONTH_NAME"
ATTRIBUTE "MONTH" DETERMINES "TIMES"."CALENDAR_MONTH_NUMBER"
ATTRIBUTE "QUARTER" DETERMINES "TIMES"."CALENDAR_QUARTER_DESC"
ATTRIBUTE "QUARTER" DETERMINES "TIMES"."END_OF_CAL_QUARTER"
ATTRIBUTE "QUARTER" DETERMINES "TIMES"."DAYS_IN_CAL_QUARTER"
ATTRIBUTE "QUARTER" DETERMINES "TIMES"."CALENDAR_QUARTER_NUMBER"
ATTRIBUTE "YEAR" DETERMINES "TIMES"."CALENDAR_YEAR"
ATTRIBUTE "YEAR" DETERMINES "TIMES"."END_OF_CAL_YEAR"
ATTRIBUTE "YEAR" DETERMINES "TIMES"."DAYS_IN_CAL_YEAR"
ATTRIBUTE "FIS_WEEK" DETERMINES "TIMES"."WEEK_ENDING_DAY"
ATTRIBUTE "FIS_WEEK" DETERMINES "TIMES"."FISCAL_WEEK_NUMBER"
ATTRIBUTE "FIS_MONTH" DETERMINES "TIMES"."FISCAL_MONTH_DESC"
ATTRIBUTE "FIS_MONTH" DETERMINES "TIMES"."END_OF_FIS_MONTH"
ATTRIBUTE "FIS_MONTH" DETERMINES "TIMES"."DAYS_IN_FIS_MONTH"
ATTRIBUTE "FIS_MONTH" DETERMINES "TIMES"."FISCAL_MONTH_NAME"
ATTRIBUTE "FIS_MONTH" DETERMINES "TIMES"."FISCAL_MONTH_NUMBER"
ATTRIBUTE "FIS_QUARTER" DETERMINES "TIMES"."FISCAL_QUARTER_DESC"
ATTRIBUTE "FIS_QUARTER" DETERMINES "TIMES"."END_OF_FIS_QUARTER"
ATTRIBUTE "FIS_QUARTER" DETERMINES "TIMES"."DAYS_IN_FIS_QUARTER"
ATTRIBUTE "FIS_QUARTER" DETERMINES "TIMES"."FISCAL_QUARTER_NUMBER"
ATTRIBUTE "FIS_YEAR" DETERMINES "TIMES"."FISCAL_YEAR"
ATTRIBUTE "FIS_YEAR" DETERMINES "TIMES"."END_OF_FIS_YEAR"
ATTRIBUTE "FIS_YEAR" DETERMINES "TIMES"."DAYS_IN_FIS_YEAR";

alter session set query_rewrite_integrity=TRUSTED;
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
SQL与绑定变量：
select * from scott.emp where empno=:b1;

*如果绑定变量出现在键值分布极度倾斜的列，执行计划有可能是错的！

跟踪sql解析时的行为
set autot trace exp

跟踪sql执行时的行为
alter session set events '10046 trace name context forever,level 12';
  执行sql语句！
alter session set events '10046 trace name context off';

select indx,ksppinm from x$ksppi where ksppinm like '%peek%';
      2179
_optim_peek_user_binds

select KSPPSTDVL from x$ksppcv where indx=2179;
alter system set "_optim_peek_user_binds"=false;
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
确认追加日志数据模式已经打开
select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
使用闪回事务查询，查找历史事务
conn / as sysdba
select XID,COMMIT_SCN,UNDO_SQL
from flashback_transaction_query
where TABLE_NAME='E01' and TABLE_OWNER='SCOTT';

XID         COMMIT_SCN
---------------- ----------
UNDO_SQL
--------------------------------------------------------------------------
06001600CF060000    2405529
update "SCOTT"."E01" set "SAL" = '800' where ROWID = 'AAAWp7AAEAAAAu7AAA';

08001000A6060000    2407852
update "SCOTT"."E01" set "SAL" = '1' where ROWID = 'AAAWp7AAEAAAAu7AAA';

DECLARE
  v1 XID_ARRAY;
BEGIN
  v1:=XID_ARRAY('06001600CF060000');
  DBMS_FLASHBACK.TRANSACTION_BACKOUT(1,v1,DBMS_FLASHBACK.NOCASCADE);
END;
/

DECLARE
  v1 XID_ARRAY;
BEGIN
  v1:=XID_ARRAY('06001600CF060000');
  DBMS_FLASHBACK.TRANSACTION_BACKOUT(1,v1,DBMS_FLASHBACK.NOCASCADE_FORCE);
END;
/