|
马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
因此我们的方案中要构造这种逆操作。Event_type增加一种FlashBACK_EVENT。这类操作形式与Query_Event相同,都是简单的SQL语句,只是包含了将数据恢复的操作。
在SQL*PLUS中使用AUTOTRACEREPORT
刘颖博
工夫:2004-1-12
mail:liuyingbo@126.com,请斧正
转载请说明出处及作者
在SQL*PLUS中,当你乐成的实行一个DML语句,好比SELECT,DELETE,UPDATE,INSERT,你能够经由过程SQL优化器和语句的实行统计主动的取得一份呈报。这份呈报关于DML语句的功能监控和调优都是很有效处的。这份呈报就是本文要讲的AUTOTRACE呈报。
设置AUTOTRACE呈报(ConfiguringtheAUTOTRACEReport)
你能够经由过程以下的AUTOTRACE体系变量来设置AUTOTRACE呈报.以下表:
TableAUTOTRACESettings
AUTOTRACESetting
Result
SETAUTOTRACEOFF
不克不及取得AUTOTRACE呈报.这是默许的.
SETAUTOTRACEONEXPLAIN
仅仅显现优化器实行企图的AUTOTRACE呈报
SETAUTOTRACEONSTATISTICS
仅仅显现SQL语句实行的统计了局的AUTOTRACE呈报
SETAUTOTRACEON
包含下面两项内容的AUTOTRACE呈报
SETAUTOTRACETRACEONLY
与SETAUTOTRACEON相似,一切的统计和数据都在,但不成以打印
实在,平常我们应当较多的就是SETAUTOTRACEON、SETAUTOTRACEOFF,谁会在意多看一点AUTOTRACE呈报呢!J
安装AUTOTRACE呈报(SetupsRequiredfortheAUTOTRACEReport)
要用这个特征,用户必需被付与PLUSTRACE脚色,而PLUSTRACE脚色必要DBA来付与。别的,该用户必需创立PLAN_TABLE表。
I.起首创立PLUSTRACE脚色而且赋给DBA:
CONNECTsys/sys’spasswordASSYSDBA
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
II.赋权限给用户
CONNECT/ASSYSDBA
GRANTPLUSTRACETOUSER(预赋权的用户名);
如许,就能够在该用户下设置AUTOTRACE呈报的显现与否了。
AUTOTRACE呈报中触及到的两个方面的内容
I.SQL语句的实行企图
实行企图就是SQL优化器实行语句的查询实行按次,每行的实行企图城市有个行号,这个行号是一连的
II.SQL语句的数据库统计
数据库统计是服务器纪录的实行以后的这条语句所必要的体系资本等,次要包含下表的内容
DatabaseStatisticName
Description
recursivecalls
Numberofrecursivecallsgeneratedatboththeuserandsystemlevel.Oraclemaintainstablesusedforinternalprocessing.WhenOracleneedstomakeachangetothesetables,itinternallygeneratesaninternalSQLstatement,whichinturngeneratesarecursivecall.
dbblockgets
NumberoftimesaCURRENTblockwasrequested.
consistentgets
Numberoftimesaconsistentreadwasrequestedforablock.
physicalreads
Totalnumberofdatablocksreadfromdisk.Thisnumberequalsthevalueof"physicalreadsdirect"plusallreadsintobuffercache.
redosize
Totalamountofredogeneratedinbytes.
bytessentviaSQL*Nettoclient
Totalnumberofbytessenttotheclientfromtheforegroundprocesses.
bytesreceivedviaSQL*Netfromclient
TotalnumberofbytesreceivedfromtheclientoverOracleNet.
SQL*Netroundtripsto/fromclient
TotalnumberofOracleNetmessagessenttoandreceivedfromtheclient.
sorts(memory)
Numberofsortoperationsthatwereperformedcompletelyinmemoryanddidnotrequireanydiskwrites.
sorts(disk)
Numberofsortoperationsthatrequiredatleastonediskwrite.
rowsprocessed
Numberofrowsprocessedduringtheoperation.
复杂利用的例子:
SQL>showuser
USERis"SYS"
(注:以后是sysdba用户)
(注:创立PLUSTRACE脚色而且赋给DBA)
SQL>start$ORACLE_HOME/sqlplus/admin/plustrce.sql
(注:start等价于@)
SQL>droproleplustrace;
droproleplustrace
*
ERRORatline1:
ORA-01919:rolePLUSTRACEdoesnotexist
SQL>createroleplustrace;
Rolecreated.
SQL>
SQL>grantselectonv_$sesstattoplustrace;
Grantsucceeded.
SQL>grantselectonv_$statnametoplustrace;
Grantsucceeded.
SQL>
SQL>grantselectonv_$sessiontoplustrace;
Grantsucceeded.
SQL>grantplustracetodbawithadminoption;
Grantsucceeded.
SQL>
SQL>setechooff
(注:赋脚色plustrace给所需用户,这里用户是bill)
SQL>grantplustracetobill;
Grantsucceeded.
(注:接上去,用bill用户毗连,测试AUTOTRACE)
SQL>connectbill/bill’spassword;
Connected.
SQL>showuser
USERis"BILL"
(注:为了看的分明些,创建一个测试的表test,然后观察AUTOTRACE呈报)
SQL>createtabletest(idnumber(1));
Tablecreated.
SQL>insertintotestvalues(1);
1rowcreated.
SQL>/
1rowcreated.
SQL>/
1rowcreated.
SQL>/
1rowcreated.
SQL>commit;
Commitcomplete.
SQL>select*fromtest;
ID
----------
1
1
1
1
SQL>setAUTOTRACEon
SQL>select*fromtest;
ID
----------
1
1
1
1
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE
10TABLEACCESS(FULL)OFTEST
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
4consistentgets
0physicalreads
0redosize
547bytessentviaSQL*Nettoclient
655bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
4rowsprocessed
下面我们就能够看到,当你设置了setAUTOTRACEon后,实行响应的DML语句,就会有响应的AUTOTRACE呈报呈现了。固然,我们的这类设置是session级其余。如许,我们就能够依据AUTOTRACE呈报对我们实行的DML语句举行剖析和调优了!
参考:
oracle的官方文档(otn.oracle.com)
对于update操作,只需要把event中的旧行和新行值对调即可。 |
|