实验:
潜在的数据类型转换 不会使用索引
工具1 dbms_system.set_sql_trace_in_session ;
工具2 autotrace
窗口一
conn shihf/oracle
SQL> select sid from v$mystat where rownum=1;
SID
----------
380
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
NO VARCHAR2(10)
NAME VARCHAR2(10)
SQL> select * from t1;
NO NAME
---------- ----------
1 a
2 b
3 c
4 d
5 d
6 d
7 d
8 d
9 d
10 d
10 rows selected.
SQL> select index_name,column_name,table_name from user_ind_columns where lower(table_name)='t1';
INDEX_NAME COLUMN_NAME TABLE_NAME
------------------------------ -------------------- ------------------------------
T1_IND_NO NO T1
窗口2
conn / as sysdba
SQL> select sid,serial#,username from v$session where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
380 7 SHIHF
381 8 SYS
sql> exec dbms_system.set_sql_trace_in_session (380,7,true);
窗口1
sql>show users
shihf
sql>select * from t1 where no=1;
窗口2
conn / as sysdba
sql> exec dbms_system.set_sql_trace_in_session (380,7,false);
此时在 trace 生成几个默认trc 文件,这个文件名为 'sid'_ora_'spid'.trc,sid 为实例名,spid 为v$process一列 ,v$process.addr 与v$session.paddr 相关联
SQL> select spid from v$process,v$session where v$process.addr=v$session.paddr and v$session.sid=380;
SPID
------------------------
6715
因此trace 文件为 shihf_ora_6715.trc tkprof shihf_ora_6715.trc 1.trc more 1.trc
####################################
select *
from
t1 where no=1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 8 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 16 0 &nbqp; 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL T1 (cr=8 pr=0 pw=0 time=0 us cost=3 size=14 card=1)
######################################## 没有走索引
窗口1
SQL> set autotrace on;
SQL> select * from t1 where no=1;
NO NAME
---------- ----------
1 a
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 14 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("NO")=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
587 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from t1 where no='1';
NO NAME
---------- ----------
1 a
Execution Plan
----------------------------------------------------------
Plan hash value: 805714942
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 14 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_IND_NO | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NO"='1')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
591 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off;
比较逻辑读的数量,执行计划。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26179376/viewspace-1981681/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26179376/viewspace-1981681/