V8 Bundled Exec call – and Oracle Program Interface (OPI) calls
Tanel Poder
2011-08-23
原文链接：https://tanelpoder.com/2011/08/23/v8-bundled-exec-call-and-oracle-program-interface-opi-calls/So, what he hell is that
V8 Bundled Exec call which shows up in various Oracle 11g monitoring reports?!It’s yet another piece of instrumentation which can be useful for diagnosing non-trivial performance problems. Oracle ASH has allowed us to measure what is the top wait event or top SQLID for a long time, but now it’s also possible to take a step back and see what
type of operation the database session is servicing. I am talking about Oracle Program Interface (OPI) calls. Basically for each OCI call in the client side (like , OCIStmtExecute, OCIStmtFetch, etc) there’s a corresponding server side OPI function (like opiexe(), opifch2() etc). It has been possible to trace all the OPI calls with event 10051 as I’ve explained
here, but since Oracle 11g this data is also conveniently accessible from ASH views (the various monitoring reports, including SQL Monitoring report also use ASH data for some features).So, I can write a simple query against ASH, which doesn’t group the samples by wait event or SQL_ID, but just by the general OPI call type (TOP_LEVEL_CALL_NAME column) and also by the SQL command type (using V$SQLCOMMAND in 11.2):
SQL> SELECT
2 a.top_level_call#
3 , a.top_level_call_name
4 , a.top_level_sql_opcode
5 , s.command_name
6 , COUNT(*)
7 FROM
8 v$active_session_history a
9 , v$sqlcommand s
10 WHERE
11 a.top_level_sql_opcode = s.command_type
12 GROUP BY
13 a.top_level_call#
14 , a.top_level_call_name
15 , a.top_level_sql_opcode
16 , s.command_name
17 ORDER BY
18* COUNT(*) DESC
19 /
TOP_LEVEL_CALL# TOP_LEVEL_CALL_NAME top_op# COMMAND_NAME COUNT(*)
--------------- -------------------- ---------- ------------------------------ ----------
94 V8 Bundled Exec 7 DELETE 10505
0 0 4041
59 VERSION2 0 579
59 VERSION2 47 PL/SQL EXECUTE 377
59 VERSION2 3 SELECT 191
96 LOB/FILE operations 170 CALL METHOD 67
59 VERSION2 170 CALL METHOD 66
94 V8 Bundled Exec 6 UPDATE 52
59 VERSION2 6 UPDATE 41
59 VERSION2 7 DELETE 36
94 V8 Bundled Exec 3 SELECT 24
96 LOB/FILE operations 47 PL/SQL EXECUTE 18
59 VERSION2 2 INSERT 8
94 V8 Bundled Exec 1 CREATE TABLE 5
0 3 SELECT 3
59 VERSION2 15 ALTER TABLE 1
96 LOB/FILE operations 0 1
59 VERSION2 12 DROP TABLE 1
5 FETCH 3 SELECT 1
94 V8 Bundled Exec 12 DROP TABLE 1
20 rows selected.
Aas you see above, most of the ASH samples in my test database have been created by a DELETE type SQL statement, executed via
V8 Bundled Exec type of an OPI call.So, what is this call about? Let’s explain its name. Look into other call types in the above output. In the bottom you see a FETCH call (fetching from a SELECT type statement). Also there are a few LOB/FILE operations calls, which are used exclusively for accessing LOB data (via LOB locator, bypassing the usual SQL processing layer).In Oracle 7 you would also see PARSE and EXECUTE calls, but starting from Oracle 8 not anymore. This is because starting from Oracle 8, the OPI layer in database side can accept bundled OCI calls from the client – to reduce the number of network roundtrips. So, basically instead of sending the PARSE and EXEC requests in separate SQL_Net roundtrips (increasing the latency), the OCI client libraries can bundle these requests together and send them to database in one SQL_Net payload. The database server side understands it and is able to extract these separate OPI requests from the bundled packet (in right order) and execute the corresponding OPI function for each separate call. Note that this is why you frequently see the
kpoal8() function close to the beginning in Oracle server process stack traces (and where I usually start reading them from), this is the function which processes all the OPI requests sent to it in a bundled package. So, whenever there’s a OCIStmtExecute() call extracted from the bundle, the
opiexe() function is called in Oracle kernel with appropriate arguments extracted from the same bundle. Whenever we extract an OCIStmtFetch2() call from the bundle, the corresponding
opifch2() function is called in the kernel.Hopefully this explains why is there such a call “V8 Bundled Exec” in Oracle. It just allows to reduce client – server communication latency by allowing to bundle multiple database requests together into a single SQL*Net payload. In other words, it’s just how Oracle works and it’s
perfectly normal to see V8 Bundled Exec as the top OPI call type in performnace reports. If you see this OPI call as the top one, then you’d need to drill down into what’s the actual SQLID which consumes the most of the response time (and further breakdown like which wait event and execution plan step takes the most time).But the ability of breaking down database response time by OPI call becomes much more useful when troubleshooting somewhat more exotic performance problems like LOB access times (where there’s no SQL statement associated with the database call) or other direct OPI calls which are executed without parsing and running a SQL cursor.For example, have you noticed that the behavior of ROLLBACK command in sqlplus is different from the shorter ROLL command?When you issue a ROLLBACK, then Oracle will actually send the string “ROLLBACK” to the database as a regular SQL statement (using V8 Bundled Exec), it will be parsed there (with all the latching and shared pool overhead) as regular SQL – and then Oracle realizes that the command in it is a rollback. Then rollback is performed.But if you issue a ROLL command, then sqlplus understands it and doesn’t send it to the database for parsing like a regular SQL statement. Instead it will send an OCITransRollback() call, which will call the corresponding OPI function directly, bypassing the SQL processing layer completely. Instead of the usual “V8 Bundled Exec” bundle call you would see a “Transaction Commit/Rollback” OPI call as it was called directly, without any SQL statement processing involved. This is why you sometimes see
WAIT#0 lines in SQL_Trace, where the waits seem to be associated with some non-existent cursor #0. Whenever the wait happens when the database session is servicing an OPI call which bypasses the SQL processing layer (kks/kkx modules) then the SQL_Trace just shows cursor#0 as the wait’s “owner”.The same happens when using things like
connection.commit() in JDBC, the client does not send a SQL statement with text “commit” into the datbase, but rather will call out the OPI commit function out directly.So, how many different OPI calls are there? Well, a lot, as you can see from
v$toplevelcall (or its underlying
x$orafn) in Oracle 11.2:
SQL> SELECT * FROM v$toplevelcall;
TOP_LEVEL_CALL# TOP_LEVEL_CALL_NAME
--------------- ----------------------------------------------------------------
0
2 OPEN
3 PARSE
4 EXECUTE
5 FETCH
8 CLOSE
9 LOGOFF
10 DESCRIBE
11 DEFINE
12 COMMIT ON
13 COMMIT OFF
14 COMMIT
15 ROLLBACK
16 SET OPTIONS
17 RESUME
18 VERSION
20 CANCEL
21 GET ERR MSG
23 SPECIAL
24 ABORT
25 DEQ ROW
26 FETCH LONG
31 HOW MANY
32 INIT
33 CHANGE USER
34 BIND REF POS
35 GET BIND VAR
36 GET INTO VAR
37 BINDV REF
38 BINDN REF
39 PARSE EXE
40 PARSE SYNTAX
41 PARSE SYNSDI
42 CONTINUE
43 ARRAY DESC
44 INIT PARS
45 FIN PARS
46 PUT PAR
48 START ORACLE
49 STOP ORACLE
50 RUN IND PROC
52 ARCHIVE OP
53 MED REC STRT
54 MED REC TABS
55 MED REC GETS
56 MED REC RECL
57 MED REC CANC
58 LOGON
59 VERSION2
60 INIT
62 EVERYTHING
65 DIRECT LOAD
66 UL BUFFER XMIT
67 DISTRIB XACTION
68 DESCRIBE INDEXES
69 SESSION OPS
70 EXEC w/SCN
71 FAST UPI
72 FETCH LONG
74 V7 PARSE
76 PL/SQL RPC
78 EXEC & FCH
79 XA OPS
80 KGL OP
81 LOGON
82 LOGON
83 Streaming op
84 SES OPS (71)
85 XA OPS (71)
86 DEBUG
87 DEBUGS
88 XA XA Start
89 XA XA Commit
90 XA XA Prepare
91 x/import
92 KOD OP
93 RPI Callback with ctxdef
94 V8 Bundled Exec
95 Streaming op
96 LOB/FILE operations
97 FILE Create
98 V8 Describe Query
99 Connect
100 OPEN Recursive
101 Bundled KPR
102 Bundled PL/SQL
103 Transaction Start/End
104 Transaction Commit/Rollback
105 Cursor close all
106 Failover session info
107 SES OPS (80)
108 Do Dummy Defines
109 INIT V8 PARS
110 FIN V8 PARS
111 PUT V8 PAR
112 TERM V8 PARS
114 INIT UNTR CB
115 OAUTH
116 Failover get info
117 Commit Remote Sites
118 OSESSKEY
119 V8 Describe Any
120 Cancel All
121 Enqueue
122 Dequeue pre 8.1
123 Object Transfer
124 RFS op
125 Notification
126 Listen
127 Commit Remote Sites >= V813
128 DirPathPrepare
129 DirPathLoadStream
130 DirPathMiscOps
131 MEMORY STATS
132 AQ Prop Status
134 remote Fetch Archive Log (FAL)
135 Client ID propagation
136 DR Server CNX Process
138 SPFILE parameter put
139 KPFC exchange
140 V82 Object Transfer
141 Push transaction
142 Pop transaction
143 KFN Operation
144 DirPathUnloadStream
145 AQ batch enqueue/dequeue
146 File transfer
147 PING
148 TSM
150 Begin TSM
151 End TSM
152 Set schema
153 Fetch from suspended result-set
154 Key value pair
155 XS Create Session Op
156 XS Session RoundtripOp
157 XS Piggyback Oper.
158 KSRPC Execution
159 Streams combined capture/apply
160 AQ replay information
161 SSCR
162 OSESSGET
163 OSESSRLS
165 workload replay data
166 replay statistic data
167 Query Cache Stats
168 Query Cache IDs
169 RPC Test Stream
170 replay plsql rpc
171 XStream Out
172 Golden Gate RPC
151 rows selected.
A lot of calls … special stuff (like DESCRIBE) which will bypass the SQL layer completely (but may in turn invoke further recursive SQL statements through the Recursive Program Interface – RPI).Ok, time to stop – if you want to learn more, enable SQL trace with waits & binds and event 10051 at level 1 in your test database and try to describe a table or read some LOB columns for example!
