Shulou(Shulou.com)06/01 Report--

10046 you can see the execution of the statement, including various steps, how they are associated, how much time is spent in each step, what waiting events are available, and so on.

1. Database call

There are three steps: parsing, executing, and getting

Parsing:

PARSING IN CURSOR # 140186891763128 len=84 dep=0 uid=0 oct=3 lid=0 tim=1542789588408762 hv=2629212467 ad='9b65c8d8' sqlid='d2knts2fbd69m'

Select count (1)

From dba_objects a,scott.tb_test2 b

Where a.object_id = b.object_id

END OF STMT

PARSE # 140186891763128Fluence 14309 remanent 14309 reigneur pantomime 12 magistrate cuttle0memoir 1memorialronomic0depended 0memorialogogtalityplhobie2135238665Time 1542789588408759

PARSING IN CURSOR parameters:

Len:SQL statement length

Dep: recursive call depth

Uid: parses the user identifier, corresponding to dba_users.user_id and v$sql.parsing_user_id

Oct:Oracle command type. Corresponding to v$sql.command_type, v$session.command. Create table:1 delete:7 select:3 insert:2 update:6 truncate:85 drop table:12

Lid: parse schema identifier, corresponding to dba_users.user_id and v$sql.parsing_schema_id

Tim:Timestamp. The value in v$timer when the current line is written to the file

Hv:sql hash value, corresponding to v$sql.hash_value

Ad:sql address, corresponding to v$sql.address

Sqlid:sql id, corresponding to v$sql.sql_id

PARSE parameters:

C:CPU overhead. Seconds before 9i, microseconds after 9i

E: run time. Seconds before 9i, microseconds after 9i

P: number of physical reads

Cr: data blocks that are logically read

Data blocks read by cu:current mode

Mis:1: hard parsing 0: soft parsing

R: number of rows processed

Dep: recursive call depth

Og: optimizer mode: 1=All_Rows, 2=First_Rows, 3=Rule, 4=Choose

Tim: timestamp

Plh:plan hash value, the hash value of the execution plan, corresponding to v$sql_plan.plan_hash_value, v$sql_plan_statistics_all.plan_hash_value, v$sqlstats.plan_hash_value

When parsing error:

PARSE ERROR # 140092551821528:len=30 dep=0 uid=0 oct=3 lid=0 tim=1542780378216064 err=942

Select count (1) from scott.tb

PARSE ERROR parameters:

Len: length of SQL statement

Dep: recursive call depth

Uid: parses the user identifier, corresponding to dba_users.user_id and v$sql.parsing_user_id

Oct: Oracle command type. Corresponding to v$sql.command_type and v$session.command

Lid: parse schema identifier, corresponding to dba_users.user_id and v$sql.parsing_schema_id

Tim:Timestamp.

Err:Oracle error code (e.g. ORA-XXXXX) reported

Execute and obtain:

EXEC # [CURSOR]: cymbidium0memorie0Difference0reach0reach0reach0memoriaCub0reach0memoriality0way0wish0way0wish0way0weredepicting0wereoglemagectionsplhoria2295886755thumbnails 0

FETCH # [CURSOR]: cymbidium0memorie0Difference0reach0reach0reach0memoriaCub0reach0memoriality0way0wish0way0wish0way0weredepicting0wereoglemagectionsplhoria2295886755thumbnails 0

Parameters are the same as PARSE parameters

two。 Transaction entry

XCTEND rlbk=0, rd_only=0, tim=1542780757243972

XCTEND parameters:

Rlbk:1:rollback, 0:commit.

Rd_only: 1: read-only transaction, 0: data has been modified in the transaction.

Tim: timestamp

3.STAT entry

STAT # [CURSOR] id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=6 pr=0 pw=0 time=483 us)'

STAT # [CURSOR] id=2 cnt=14 pid=1 pos=1 obj=75510 op='TABLE ACCESS FULL EMP (cr=6 pr=0 pw=0 time=432 us cost=2 size=0 card=14)'

STAT parameters:

Id: the identifier in the execution plan that indicates the order of the row data sources. Usually the id=1 of the first STAT of an execution plan

Cnt: the number of rows returned by the current row source

Pid: id of the parent node of the current row source

Pos:Position in explain plan.

Obj: the object id of the row source, corresponding to dba_objects.object_id and v$sql_plan.object#

Op: operations performed, such as table access, index scan, sort, union, etc., corresponding to v$sql_plan.operation

STAT execution statistics parameters:

Cr:Consistent reads. Logical reading

Pr:Physical reads. Physical reading

Pw:Physical writes. Physical writing

Time: elapsed time, microseconds

Estimated cost of cost:CBO

Size: the estimated amount of data in bytes. Object-based statistics

Card: estimated cardinality, object-based statistics

4.WAIT entry

WAIT # 140092549877616: nam='Disk file operations I ela= 40 FileOperation=2 fileno=1 filetype=2 obj#=106 tim=1542780935083885

WAIT # 140092549877616: nam='db file sequential read' ela= 8945 file#=1 block#=69315 blocks=1 obj#=106 tim=1542780935092868

WAIT # 140092549877616: nam='db file sequential read' ela= 8859 file#=1 block#=69378 blocks=1 obj#=104 tim=1542780935101864

WAIT parameters:

Nam: wait event name

Ela: run time, microsecond

P1: the first parameter of the waiting event

P2: the second parameter of the waiting event

P3: the third parameter of the waiting event

Obj#: object number, that is, dba_object.object_id

Tim: timestamp

5. Bind variable entry

BINDS # 140092551821528:

Bind#0

Oacdty=02 mxl=22 (22) mxlc=00 mal=00 scl=00 pre=00

Oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0

Kxsbbbfp=7f69d6c94c20 bln=22 avl=02 flg=05

Value=20

BINDS parameters:

Bind#N: where the binding variable is used

Oacdty: bind variable data type

Mxl: the maximum length of the bound variable value

Mxlc: unknown

Mal: array length

Scl: proportion.

Pre: precision.

Oacflg: a special flag indicating binding options

The second part of fl2:oacflg

Frm: unknown

Csi: the character set identifier of the database or international character set

Siz: the total amount of memory allocated to the chunk

Off: the offset of the binding buffer in chunk

Kxsbbbfp: bind address

Bln: bind buffer length

Avl: the length of the actual value

Flg: bind variable statu

Value: bind variable value

Data type code:

1 varchar2/nvarchar2

2 number

12 date

96 char/nchar

112 clob/nclob

113 blob

180 timestamp

Common character sets and identifiers:

1 US7ASCII

31 WE8ISO8859P1

46 WE8ISO8859P15

170 EE8MSWIN1250

178 WE8MSWIN1252

871 UTF8

873 AL32UTF8

2000 AL16UTF16

6. Release cursor

CLOSE # 140092551821528 Vorcantlas 1542780378208132

Parameters:

C:CPU overhead. Seconds before 9i, microseconds after 9i

E: run time. Seconds before 9i, microseconds after 9i

Dep: cursor recursion depth

Type:type of close operation

Tim:Timestamp timestamp

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.