© 2010 eaton corporation. all rights reserved. database-request statement cache ben holmes – july...

© 2010 Eaton Corporation. All rights reserved.

Database-Request Statement Cache

Ben Holmes – July 2014

2 2© 2009 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

©


Enablement - Multi-level activation

promon R&D

1. Status Displays ...

18. Client Database-Request Statement Cache ...

Activate 1. Activate For Selected Users2. Activate For All Users3. Activate For All Future Users

4. Deactivate For Selected Users5. Deactivate For All Users6. Deactivate For All Future Users

7. View Database-Request Statement Cache

8. Specify Directory for Statement Cache Files

©


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

©


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

©


Viewing the Statement Cache

Let’s have a look at…promon R&D



1. Activate For Selected Users2. Activate For All Users3. Activate For All Future Users


View 7. View Database-Request Statement Cache

8. Specify Directory for Statement Cache Files

©


Caching Types

12/07/10 OpenEdge Release 11 Monitor (R&D)11:20:50 View Database-Request Statement Cache

Usr Name Type Login time5 userb SELF/ABL 12/07/10 09:38

23 userb REMC/ABL 12/07/10 09:4024 userb REMC/ABL 12/07/10 09:40

“L1”: Level 1

Serv Type Cache Update0 L1 12/07/10 09:501 L2 12/07/10 09:501 RQ 12/07/10 09:50

• Continuous procedure at top of stack only

• “SQL Statement or Single ABL Program Name”

“L2”: Level 2

• Continuous full stack32,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.


Procedure Call Stack

It’s just a LIFO…

Top is last procedure executed

Bottom is first procedure executed

Top down, newest to oldest

TopNewest

Oldest

Bottom

# Procedure Name File Name

19 : reallyLongNamedInternalProcedure3 proctestb.r12 : reallyLongNamedInternalProcedure2 proctestb.r

5 : reallyLongNamedInternalProcedure1 proctesta.r445 : reallyLongNamedInternalProcedure0 proctesta.r

1 : /usr1/stmtest/p72340_Untitled1.ped

More on data format later…

©


Statement Caching and -1

12/07/10OpenEdge Release 11 Monitor (R&D)11:25:34View Database-Request Statement Cache

…Statement cache information:

-1 : reallyLongNamedProcedure1 proctest2.p445 : proctest2.p16 : methodB test13d

3 : runner.p1 : /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

©


File Overflow Directory

Let’s have a look at…promon R&D



1. Activate For Selected Users2. Activate For All Users3. Activate For All Future Users


7. View Database-Request Statement Cache

Other 8. Specify Directory for Statement Cache Files

©


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)

©


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-labelEND.


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

©


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-labelEND.

©


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) **

©

16 16© 2009 Eaton Corporation. All rights reserved.©

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):


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 : 956

User name : cognos1

User type : REMC/SQLC

Login date/time : 07/29/14 14:10

Statement caching type : SQL Statement or Partial ABL Program Stack

Statement caching last updated : 07/29/14 14:10

Find out what is SQL doing…


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")+7-

1,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):

SQL String


QUESTIONS

© 2010 eaton corporation. all rights reserved. database-request statement cache ben holmes – july...

Documents