create table exchage_table
(
bill_code number(10),
exchage_rate number(16,3)
)
BILL_CODE
EXCHAGE_RATE
100
4.678
200
5.235
300
5.430
400
2.654
create or replace functiontoday_exchage(p_code in number) return number is
v_exange number(16,3);
begin
selectexchage_rate into v_exange from exchage_table
wherebill_code=p_code;
returnv_exange;
end;
create table affair
(
trans_id number(10),
bill_code number(10),
balance number(16,2)
)
TRANS_ID
BILL_CODE
BALANCE
1000
100
1234.00
2000
200
4324.32
3000
300
65464.23
较好的写法为
select
trans_id,
(select exchage_rate
fromexchage_table
wherebill_code=affair.bill_code )*balance
from affair
举例3
drop table tppp purge
create table tppp(p integer)
create or replace trigger t_trigger
before insert on tppp
for each row
declare
-- local variables here
begin
if:new.p>5 then
raise_application_error(-20001,'bbbbbbbb');
end if;
end t_trigger;
统计信息
----------------------------------------------------------
29 recursive calls
19 db block gets
54 consistent gets
0 physical reads
1172 redo size
676 bytes sent via SQL*Net to client
627 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
在一次运行
统计信息
---------------------------------------------------------
29 recursive calls
0 db block gets
117 consistent gets
1 physical reads
0 redo size
483 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
drop trigger t_trigger;
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
108 consistent gets
0 physical reads
0 redo size
483 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
解决方法为
1、 编写高效的trigger
2、 用过程代替trigger
举例4:
自我管理表空间与数据字典表空间
本地管理的表空间能够减少递归sql
输出内容: 逻辑I/O （DB BLOCKS| CONSISTENT GETS）
解释
对于一个SQL 逻辑I/O越小越好，通常通过SQL调整实现的
TKPROF
使用TKPROF 工具简介
TKPROF 工具简介
TKPROF 工具的使用步骤
TKPROF 工具如何分析trace 文件
启用TKPROF
如何设置自动跟踪
1、设定执行表，autotrace。方法如前所述，这里再重复一边。
用system登录
执行$ORACLE_HOME/rdbms/admin/utlxplan.sql创建计划表
执行$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色
如果想计划表让每个用户都能使用，则
SQL>create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;
2、设定tkprof
ALTER SESSION SETSQL_TRACE = TRUE
ALTER SESSION SETTIMED_STATISTICS = TRUE;
alter session set events ‘10046 trace name contextforever,level 12’;
alter session set max_dump_file_size=unlimited；
alte