oracle性能分析和调优

/*单条sql统计信息v$sql*/
select a.SQL_TEXT,
       a.EXECUTIONS,
       a.ELAPSED_TIME,
       a.PHYSICAL_READ_BYTES,
       a.LOCKED_TOTAL,
       a.ROWS_PROCESSED
  from v$sql a
 where a.EXECUTIONS > 10
   and a.module = 'JDBC Thin Client'
   and a.LAST_ACTIVE_TIME >
       to_date('2014/5/19 17:49:47', 'yyyy/MM/dd HH24:mi:ss')
 order by a.EXECUTIONS desc
 
 
  /*分组统计 v$SQLAREA  */     
 select sql_text,
        executions,
        disk_reads,
        optimizer_mode,
        buffer_gets,
        hash_value,
        sq.SQL_ID,
        sq.ADDRESS
   from v$SQLAREA sq
  where sq.LAST_ACTIVE_TIME >
        to_date('2014/5/19 17:49:47', 'yyyy/MM/dd HH24:mi:ss')
        and sq.EXECUTIONS>10
        and sq.MODULE='JDBC Thin Client'
        order by sq.EXECUTIONS desc
 
     
  
  
/* 得到某条SQL的查询计划 ,先得到一条sql的地址  */  
 select lpad(' ', 2 * (level - 1)) || operation "Operation",
        options "Options",
        decode(to_char(id),
               '0',
               'Cost=' || nvl(to_char(position), 'n/a'),
               object_name) "Object Name",
        substr(optimizer, 1, 6) "Optimizer"
   from v$sql_plan a
  start with address = '91F413F4'
         and id = 0
 connect by prior id = a.parent_id
        and prior a.address = a.address
        and prior a.hash_value = a.hash_value;
               
/* 用户连接操作数据信息 */
select u.AUDSID,u.SADDR,u.USERNAME,u.STATUS,u.SCHEMANAME,u.MACHINE,u.MODULE from v$SESSION u
/*当前用户*/
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS FROM V$SESSION WHERE audsid = userenv( 'SESSIONID' );
/*查看共享模式参数和配置*/
select * from v$shared_server;
select * from v$dispatcher; 
select * from v$session;






 




 /*扩展，查找十条性能最差的sql,按照磁盘读排序*/
SELECT *
  FROM (select PARSING_USER_ID,
               EXECUTIONS,
               SORTS,
               COMMAND_TYPE,
               DISK_READS,
               sql_text
          FROM v$sqlarea where module = 'JDBC Thin Client'
         order BY disk_reads DESC)
 where ROWNUM < 10
 
 
 
/* 当前数据库环境信息，或者当前用户信息*/
/*返回当前用户环境的信息*/
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE


select userenv('SESSIONID') from dual;
select userenv('LANGUAGE') from dual;


 select 
　　SYS_CONTEXT('USERENV','TERMINAL') terminal, 
　　SYS_CONTEXT('USERENV','LANGUAGE') language, 
　　SYS_CONTEXT('USERENV','SESSIONID') sessionid, 
　　SYS_CONTEXT('USERENV','INSTANCE') instance, 
　　SYS_CONTEXT('USERENV','ENTRYID') entryid, 
　　SYS_CONTEXT('USERENV','ISDBA') isdba, 
　　SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory, 
　　SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency, 
　　SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar, 
　　SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format, 
　　SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language, 
　　SYS_CONTEXT('USERENV','NLS_SORT') nls_sort, 
　　SYS_CONTEXT('USERENV','CURRENT_USER') current_user, 
　　SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid, 
　　SYS_CONTEXT('USERENV','SESSION_USER') session_user, 
　　SYS_CONTEXT('USERENV','SESSION_USERID') session_userid, 
　　SYS_CONTEXT('USERENV','PROXY_USER') proxy_user, 
　　SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid, 
　　SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain, 
　　SYS_CONTEXT('USERENV','DB_NAME') db_name, 
　　SYS_CONTEXT('USERENV','HOST') host, 
　　SYS_CONTEXT('USERENV','OS_USER') os_user, 
　　SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name, 
　　SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address, 
　　SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol, 
　　SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id, 
　　SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id, 
　　SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type, 
　　SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data 
　　from dual;
        
/* 共享模式（SGA）*/
select pool,sum(bytes)/1024/1024 ||'M'  from v$sgastat where pool='shared pool' group by pool;
select * from v$sgastat;
/*增加共享池大小*/
alter system set shared_pool_size = '500M';


select * from v$librarycache ;
/*使用如下语句可以确定库快存和数据字典快存的命中率：*/
select(sum(pins-reloads))/sum(pins) "Lib Cache" from v$librarycache ;
/*共享池中的自由内存可以查看：*/
select * from v$sgastat where name='free memory';
/*1. 检查库快存的活动*/
select sum(pins) "Executions",sum(reloads) "Cache Misses while Executing" from v$librarycache ;






/*每个session最大能够打开的游标数目：*/
select * from v$parameter where name = 'open_cursors'
--每个session（会话）最多能同时打开多少个cursor（游标）  
show parameter open_cursors   
--每个session（会话）最多可以缓存多少个关闭掉的cursor    
show parameter session_cached_cursor;
--是指当前实例的某个时刻的打开的cursor数目        
select count(*) from v$open_cursor; 


/*检查游标设置是否合理*/
SELECT MAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR  
      FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P  
     WHERE A.STATISTIC# = B.STATISTIC#  
      AND B.NAME = 'opened cursors current'  
       AND P.NAME = 'open_cursors'  
     GROUP BY P.VALUE;   
 
/*是否存在游标泄露 */   
 SELECT A.VALUE, S.USERNAME, S.SID, S.SERIAL#
   FROM V$SESSTAT A, V$STATNAME B, V$SESSION S
  WHERE A.STATISTIC# = B.STATISTIC#
    AND S.SID = A.SID
    AND B.NAME = 'opened cursors curent';
    
    
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%cursor%';     
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%parse%';
/*游标使用率*/
SELECT 'session_cached_cursors' PARAMETER,
       LPAD(VALUE, 5) VALUE,
       DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE, '990') || '%') USAGE
  FROM (SELECT MAX(S.VALUE) USED
          FROM V$STATNAME N, V$SESSTAT S
         WHERE N.NAME = 'session cursor cache count'
           AND S.STATISTIC# = N.STATISTIC#),
       (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors')
UNION ALL
SELECT 'open_cursors',
       LPAD(VALUE, 5),
       TO_CHAR(100 * USED / VALUE, '990') || '%'
  FROM (SELECT MAX(SUM(S.VALUE)) USED
          FROM V$STATNAME N, V$SESSTAT S
         WHERE N.NAME IN
               ('opened cursors current', 'session cursor cache count')
           AND S.STATISTIC# = N.STATISTIC#
         GROUP BY S.SID),
       (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors');