When running SAP on top of SAP ASE database, you can use the DBACOCKPIT transaction to help produce an EXPLAIN PLAN (execution plan).
It’s also possible to use the “EXPLAIN” button from an SQL trace (from ST05 or ST12).
However, under certain circumstances it may not be possible (or it just may not function – it’s Java based) and you may want to use command line iSQL to generate the EXPLAIN PLAN output at the database level directly.
Here’s how.
From the SQL trace (ST05 or ST01) in the SAP GUI, you can double click to obtain the SQL text, which will usually be a prepared statement (contain ‘?’ in place of actual data).
It may look like the following (sorry for the large statement):
SELECT
“MANDT” ,”PERNR” ,”SUBTY” ,”OBJPS” ,”SPRPS” ,”ENDDA” ,”BEGDA” ,”SEQNR” , “AEDTM” ,”UNAME” ,”HISTO” ,”ITXEX” ,”REFEX” ,”ORDEX” ,”ITBLD” ,”PREAS” , “FLAG1″ ,”FLAG2″ ,”FLAG3″ ,”FLAG4″ ,”RESE1″ ,”RESE2″ ,”GRPVL” ,”ANSSA” , “NAME2″ ,”STRAS” ,”ORT01″ ,”ORT02″ ,”PSTLZ” ,”LAND1″ ,”TELNR” ,”ENTKM” , “WKWNG” ,”BUSRT” ,”LOCAT” ,”ADR03″ ,”ADR04″ ,”STATE” ,”HSNMR” ,”POSTA” , “BLDNG” ,”FLOOR” ,”STRDS” ,”ENTK2″ ,”COM01″ ,”NUM01″ ,”COM02″ ,”NUM02″ , “COM03″ ,”NUM03″ ,”COM04″ ,”NUM04″ ,”COM05″ ,”NUM05″ ,”COM06″ ,”NUM06” , “INDRL” ,”COUNC” ,”RCTVC” ,”OR2KK” ,”CONKK” ,”OR1KK” ,”RAILW”
FROM
“PA0006”
WHERE
“MANDT” = ? AND “PERNR” IN ( ? , ? , ? , ? , ? ) /* R3:SAPDBPNP:11498 T:PA0006 M:100 */ /*unc. rd.*/
Variable
A0(CH,3) = 100
A1(NU,8) = 00000001
A2(NU,8) = 00000002
A3(NU,8) = 00000003
A4(NU,8) = 00000004
A5(NU,8) = 00000005
The text after the first “/*” is comment text added by the SAP DBSL layer to indicate the calling module and line number, as well as the client and table against which it is executing.
All you need to do to use this SQL directly at the DB level is to populate the ‘?’ with the actual data which you can see in the variable list at the bottom left.
The variables run in order, left to right.
Therefore, A1 will be the first ‘?’ in the SQL statement.
You will also see from the variable list that the ABAP data type is included.
CH = CHARacter and NU = Number.
All you need to know, is that SAP rarely uses the underlying database data type, ensuring the agnostic nature of the OPEN SQL.
This means you enclose all of the variables in quotes as though they were characters.
Based on the example above, removing the comment area, you should then have the following SQL statement:
SELECT
“MANDT” ,”PERNR” ,”SUBTY” ,”OBJPS” ,”SPRPS” ,”ENDDA” ,”BEGDA” ,”SEQNR” , “AEDTM” ,”UNAME” ,”HISTO” ,”ITXEX” ,”REFEX” ,”ORDEX” ,”ITBLD” ,”PREAS” , “FLAG1″ ,”FLAG2″ ,”FLAG3″ ,”FLAG4″ ,”RESE1″ ,”RESE2″ ,”GRPVL” ,”ANSSA” , “NAME2″ ,”STRAS” ,”ORT01″ ,”ORT02″ ,”PSTLZ” ,”LAND1″ ,”TELNR” ,”ENTKM” , “WKWNG” ,”BUSRT” ,”LOCAT” ,”ADR03″ ,”ADR04″ ,”STATE” ,”HSNMR” ,”POSTA” , “BLDNG” ,”FLOOR” ,”STRDS” ,”ENTK2″ ,”COM01″ ,”NUM01″ ,”COM02″ ,”NUM02″ , “COM03″ ,”NUM03″ ,”COM04″ ,”NUM04″ ,”COM05″ ,”NUM05″ ,”COM06″ ,”NUM06” , “INDRL” ,”COUNC” ,”RCTVC” ,”OR2KK” ,”CONKK” ,”OR1KK” ,”RAILW”
FROM
“PA0006”
WHERE
“MANDT” = ‘100’ AND “PERNR” IN (‘00000001’, ‘00000002’ , ‘00000003’ , ‘00000004’ , ‘00000005’ )
Now to run it at the database level.
Log onto the database server as either the syb<sid> or <sid>adm (I’m using Unix/Linux) user (both usually have the isql binary in their path).
Once logged on, run isql and connect into the <SID> database as the SAPSR3 (or SAPSR3DB on Java stacks) user:
isql –USAPSR3 –S<SID> -D<SID> -W999 –X
Now you’re connected, we need to set some session settings so that we get the explain plan output.
NOTE: The “use” is optional as we’ve already specified the DB we want to use with the “-D” parameter at the isql command line.
use <SID>
go
We want to show the resultant execution (EXPLAIN) plan.
set showplan on
go
We would like some additional useful details from the EXPLAIN plan.
set statistics io on
go
set statistics time on
go
set statistics plancost on
go
We don’t want any of our SQL to be cached, just incase we want to change it and the system decides to try and use the most efficient one.
set statement_cache off
go
We make life easier by enabling the use of quotes, since SAP has already put them there for us.
set quoted_identifier on
go
Finally, we paste the SQL itself, followed by the ASE SQL command terminator “go”:
SELECT
“MANDT” ,”PERNR” ,”SUBTY” ,”OBJPS” ,”SPRPS” ,”ENDDA” ,”BEGDA” ,”SEQNR” , “AEDTM” ,”UNAME” ,”HISTO” ,”ITXEX” ,”REFEX” ,”ORDEX” ,”ITBLD” ,”PREAS” , “FLAG1″ ,”FLAG2″ ,”FLAG3″ ,”FLAG4″ ,”RESE1″ ,”RESE2″ ,”GRPVL” ,”ANSSA” , “NAME2″ ,”STRAS” ,”ORT01″ ,”ORT02″ ,”PSTLZ” ,”LAND1″ ,”TELNR” ,”ENTKM” , “WKWNG” ,”BUSRT” ,”LOCAT” ,”ADR03″ ,”ADR04″ ,”STATE” ,”HSNMR” ,”POSTA” , “BLDNG” ,”FLOOR” ,”STRDS” ,”ENTK2″ ,”COM01″ ,”NUM01″ ,”COM02″ ,”NUM02″ , “COM03″ ,”NUM03″ ,”COM04″ ,”NUM04″ ,”COM05″ ,”NUM05″ ,”COM06″ ,”NUM06” , “INDRL” ,”COUNC” ,”RCTVC” ,”OR2KK” ,”CONKK” ,”OR1KK” ,”RAILW”
FROM
“PA0006”
WHERE
“MANDT” = ‘100’ AND “PERNR” IN (‘00000001’, ‘00000002’ , ‘00000003’ , ‘00000004’ , ‘00000005’ )
Here’s the sample output plan:
================ Lava Operator Tree ================
Emit
(VA = 3)
r:36 er:39
cpu: 0
/
NestLoopJoin
Inner Join
(VA = 2)
r:36 er:39
l:0 el:31
p:0 ep:25
/
OrScan IndexScan
Max Rows: 5 PA0006~0
(VA = 0) (VA = 1)
r:5 er:-1 r:36 er:39
l:0 el:-1 l:35 el:31
p:0 ep:-1 p:1 ep:25
=====================================================
Table: PA0006 scan count 5, logical reads: (regular=35 apf=0 total=35), physical
reads: (regular=1 apf=0 total=1), apf IOs used=0
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 10 ms. Adaptive Server elapsed time: 24 ms.
(36 rows affected)
Reading the plan from the lowest “VA” value, we start with an OrScan (breaks down the 5 “IN” list values we passed into the query, into an SQL “OR” statement).
The OrScan is returning 5 rows (r:5) and performed zero logical reads (l:0) and zero physical reads (p:0).
There were no estimated logical or physical reads (el & ep) due to the type of operation.
We were then using an index (IndexScan) of PA0006~0 (a primary key on table PA0006). Of which we estimated that we would return 39 rows (er:39), based on statistics (I would hope), but we actually returned 36 (r:36).
We performed 35 logical reads and 1 physical read on the index (I believe that logical reads encompass physical reads, just like Oracle).
Which was better than we anticipated with the estimated logical and physical values of 31 and 25 respectively.
Both the OrScan and the IndexScan are accessed from the parent NestLoopJoin(VA = 2).
We return 36 rows (r:36) upto the EMIT for return back to the client (isql in this case, but it would normally be our SAP dialog work process).
The summary at the very bottom of the SQL output shows a nice set of easy to interpret values.
Because our query was broken into an “OR” statement like this:
SELECT column1
FROM table1
WHERE column1 = value1
OR column1 = value2
OR column1 = value3
OR column1 = value4
OR column1 = value5
It means that the index was scanned 5 times against the matching key column (scan count 5).
The sum of the logical reads and physical reads is shown (APF reads – Asynchronous Pre-Fetch, are reads to/from the data cache in a hopeful way).
We then have visibility of the actual SQL execution time plus the required CPU time and the overall elapsed time.
Additional information can be found on the Sybase infocenter site, although I feel it’s lifetime is limited.
