Oracle常用的性能诊断语句
来源:互联网 发布:文言文辞职信走红网络 编辑:程序博客网 时间:2024/05/14 22:37
--2.查找前10条性能差的SQL语句(磁盘读取较大,缺少索引或语句不合理)SELECT * FROM (SELECT PARSING_USER_ID, EXECUTIONS, SORTS, COMMAND_TYPE, DISK_READS, sql_text FROM v$sqlarea ORDER BY disk_reads DESC) WHERE ROWNUM < 10;-- 查询消耗资源最多的SQLSELECT sql_text, hash_value, executions, buffer_gets, disk_reads, parse_calls FROM V$SQLAREAWHERE buffer_gets > 10000000 OR disk_reads > 100000ORDER BY buffer_gets + 100 * disk_reads DESC;--附SELECT EXECUTIONS, DISK_READS, BUFFER_GETS , ROUND((BUFFER_GETS-DISK_READS) / BUFFER_GETS, 2) Hit_radio , TRUNC(DISK_READS / EXECUTIONS) Reads_per_run , SQL_TEXTFROM V$SQLAREA WHERE EXECUTIONS > 0 AND DISK_READS > 0 and BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS) / BUFFER_GETS < 0.8 ORDER BY 5 DESC;-- 查看某条SQL语句的资源消耗 SELECT HASH_VALUE, BUFFER_GETS, DISK_READS, EXECUTIONS, PARSE_CALLS FROM V$SQLAREA WHERE HASH_VALUE = 228801498 AND ADDRESS = HEXTORAW('CBD8E4B0');-- 运行时间很长的SQL(session_longops视图显示运行超过6秒的操作。包括备份,恢复,统计信息收集,查询等等)select b.USERNAME, b.SID, b.SERIAL#, b.START_TIME, b.LAST_UPDATE_TIME , round(sofar * 100 / totalwork, 0) || '%' as progress , a.sql_text from v$sqlarea a join v$session_longops b on a.SQL_ID = b.SQL_ID--where b.TIME_REMAINING != 0 -- TIME_REMAINING:预计完成操作的剩余时间(秒) -- and b.USERNAME = 'sccot'-- 最近10分钟最消耗CPU的SQL语句: select sql_text from ( select sql_id,count(*) as cn from v$active_session_history where sample_time > sysdate - 10/24/60 and session_type <> 'BACKGROUND' and SESSION_STATE = 'ON CPU' group by sql_id order by cn desc ) ash, v$sql swhere ash.sql_id=s.sql_id;-- 最近10分钟最消耗IO的SQL语句:select sql_text from ( select sql_id,count(*) as cn from v$active_session_history where sample_time > sysdate - 10/24/60 and session_type <> 'BACKGROUND' and WAIT_CLASS='User I/O' group by sql_id order by cn desc ) ash, v$sql swhere ash.sql_id=s.sql_id;-- 补充1,根据时间找出响应时间较长的SQL: select t.SQL_TEXT, t.SQL_FULLTEXT, t.ELAPSED_TIME, t.LAST_LOAD_TIME, t.* from v$sql t where t.LAST_LOAD_TIME > to_char(sysdate - 10/(24*60), 'yyyy-mm-dd/hh24:mi:ss') --and t.sql_text like '%LSHSXM%' order by t.ELAPSED_TIME desc;-- 补充2,查找硬解析严重的SQL:select max(sql_id), substr(sql_text,0,100), count(1)from v$sql where executions < 10 group by substr(sql_text,0,100) having count(1) > 1000order by count(1) desc;--3.统计行数或空间占用较大的表select t.table_name, t.num_rows, t.last_analyzed, t.temporary, t.*from user_tables twhere t.num_rows > 0order by t.num_rows desc-- 普通表数据大小排序select segment_name, trunc(bytes/1024/1024)from user_segments where segment_type = 'TABLE'order by bytes desc;-- 含LOB类型表的数据大小SELECT A.TABLE_NAME, A.COLUMN_NAME, trunc(B.BYTES / 1024 / 1024), B.SEGMENT_NAME, B.SEGMENT_TYPE, B.TABLESPACE_NAME, B.BLOCKS, B.EXTENTS FROM USER_LOBS A, USER_SEGMENTS B WHERE A.SEGMENT_NAME = B.SEGMENT_NAME ORDER BY B.BYTES DESC;-- 数据表总的数据大小占用select rowSize + nvl(lobSize, 0) dataSize, t1.*, t2.*from ( select segment_name, trunc(bytes/1024/1024) rowSize from user_segments where segment_type = 'TABLE' ) t1 left join ( SELECT A.TABLE_NAME, A.COLUMN_NAME, trunc(B.BYTES / 1024 / 1024) lobSize, B.SEGMENT_NAME, B.SEGMENT_TYPE, B.TABLESPACE_NAME, B.BLOCKS, B.EXTENTS FROM USER_LOBS A, USER_SEGMENTS B WHERE A.SEGMENT_NAME = B.SEGMENT_NAME ) t2 on t1.segment_name = t2.table_nameorder by rowSize + nvl(lobSize, 0) desc补充,查询该表实际使用的blocks: SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) || DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) AS Used_Blocks FROM table_name;--4.查看Oracle内存参数配置 select *from v$parameter twhere t.NAME in ( 'memory_max_target', 'memory_target', 'sga_max_size', 'sga_target', 'pga_aggregate_target', 'cpu_count' , 'db_cache_size', 'shared_pool_size', 'large_pool_size', 'java_pool_size', 'streams_pool_size', 'log_buffer' , 'db_2k_cache_size', 'db_4k_cache_size', 'db_8k_cache_size', 'db_16k_cache_size', 'db_32k_cache_size' );-- 查询SGA区的分配细节及使用情况select t1.pool, t1.reserved_size_mb, t2.used_size_mb, t1.reserved_size_mb - t2.used_size_mb AS unused_size_mbfrom ( select t.POOL, trunc(sum(t.BYTES)/1024/1024) reserved_size_mb from v$sgastat t where t.POOL is not null group by t.POOL ) t1 left join ( select t.POOL, trunc(sum(t.BYTES)/1024/1024) used_size_mb from v$sgastat t where t.pool is not null and t.name != 'free memory' group by t.POOL ) t2 on t1.pool = t2.poolunion allselect t.name, trunc(sum(t.BYTES)/1024/1024) reserved_size_mb, null, null from v$sgastat t where t.pool is null group by t.NAMEorder by 2 desc;-- 查看系统的缓存命中率与软解析率 SELECT 'Cache hit ratio' as name, (1-(physical_reads/(db_block_gets+consistent_gets)))*100 value FROM v$buffer_pool_statisticsunion all select 'Soft parse ratio', 100-100*(a.value/b.value) from v$sysstat a, v$sysstat b Where a.name='parse count (hard)' and b.name='parse count (total)'; --5.查询锁表的语句select 'kill -9 '||PS.SPID, 'alter system kill session '''||vs.sid||','||vs.serial#||''';', /* DECODE(V$LOCK.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,*/ Decode(VL.LOCKED_MODE,0,'[0] none', 1,'[1] null 空', 2,'[2] Row-S 行共用(RS):共用表鎖,sub share ', 3,'[3] Row-X 行獨佔(RX):用於行的修改,sub exclusive ', 4,'[4] Share 共用鎖(S):阻止其他DML操作,share', 5,'[5] S/Row-X 共用行獨佔(SRX):阻止其他事務操作,share/sub exclusive ', 6,'[6] exclusive 獨佔(X):獨立訪問使用,exclusive ', '['||VL.LOCKED_MODE||'] Other Lock') LockMode, PS.SPID,OS_USER_NAME,VS.PROGRAM,VS.MACHINE,ORACLE_USERNAME,OBJECT_NAME,vs.LOGON_TIME , Vs.status,vs.MODULE, NVL(currentSql.SQL_TEXT, prevSql.SQL_TEXT) as sql_textfrom V$LOCKED_OBJECT VL join DBA_OBJECTS OB on VL.OBJECT_ID = OB.OBJECT_ID join V$SESSION VS on VL.SESSION_ID = VS.SID join v$process PS on PS.ADDR = VS.PADDR --left join v$sql currentSql on vs.SQL_HASH_VALUE = currentSql.HASH_VALUE and vs.SQL_ADDRESS = currentSql.ADDRESS --left join v$sql prevSql on vs.PREV_HASH_VALUE = prevSql.HASH_VALUE and vs.PREV_SQL_ADDR = prevSql.ADDRESS left join v$sql currentSql on vs.SQL_ID = currentSql.SQL_ID and vs.SQL_Child_Number = currentSql.Child_Number left join v$sql prevSql on vs.Prev_SQL_ID = prevSql.Sql_Id and vs.PREV_Child_Number = prevSql.Child_Numberorder by vs.logon_time;--6.产生kill会话的语句 select A.SID, B.SPID, A.SERIAL#, a.lockwait, A.USERNAME, A.OSUSER, a.logon_time, a.last_call_et / 3600 LAST_HOUR, A.STATUS, 'orakill ' || sid || ' ' || spid HOST_COMMAND, 'alter system kill session ''' || A.sid || ',' || A.SERIAL# || '''' SQL_COMMAND from v$session A, V$PROCESS B where A.PADDR = B.ADDR AND SID > 6;--7.查看IO情况select df.name 文件名, fs.phyrds 读次数, fs.phywrts 写次数, (fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) 读时间, (fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) 写时间from v$datafile df, v$filestat fswhere df.file#=fs.file#order by df.name;--8.查看表空间情况SELECT UPPER(F.TABLESPACE_NAME) "表空间名", D.TOT_GROOTTE_MB "表空间大小(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比", F.TOTAL_BYTES "空闲空间(M)", F.MAX_BYTES "最大块(M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC;--9.根据unix上Top命令看到的PID,查找对应的SQlSELECT P.pid pid, S.sid sid, P.spid spid, S.username username, S.osuser osname, P.serial# S_#, P.terminal, P.program program, P.background, S.status, RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL FROM v$process P, v$session S, v$sqlarea A WHERE P.addr = s.paddr AND S.sql_address = a.address(+) AND P.spid LIKE '%CPU最高的进程对应的PID%';-- 数据库及实例的状态信息select name, log_mode, open_mode, flashback_on, supplemental_log_data_min, platform_name from v$database;select instance_number, instance_name, host_name, version, startup_time, status from v$instance;--清空缓存(共享池和数据缓存),Command window执行:alter system flush shared_pool;alter system flush buffer_cache;--索引信息查看: select t.table_owner, t.index_name, t.blevel, t.leaf_blocks , t.last_analyzed, t.distinct_keys, t.num_rows, t.sample_sizefrom user_indexes t where t.table_name = upper('workitem');--analyze table 更新统计信息:analyze table my_table compute statistics; analyze index idx_name compute statistics;--OREXEC DBMS_STATS.gather_table_stats(ownname => 'socct', tabname =>'WORKITEM', estimate_percent =>100, cascade =>true, method_opt => 'for all columns size auto');/*参数说明:一、 estimate_percent :抽样统计百分比二、 cascade :为 false 时 , 只对表进行统计分析;为 true 时 , 同时对表和索引进行统计分析 .三、 method_opt : method_opt: 决定 histograms 信息是怎样被统计的 .method_opt 的取值如下 : for all columns: 统计所有列的 histograms. for all indexed columns: 统计所有 indexed 列的 histograms. for all hidden columns: 统计你看不到列的 histograms for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY SIZE :统计指定列的 histograms.N 的取值范围 [1,254]; REPEAT 上次统计过的 histograms; AUTO 由 oracle 决定 N 的大小 ; 四、 degree :并发性,可调值为, 8/16/24/32...*/ --删除数据后,释放数据空间alter table my_table enable row movement;alter table my_table shrink space cascade;alter table my_table disable row movement;oralter table table_name move; -- 执行此操作后,需要重建索引select t.NUM_ROWS, t.TEMPORARY, t.last_analyzed, t.* from user_tables t where t.TABLE_NAME = upper('gspauresult_tkk0107');select t.num_rows, t.distinct_keys, t.sample_size, t.last_analyzed, t.* from user_indexes t where t.table_name = upper('tkk007');select t.* from user_ind_columns t where t.TABLE_NAME = upper('gspauresult_tkk0107')order by t.INDEX_NAME, t.COLUMN_POSITION;select dbms_metadata.get_ddl('TABLE',upper('gspauresult_tkk0107')) from dual;select dbms_metadata.get_ddl('INDEX',upper('idx_gspauresult_tkk0107')) from dual;select dbms_metadata.get_ddl('CONSTRAINT',upper('tkk0107')) from dual;select dbms_metadata.get_ddl('REF_CONSTRAINT',upper('tkk0107')) from dual;select * from user_tables t order by dbms_random.random;获取指定SQL在内存中的执行计划:select sql_id,child_number,sql_text from v$sql where sql_text like 'select count(1) from emp a where a.dept_no =%';select * from table(DBMS_XPLAN.DISPLAY_CURSOR('sql_id',0)); 1、PL/SQL Developer 中使用F52、explain plan for select count(1) from emp a where a.dept_no=5; select * from table(dbms_xplan.display());3、sqlplus中使用 set autotrace traceonly exp;
0 0
- Oracle常用的性能诊断语句
- Oracle性能诊断的方法
- Oracle性能诊断的方法
- Oracle常用性能分析语句
- Oracle 常用性能查看语句
- 性能测试常用Oracle语句
- oracle 诊断语句
- 诊断oracle性能
- oracle性能诊断工具
- Oracle常用诊断事件
- oracle实时性能诊断命令
- 读书笔记 《Oracle性能诊断艺术》
- 常用的oracle语句
- oracle 常用的语句
- Oracle常用性能监控SQL语句
- ORACLE常用性能监控SQL语句
- Oracle常用性能监控语句解析
- Oracle常用性能监控语句解析
- Perfect Number
- linux 笔记
- Ora-01830 日期格式图片在转换整个输入字符串之前结束
- SpringMVC BindingResult验证框架Validation特殊用法
- MySQL连接失效问题
- Oracle常用的性能诊断语句
- 套路深!软件测试各阶段的测试策略是什么?
- 关于Dev 控件里 gridcontrol 的gridview 显示序号和checkbox
- 【笔记】java核心技术-对象的克隆
- Java增强型for循环
- Linux下测试网速
- 快速排序
- Visio2010建立ER图并直接导出为SQL语句
- 简谈stm32的地址映射