Oracle专家高级编程
此书在itpub被大力推荐：一个早就认识到的网站，却从那里成长出了很多高手。。 潜心学习吧
概念翻译：cardinality：基数
1. Basic commands.
select * from resources FOR UPDATE (to block other sessions)
a. create table resources (resouceid varchar2(25) primary key,...)
or create table as select * from oldtable
b.
create procedure p (Empname in varchar2 )
as
begin
for x in (select * from EmpInf where empname=Empname) loop
DBMS_output.putline (x.EmpNo)
end loop
end
c. variable x refcursor;
begin
open :x as select * from table
end
/
print x
alter table EMP add TCN integer;
insert into table select *
instead of (trigger)
create or replace trigger EMP_BUTRG
before update on EMP for each row
begin
/* 外加insert 触发器可以防止乐观死锁并行失败test for concurrency failure */
if( :new.TCN != :old.TCN+1 ) then
raise_application_error( -20000, ‘Concurrency Failure’ );
end if;
/* NOTE - additional pre-update code may go here */
/* update the transaction control number */
:new.TCN := dbms_utility.get_time;
end;
plsql>declare
va1 number;
va2 varchar2;
begin
select count(*) from emp;
for x in (select * from emp)
loop
end loop;
commit;
end;
create index id_name on table(column1, column2)
当查询开始时，多版本（快照）就会存在。可以表锁定（这样查询开始时就会得到确定结果，设想查询过程中表被更新，想查balance之和），也可以列锁定（查询结束才有结果）。
oracle支持列锁定，但不会造成以上困扰：oracle不需要共享读锁定，直接从回滚段中取得老的余额（快照）。
（行锁定+非阻塞读取）的副作用是：2个人同时搜索某个列（会议室占用）不存在，之后想根据结果同时插入新预定，2者都能插入
当更新时，oracle自动插入redo, undo
2. MTS (多线程，connection pool）只适合短时间的sql. 对于超长占用cpu的语句：采用advanced queue,但可能还要用伪transaction ID来敷衍应用程序，直到aq给出真id再替换
3。绑定变量：提高伸缩性scalarbility: select * from emp where empno=:empno. 第二次查询时,即查询经过编译后的方案在shared pool缓存中。否则，多耗资源，且硬编码变量将使用锁存latchet
for i in 1..1000
loop open l_rc for
'select * from emp where empno=:x"
using i
fetch l_rc to l_dummy
close l_rc
end loop
比无绑定快10倍（1.4s vs 14s)
4.Oracle封锁策略：1。修改时在该行上所定；2写入不会阻塞读取
5。oracle不支持null比较， use NVL instead (substitute a value when null is encountered
6. 生成unique id: create sequence t-sq; select t-sq.nextval into : new.pk from dual
7.追求完全独立于数据库实现是很困难的。为何要选择VB, ActiveX Control, IIS, Oracle?而不用java,ejb/cobra,Apache,所以，不利用oracle特性（哪怕简单如存储过程），是没有道理的
更甚，所谓”数据库开放性“把应用程序逻辑，乃至更重要的安全性，放到数据库外（ejb, jsp, iis），导致了数据库封闭。20多年来，数据前端每天都在变，只有数据库本身维持了20多年。所以，安全不在数据库里，成了进一步发展的障碍
8. DBA告诉我：是否用oracle内建feature，取决于商业：比如，Agresso不用外键，那么如何确保不插入外键为空的值？做一个全表扫描，符合SQL92标准即可。否则Oracle error code -192可能和SQL server error code不一致，需要在程序逻辑中判断
SQL92 4层（所有厂商支持），过渡（包括外连接，内连接语法），中间，完全。最佳的oracle实现唯一id:
create table t (name1 number primary key,..);
create sequenct t_seq
create trigger t_trigger before insert on t for each row
BEGIN
select t_seq.nextval into :new.name1 from dual
END;
8。oracle不推荐在sp中建立临时表，sql server不支持所有用户用同一张临时表
9. 1 CREATE TABLESPACE "SAMPLE"
2 LOGGING
3 DATAFILE 'D:\ORACLE\ORADATA\ORA92\SAMPLE.ora' SIZE 5M,
4 'D:\ORACLE\ORADATA\ORA92\dd.ora' SIZE 5M
5 EXTENT MANAGEMENT LOCAL
6 UNIFORM SEGMENT SPACE MANAGEMENT
7* AUTO
10. Pfile 参数文件，包含数据库名和控制文件位置。控制文件不超过64m，应有多个副本
外键如果没有索引，可能更新字表时引起阻塞或死锁？Exceptions resulting from foreign key conflicts due to violations of database referential integrity
?乐观锁定的并行解决：数据库时间戳。 可是如果某一方直接update,莫非数据库还要记忆他上面n步是否读取过该数据？？？如果超时了呢？
oracle锁定存储为数据的属性
delete from (select 可以用in/join)
For update时创建2个锁：所选定行和row share table锁（防止更改表结构），更新时将转为row exclusive table锁
每个表都有INITTRANS和MAXTRANS来管理阻塞，超出即阻塞
?哈希需要质数作为目的？ 哈希通常有冲突.hash消耗cpu,indexing查找消耗io
Full table scan will search all (empty) blocks under High Watermark
3。为何不用索引
更改一个参数.optimizer_index_cost_adj 调整到40看看
optimizer_index_cost_adj
这个初始化参数代表一个百分比,取值范围在1到10000之间。
该参数表示索引扫描和全表扫描成本的比较。缺省值100表示索引扫描成本等价转换与全表扫描成本。
这些参数对于cbo的执行具有重大影响，其缺省值对于数据库来说通常需要调整。
一般来说对于optimizer_index_caching可以设置为90左右。
对于大多数oltp系统，optimizer_index_cost_adj可以设置在10到50之间。对于数据仓库和dss系统，可能不能简单的把optimizer_index_cost_adj设置为50，通常我们需要反复调整取得一个合理值。
更为具体的可以根据统计信息,db file scattered reads/db file sequential reads来计算。
analyze table 表名 estimate statistics sample 25 percent for table for all indexes for all indexed columns;
--------------------------------------------------------------------------------
这一招管用，谢谢了，不过这个是啥原理呢？
Card=185642
这是oracle认为的行数，也就是为什么不用索引的根源
上面兄弟提供的analyze语句包含了更新柱状图信息，应该就可以了
如果你收集了柱状图信息，以后记得勤更新，否则有问题会等着你。。。。
select /*+ ordered use_nl(小表名, 大表名) */
小表名.name,大表名.id
from 小表,大表
where XXXX
AND 小表.ID=大表.id
__________________
[URL]http://atgc.itpub.net[/URL]
引用 回复 报告
atgc
高级会员
精华贴数 1
个人空间 0
技术积分 7449 (144)
社区积分 144 (2336)
注册日期 2004-7-22
论坛徽章:24
#19发表于 2008-1-22 15:27
上面方法慎用，只有你确认小表确实很小的时候才用
为何出现ora-01555错误:一个update在更新大量块，另一个select在查询，同时回滚段不够多，逐渐被覆盖，当查询到已经无法回滚得内容时，snapshot太老（无法生成查询开始时的数据一致）
问题：？？01555两个update互相冲突时，出错是在分析时还是update一半时？pdf1910, page 159
概念翻译：cardinality：基数
1. Basic commands.
select * from resources FOR UPDATE (to block other sessions)
a. create table resources (resouceid varchar2(25) primary key,...)
or create table as select * from oldtable
b.
create procedure p (Empname in varchar2 )
as
begin
for x in (select * from EmpInf where empname=Empname) loop
DBMS_output.putline (x.EmpNo)
end loop
end
c. variable x refcursor;
begin
open :x as select * from table
end
/
print x
alter table EMP add TCN integer;
insert into table select *
instead of (trigger)
create or replace trigger EMP_BUTRG
before update on EMP for each row
begin
/* 外加insert 触发器可以防止乐观死锁并行失败test for concurrency failure */
if( :new.TCN != :old.TCN+1 ) then
raise_application_error( -20000, ‘Concurrency Failure’ );
end if;
/* NOTE - additional pre-update code may go here */
/* update the transaction control number */
:new.TCN := dbms_utility.get_time;
end;
plsql>declare
va1 number;
va2 varchar2;
begin
select count(*) from emp;
for x in (select * from emp)
loop
end loop;
commit;
end;
create index id_name on table(column1, column2)
当查询开始时，多版本（快照）就会存在。可以表锁定（这样查询开始时就会得到确定结果，设想查询过程中表被更新，想查balance之和），也可以列锁定（查询结束才有结果）。
oracle支持列锁定，但不会造成以上困扰：oracle不需要共享读锁定，直接从回滚段中取得老的余额（快照）。
（行锁定+非阻塞读取）的副作用是：2个人同时搜索某个列（会议室占用）不存在，之后想根据结果同时插入新预定，2者都能插入
当更新时，oracle自动插入redo, undo
2. MTS (多线程，connection pool）只适合短时间的sql. 对于超长占用cpu的语句：采用advanced queue,但可能还要用伪transaction ID来敷衍应用程序，直到aq给出真id再替换
3。绑定变量：提高伸缩性scalarbility: select * from emp where empno=:empno. 第二次查询时,即查询经过编译后的方案在shared pool缓存中。否则，多耗资源，且硬编码变量将使用锁存latchet
for i in 1..1000
loop open l_rc for
'select * from emp where empno=:x"
using i
fetch l_rc to l_dummy
close l_rc
end loop
比无绑定快10倍（1.4s vs 14s)
4.Oracle封锁策略：1。修改时在该行上所定；2写入不会阻塞读取
5。oracle不支持null比较， use NVL instead (substitute a value when null is encountered
6. 生成unique id: create sequence t-sq; select t-sq.nextval into : new.pk from dual
7.追求完全独立于数据库实现是很困难的。为何要选择VB, ActiveX Control, IIS, Oracle?而不用java,ejb/cobra,Apache,所以，不利用oracle特性（哪怕简单如存储过程），是没有道理的
更甚，所谓”数据库开放性“把应用程序逻辑，乃至更重要的安全性，放到数据库外（ejb, jsp, iis），导致了数据库封闭。20多年来，数据前端每天都在变，只有数据库本身维持了20多年。所以，安全不在数据库里，成了进一步发展的障碍
8. DBA告诉我：是否用oracle内建feature，取决于商业：比如，Agresso不用外键，那么如何确保不插入外键为空的值？做一个全表扫描，符合SQL92标准即可。否则Oracle error code -192可能和SQL server error code不一致，需要在程序逻辑中判断
SQL92 4层（所有厂商支持），过渡（包括外连接，内连接语法），中间，完全。最佳的oracle实现唯一id:
create table t (name1 number primary key,..);
create sequenct t_seq
create trigger t_trigger before insert on t for each row
BEGIN
select t_seq.nextval into :new.name1 from dual
END;
8。oracle不推荐在sp中建立临时表，sql server不支持所有用户用同一张临时表
9. 1 CREATE TABLESPACE "SAMPLE"
2 LOGGING
3 DATAFILE 'D:\ORACLE\ORADATA\ORA92\SAMPLE.ora' SIZE 5M,
4 'D:\ORACLE\ORADATA\ORA92\dd.ora' SIZE 5M
5 EXTENT MANAGEMENT LOCAL
6 UNIFORM SEGMENT SPACE MANAGEMENT
7* AUTO
10. Pfile 参数文件，包含数据库名和控制文件位置。控制文件不超过64m，应有多个副本
外键如果没有索引，可能更新字表时引起阻塞或死锁？Exceptions resulting from foreign key conflicts due to violations of database referential integrity
?乐观锁定的并行解决：数据库时间戳。 可是如果某一方直接update,莫非数据库还要记忆他上面n步是否读取过该数据？？？如果超时了呢？
oracle锁定存储为数据的属性
delete from (select 可以用in/join)
For update时创建2个锁：所选定行和row share table锁（防止更改表结构），更新时将转为row exclusive table锁
每个表都有INITTRANS和MAXTRANS来管理阻塞，超出即阻塞
?哈希需要质数作为目的？ 哈希通常有冲突.hash消耗cpu,indexing查找消耗io
Full table scan will search all (empty) blocks under High Watermark
3。为何不用索引
更改一个参数.optimizer_index_cost_adj 调整到40看看
optimizer_index_cost_adj
这个初始化参数代表一个百分比,取值范围在1到10000之间。
该参数表示索引扫描和全表扫描成本的比较。缺省值100表示索引扫描成本等价转换与全表扫描成本。
这些参数对于cbo的执行具有重大影响，其缺省值对于数据库来说通常需要调整。
一般来说对于optimizer_index_caching可以设置为90左右。
对于大多数oltp系统，optimizer_index_cost_adj可以设置在10到50之间。对于数据仓库和dss系统，可能不能简单的把optimizer_index_cost_adj设置为50，通常我们需要反复调整取得一个合理值。
更为具体的可以根据统计信息,db file scattered reads/db file sequential reads来计算。
analyze table 表名 estimate statistics sample 25 percent for table for all indexes for all indexed columns;
--------------------------------------------------------------------------------
这一招管用，谢谢了，不过这个是啥原理呢？
Card=185642
这是oracle认为的行数，也就是为什么不用索引的根源
上面兄弟提供的analyze语句包含了更新柱状图信息，应该就可以了
如果你收集了柱状图信息，以后记得勤更新，否则有问题会等着你。。。。
select /*+ ordered use_nl(小表名, 大表名) */
小表名.name,大表名.id
from 小表,大表
where XXXX
AND 小表.ID=大表.id
__________________
[URL]http://atgc.itpub.net[/URL]
引用 回复 报告
atgc
高级会员
精华贴数 1
个人空间 0
技术积分 7449 (144)
社区积分 144 (2336)
注册日期 2004-7-22
论坛徽章:24
#19发表于 2008-1-22 15:27
上面方法慎用，只有你确认小表确实很小的时候才用
为何出现ora-01555错误:一个update在更新大量块，另一个select在查询，同时回滚段不够多，逐渐被覆盖，当查询到已经无法回滚得内容时，snapshot太老（无法生成查询开始时的数据一致）
问题：？？01555两个update互相冲突时，出错是在分析时还是update一半时？pdf1910, page 159
posted by hunter at 10:57 PM
0 Comments:
Post a Comment
<< Home