高分求 测试SQL语句性能和运行时间的工具 小弟最近研究SQL优化语句 可是在PL/SQL中的命令窗口执行的语句时间不确定。看不出优化完与优化前的差距,哪个语句性能更好请大家帮忙 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 有如下查看执行计划方式:1.sqlplus中autotrace2.dbms_system.set_sql_trace_in_session()3.statspack4.explain plan for your sql5.10046 显示时间用:set timing on执行计划用:set autotrace on 还有:alter session|system set sql_trace=true OPER@tl>create table test(aaa number);表已创建。OPER@tl>set autotrace onOPER@tl>insert into test values(10);已创建 1 行。执行计划-----------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |-------------------------------------------------------------------------统计信息---------------------------------------------------------- 3 recursive calls 22 db block gets 5 consistent gets 0 physical reads 0 redo size 913 bytes sent via SQL*Net to client 936 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processedOPER@tl>select * from test; AAA---------- 10执行计划----------------------------------------------------------Plan hash value: 1357081020--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS FULL| TEST | 1 | 13 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------Note----- - dynamic sampling used for this statement统计信息---------------------------------------------------------- 28 recursive calls 0 db block gets 17 consistent gets 0 physical reads 0 redo size 407 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedOPER@tl> SQL> SET TIMING ONSQL> SET AUTOTRACE TRACEONLYOR OPEN "PL\SQL DEVELOPER TOOL" -> F5 OPER@tl>这个是在什么里?用的是PL/SQL么 有如下查看执行计划方式: 1.sqlplus中autotrace 2.dbms_system.set_sql_trace_in_session() 3.statspack 4.explain plan for your sql 5.10046 所有工具均只能得到SQL的开销,具体不会给出任何优化建议。所以,需要懂得那些数据是对性能有较大影响的。是可以花费较少的成本得到较好的优化效果的。如:统计信息---------------------------------------------------------- 28 recursive calls 0 db block gets 17 consistent gets 0 physical reads 0 redo size 407 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed中的: 17 consistent gets 表示逻辑读,如果读取很少几条 rows ,而数据很大,则重点优化对应步骤中的最多逻辑读的SQL。 如何insert CLOB字段? Oracle与插件DevExpress XtraTreeList兼容性问题 oracle计算工作完工时间 请教:oracle中比较数据库中两个用户结构的差异 Oracle创建表失败 oracle8i客户端和9的客户端字符集不一样,故软件在启动时会出现乱码,如何一次性解决版本引起的字符集冲突???? 100分求教FROM的条件查询问题! 一愁莫展Form Builder 请问如何实现特定顺序的查询? 在XP上无法安装9i client,请高手指教,高分奉上。 sqlldr 如何导入文本为clob类型 请教一个小问题
1.sqlplus中autotrace
2.dbms_system.set_sql_trace_in_session()
3.statspack
4.explain plan for your sql
5.10046
set timing on执行计划用:
set autotrace on
OPER@tl>insert into test values(10);已创建 1 行。
执行计划
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
统计信息
----------------------------------------------------------
3 recursive calls
22 db block gets
5 consistent gets
0 physical reads
0 redo size
913 bytes sent via SQL*Net to client
936 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processedOPER@tl>select * from test; AAA
----------
10
执行计划
----------------------------------------------------------
Plan hash value: 1357081020--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 13 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedOPER@tl>
SQL> SET AUTOTRACE TRACEONLY
OR OPEN "PL\SQL DEVELOPER TOOL" -> F5
这个是在什么里?
用的是PL/SQL么
1.sqlplus中autotrace
2.dbms_system.set_sql_trace_in_session()
3.statspack
4.explain plan for your sql
5.10046
所以,需要懂得那些数据是对性能有较大影响的。是可以花费较少的
成本得到较好的优化效果的。
如:
统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
中的:
17 consistent gets 表示逻辑读,如果读取很少几条 rows ,而数据很大,则重点优化
对应步骤中的最多逻辑读的SQL。