------------savepoint 练习------------
CREATE TABLE TEST.TEST
(ID VARCHAR2(2),
NAME VARCHAR2(5));
insert into test values('1','dd');
update test set name='qq' where id='1';
savepoint save_1;
update test set name='aa' where id='1';
savepoint save_2;
update test set name='ww' where id='1';
savepoint save_3;
rollback to savepoint save_2;
update test set name='vv' where id='1';
savepoint save_4;
rollback to save_4;
commit;
SQL> select * from test;
ID NAME
-- -----
1 vv
总结：savepoint 会保留当前的值。例如rollback to save_4;会保留save_4的值。
------------lock 练习------------
lock table test.test in exclusive mode;
select * from v$locked_object; 查询lock的对象
commit; 可以解锁
------------tkprof 练习：tkprof 可以让trc文件的内容变得可读------------
tkprof可对.trc文件进行分析,并生成一个分析文件.并且在分析.trc文件,生成分析文件时按各种选项
进行排序,其中比较有用的一个排序选项是fchela，即按照elapsed time fetching来对分析的结果排序
（记住要设置初始化参数time_statistics=true），生成的.prf文件将把最消耗时间的sql放在最前面显示。
另外一个有用的参数就是sys，这个参数设置为no可以阻止所有以sys用户执行的sql被显示出来，这样可以
减少分析出来的文件的复杂度，便于查看。
insert into test values('2','dd');
insert into test values('3','dd');
insert into test values('4','dd');
commit；
有两种方法开启sql_trace
先确认timed_statistics=true;必须为true 才能设置sql_trace.
1、ALTER SESSION SET SQL_TRACE=TRUE; 这种方法会将设置为TRUE -- FALSE 之间的sql都跟踪记录
SQL> ALTER SESSION SET SQL_TRACE=TRUE;
Session altered.
SQL> select * from test;
ID NAME
-- -----
1 ww
2 dd
3 dd
4 dd
SQL> alter session set sql_trace=false;
Session altered.
找到刚才产生的trc文件
[oracle@localhost udump]$ tkprof catlogdb_ora_4411.trc
output = test.txt
TKPROF: Release 10.2.0.1.0 - Production on Tue Jul 29 19:23:27 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
执行后就会在当前目录下产生test.txt文件。
[oracle@localhost udump]$ more test.txt
TKPROF: Release 10.2.0.1.0 - Production on Tue Jul 29 19:23:27 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: catlogdb_ora_4411.trc
Sort options: default
****************************************************************************
****
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
****************************************************************************
****
ALTER SESSION SET SQL_TRACE=TRUE
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
Parse 0 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
total 1 0.00 0.00 0 0 0
0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57
****************************************************************************
****
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
NVL(SUM(C2),:"SYS_B_1")
FROM
(SELECT /*+ NO_PARALLEL("TEST") FULL("TEST") NO_PARALLEL_INDEX("TEST") */
:"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "TEST" "TEST") SAMPLESUB
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 1 0.00 0.00 0 7 0
1
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
total 3 0.00 0.00 0 7 0
1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us)
4 TABLE ACCESS FULL TEST (cr=7 pr=0 pw=0 time=0 us)
****************************************************************************
****
select *
from
test
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
Parse 1 0.00 0.00 0 1 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 0.00 0.00 0 8 0
4
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
total 4 0.00 0.00 0 9 0
4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57
Rows Row Source Operation
------- ---------------------------------------------------
4 TABLE ACCESS FULL TEST (cr=8 pr=0 pw=0 time=0 us)
****************************************************************************
****
alter session set sql_trace=false
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
total 2 0.00 0.00 0 0 0
0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57
****************************************************************************
****
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
Parse 2 0.00 0.00 0 1 0
0
Execute 3 0.00 0.00 0 0 0
0
Fetch 2 0.00 0.00 0 8 0
4
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
total 7 0.00 0.00 0 9 0
4
Misses in library cache during parse: 2
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 1 0.00 0.00 0 7 0
1
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
total 3 0.00 0.00 0 7 0
1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
4 user SQL statements in session.
0 internal SQL statements in session.
4 SQL statements in session.
****************************************************************************
****
Trace file: catlogdb_ora_4411.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
4 user SQL statements in trace file.
0 internal SQL statements in trace file.
4 SQL statements in trace file.
4 unique SQL statements in trace file.
48 lines in trace file.
1 elapsed seconds in trace file.
2、对单个session进行跟踪
SQL> select sid, USERNAME,sql_trace from v$session where username='TEST';
SID USERNAME SQL_TRAC
---------- ------------------------------ --------
148 TEST DISABLED
SQL> SELECT SID,SERIAL#,USERNAME FROM V$SESSION WHERE USERNAME='TEST';
SID SERIAL# USERNAME
---------- ---------- ------------------------------
148 27 TEST
使用sysdba 登录执行 开启对test用户的跟踪
SQL> exec dbms_system.set_sql_trace_in_session(148,27,true)
PL/SQL procedure successfully completed.
使用test用户执行需要跟踪的语句
SQL> select * from test;
ID NAME
-- -----
1 ww
2 dd
3 dd
4 dd
使用sysdba 登录执行 关闭对test用户的跟踪
SQL> exec dbms_system.set_sql_trace_in_session(148,27,false)
PL/SQL procedure successfully completed.
使用tkprof 分析该trace文件
tkprof catlogdb_ora_4436.trc sys=no sort=fchela
[oracle@localhost udump]$ tkprof catlogdb_ora_4436.trc sys=no sort=fchela
output = test.txt
TKPROF: Release 10.2.0.1.0 - Production on Tue Jul 29 19:24:58 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
附录：tkprof 使用参数
tkprof inputfile outputfile [optional | parameters ]
参数和选项：
explain=user/password 执行explain命令将结果放在SQL trace的输出文件中
table=schema.table 指定tkprof处理sql trace文件时临时表的模式名和表名
insert=scriptfile 创建一个文件名为scriptfile的文件，包含了tkprof存放的输出sql语句
sys=[yes/no] 确定系统是否列出由sys用户产生或重调的sql语句
print=number 将仅生成排序后的第一条sql语句的输出结果
record=recordfile 这个选项创建一个名为recorderfile的文件，包含了所有重调用的sql语句
sort=sort_option 按照指定的方法对sql trace的输出文件进行降序排序
sort_option 选项
prscnt 按解析次数排序
prscpu 按解析所花cpu时间排序
prsela 按解析所经历的时间排序
prsdsk 按解析时物理的读操作的次数排序
prsqry 按解析时以一致模式读取数据块的次数排序
prscu 按解析时以当前读取数据块的次数进行排序
execnt 按执行次数排序
execpu 按执行时花的cpu时间排序
exeela 按执行所经历的时间排序
exedsk 按执行时物理读操作的次数排序
exeqry 按执行时以一致模式读取数据块的次数排序
execu 按执行时以当前模式读取数据块的次数排序
exerow 按执行时处理的记录的次数进行排序
exemis 按执行时库缓冲区的错误排序
fchcnt 按返回数据的次数进行排序
fchcpu 按返回数据cpu所花时间排序
fchela 按返回数据所经历的时间排序
fchdsk 按返回数据时的物理读操作的次数排序
fchqry 按返回数据时一致模式读取数据块的次数排序
fchcu 按返回数据时当前模式读取数据块的次数排序
fchrow 按返回数据时处理的数据数量排序
sql trace 的输出结果
count：提供OCI过程的执行次数
CPU： 提供执行CPU所花的时间单位是秒
Elapsed：提供了执行时所花的时间。单位是秒。这个参数值等于用户响应时间
Disk：提供缓存区从磁盘读取的次数
Query：以一致性模式从缓存区获得数据的次数
Current：以当前模式从缓存区获得数据的次数
ROWs： 返回调用或执行调用时，处理的数据行的数量。
