学步园

1、查看SQL语句的解析情况：

SELECT *
  FROM V$SYSSTAT
 WHERE NAME IN
       ('parse time cpu', 'parse time elapsed', 'parse count (hard)');

    这里"parse time cpu”是系统服务时间，"parse time elapsed"是响应时间，用户等待时间waite time = parse time elapsed - parse time cpu。

2、查看是什么SQL语句解析效率比较低：

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA WHERE ROWNUM<10 ORDER BY PARSE_CALLS; 

    可以优化这些语句，或者增加Oracle参数SESSION_CACHED_CURSORS的值。

     查看使用频率最高的10条sql

select sql_text, executions,sysdate
  from (select sql_text,
               executions,
               rank() over(order by executions desc) exec_rank
          from v$sql)
 where exec_rank <= 10; 

3、根据v$process中的pid值到v$session中找到对应的sid：

SELECT SID, SERIAL#, USERNAME, MACHINE
  FROM v$session b
 WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&pid');

      根据sid获取sql

select sql_text from v$sqltext a where a.hash_value=(select sql_hash_value from v$session b where b.SID='&sid')
ORDER BY piece ASC;

4、寻找CPU使用过量的session ，找出高CPU利用率的SQL：

SELECT sql_text
  FROM v$sqltext a
 WHERE (a.hash_value, a.address) IN
       (SELECT decode(sql_hash_value, 0, prev_hash_value, sql_hash_value),
               decode(sql_hash_value, 0, prev_sql_addr, sql_address)
          FROM v$session b
         WHERE b.paddr =
               (SELECT addr FROM v$process c WHERE c.spid = '&pid'))
 ORDER BY piece ASC;

5、查看每个Session的CPU利用情况：

select ss.sid, se.command, ss.value CPU, se.username, se.program
  from v$sesstat ss, v$session se
 where ss.statistic# in
       (select statistic#
          from v$statname
         where name = 'CPU used by this session')
   and se.sid = ss.sid
   and ss.sid > 6
 order by ss.sid;

6、比较上述Session(第5个)，看那个session的CPU使用时间最多，然后查看该Session的具体情况：

select s.sid, s.event, s.wait_time, w.seq#, q.sql_text
  from v$session_wait w, v$session s, v$process p, v$sqlarea q
 where s.paddr = p.addr
   and s.sid = &p
   and s.sql_address = q.address;

得到上述信息后，查看相应操作是否有hash joins 和 full table scans。如果有hash joins 和 full table scans那么必须创建相应的Index或者检查Index是否有效。
7、用来查询数据文件、临时文件与表空间对应及数据文件序号：

select ts.tablespace_name, df.file_name, df.file_id, tf.file_name
  from dba_tablespaces ts, dba_data_files df, dba_temp_files tf
 where ts.tablespace_name = df.tablespace_name(+)
   and ts.tablespace_name = tf.tablespace_name(+);

8、根据spid查出正在运行的sql

select b.sid,
       b.serial#,
       b.status,
       b.osuser || ':' || b.terminal || ':' || b.program || '@' ||
       b.machine as hostuserapp,
       a.piece,
       a.sql_text
  from v$sqltext_with_newlines a, v$session b
 where a.address(+) =
       decode(b.sql_hash_value, 0, b.prev_sql_addr, b.sql_address)
   and a.hash_value(+) =
       decode(b.sql_hash_value, 0, b.prev_hash_value, b.sql_hash_value)
   and b.type <> upper('background')
   and b.sid in (select b.SID
                   from v$process a, v$session b
                  where a.addr = b.PADDR
                    and a.spid = &spid)
 order by b.sid, a.piece;

9、定位消耗资源多的sql

select sql_text from v$sql where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 3000);

10、查询系统资源 (sessions和process连接数情况)

SELECT resource_name,
       current_utilization,
       max_utilization,
       LIMIT,
       ROUND(max_utilization / LIMIT * 100) || '%' rate
  FROM (SELECT resource_name,
               current_utilization,
               max_utilization,
               TO_NUMBER(initial_allocation) LIMIT
          FROM v$resource_limit
         WHERE resource_name IN ('processes', 'sessions')
           AND max_utilization > 0);

alter system set processes=500 scope=spfile;
alter system set sessions=500 scope=spfile; 

然后重启数据库

11、查询当前数据库使用全表扫描的SQL

select a.sid,
       a.serial#,
       a.username,
       a.status,
       a.program,
       a.machine,
       c.sql_text
  from v$session a, v$session_wait b, v$sql c
 where a.sid = b.sid
   and a.sql_hash_value = c.hash_value
   and a.sql_address = c.address
   and b.event like 'db file scattered read%';

12、查询表空间使用、分配等情况

select (select decode(extent_management,'LOCAL','*',' ') || 
               decode(segment_space_management,'AUTO','a ','m ')
         from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name, 
          nvl(a.tablespace_name,'UNKOWN')) name,
      MB_Allocated MB_Allocated, 
      MB_Allocated-nvl(Free_MB,0) used,
      nvl(Free_MB,0) free, 
      ((MB_Allocated-nvl(Free_MB,0))/ 
                    nvl(Max_MB,MB_Allocated))*100 pct_used,
      nvl(Max_MB,MB_Allocated) Max_Size,
      decode( Max_MB, 0, 0, (MB_Allocated/Max_MB)*100) pct_max_used
from ( select sum(bytes)/1048576 Free_MB, 
           max(bytes)/1048576 largest,
           tablespace_name
      from  sys.dba_free_space 
      group by tablespace_name ) a,
     ( select sum(bytes)/1048576 MB_Allocated, 
           sum(maxbytes)/1048576 Max_MB,
           tablespace_name 
      from sys.dba_data_files 
      group by tablespace_name 
      union all
      select sum(bytes)/1048576 MB_Allocated, 
           sum(maxbytes)/1048576 Max_MB,
           tablespace_name 
      from sys.dba_temp_files 
      group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 1
/

13、查询创建表空间的原始语句

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name) FROM DBA_TABLESPACES TS;

 14、查询oracle一张表的数据块上有多少条记录

select dbms_rowid.rowid_block_number(rowid),count(dbms_rowid.rowid_block_number(rowid)) from t group by dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
------------------------------------ -------------------------------------------
                               28584                                          68   －指第28584号块上有68条记录