1. No category

Database-Request Statement Cache
Ben Holmes – July 2014
© 2010 Eaton Corporation. All rights reserved.
Client Database-Request Statement Caching
What is it?
• Client execution stack trace dump
• Includes: Line #, procedure name, file name
• Introduced in 10.1C
- Not a new feature
When should you use it?
• Diagnosing deployed application problems
• Local and remote access
What’s in a name?
• Don’t confuse with other “statement caches”
Documentation
• OpenEdge Data Management: Database Administration
©
© 2009 Eaton Corporation. All rights reserved.
2
2
Enablement - Multi-level activation
promon
R&D
1. Status Displays ...
18. Client Database-Request Statement Cache ...
Activate
1. Activate For Selected Users
2. Activate For All Users
3. Activate For All Future Users
4. Deactivate For Selected Users
5. Deactivate For All Users
6. Deactivate For All Future Users
7. View Database-Request Statement Cache
8. Specify Directory for Statement Cache Files
©
© 2009 Eaton Corporation. All rights reserved.
3
3
Activation Types
1. Single
• Report top of the stack
• Last procedure call only
• Better performance
• Continual update
2. Stack
• Reports entire stack (31 deep, sometimes more)
• 32,000 byte maximum stack size
• Most information
- How did I get there?
• Continual update
©
© 2009 Eaton Corporation. All rights reserved.
4
4
Activation Types - It’s not a trace; It’s a current stack dump.
3. One time stack
• Full stack snapshot
• Reports stack one time (on next DB operation)
• Not continuous update
- Remembered until deactivated or reactivated
- Re-activate for update
• Diagnose quickly changing stack
Summary
• Select, current, future users
• One time vs continuous
• Top of stack or full stack
• SQL exception - statement level only
©
© 2009 Eaton Corporation. All rights reserved.
5
5
Viewing the Statement Cache
Let’s have a look at…
promon
R&D
1. Status Displays ...
18. Client Database-Request Statement Cache ...
1. Activate For Selected Users
2. Activate For All Users
3. Activate For All Future Users
4. Deactivate For Selected Users
5. Deactivate For All Users
6. Deactivate For All Future Users
View
7. View Database-Request Statement Cache
8. Specify Directory for Statement Cache Files
©
© 2009 Eaton Corporation. All rights reserved.
6
6
Caching Types
12/07/10 OpenEdge Release 11 Monitor (R&D)
11:20:50 View Database-Request Statement Cache
Usr
5
23
24
Name
userb
userb
userb
Type
SELF/ABL
REMC/ABL
REMC/ABL
Login time Serv Type Cache Update
L1 12/07/10 09:50
12/07/10 09:38 0
L2 12/07/10 09:50
12/07/10 09:40 1
RQ 12/07/10 09:50
12/07/10 09:40 1
“L1”: Level 1
• Continuous procedure at top of stack only
• “SQL Statement or Single ABL Program Name”
“L2”: Level 2
• Continuous full stack
32,000 byte maximum
• “SQL Statement or Partial ABL Program Stack”
“RQ”: Client Trace Request (level 3)
• One time stack
• “SQL Statement or Partial ABL Program Stack”
18
©2013 Progress Software Corporation. All rights reserved.
© 2009 Eaton Corporation. All rights reserved.
7
7
Procedure Call Stack
It’s just a LIFO…
Top is last procedure executed
Bottom is first procedure executed
Top down, newest to oldest
#
Top
Procedure Name
File Name
Newest 19 : reallyLongNamedInternalProcedure3 proctestb.r
12 : reallyLongNamedInternalProcedure2 proctestb.r
5 : reallyLongNamedInternalProcedure1 proctesta.r
445 : reallyLongNamedInternalProcedure0 proctesta.r
Oldest 1 : /usr1/stmtest/p72340_Untitled1.ped
Bottom
More on data format later…
©
© 2009 Eaton Corporation. All rights reserved.
8
8
Statement Caching and -1
12/07/10 OpenEdge Release 11 Monitor (R&D)
11:25:34 View Database-Request Statement Cache
…
Statement cache information
:
-1 : reallyLongNamedProcedure1 proctest2.p
445 : proctest2.p
16 : methodB test13d
3 : runner.p
1 : /usr1/userb/11/stmtest/p49070_Untitled1.ped
Line # of -1
• Database action at end of procedure
• Not a specific line # in a .p
• Often the result of buffer flushing
©
© 2009 Eaton Corporation. All rights reserved.
9
9
File Overflow Directory
Let’s have a look at…
promon
R&D
1. Status Displays ...
18. Client Database-Request Statement Cache ...
1. Activate For Selected Users
2. Activate For All Users
3. Activate For All Future Users
4. Deactivate For Selected Users
5. Deactivate For All Users
6. Deactivate For All Future Users
7. View Database-Request Statement Cache
Other
8. Specify Directory for Statement Cache Files
©
© 2009 Eaton Corporation. All rights reserved.
10
10
VST Monitoring
_Connect…
_Connect-CachingType
• Caching level: 01, 02, 03
• Value “requested” in promon
(top, stack, one-time)
_Connect-CacheInfoType
• "ABL Program", "SQL Statement", "ABL Stack“
• Value of current stack type displayed
• “?”: stack requested and no stack yet
"ABL Program“ (01)
• Procedure and .p name displayed
"ABL Stack“ (02 & 03)
“SQL Statement” (01, 02 & 03)
©
© 2009 Eaton Corporation. All rights reserved.
11
11
VST Monitoring
FOR EACH _Connect WHERE
_Connect-CacheInfoType <> ?:
CacheInfoType set when data exists, not pending.
DISPLAY _Connect-id _Connect-Usr
_Connect-CachingType
_Connect-CacheInfoType format "x(12)“
_Connect-CacheLineNumber[1] label "Line“
_Connect-CacheInfo[1]
label "Entry“
_Connect-CacheLineNumber[2] no-label
_Connect-CacheInfo[2]
no-label
END.
©
© 2009 Eaton Corporation. All rights reserved.
12
12
VST Monitoring
_Connect…
_Connect-CacheLastUpdate
• Date/time of cache update
• One time stack - age of information
• Continual stack - time of last DB request
_Connect-CacheInfo[32]
• Up to “last“ 32 stack entries
• Procedure & executing image name
- .p or .r executed (run)
– Pathname specified (not fully qualified)
_Connect-CacheLineNumber[32]
• Up to “last” 32 stack entries
• Line number of code
©
© 2009 Eaton Corporation. All rights reserved.
13
13
VST Monitoring
FOR EACH _Connect WHERE _Connect-CacheInfoType <> ?:
CacheInfoType set when data exists, not pending.
DISPLAY _Connect-id _Connect-Usr
_Connect-CachingType
_Connect-CacheInfoType format "x(12)“
_Connect-CacheLineNumber[1] label "Line“
_Connect-CacheInfo[1]
label "Entry“
_Connect-CacheLineNumber[2] no-label
_Connect-CacheInfo[2]
no-label
END.
©
© 2009 Eaton Corporation. All rights reserved.
14
14
Manually Generate Stack Trace
Stack also available via “prostack”
• Useful for “non-responsive” connections
• Available with 10.1c
• Must have access to client’s machine
• “progetstack on Windows
kill -SIGUSR1 <pid> (Don’t forget the dash)
• protrace.<pid>
- Startup parameters
- Execution stack (Statement cache)
- ** ABL Stack Trace **
- ** Persistent procedures/Classes **
- ** PROPATH **
- ** Databases (logical/type/physical) **
©
© 2009 Eaton Corporation. All rights reserved.
15
15
07/29/14
OpenEdge Release 10 Monitor (R&D)
12:25:23
View Database-Request Statement Cache
User number
: 181
User name
: B1311
User type
: SELF/ABL
Login date/time
: 07/29/14 12:23
Statement caching type
: SQL Statement or Partial ABL Program Stack
Statement caching last updated : 07/29/14 12:25
Statement cache information
: 15832 : create-sod-line-details us/ed/edomack.p
10618 : us/ed/edomack.p
4445 : us/mg/mgbcs2_e.p
2599 : us/mg/mgbcs1_e.r
2186 : us/gp/gpwinrun.p
5764 : us/mf/mfmenu.p
10937 : us/mf/mf1a.p
1800 : mf1.p
122 : mf.p
3 : /nhb/log/qad/B1311/XXHEMT_1311.p
Enter user number, P, T, or X (? for help):
©
© 2009 Eaton Corporation. All rights reserved.
16
16
Find out what is SQL doing…
961 cognos1 REMC/ 961 cognos1 REMC/SQLC 07/29/14 14:17 1 L2 07/29/14
14:17 IPV4 172.23.201.235 SQLC 07/29/14 14:17 1 L2 07/29/14 14:17 IPV4 172.
23.201.235
07/29/14
OpenEdge Release 10 Monitor (R&D)
14:20:17
View Database-Request Statement Cache
User number
User name
User type
: 956
: cognos1
: REMC/SQLC
Login date/time
Statement caching type
: 07/29/14 14:10
: SQL Statement or Partial ABL Program Stack
Statement caching last updated : 07/29/14 14:10
©
© 2009 Eaton Corporation. All rights reserved.
17
17
SQL String
Statement cache information : select ( "Work_Order_Route__Wr_Route_"."wr_start" + -(MOD(DAYOFWEEK("Work_Order_Route__Wr_Route_"."wr_start")+7-1,7)+1)
+ 7), {fn CURDATE() }, YEAR(( "Work_Order_Route__Wr_Route_"."wr_start" + -(MOD(DAYOFWEEK("Work_Order_Route__Wr_Route_"."wr_start")+7-1,7)+1) + 7)),
YEAR({fn CURDATE() }), upper ("Work_Order_Route__Wr_Route_"."wr_wkctr"), "Work_Order_Route__Wr_Route_"."wr_op",
"Work_Order_Route__Wr_Route_"."wr_status", "Work_Order_Route__Wr_Route_"."wr_tool", "Work_Order_Route__Wr_Route_"."wr_start",
"Work_Order_Route__Wr_Route_"."wr_due", "Work_Order_Master__Wo_Mstr_"."wo_status", "Work_Order_Master__Wo_Mstr_"."wo_lot",
Work_Order_Route__Wr_Route_"."wr_part", "Work_Master__Wc_Mstr_"."wc_desc", (MOD(DAYOFWEEK("Work_Order_Route__Wr_Route_"."wr_start")+71,7)+1), "Work_Order_Master__Wo_Mstr_"."wo_rel_date", "Work_Order_Route__Wr_Route_"."wr_qty_ord", "Work_Order_Route__Wr_Route_"."wr_qty_comp",
"Work_Order_Master__Wo_Mstr_"."wo_qty_rjct", "Work_Order_Route__Wr_Route_"."wr_setup", "Work_Order_Route__Wr_Route_"."wr_run",
"Work_Order_Route__Wr_Route_"."wr_act_setup", "Work_Order_Route__Wr_Route_"."wr_act_run" from "PUB"."wr_route" "Work_Order_Route__Wr_Route_",
"PUB"."wo_mstr" "Work_Order_Master__Wo_Mstr_", "PUB"."wc_mstr" "Work_Master__Wc_Mstr_" where "Work_Master__Wc_Mstr_"."wc_mch" =
"Work_Order_Route__Wr_Route_"."wr_mch" and "Work_Master__Wc_Mstr_"."wc_wkctr" = "Work_Order_Route__Wr_Route_"."wr_wkctr" and
"Work_Order_Master__Wo_Mstr_"."wo_nbr" = "Work_Order_Route__Wr_Route_"."wr_nbr" and "Work_Order_Master__Wo_Mstr_"."wo_lot" = "Work_Orde
r_Route__Wr_Route_"."wr_lot" and instr('''Adaptive Analytics Users'', ''Aerospace MFGPro US National'', ''All Authenticated Users'', ''CRN Query Studio'', ''CRN
Query Studio Production'', ''CRN Report Studio'', ''CRN Report Studio Production'', ''Cognos 8 Analysis Studio Production and QA'', ''Cognos 8 Query Studio Pro
duction and QA'', ''Cognos Series 7'', ''Consumers'', ''Deitrick David E0053337'', ''Everyone'', ''FPG ALL USERS A_K'', ''FPG Aerospace User Group'', ''FPG_Aero
Space_Users'', ''PowerPlay Users''', 'Aerospace MFGPro UK Citizen') > 0 and "Work_Order_Route__Wr_Route_"."wr_status" <> 'C' and
Work_Order_Master__Wo_Mstr_".“ wo_status" = 'R' and ("Work_Order_Route__Wr_Route_"."wr_qty_ord" - "Work_Order_Master__Wo_Mstr_"."wo_qty_rjct") "Work_Order_Route__Wr_Route_"."wr_qty_comp" > 0 and (1 <> 1 or upper ("Work_Order_Route__Wr_Route_"."wr_wkctr") like '35S%' or upper
("Work_Order_Route__Wr_Route_"."wr_wkctr") like '35RS%') and "Work_Order_Master__Wo_Mstr_"."wo_site" = '35' and
"Work_Order_Route__Wr_Route_"."wr_wkctr"<> '35SM398' and "Work_Order_Route__Wr_Route_"."wr_wkctr" <> '35SM0'
Enter user number, P, T, or X (? for help):
©
© 2009 Eaton Corporation. All rights reserved.
18
18
QUESTIONS
©
© 2009 Eaton Corporation. All rights reserved.
19
19
© 2009 Eaton Corporation. All rights reserved.
20
20