AVG_POOL_INDEX_L_READS

Gopalan Venkatramani

AVG_POOL_INDEX_L_READS


I've a query that showed recently in performance analysis that its avg_pool_index_l_reads is high. I took the db2exfmnt and try to find where is the index reads are high however I am not finding anything.

What is the meaning of having high avg_pool_index_l_reads ? 

"Indicates the number of index pages which have been requested from the buffer pool (logical) for regular and large table spaces."

My understanding is that to accomplish the query the requested number index pages from disk to bufferpool  is high. How well can this definition be?

Need an experts advice on what I should be doing to tweak the query?

 

 

Glenn

DB2 LUW

Philip Gunning

AVG_POOL_INDEX_L_READS
(in response to Gopalan Venkatramani)
What did you use to gather the performance info that was telling you they were high? What kind of index access does the db2exfmnt show? All start stop predicates or index scan with fetch? Is there a better index solution?


>
> IBM Champion
>
> IBM Gold Consultant
>
> Certified Information Systems Security Professional(CISSP)
>
> Certification Number 539059
>
> Certified DB2 DBA v10.5
>
> Certified Database Adminstrator, DB2 11.1
>
> IBM DB2 LUW Support Page -- https://www.ibm.com/analytics/us/en/technology/db2/db2-linux-unix-windows.html
>
> Skype: DB2LUW
>
> Twitter: DB2LUW
>
> Direct +1.610.451.5801
>
> IDUG DB2-L Hall of Fame
>
> www.philipkgunning.com
>
> IBM Business Partner
>
>> This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system
>
Sent from my iPhone

> On May 17, 2019, at 1:23 PM, Gopalan Venkatramani <[login to unmask email]> wrote:
>
>
> I've a query that showed recently in performance analysis that its avg_pool_index_l_reads is high. I took the db2exfmnt and try to find where is the index reads are high however I am not finding anything.
>
> What is the meaning of having high avg_pool_index_l_reads ?
>
> "Indicates the number of index pages which have been requested from the buffer pool (logical) for regular and large table spaces."
>
> My understanding is that to accomplish the query the requested number index pages from disk to bufferpool is high. How well can this definition be?
>
> Need an experts advice on what I should be doing to tweak the query?
>
>
>
>
>
> Glenn
>
> DB2 LUW
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
> BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Gopalan Venkatramani

RE: AVG_POOL_INDEX_L_READS
(in response to Philip Gunning)

Hi - Thanks for your reply. I'm using IBM performance analysis suite 1.1.4 ( its a free tool ) and using the snapshot method. In the db2exfmt there are index scans however it seems to be a normal costs and I/O. However in the performance analysis suite the avg_pool_index_l_reads is 483979.690 for 1811 number of executions. Rest of the queries which is on the list has only 2 digit numbers like 12, 45, 

 

 

Glenn

DB2 LUW