oracle杂烩笔记

execute dbms_session.set_sql_trace(sql_trace=>true);
select distinct sid from v$mystat;
select spid from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat));
execute dbms_session.set_sql_trace(sql_trace=>false);

在timed_statistic用时，查cpu列来确定最高的语句。
  timed_statistics未使用时，查query和current列。



hint:

access method

full
rowid
cluster
hash
index
index_asc
index_combine
index_desc
index_ffs
no_index
and_equal
use_concat
no_expand
rewrite
norewrite



select dbms_rowid.rowid_relative_fno(rowid) from emp;
select dbms_rowid.rowid_object(rowid) from emp;---link dba_objects.object_id
select dbms_rowid.rowid_block_number(rowid) from emp


create table learn(id number,sex varchar2(3));

begin
for i in 1..2000000 loop
insert into learn values(i,'nan');
if mod(i,5000)=0 then
commit;
end if;
end loop;
end;
/






begin
for i in 1..2000000 loop
delete from learn where rownum=i;
if mod(i,10000)=0 then
commit;
end if;
end loop;
end;
/



select count(extent_id),sum(bytes/1024/1024) mb from dba_extents
group by segment_name having segment_name='LEARN'
/



select sql_text from v$sqlarea where address=(select prev_sql_addr from v$session where username='ZXY')
/


select sql_text from v$sqlarea where address=(select sql_address from v$session where username='ZXY')
/


select saddr,sid,serial#,username,command,taddr,lockwait,status,server,
       prev_child_number,prev_sql_addr,row_wait_obj#,row_wait_file#,
       row_wait_block#,logon_time,failover_type,sql_trace,service_name
from v$session where rownum<2
/


select opname,target,round(sofar/totalwork*100,2) as progress,time_remaining,elapsed_second
from v$session_longops where sofar       









select a.object_owner, a.object_name
from v$sql_plan a, v$sqlarea b
where a.sql_id = b.sql_id
and a.object_type='INDEX'
and b.last_load_time > ;

alter index index_learn monitoring usage;
select count(*) from learn;
select index_name,table_name,used,starting_time,end_time from v$object_usage;
alter index index_learn nomonitoring usage;
利用上述方法，过滤掉大部分肯定被使用的index后，
再综合应用，选择可疑索引进行监控，找出并删除无用索引，为数据库减肥。




































ORACLE里锁有以下几种模式:
0：none
1：null 空    1级锁有：Select，有时会在v$locked_object出现     
2：Row-S 行共享(RS)：共享表锁，sub share     Select for update,Lock For Update,Lock Row Share
3：Row-X 行独占(RX)：用于行的修改，sub exclusive   Insert, Update, Delete, Lock Row Exclusive
4：Share 共享锁(S)：阻止其他DML操作，share
   Create Index, Lock Share
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。
00054, 00000, "resource busy and acquire with NOWAIT specified"
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.

5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive    具体来讲有主外键约束时update / delete ... ; 可能会产生4,5的锁。
6：exclusive 独占(X)：独立访问使用，exclusive  Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

数字越大锁级别越高, 影响的操作越多。


以DBA角色, 查看当前数据库里锁的情况可以用如下SQL语句
select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l,dba_objects b
where l.object_id=b.object_id
/

select t2.username,t2.sid,t2.serial#,to_char(t2.logon_time,'yyyy-mm-dd hh24:mi:ss') logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid
order by t2.logon_time
/

如果有长期出现的一列，可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁：

alter system kill session 'sid,serial#';

如果出现了锁的问题, 某个DML操作可能等待很久没有反应。

当你采用的是直接连接数据库的方式，
也不要用OS系统命令 $kill process_num 或者 $kill -9 process_num来终止用户连接，
因为一个用户进程可能产生一个以上的锁, 杀OS进程并不能彻底清除锁的问题




plsql学习

create or replace procedure create_table
as  ---字符串可能重用
  str varchar2(1000);
  v_count number;
begin
  str:='create table test_learn(a number,b number)';
   execute immediate str;
  
  for v_count in 1..100 loop
    str:='insert into test_learn(a,b) values(:1,:2)';
    execute immediate str using v_count,v_count+1.0;
   
  end loop;
commit;
end;



create or replace procedure remove_dup
(i_table_name in varchar2,  ---procedure参数不能具体化,as后也有procedure内参数
i_condition in varchar2 default null)
as
v_where_clause varchar2(2000):=' where '||i_condition;
v_sql_str varchar2(4000);
v_row_count number:=0;
begin
dbms_output.put_line(i_condition);
dbms_output.put_line(v_where_clause);

if i_condition is not null then
    v_sql_str:='delete from '||i_table_name||'  where rowid not in
                (select max(rowid) from '||i_table_name||v_where_clause||')';
  dbms_output.put_line(v_sql_str);
  execute immediate v_sql_str;
  commit;
  v_row_count:=sql%rowcount;
  dbms_output.put_line('number of records deleted:'||v_row_count);
else
   dbms_output.put_line('no where clause,nothing done');
   dbms_output.put_line(v_row_count);
end if;
end;


execute zxy.remove_dup('LEARN',null);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-350546/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-350546/