1.您的缓冲区命中率是多少？
缓冲区命中率
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.您的数据字典命中率是多少？
数据字典缓存命中率
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.在内存中执行的排序操作所占比例是多少？
获得内存和磁盘排序的查询
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（表空闲）的缓冲区比例是多少
空闲的数据缓冲区的比例
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视图中获得使用内存读最多的10个语句占全部使用内存读语句的比例是多少？
最浪费内存的前十个语句占所有语句的比例
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个最占用内存的语句中，您尝试着调整了多少
查询获得25个最浪费内存的语句
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.提取有关回滚段和他们的位置信息的查询
select segment_name,file_name from dba_data_files,dba_rollback_segs where dba_data_files.file_id=dba_rollback_segs.file_id;
