oracle pl/sql性能

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;

oracle pl/sql性能