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))