| oracle 9i前 中的排序受sort_area_size這個參數(shù)的影響。 oracle 9i后 pga 管理則主要受pga_aggregate_target 這個參數(shù)的影響。 如果內(nèi)存無法容納排序操作，則oracle使用臨時表空間作為臨時空間進行排序， 使用臨時表就可的排序在oracle中稱為磁盤排序（sort disk），磁盤排序的性能遠遠底于內(nèi)存排序（sort memory） 數(shù)據(jù)庫的排序信息可以通過動態(tài)性能視度v$sysstat查詢得到。 select name,value from v$sysstat where name like 'sort%'; sorts (memory)634672911 sorts (disk)183 sorts (rows)16008072120 排序使用臨時表空間的方式和永久表空間不同，但第一個使用臨時表空間的排序開始后，臨時段被創(chuàng)建， 區(qū)間被分配到這個臨時段中供排序操作使用，但排序完成后，這個臨時段并不回刪除，oracle會將這個臨時段中的區(qū)間標記為free。 其他排序操作可以繼續(xù)使用這個臨時段，也就是說，臨時段中的區(qū)間是一次分配，循環(huán)使用。 oracle 根據(jù)排序的空間需求，逐漸分配區(qū)間加入到這個臨時段中， 增加的區(qū)間可以通過v$sort_segment 中的ADDED_EXTENTS字段查詢到。 select t.ADDED_EXTENTS from v$sort_segment t; ----可以用 select * from dba_tab_columns a,dba_tab_columns b order by a.OWNER,b.TABLE_NAME; -----這樣的一條語句測試，千萬不要在正式的生產(chǎn)庫去做。 當前正在排序的的用戶信息可以通過v$sort_usage 視圖得到。 select * from v$sort_usage; select * from v$sort_segment; 可以通過這個條語句查詢到是那個用戶。 select b.tablespace,b.blocks,a.sid,a.serial#,a.USERNAME,a.STATUS from v$session a,v$sort_usage b where a.SADDR=b.SESSION_ADDR； select * from v$sqltext order by piece; select * from v$latchname where name like 'sort%'; select * from v$latch where latch#=184; select * from v$temp_extent_pool; --查詢temp表空間的大小 select sum(bytes)/1024/1024 from v$tempfile; select sum(bytes)/1024/1024 from dba_temp_files; ----當前正在使用temp表空間的的用戶和sql語句。 select distinct a.sid,a.process,a.serial#,to_char(a.logon_time,'yyyy-mm-dd hh24:mi:ss'), a.osuser,tablespace ,b.sql_text from v$session a ,v$sql b ,v$sort_usage c where a.sql_address=b.ADDRESS and a.SADDR=c.SESSION_ADDR; select /*+ rule */ distinct a.sid,a.process,a.serial#,to_char(a.logon_time,'yyyy-mm-dd hh24:mi:ss'), a.osuser,tablespace ,b.sql_text from v$session a ,v$sql b ,v$sort_usage c where a.sql_address=b.ADDRESS and a.SADDR=c.SESSION_ADDR; ------------- 查看當前默認的temp表空間 select* from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'
我們通過轉(zhuǎn)儲控制文件可以得到相關(guān)命令。轉(zhuǎn)儲控制文件的創(chuàng)建語句是 alter database backup controlfile to trace;
在進入這個目錄 user_dump,就可以得到如下語句。 ALTER TABLESPACE TEMP3 ADD TEMPFILE '/oracle/oradata/xjcskfdb/temp104.dbf' SIZE 10240M REUSE AUTOEXTEND OFF; 等。。。。
-------創(chuàng)建temp表空間 ---CREATE TEMPORARY TABLESPACE temp1 TEMPFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP00.DBF' SIZE 10m AUTOEXTEND ON NEXT 2m MAXSIZE 2048m EXTENT MANAGEMENT LOCAL;
----添加temp表空間的數(shù)據(jù)文件 alter tablespace temp add tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP001.DBF' size 10m autoextend on next 2m maxsize 1024m extent management local;
ALTER TABLESPACE "TEMP" ADD TEMPFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP001.DBF' SIZE 5M autoextend on next 2m maxsize 1024m extent management local;
----改變temp表空間 alter database default temporary tablespace temp1; select * from dba_users;
--刪除temp表空間的數(shù)據(jù)文件。 alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP02.DBF' offline; alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP02.DBF' drop;
---刪除表空間
drop tablespace temp;
一樣可以刪除臨時表空間，但數(shù)據(jù)文件一般沒有刪除， 可以用 drop tablespace temp1 including contents and datafiles; 這樣的話，數(shù)據(jù)文件也就刪除了。。
------有關(guān)v$sort_usage和v$tempseg_usage 其實從oracle 9i 開始，v$sort_usage 視圖就基本不用了，取而代之的是用v$tempseg_usage 視圖。 這一改變因為sort一詞可能引起誤解，雖然排序是使用臨時段的主要操作，但除了排序外，很多其他操作也會用到臨時段。 使用用v$tempseg_usage更確切些。
select * from v$tempseg_usage; select * from dba_objects where object_name=upper('v$tempseg_usage') 可以看出v$tempseg_usage其實就是一個同義詞。
select * from dba_synonyms t where t.synonym_name=upper('v$tempseg_usage') --------------------------------------------- lob 對象與臨時段 lob對象的處理機制，對lob對象的操作過程中，oracle會生成臨時的lob數(shù)據(jù)，這部分也會使用臨時段。
-----測試lob --session1 declare A CLOB; BEGIN A:='ABC'; DBMS_LOCK.SLEEP(120); END; / -- session2 select s.USERNAME,s.SID,u.TABLESPACE,u.CONTENTS,u.SEGTYPE,round(u.BLOCKS*8192/1024/1024,2) mb from v$session s ,v$tempseg_usage u where s.SADDR=u.SESSION_ADDR and u.CONTENTS='TEMPORARY' ORDER BY mb desc;
select * from v$sort_usage;
------------------------------------------------------- ---找出引發(fā)temp的sql語句。 select /*+ rule */ distinct a.sid,a.process,a.serial#, to_char(a.logon_time,'yyyy-mm-dd hh24:mi:ss'), a.osuser,tablespace ,b.sql_text from v$session a ,v$sql b ,v$sort_usage c where a.sql_address=b.ADDRESS and a.SADDR=c.SESSION_ADDR; |
