VIEW Performance and VIEW EXPLAIN

Salvatore S Bertolino

VIEW Performance and VIEW EXPLAIN
Hi All,

I have encountered an interesting scenario. We have a package installed on
db2 V6 that creates a view where the SELECT FROM clause contains more than
15 tables (before the apar was applied reducing max to 15 tables), after the
view is created then all the data from the view is selected after which the
view is dropped. The select runs for about 5 mins before completing.

I have extracted the sql for the create view statement and have done a few
tests with results that I don't completely understand and hope that someone
on the list can explain:

1.) When I create the view and run the select from the view, the select
statement runs for about 5 mins again.(which is expected, interesting thing
is that in the omegamon the statement is using quite a lot of CPU and only
does about 350 getpages)

2.) If I run the query again the result is returned in less than a second
(expected since the datapages needed for the view should still be in the
bufferpool)

3.) If I now drop the view and recreate it, the select takes about 5 mins
(why is this since the datapages needed for the view should still be in the
bufferpool?)

4.) Does dropping the view somehow invalidate the datapages in the
bufferpool?

5.) The EXPLAIN of the select from the view has the first qblockno as a
tablespace scan from the view and the 2nd qblockno as the joins required to
create the view (Is this correct as I would have expected the first qblockno
to materialise the view and the 2nd qblockno to read the materialised
data????)

TIA
Sal



______________________________________________

Disclaimer and confidentiality note

Everything in this e-mail and any attachments relating to the official business of Standard Bank Investment Corporation (Stanbic)
is proprietary to the company. It is confidential, legally privileged and protected by law.\
Stanbic does not own and endorse any other content. Views and opinions are those of the sender unless clearly stated as being that of Stanbic.

The person addressed in the e-mail is the sole authorised recipient.
Please notify the sender immediately if it has unintentionally reached you and do not read, disclose or use the content in any way.

Stanbic can not assure that the integrity of this communication has been maintained
nor that it is free of errors, virus, interception or interference.


______________________________________________

Sanjeev (CTS) S

Re: VIEW Performance and VIEW EXPLAIN
(in response to Salvatore S Bertolino)
Hi Sal,

> 1.) When I create the view and run the select from the view, the select
> statement runs for about 5 mins again.(which is expected, interesting
> thing is that in the omegamon the statement is using quite a lot of CPU
> and only does about 350 getpages)
Answer : It depends on the select statement of the create view.

> 2.) If I run the query again the result is returned in less than a second
> (expected since the datapages needed for the view should still be in the
> bufferpool)
Answer : Quite possible

> 3.) If I now drop the view and recreate it, the select takes about 5 mins
> (why is this since the datapages needed for the view should still be in
> the bufferpool?)
Answer Again depends on the create view statement and if the pages
are in the pool or not.
> 4.) Does dropping the view somehow invalidate the datapages in the
> bufferpool?
Answer I think dropping the view should not invalidate or remove
the pages from pool because it is completely logical object which is
resolved at the run time and DB2 doesn't keep the pages in the pool with the
reference of view or logical name rather it keeps it with the DBID,OBID
,pageset ids and numbers.This is my opinion, however i have never tried the
same thing monitoring the way it is happening with you.

> 5.) The EXPLAIN of the select from the view has the first qblockno as a
> tablespace scan from the view and the 2nd qblockno as the joins required
> to create the view (Is this correct as I would have expected the first
> qblockno to materialise the view and the 2nd qblockno to read the
> materialised data????)
Answer I am not sure about the Qblock number as first you are
mentioning but I think the tablespace scan on view is the view
materialization only. If the TNAME column in showing view name and the
accesstype is R then it is view materialization.Please send the explain
output and query so that more research can be done.


HTH
Regards
Sanjeev

>
>
>
>
> ______________________________________________
>
> Disclaimer and confidentiality note
>
> Everything in this e-mail and any attachments relating to the
> official business of Standard Bank Investment Corporation (Stanbic) is
> proprietary to the company. It is confidential, legally privileged and
> protected by law. Stanbic does not own and endorse any other content.
> Views and opinions are those of the sender unless clearly stated as being
> that of Stanbic.
>
> The person addressed in the e-mail is the sole authorised recipient.
> Please notify the sender immediately if it has unintentionally reached you
> and do not read, disclose or use the content in any way.
>
>
>
> Stanbic can not assure that the integrity of this communication has
> been maintained nor that it is free of errors, virus, interception or
> interference.
>
>
>
> _______________________________________________
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



James Campbell

Re: VIEW Performance and VIEW EXPLAIN
(in response to Sanjeev (CTS) S)
Had a thought about this:

1) If each table has, say, 2 indexes that means there are 3 access paths to
each table. That means DB2 has to evaluate 45 possible access initial table
access paths (ie evaluate each of the 15 as the first table and for each its
3 access paths), 42 to the second table (with 3 possible join techniques),
39 to the third (with 3 ...) etc, giving: 45*42*3*39*3* .... 6*3*3 = 29E24
possible ways of joining the tables together. OK, perhaps the optimizer is
going to discard some of these without evaluating the cost (eg where there
isn't a join between tables and its not considering a star join) but you get
the idea? This is where the CPU time goes.

2) Read Appl Prog & SQL Guide section 7.1.2 Caching dynamic SQL statements.

3) Dropping the view invalidates the dynamic statement cache

4) you should know the answer by now

5) I think you'll find that QBLOCKNO=2 is an inner subselect that is
evaluated and materialised first. QBLOCKNO=1 then processes the results of
the materialised view.

/* standard disclaimer */
James Campbell
DBA
Hansen Corporation, Doncaster
+61 3 9843 8442
[login to unmask email]



-----Original Message-----
From: Bertolino, Salvatore S [mailto:[login to unmask email]
Sent: Wednesday, December 20, 2000 1:21 AM
To: [login to unmask email]
Subject: [DB2-L] VIEW Performance and VIEW EXPLAIN



Hi All,

I have encountered an interesting scenario. We have a package installed on
db2 V6 that creates a view where the SELECT FROM clause contains more than
15 tables (before the apar was applied reducing max to 15 tables), after the
view is created then all the data from the view is selected after which the
view is dropped. The select runs for about 5 mins before completing.

I have extracted the sql for the create view statement and have done a few
tests with results that I don't completely understand and hope that someone
on the list can explain:

1.) When I create the view and run the select from the view, the select
statement runs for about 5 mins again.(which is expected, interesting thing
is that in the omegamon the statement is using quite a lot of CPU and only
does about 350 getpages)

2.) If I run the query again the result is returned in less than a second
(expected since the datapages needed for the view should still be in the
bufferpool)

3.) If I now drop the view and recreate it, the select takes about 5 mins
(why is this since the datapages needed for the view should still be in the
bufferpool?)

4.) Does dropping the view somehow invalidate the datapages in the
bufferpool?

5.) The EXPLAIN of the select from the view has the first qblockno as a
tablespace scan from the view and the 2nd qblockno as the joins required to
create the view (Is this correct as I would have expected the first qblockno
to materialise the view and the 2nd qblockno to read the materialised
data????)


TIA
Sal



______________________________________________

Disclaimer and confidentiality note

Everything in this e-mail and any attachments relating to the
official business of Standard Bank Investment Corporation (Stanbic) is
proprietary to the company. It is confidential, legally privileged and
protected by law. Stanbic does not own and endorse any other content. Views
and opinions are those of the sender unless clearly stated as being that of
Stanbic.

The person addressed in the e-mail is the sole authorised recipient.
Please notify the sender immediately if it has unintentionally reached you
and do not read, disclose or use the content in any way.



Stanbic can not assure that the integrity of this communication has
been maintained nor that it is free of errors, virus, interception or
interference.



_______________________________________________



**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************