oracle 最佳化SQL語句

標籤：ber   sql語句   between   bms   not   execution   des   ast   user   

--查詢正在執行的SQL語句

select a.program, b.spid, c.sql_text,c.SQL_FULLTEXT,c.SQL_ID
from v$session a, v$process b, v$sqlarea c
where a.paddr = b.addr
and a.sql_hash_value = c.hash_value
and a.username is not null;

 

---查詢執行過的SQL語句 
select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT
  from v$sqlarea b
where b.FIRST_LOAD_TIME between ‘2009-10-15/09:24:47‘ and
       ‘2009-10-15/09:24:47‘ order by b.FIRST_LOAD_TIME 
（此方法好處可以查看某一時間段執行過的sql，並且 SQL_FULLTEXT 包含了完整的 sql 語句）

 

 

--查詢比較耗CPU的SQL語句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.cpu_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

 

--查詢比較耗磁碟的SQL語句

select * from (select v.sql_id,
v.child_number, v.sql_text, 
v.elapsed_time, v.cpu_time, 
v.disk_reads, 
rank() over(order by v.disk_reads desc) elapsed_rank 
from v$sql v) a where elapsed_rank <= 10;

 

--查詢比較慢的SQL語句

select * from (
select parsing_user_id,executions,sorts
command_type,disk_reads,sql_text from v$sqlarea order by disk_reads desc
)where rownum<10

 

--Oracle 對未提交事務的查詢

select a.sid,a.blocking_session,a.last_call_et,a.event,
object_name,
dbms_rowid.rowid_create(1,data_object_id,rfile#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) "rowid" ,
c.sql_text,c.sql_fulltext
from v$session a,v$sqlarea c ,dba_objects,v$datafile
where a.blocking_session is not null
and a.sql_hash_value = c.hash_value 
and ROW_WAIT_OBJ#=object_id and file#=ROW_WAIT_FILE#;

oracle 最佳化SQL語句