Low DSNTIAUL and SPUFI Execution BP0 Hit Ratio

Douglas Hecht

Low DSNTIAUL and SPUFI Execution BP0 Hit Ratio
Up until last week, the average bufferpool hit ratio in BP0 for a 24-hour
period has been very high (95 to 100%). Now it is between 20 and 30% in both
the PM Accounting and Statistics Reports.

Only the V7 Catalog and Dictionary Tables reside in BP0. The BP definitions
are shown below, including the DWQT and VDWQT values (zero in both cases.)

For plans DSTINB71 (DSNTIAUL) and DSNESPCS (SPUFI) the bufferpool hit ratios
are low, but for the business application plan, WCVJDBC, the bufferpool hit
ratio is high.

I understand the bufferpool hit ratio is reduced when the number of
asynchronous pages read into memory via prefetch exceeds the number of pages
actually accessed.

I also understand that both DSNTIAUL and SPUFI build dynamic SQL.

DSNTIAUL is only being executed once in the 24-hour period. SPUFI is being
executed a number of times via ISPF.

1) Are the catalog tables read into memory via matching index scans as
opposed to direct index access, and does this account for high asynchronous
processing and the possiblity more pages are being read in than are being
used? (The catalog and dictionaryry tables were reorged about a month ago.)

2) Does the dynamic SQL result in more catalog and dictionary IOs than
static SQL? The business application is 100% dynamic SQL and doesn't appear
to show any problems. All three plans are bound with CS and KEEPDYNAMIC NO.
CACHEDYN is set to YES. Should the DSNTIAUL and SPUFI SQL be cached as
prepared statements (KEEPDYNAMIC YES)? This would only make sense if the
statements are being prepared and executed more than once within a thread.
UR would only apply in regards to performance because the number of locks
would be reduced. It doesn't appear to have any bearing here.

3) Why does the average BP0 bufferpool hit ratio shown in the PM Accounting
Report not match the value when all the hit ratios are manually averaged
(see below)?

4) Why are the catalog and directory read counts for the business
application so much lower than DSNTIAUL and SPUFI when the business
application DML counts are high?

in short, what would cause the sudden drop in DSNTIAUL and SPUFI hit ratios?

Thanks.

BP0 - 5000 Buffers
VP SEQUENTIAL = 80
DEFERRED WRITE = 50
VERTICAL DEFERRED WRT = 10,0

HORIZ.DEF.WRITE THRESHOLD 0.00
VERTI.DEF.WRITE THRESHOLD 0.00

PAGE-INS REQUIRED FOR READ 0.00

PLANNAME: DSNTIB71
PROGRAM: DSNTIAUL

BP0 BPOOL ACTIVITY AVERAGE TOTAL
--------------------- -------- --------
BPOOL HIT RATIO (%) 1.13 N/A
GETPAGES 162.1K 1944795
GETPAGES-FAILED 0.00 0
BUFFER UPDATES 0.00 0
SYNCHRONOUS WRITE 0.00 0
SYNCHRONOUS READ 111.42 1337
SEQ. PREFETCH REQS 5231.33 62776
LIST PREFETCH REQS 0.00 0
DYN. PREFETCH REQS 0.00 0
PAGES READ ASYNCHR. 160.1K 1921403

PLANNAME: DSNESPCS
PROGRAM: SPUFI

BP0 BPOOL ACTIVITY AVERAGE TOTAL
--------------------- -------- --------
BPOOL HIT RATIO (%) 2.38 N/A
GETPAGES 4605.05 96706
GETPAGES-FAILED 0.00 0
BUFFER UPDATES 0.00 0
SYNCHRONOUS WRITE 0.00 0
SYNCHRONOUS READ 4.52 95
SEQ. PREFETCH REQS 146.71 3081
LIST PREFETCH REQS 0.00 0
DYN. PREFETCH REQS 0.19 4
PAGES READ ASYNCHR. 4491.14 94314

PLANNAME: WCVJDBC
PROGRAM: Business Application

BP0 BPOOL ACTIVITY AVERAGE TOTAL
--------------------- -------- --------
BPOOL HIT RATIO (%) 98.99 N/A
GETPAGES 1201.44 51662
GETPAGES-FAILED 0.00 0
BUFFER UPDATES 0.00 0
SYNCHRONOUS WRITE 0.00 0
SYNCHRONOUS READ 5.86 252
SEQ. PREFETCH REQS 0.00 0
LIST PREFETCH REQS 0.00 0
DYN. PREFETCH REQS 2.02 87
PAGES READ ASYNCHR. 6.26 269

DML Counts for Business Application:

SQL DML AVERAGE TOTAL
-------- -------- --------
SELECT 0.00 0
INSERT 3234.79 139096
UPDATE 629.91 27086
DELETE 477.44 20530

DESCRIBE 6710.65 288558
DESC.TBL 0.00 0
PREPARE 20594.33 885556
OPEN 130.4K 5606076
FETCH 331.1K 14239233
CLOSE 130.4K 5606075

DML-ALL 623.5K 26812210

*** GRAND TOTAL ***

BP0 BPOOL ACTIVITY AVERAGE TOTAL
--------------------- -------- --------
BPOOL HIT RATIO (%) 20.84 N/A
GETPAGES 3022.88 2608742
GETPAGES-FAILED 0.00 0
BUFFER UPDATES 92.68 79983
SYNCHRONOUS WRITE 0.00 2
SYNCHRONOUS READ 7.34 6332
SEQ. PREFETCH REQS 76.95 66411
LIST PREFETCH REQS 2.54 2195
DYN. PREFETCH REQS 14.46 12477
PAGES READ ASYNCHR. 2385.44 2058636

All BP Hit ratios for all plans and average. Plans above and three not shown.)

99.81
99.05
51.69
2.38
99.97
1.13
90.37
99.12

67.94 Average

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Venkat Srinivasan

Re: Low DSNTIAUL and SPUFI Execution BP0 Hit Ratio
(in response to Douglas Hecht)
I/O to sysdbase will increase as the number of Full Prepare increases.
SPUFI probably has more chance to execute different SQLs from different
authid. Hence the cache miss rate could be higher whereas a business
application can be executing the same SQL over and over again. If it
prepares using parameter markers then chances of cache hit is more likely.
I am not sure what type of access to catalog you are questioning. Is it
the access during sql optimization or access in general?.
Access during SQL optimization will depend on the input SQL, number of
tables referred, predicates etc. Any other access is specific to each
installation.

Binding spufi with keepdynamic in order to see a hit in local cache may be
counter productive if there are many users accesing spufi. You will simply
pollute the cache. In order to match the cache in SQL Cache auth id should
be the same. Local cache is for prepare avoidance.

PM calculated the average hit ratio from the getpage counters and not from
the average of independent package buckets. According to the total
getpages, the hit ratio is 20.84 It doesnt average the percentages.
2608742 - 2058636 - 6332 / 2608742 = 20.8442.

Catalog i/o to the business application is probably lower because it
reuses the same SQL and number of prepares is less. The reverse is
probably happening for spufi and dsntiaul.
Low hit ratio is due to the aycnc page reads.
Regards,
venkat

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Douglas Hecht

Re: Low DSNTIAUL and SPUFI Execution BP0 Hit Ratio
(in response to Venkat Srinivasan)
It seems the only way all that IO could be occuring against sysdbase is if
the DSNTIAUL statement is being prepared for each table row retrieved. There
are 1/2 million rows. This seems unlikely.

Thanks.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tek-Hoe Tan

DSNTIAUL
(in response to Douglas Hecht)
Can you use DSNTIAUL to perform insert, delete and update ?

Is there a documentation about it?

Thank you

Regards,
TekHoe Tan

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: DSNTIAUL
(in response to Tek-Hoe Tan)
No - DSNTIAUL just performs a SELECT from the table/view etc that you
specify

However, you can use DSNTEP2 to issue any SQL


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125752
[login to unmask email]

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Tek-Hoe Tan
Sent: 12 December 2006 13:36
To: [login to unmask email]
Subject: [DB2-L] DSNTIAUL

Can you use DSNTIAUL to perform insert, delete and update ?

Is there a documentation about it?

Thank you

Regards,
TekHoe Tan

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

gerhard heiss

Re: DSNTIAUL
(in response to Phil Grainger)
Yes, you can when you use PARM='SQL'.
From the sourcecode-documentation (member DSNTIAUL in SDSNSAMP lib):


* INPUT *
* SYMBOLIC LABEL/NAME = PARM *
* DESCRIPTION = PARAMETER FOR DESIGNATING INPUT TYPE. *
* IF THE VALUE 'SQL' IS SPECIFIED FOR THIS PARAMETER, *
* INPUT IS IN THE FORM OF COMPLETE SQL STATEMENTS *
* OF UP TO THE MAXIMUM LENGTH ALLOWED BY DB2. EACH SQL *
* STATEMENT MUST BE TERMINATED BY A SEMICOLON. *
* SYMBOLIC LABEL/NAME = SYSIN *
* DESCRIPTION = DDNAME OF SEQUENTIAL DATA SET CONTAINING *
* SQL STATEMENTS TO BE EXECUTED. *
* FOR FORMAT, SEE NOTES SECTION. *


regards gerhard


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Tek-Hoe Tan
Sent: Tuesday, December 12, 2006 2:36 PM
To: [login to unmask email]
Subject: [DB2-L] DSNTIAUL


Can you use DSNTIAUL to perform insert, delete and update ?

Is there a documentation about it?

Thank you

Regards,
TekHoe Tan

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm