SQLPLUS的AutoTrace是分析SQL的执行计划，执行效率的一个非常简单方便的工具，在绝大多数情况下，也是非常有用的工具。
这里，我们试图归纳一些常见的问题，并进行一定的分析
1。如何设置和使用AUTOTRACE
SQL> connect / as sysdba
SQL> @?/rdbms/admin/utlxplan.sql
Table created.
SQL> create public synonym plan_table for plan_table;
Synonym created.
SQL> grant select,update,insert,delete on plan_table to public;
Grant succeeded.
SQL> @?/sqlplus/admin/plustrce.sql
SQL>grant plustrace to public.
2. 理解和使用AutoTrace
对于SQL 调整，使用Autotrace是最简单的方法了，我们只需要做：
SQL>SET AUTOTRACE ON
我们就可以看到我们SQL的执行计划，执行成本（PHYSICAL READ/CONSISTENT READ...)
加上SET Timing On或者Set Time On，我们可以得到很多我们需要的数据。
SQL> select nvl(title,' ') from punishinfo_cs where ci_id=45672 ;
NVL(TITLE,'')
--------------------------------------------------
阎王令
Elapsed: 00:00:00.00
SQL> set autotrace on
SQL> /
NVL(TITLE,'')
--------------------------------------------------
阎王令
Elapsed: 00:00:00.71
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PUNISHINFO_CS' (Cost=2 C
ard=1 Bytes=32)
2 1 INDEX (UNIQUE SCAN) OF 'SYS_C001084' (UNIQUE) (Cost=1 Ca
rd=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
376 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3.关于使用Autotrace的一些常见疑问：
a.比如我上面的例子，我不用Autotrace，我的时间小于0.01S，但是用了Autotrace，我的执行时间变成了0.71S.
不注意的人往往会认为，或者没有测试不用Autotrace时候的情况，往往会忽视这个数字，认为时间就是0.71S.
实际上，这个0.7S，是花在Autotrace里面的时间。由于Autotrace需要记录你的SQL执行的成本，这个本身是往数据库里面读取和写入一定的数据的，需要一定的时间。当你的SQL执行时间足够短的时候，这个由于Autotrace带来的时间就变成非常可观的了。我们就需要通过不用Autotrace的时间，和使用Autotrace的执行成本来结合比较。
我们通过结合Autotrace和Tkprof/SQLTRACE，很容易知道，AUtotrace就近作了什么：
select nvl(title,' ')
from
punishinfo_cs where ci_id=45672
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 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 3 0 1
DELETE FROM PLAN_TABLE
WHERE
STATEMENT_ID=:1
EXPLAIN PLAN SET STATEMENT_ID='PLUS185025' FOR select nvl(title,' ') from
punishinfo_cs where ci_id=45672
insert into plan_table (statement_id, timestamp, operation, options,
object_node, object_owner, object_name, object_instance, object_type,
search_columns, id, parent_id, position, other,optimizer, cost, cardinality,
bytes, other_tag, partition_start, partition_stop, partition_id,
distribution )
values
(:1,SYSDATE,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,
:20,:21,:22)
SELECT ID ID_PLUS_EXP,PARENT_ID PARENT_ID_PLUS_EXP,LPAD(' ',2*(LEVEL-1))
||OPERATION||DECODE(OTHER_TAG,NULL,'','*')||DECODE(OPTIONS,NULL,'','
('||OPTIONS||')')||DECODE(OBJECT_NAME,NULL,'',' OF '''||OBJECT_NAME||'''')
||DECODE(OBJECT_TYPE,NULL,'',' ('||OBJECT_TYPE||')')||DECODE(ID,0,
DECODE(OPTIMIZER,NULL,'',' Optimizer='||OPTIMIZER))||DECODE(COST,NULL,'','
(Cost='||COST||DECODE(CARDINALITY,NULL,'',' Card='||CARDINALITY)
||DECODE(BYTES,NULL,'',' Bytes='||BYTES)||')') PLAN_PLUS_EXP,OBJECT_NODE
OBJECT_NODE_PLUS_EXP
FROM
PLAN_TABLE START WITH ID=0 AND STATEMENT_ID=:1 CONNECT BY PRIOR ID=PARENT_ID
AND STATEMENT_ID=:1 ORDER BY ID,POSITION
SELECT ID ID_PLUS_EXP,OTHER_TAG OTHER_TAG_PLUS_EXP,OTHER OTHER_PLUS_EXP
FROM
PLAN_TABLE WHERE STATEMENT_ID=:1 AND OTHER_TAG IS NOT NULL ORDER BY ID
我们看到，由于我们的Autotrace，简简单单的一句话，实际上oracle 做了那么多的事情。
一:How do i decide which query is better depending on the values of the each parameter in statistics?
1 mportant parameters:
db blocks gets,consistent gets :logical reads or memory usage.
physical reads is disk I/O
Depending on this we can estimate the memory usage.
2:secondly,recursive calls,redo size,sorts(memory),sorts(disk)
3:thirdly:bytes sent via SQL&Net from client ,bytes received via SQL&Net from client,SQL&Net roundtrips to/from client
二:What these parameters really mean?
Recursive calls – The number of recursive calls to the database. This type of call occurs for a few reasons – misses in
the dictionary cache, dynamic storage extension, and when PL/SQL statements are executed. Generally, if the number of
recursive calls is more than 4 per process, you should check the dictionary hit cache ratio. Recursive Calls These occur because of cache misses and dynamic storage extension. If the dictionary data is found in cache, a recursive call is not made and the data is read from cache directly. In general, if recursive calls are greater than four per process, the data dictionary cache should be optimized and segments should be rebuilt with storage clauses to have a few large extents. Segments include tables, indexes, and rollback segments.Recursive calls should be fewer than user calls (less than one-tenth). Where there is an imbalance, the aim should be to reduce parsing. High levels of recursive SQL may also be attributable to significant use of PL/SQL. For each SQL statement in a PL/SQL
block, on each iteration, there are recursive calls to do the equivalent of bind and define.
DB Block gets – The number of blocks in the buffer cache that were accessed for INSERT, UPDATE, DELETE and SELECT for UPDATE statements.
Consistent gets – The number of blocks accessed in the buffer cache for queries without the SELECT FOR UPDATE clause. The value for this statistic plus the value of the “db block gets” statistic constitute what is referred to as a logical read.
Physical Reads – the number of data blocks that were read from disks to satisfy a SELECT, SELECT FOR UPDATE, INSERT, UPDATE or DELETE.
Data Cache Hit Ratio
Hit Ratio = (Logical Reads – Physical Reads) / Logical Reads
Redo Size – the size in bytes of the amount of redo information that was written to the redo logs. This information can be used to help size the redo logs and the LOG_SMALL_ENTRY_MAX_SIZE parameter in the init.ora.
Sorts(memory) – the number of sorts that were performed in memory
Sorts(disk) – the number of sorts that were unable to be performed in memory and therefore required the creation of a temp segment in the temporary tablespace. This statistic divided by the sorts(memory) should not be above the 5 percent. If it is, you should increase the SORT_AREA_SIZE parameter in the init.ora.
有时候我们会看到一些看上去很奇怪的问题，比如，有时候，我们的Autotrace，会显示，Select语句也会有Redo 产生。
这里可能有两种情况：
1。Delayed Block Cleanout：
比如下面这个例子：
SQL>DELETE FROM T WHERE ROWNUMDELETE FROM T WHERE ROWNUMDELETE FROM T WHERE ROWNUMDELETE FROM T WHERE ROWNUM select count(*) from t;
COUNT(*)
----------
25606
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T'
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
326 consistent gets
0 physical reads
360 redo size
369 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed第二种情况是：
用户用来排序的临时表空间不是真正的临时表空间：
例子：
SQL> L
1* ALTER USER TEST TEMPORARY TABLESPACE SYSTEM
SQL> /
User altered.
SQL> SELECT * FROM T ORDER BY 1,2,3,4,5,6;
25606 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (FULL) OF 'T'
Statistics
----------------------------------------------------------
1382 recursive calls
286 db block gets
740 consistent gets
809 physical reads
28264 redo size
1239304 bytes sent via SQL*Net to client
189903 bytes received via SQL*Net from client
1709 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
25606 rows processed
我们把用户的临时表空间重新改成Locally MANAGED TEMPORARY TABLESPACE:
SQL> ALTER USER TEST TEMPORARY TABLESPACE TEMP;
User altered.
SQL> CONN TEST/TEST
Connected.
SQL> SET AUTOTRACE TRACEONLY;
SQL> SELECT * FROM T ORDER BY 1,2,3,4,5,6;
25606 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (FULL) OF 'T'
Statistics
----------------------------------------------------------
0 recursive calls
53 db block gets
320 consistent gets
808 physical reads
0 redo size
1239304 bytes sent via SQL*Net to client
189903 bytes received via SQL*Net from client
1709 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
25606 rows processed
还有一个挺难理解的现象：
SQL> conn internal
Connected.
SQL> set autotrace traceonly;
SQL> select * from test.t ORDER BY 1,2,3,4,5,6;
25606 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (FULL) OF 'T'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25606 rows processed
同样的语句，没有任何Trace的值。
这里，是因为我用Internal用户的连接，或者说，SYSDBA权限的连接。
用这个用户连接，没有Trace的结果的。
这一点，特别感谢Oldwain老哥，上面有:
DB Block gets – The number of blocks in the buffer cache that were accessed for INSERT, UPDATE, DELETE and SELECT for UPDATE statements.
为什么 普通的select 语句也有 db block gets??
如:
SQL> select empno,deptno from hashemp where deptno=10;
EMPNO DEPTNO
---------- ----------
7782 10
7839 10
7934 10
Elapsed: 00:00:02.36
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (HASH) OF 'HASHEMP'
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
2 consistent gets
0 physical reads
0 redo size
522 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
我这个我就直接应用Tom的原话了：
recursive calls = basically sql performed on behalf of your sql. So, if we had
to PARSE the query for example, we might have had to run some other queries to
get data dictionary info. that would be recursive calls.
db block gets = blocks gotten in "current" mode. That is, blocks gotten as they
exist right now. You'll see these for full table scans (segment headers are
read in current mode) and modification statements (we modify the block as it
exists "right now"
consistent gets = blocks gotten in consistent read mode. This is the mode we
read blocks in with a select for example. Also, when you do a searched
UPDATE/DELETE, we read the blocks in consistent read mode and then get the block
in current mode to actually do the modification. A select for update will do
this as well.
physical reads = self explanatory, physical IO
redo size = self explanatory -- amount of redo generated
sorts (memory)/(disk) -- sorts done.
当时也是想了很久也没有想出来。
里面居然发现了Oldwain老大,呵呵
|
