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);