1.您的緩沖區(qū)命中率是多少？

緩沖區(qū)命中率
select (1-(sum(decode(name,'physical reads',value,0))/(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0)))))*100 "Hit Ratio" from v$sysstat;

?

2.您的數(shù)據(jù)字典命中率是多少？

數(shù)據(jù)字典緩存命中率
select (1-(sum(getmisses)/sum(gets)))*100 "Hit Ratio" from v$rowcache;

?

3.您的庫緩存命中率是多少？

庫緩存命中率
select Sum(Pins)/(Sum(Pins)+Sum(Reloads))*100 "Hit Ratio" from V$LibraryCache;

?

4.在內(nèi)存中執(zhí)行的排序操作所占比例是多少？

獲得內(nèi)存和磁盤排序的查詢
select a.value "Disk Sorts",b.value "Memory Sorts",round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2) "Pct Memory Sorts" from v$sysstat a,v$sysstat b where a.name='sorts (disk)' and b.name='sorts (memory)';

?

5.在該查詢運行了2個小時后，X$BH表中的state＝0（表空閑）的緩沖區(qū)比例是多少

空閑的數(shù)據(jù)緩沖區(qū)的比例
select decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3,'BEING USED',state) "BLOCK STATUS", count(*) from x$bh group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3,'BEING USED',state);

?

6.在V$SQLAREA視圖中獲得使用內(nèi)存讀最多的10個語句占全部使用內(nèi)存讀語句的比例是多少？

最浪費內(nèi)存的前十個語句占所有語句的比例
set serverout on
DECLARE
?CURSOR c1 is select buffer_gets from v$sqlarea order by buffer_gets DESC;
CURSOR c2 is select sum(buffer_gets) from v$sqlarea;

sumof10 NUMBER:=0;
mybg NUMBER;
mytotbg NUMBER;

BEGIN
dbms_output.put_line('Percent');
dbms_output.put_line('-------------');
OPEN c1;
FOR i IN 1..10 LOOP FETCH c1 INTO mybg;
?sumof10:=sumof10+mybg;
END
LOOP;
CLOSE c1;
OPEN c2;
FETCH c2 INTO mytotbg;
CLOSE c2;
?dbms_output.put_line(sumof10/mytotbg*100);
END;
/

?

7.在V$SQLAREA視圖中前25個最占用內(nèi)存的語句中，您嘗試著調(diào)整了多少

查詢獲得25個最浪費內(nèi)存的語句
set serverout on size 1000000
declare
??? top25 number;
??? text1 varchar2(4000);
??? x number;
??? len1 number;
cursor c1 is
??? select buffer_gets,substr(sql_text,1,4000) from v$sqlarea order by buffer_gets desc;
begin
??? dbms_output.put_line('Gets'||''||'Text');
??? dbms_output.put_line('---------'||''||'---------------------------');
??? open c1;
??? for i in 1..25 loop fetch c1 into top25,text1;
??? dbms_output.put_line(rpad(to_char(top25),9)||''||substr(text1,1,66));
??? len1:=length(text1);
??? x:=66;
??? while len1 > x-1 loop
?????? dbms_output.put_line('"????????? '||substr(text1,x,66));
?? x:=x+66;
? end loop;
????? end loop;
?end;
/

?

8.查詢25個濫用磁盤讀操作的最主要語句
set serverout on size 1000000
declare
???? top25 number;
???? text1 varchar2(4000);
??? x number;
??? len1 number;
cursor c1 is
??? select disk_reads,substr(sql_text,1,4000) from v$sqlarea order by disk_reads desc;
begin
??? dbms_output.put_line('Reads'||''||'Text');
??? dbms_output.put_line('---------'||''||'---------------------------');
??? open c1;
??? for i in 1..25 loop fetch c1 into top25,text1;
???? dbms_output.put_line(rpad(to_char(top25),9)||''||substr(text1,1,66));
??? len1:=length(text1);
??? x:=66;
??? while len1 > x-1 loop
?????? dbms_output.put_line('"??????????????? '||substr(text1,x,66));
?? x:=x+66;
? end loop;
????? end loop;
?end;
/

?

9.

最浪費磁盤讀操作的前十個語句所占所有語句的比例set serverout on
DECLARE
?CURSOR c1 is select disk_reads from v$sqlarea order by disk_reads DESC;
CURSOR c2 is select sum(disk_reads) from v$sqlarea;

sumof10 NUMBER:=0;
mydr NUMBER;
mytotdr NUMBER;

BEGIN
dbms_output.put_line('Percent');
dbms_output.put_line('-------------');
OPEN c1;
FOR i IN 1..10 LOOP FETCH c1 INTO mydr;
?sumof10:=sumof10+mydr;
END
LOOP;
CLOSE c1;
OPEN c2;
FETCH c2 INTO mytotdr;
CLOSE c2;
?dbms_output.put_line(sumof10/mytotdr*100);
END;
/

?

10.提取有關(guān)回滾段和他們的位置信息的查詢
select segment_name,file_name from dba_data_files,dba_rollback_segs where dba_data_files.file_id=dba_rollback_segs.file_id;

oracle pl/sql性能