如何理解这个CPU和ELAPSED的区别
Disable bind variable peeking by setting “_OPTIM_PEEK_USER_BINDS=false”. With this the optimizerreturns to the pre-Oracle9i calculation of selectivities of columns with histograms. Since this is an
undocumented (and unsupported) initialization parameter, it would be safer to just delete the histograms
call count cpu elapsed disk query current rows
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.57 8.97 44109 47534 0 1
cpu = cpu time in seconds executing
elapsed= elapsed time in seconds executing
CPU = CPU执行的时间
ELAPSED 执行的时候消耗的时候
没有看明白什么意思 CPU time is the time the process runs on the CPU(s). Elapsed time is the wall clock time, ending time minus starting time. Does that make sense?
Yong Huang 原帖由 Yong Huang 于 2009-1-13 02:20 发表 http://www.itpub.net/images/common/back.gif
CPU time is the time the process runs on the CPU(s). Elapsed time is the wall clock time, ending time minus starting time. Does that make sense?
Yong Huang
帮主，下面这个怎么解释呢？
asktom 解释的 parse >exectue,怎么理解？我一直都没有搞明白，希望这次能搞明白
在数据库优化的时候经常遇到了关于parse /exectue > 1的情况 ，一般的解释都是认为没有使用绑定变量的原因。
但是小弟实在是不明白为什么会有语句被parse 了，却没有被exectue呢？
也就是说是什么情况导致了，会有语句被parse 了，但该语句却没有被exectue呢？
不知道哪位大哥能举个例子，看了asktom，他说了个例子，但是e文差，没有看明白，我先把它贴在下面了，看懂了的，请顶啊！
There are tools (bad ones) that parse a statement but never execute it.Or they
parse it to discover what inputs/outputs it takes and then later on parse it
again to execute it (so the parse/execute is 2:1). 就是对语句只进行了解析（parse），而不去执行（execute）这个语句造成了parse > execute.
比如：
SQL> set linesize 170 pagesize 10000
SQL> col name for a30
SQL>
SQL> select name,value
2from v$sesstat a,v$statname b
3where a.statistic#=b.statistic#
4and a.statistic# in (232,235)
5and a.sid=(select sid from v$mystat where rownum=1)
6order by 1;
NAME VALUE
------------------------------ ----------
execute count 4
parse count (total) 4
SQL>
SQL> declare
2 v_cursor integer default dbms_sql.open_cursor;
3 v_sql varchar2(500);
4begin
5 for i in 1..1000 loop
6 v_sql:='select ' || i || ' from dual';
7 dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);
8 end loop;
9end;
10/
PL/SQL 过程已成功完成。
SQL>
SQL> select name,value
2from v$sesstat a,v$statname b
3where a.statistic#=b.statistic#
4and a.statistic# in (232,235)
5and a.sid=(select sid from v$mystat where rownum=1)
6order by 1;
NAME VALUE
------------------------------ ----------
execute count 6
parse count (total) 1006
可以看到，parse count比execute多了1000，正好是那段PL/SQL代码中动态解析的次数。
[ 本帖最后由 magic007 于 2009-1-13 19:51 编辑 ] CPU:消耗的CPU时间
ELAPSED：消耗的总CPU时间，保护等待时间。 原帖由 moumouyu 于 2009-1-12 22:37 发表 http://www.itpub.net/images/common/back.gif
帮主，下面这个怎么解释呢？
asktom 解释的 parse >exectue,怎么理解？我一直都没有搞明白，希望这次能搞明白
在数据库优化的时候经常遇到了关于parse /exectue > 1的情况 ，一般的解释都是认为没有使用绑定变量的原因。
但是小弟实在是不明白为什么会有语句被parse 了，却没有被exectue呢？
也就是说是什么情况导致了，会有语句被parse 了，但该语句却没有被exectue呢？
不知道哪位大哥能举个例子，看了asktom，他说了个例子，但是e文差，没有看明白，我先把它贴在下面了，看懂了的，请顶啊！
There are tools (bad ones) that parse a statement but never execute it.Or they
parse it to discover what inputs/outputs it takes and then later on parse it
again to execute it (so the parse/execute is 2:1).
One important case I want to add to what Tom Kyte says is that if the SQL is run in parallel, the SQL will be parsed multiple times by the query coordinator and all parallel slaves I assume, but this particular SQL itself is only executed by the query coordinator, because after each slave parses it, the slave creates its only SQL with slightly different sql_text (and therefore hash_value and sql_id).
magic007's case makes sense. But in reality, it doesn't happen often.
Yong Huang 原帖由 Yong Huang 于 2009-1-14 02:55 发表 http://www.itpub.net/images/common/back.gif
One important case I want to add to what Tom Kyte says is that if the SQL is run in parallel, the SQL will be parsed multiple times by the query coordinator and all parallel slaves I assume, but this particular SQL itself is only executed by the query coordinator, because after each slave parses it, the slave creates its only SQL with slightly different sql_text (and therefore hash_value and sql_id).
magic007's case makes sense. But in reality, it doesn't happen often.
Yong Huang
按照这个理解，parse大于execute，在parallel服务器上面更容易出现？ 原帖由 moumouyu 于 2009-1-13 20:07 发表 http://www.itpub.net/images/common/back.gif
按照这个理解，parse大于execute，在parallel服务器上面更容易出现？
You mean RAC? Not necessarily. It's parallel executions of SQLs, not Oracle parallel server or RAC.
Yong Huang 可以这样理解
elapse time - cpu time = wait time 原帖由 root_dba 于 2009-1-15 09:48 发表 http://www.itpub.net/images/common/back.gif
可以这样理解
elapse time - cpu time = wait time
减出来应该是io time吧
页:
[1]
2