-- 查看优化模式
show parameter optimizer_mode
-- 模式统计数据
exec dbms_stats.gather_schema_stats(ownname => 'PNP2',estimate_percent=>20,cascade=> TRUE);
-- 分析单个表
analyze table owner.table_name estimate statistics sample 20 percent;
-- 单个表统计数据
exec dbms_stats.gather_table_stats('PNP2','ADMINLOG', degree => 4,cascade => true);
-- 建立位图索引
CREATE BITMAP INDEX IDX_ISMISSING ON SEARCH_RECORD_DETAIL
(ISMISSING)
NOLOGGING
NOPARALLEL;
-- 建立普通B树索引
CREATE INDEX IDX_FRIENDUSER ON USER_FRIEND
(FRIENDUSER)
LOGGING
NOPARALLEL;
-- 建立临时表上的索引
CREATE INDEX IDX_TMP_ALBUMNAME ON TMP_LRC
(ALBUMNAME);
-- 建立分区局部索引
CREATE INDEX IDX_END_IP_PART2 ON CITY_IP
(END_IP)
LOCAL (
PARTITION PART1
NOLOGGING
NOCOMPRESS,
PARTITION PART2
NOLOGGING
NOCOMPRESS,
PARTITION PART3
NOLOGGING
NOCOMPRESS
)
NOPARALLEL;
-- 建立函数索引
CREATE INDEX IDX_SONGINITIAL ON SONG_KSC
(UPPER("SONGINITIAL"))
LOGGING
NOPARALLEL;
-- 索引重建
alter index index_name rebuild parallel (degree 4) nologging tablespace tbs_idx;
-- 跟踪不良SQL语句
alter session set sql_trace=true;
alter session set timed_statistics=true;
execute dbms_system.set_sql_trace_in_session('sid','serial#',TRUE);
-- 格式化跟踪文件
tkprof name.trc output.prf explain=username/password sys=no sort=fchdsk
-- 获得解释计划
set autotrace traceonly
-- 好、坏SQL语句写法对比例子
select *
from line_items
where shipped_date between sysdate and (sysdate - 30);
/*** good ***/
select /*+ full(line_items) parallel(line_items,2) */ *
from line_items
where shipped_date between sysdate and (sysdate - 30);
/*** bad ***/
select *
from orders
where upper(ord_id) = ':b1'
and ord_status = 'Not Filled'
and ord_date = sysdate;
/*** good ***/
select *
from orders
where ord_id = upper(':b1')
and ord_status = 'Not Filled'
and ord_date = sysdate;
/*** bad ***/
select first_name, last_name, hire_date
from emp
where empno = 1234;
select first_name, last_name, hire_date
from emp
where empno = 9876;
/*** good ***/
select first_name, last_name, hire_date
from emp
where empno = :b;
/*** bad ***/
declare
ord_struct orders%rowtype;
cursor c_ord is
select *
from orders
where ord_status = 'Not Filled'
and ord_date = sysdate;
begin
open c_ord;
loop
fetch c_ord into ord_struct;
exit when c_ord%notfound;
insert into temp_ord
values (ord_struct.ord_id,
ord_struct.ord_date,
ord_struct.ord_price * 1.1,
ord_struct.ord_status);
commit;
end loop;
close c_ord;
end;
/
/*** good ***/
declare
begin
insert /*+ append */ into temp_ord
select ord_id, ord_date, ord_price * 1.1, ord_status
from orders
where ord_status = 'Not Filled'
and ord_date = sysdate;
commit;
end;
/
/*** bad ***/
select outer.*
from emp outer
where outer.salary >
(select avg(salary)
from emp inner
where inner.dept_id = outer.dept_id);
/*** good ***/
select e1.*
from emp e1, (select e2.dept_id dept_id, avg(e2.salary) avg_sal
from emp e2
group by dept_id) dept_avg_sal
where e1.dept_id = dept_avg_sal.dept_id
and s1.salary > dept_avg_sal.avg_sal;
/*** bad ***/
declare
ord_seq_val orders.ord_id%type;
begin
for i in 1..10000
loop
select ord_seq.nextval
into ord_seq_val
from dual;
insert into temp_ord(ord_id)
values (ord_seq_val);
end loop;
end;
/
/*** good ***/
declare
begin
for i in 1..10000
loop
insert into temp_ord(ord_id)
values (ord_seq.nextval);
end loop;
end;
/