Question about Views - AIX 5.3/IDS 11.50 FC5

I have noticed a large number of lock requests being initiated when executing this query against a view. The sqlexplain.out shows sequential scan on all tables involved in the application query, which is using a view that also uses a view within itself. There are compound indices on all columns used to join and/or filter; i_statute_number is the Primary Key in statutes, Foreign Key in statutespeeds. Does there need to be separate indices on the individual columns or can the view be forced to use a directive? This is the application query that accesses the view: SELECT vwstatutes.*, NVL(vwstatutes.dc_totalfine,0) fine , NVL(SUM(statutespeeds.dc_totalfine),0) speedfine FROM vwstatutes, OUTER statutespeeds WHERE c_citation_type = 'C' AND d_begindate <= '09/22/2010' AND '09/22/2010' BETWEEN d_begindate AND NVL(d_enddate,TODAY) AND vwstatutes.i_statute_number = statutespeeds.i_statute_number GROUP BY 1,2,3,4,5,6,7 Here is the SQL for vwstatutes: create view vwstatutes ( i_statute_number, c_statutenumber, c_description, d_begindate, d_enddate, c_citation_type, dc_totalfine ) as select distinct s.i_statute_number, s.c_statutenumber, s.c_description, s.d_begindate, s.d_enddate, vw.c_citation_type, s.dc_totalfine from statute s, vwstatutecittype vw where s.i_statute_number = vw.i_statute_number; Here is the SQL for vwstatutecittype: create view vwstatutecittype ( i_statute_number, c_citation_type ) as select distinct s.i_statute_number, s.c_citation_type from statute_dist_xref s;

On 22/09/2010 20:29, SUSAN JONES wrote: > I have noticed a large number of lock requests being initiated when executing > this query against a view. > > The sqlexplain.out shows sequential scan on all tables involved in the > application query, which is using a view that also uses a view within itself. > There are compound indices on all columns used to join and/or filter; > i_statute_number is the Primary Key in statutes, Foreign Key in statutespeeds. > > Does there need to be separate indices on the individual columns or can the > view be forced to use a directive? > > This is the application query that accesses the view: > > SELECT vwstatutes.*, NVL(vwstatutes.dc_totalfine,0) fine , > NVL(SUM(statutespeeds.dc_totalfine),0) speedfine > FROM vwstatutes, OUTER statutespeeds > WHERE c_citation_type = 'C' > AND d_begindate<= '09/22/2010' > AND '09/22/2010' BETWEEN d_begindate AND NVL(d_enddate,TODAY) > AND vwstatutes.i_statute_number = statutespeeds.i_statute_number > GROUP BY 1,2,3,4,5,6,7 > > Here is the SQL for vwstatutes: > > create view vwstatutes (> > i_statute_number, > > c_statutenumber, > > c_description, > > d_begindate, > > d_enddate, > > c_citation_type, > > dc_totalfine > ) as > select distinct> > s.i_statute_number, > > s.c_statutenumber, > > s.c_description, > > s.d_begindate, > > s.d_enddate, > > vw.c_citation_type, > > s.dc_totalfine > from statute s, vwstatutecittype vw > where s.i_statute_number = vw.i_statute_number; > > Here is the SQL for vwstatutecittype: > > create view vwstatutecittype (> > i_statute_number, > > c_citation_type > ) as > select distinct> > s.i_statute_number, > > s.c_citation_type > from statute_dist_xref s; > UPDATE STATS? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

On 22/09/2010 20:54, Jones, Susan wrote: > Thanks for the quick reply. But, we do run update statistics every day. It isn't that the query is necessarily running that slow, it's more about the high number of lock requests being initiated due to the sequential scan combined with a committed read. I have noticed that the engine has been forced to dynamically allocate more locks multiple times in the last week. Since I need the isolation to be set to committed read, I'm not sure what the best approach would be. I can increase the locks, but I think the problem is more in the application. > > For example: One user session recorded 20 million lock requests in less that 2 hours. I know if I could use an index, I can reduce the number of locks. I'm just not that familiar in how much we can manipulate the query and force it to use an index when selecting from a view. > > Just wondering how some of you might deal with this. If the optimiser is choosing the wrong plan (and it sounds like it might be) then you will get many unnecessary locks. What UPDATE STATS are you running? > From: Obnoxio The Clown [mailto:obnoxio@serendipita.com] > Sent: Wednesday, September 22, 2010 3:32 PM > To: Jones, Susan > Subject: Re: Question about Views - AIX 5.3/IDS 11.50 FC5 [21425] > > On 22/09/2010 20:29, SUSAN JONES wrote: >> I have noticed a large number of lock requests being initiated when > executing >> this query against a view. >> >> The sqlexplain.out shows sequential scan on all tables involved in the >> application query, which is using a view that also uses a view within > itself. >> There are compound indices on all columns used to join and/or filter; >> i_statute_number is the Primary Key in statutes, Foreign Key in > statutespeeds. >> >> Does there need to be separate indices on the individual columns or can the >> view be forced to use a directive? >> >> This is the application query that accesses the view: >> >> SELECT vwstatutes.*, NVL(vwstatutes.dc_totalfine,0) fine , >> NVL(SUM(statutespeeds.dc_totalfine),0) speedfine >> FROM vwstatutes, OUTER statutespeeds >> WHERE c_citation_type = 'C' >> AND d_begindate<= '09/22/2010' >> AND '09/22/2010' BETWEEN d_begindate AND NVL(d_enddate,TODAY) >> AND vwstatutes.i_statute_number = statutespeeds.i_statute_number >> GROUP BY 1,2,3,4,5,6,7 >> >> Here is the SQL for vwstatutes: >> >> create view vwstatutes (>> >> i_statute_number, >> >> c_statutenumber, >> >> c_description, >> >> d_begindate, >> >> d_enddate, >> >> c_citation_type, >> >> dc_totalfine >> ) as >> select distinct>> >> s.i_statute_number, >> >> s.c_statutenumber, >> >> s.c_description, >> >> s.d_begindate, >> >> s.d_enddate, >> >> vw.c_citation_type, >> >> s.dc_totalfine >> from statute s, vwstatutecittype vw >> where s.i_statute_number = vw.i_statute_number; >> >> Here is the SQL for vwstatutecittype: >> >> create view vwstatutecittype (>> >> i_statute_number, >> >> c_citation_type >> ) as >> select distinct>> >> s.i_statute_number, >> >> s.c_citation_type >> from statute_dist_xref s; >> > > UPDATE STATS? > -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

I'm using AUS with the default settings. Underlying tables vary in size. 143,000, 8,500 and 1,500. Does that help? I believe small tables are run high and larger tables are run medium. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Obnoxio The Clown Sent: Wednesday, September 22, 2010 4:00 PM To: ids@iiug.org Subject: Re: Question about Views - AIX 5.3/IDS 11.50 FC5 [21426] On 22/09/2010 20:54, Jones, Susan wrote: > Thanks for the quick reply. But, we do run update statistics every day. It isn't that the query is necessarily running that slow, it's more about the high number of lock requests being initiated due to the sequential scan combined with a committed read. I have noticed that the engine has been forced to dynamically allocate more locks multiple times in the last week. Since I need the isolation to be set to committed read, I'm not sure what the best approach would be. I can increase the locks, but I think the problem is more in the application. > > For example: One user session recorded 20 million lock requests in less that 2 hours. I know if I could use an index, I can reduce the number of locks. I'm just not that familiar in how much we can manipulate the query and force it to use an index when selecting from a view. > > Just wondering how some of you might deal with this. If the optimiser is choosing the wrong plan (and it sounds like it might be) then you will get many unnecessary locks. What UPDATE STATS are you running? > From: Obnoxio The Clown [mailto:obnoxio@serendipita.com] > Sent: Wednesday, September 22, 2010 3:32 PM > To: Jones, Susan > Subject: Re: Question about Views - AIX 5.3/IDS 11.50 FC5 [21425] > > On 22/09/2010 20:29, SUSAN JONES wrote: >> I have noticed a large number of lock requests being initiated when > executing >> this query against a view. >> >> The sqlexplain.out shows sequential scan on all tables involved in the >> application query, which is using a view that also uses a view within > itself. >> There are compound indices on all columns used to join and/or filter; >> i_statute_number is the Primary Key in statutes, Foreign Key in > statutespeeds. >> >> Does there need to be separate indices on the individual columns or can the >> view be forced to use a directive? >> >> This is the application query that accesses the view: >> >> SELECT vwstatutes.*, NVL(vwstatutes.dc_totalfine,0) fine , >> NVL(SUM(statutespeeds.dc_totalfine),0) speedfine >> FROM vwstatutes, OUTER statutespeeds >> WHERE c_citation_type = 'C' >> AND d_begindate<= '09/22/2010' >> AND '09/22/2010' BETWEEN d_begindate AND NVL(d_enddate,TODAY) >> AND vwstatutes.i_statute_number = statutespeeds.i_statute_number >> GROUP BY 1,2,3,4,5,6,7 >> >> Here is the SQL for vwstatutes: >> >> create view vwstatutes (>> >> i_statute_number, >> >> c_statutenumber, >> >> c_description, >> >> d_begindate, >> >> d_enddate, >> >> c_citation_type, >> >> dc_totalfine >> ) as >> select distinct>> >> s.i_statute_number, >> >> s.c_statutenumber, >> >> s.c_description, >> >> s.d_begindate, >> >> s.d_enddate, >> >> vw.c_citation_type, >> >> s.dc_totalfine >> from statute s, vwstatutecittype vw >> where s.i_statute_number = vw.i_statute_number; >> >> Here is the SQL for vwstatutecittype: >> >> create view vwstatutecittype (>> >> i_statute_number, >> >> c_citation_type >> ) as >> select distinct>> >> s.i_statute_number, >> >> s.c_citation_type >> from statute_dist_xref s; >> > > UPDATE STATS? > -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

On 22/09/2010 21:18, Jones, Susan wrote: > I'm using AUS with the default settings. Underlying tables vary in size. > 143,000, 8,500 and 1,500. Does that help? I believe small tables are run high > and larger tables are run medium. AAAAAAAAAAAAAAAAAAAAAAAAAAAAAARGH!!! I bloody hope not! Probably best if you make sure. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

Not sure if you can rewrite the query that will only join the base tables together, NO view involved. Theoretically, the defined VIEW is for your convenience to query it directly. Personally, I always avoid joining view , I also inform the developers No View JOIN are allowed, If you think the view is not properly defined, we can redefine it for a simple and fast query ready. looks In your case, it could be lot better to join bases tables. Thanks, Frank On Wed, Sep 22, 2010 at 3:29 PM, SUSAN JONES <sjones@clerk.org> wrote: > I have noticed a large number of lock requests being initiated when > executing > this query against a view. > > The sqlexplain.out shows sequential scan on all tables involved in the > application query, which is using a view that also uses a view within > itself. > There are compound indices on all columns used to join and/or filter; > i_statute_number is the Primary Key in statutes, Foreign Key in > statutespeeds. > > Does there need to be separate indices on the individual columns or can the > view be forced to use a directive? > > This is the application query that accesses the view: > > SELECT vwstatutes.*, NVL(vwstatutes.dc_totalfine,0) fine , > NVL(SUM(statutespeeds.dc_totalfine),0) speedfine > FROM vwstatutes, OUTER statutespeeds > WHERE c_citation_type = 'C' > AND d_begindate <= '09/22/2010' > AND '09/22/2010' BETWEEN d_begindate AND NVL(d_enddate,TODAY) > AND vwstatutes.i_statute_number = statutespeeds.i_statute_number > GROUP BY 1,2,3,4,5,6,7 > > Here is the SQL for vwstatutes: > > create view vwstatutes (> > i_statute_number, > > c_statutenumber, > > c_description, > > d_begindate, > > d_enddate, > > c_citation_type, > > dc_totalfine > ) as > select distinct> > s.i_statute_number, > > s.c_statutenumber, > > s.c_description, > > s.d_begindate, > > s.d_enddate, > > vw.c_citation_type, > > s.dc_totalfine > from statute s, vwstatutecittype vw > where s.i_statute_number = vw.i_statute_number; > > Here is the SQL for vwstatutecittype: > > create view vwstatutecittype (> > i_statute_number, > > c_citation_type > ) as > select distinct> > s.i_statute_number, > > s.c_citation_type > from statute_dist_xref s; > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001485f42312fdc2b50490def931

AUS_AGE 30 days AUS_CHANGE 10% AUS_SMALL _TABLES 100 rows AUS_AUTO_RULES "on" AUS_PDQ 10 Auto Update Statistics Evaluation Start Time: 01:00 Stop Time 01:15 Run Frequency 1 00:00:00 Auto Update Statistics Refresh Start Time: 01:16 Stop Time 02:00 Run Frequency 0 00:00:01 Last Time Checked Number of Tables Refreshed 2010-09-22 01:00:06 130 Anyway, I just completed running 'Update Statistics' High on the three underlying tables and it doesn't appear to make a difference when we run the query. :( -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Obnoxio The Clown Sent: Wednesday, September 22, 2010 4:23 PM To: ids@iiug.org Subject: Re: Question about Views - AIX 5.3/IDS 11.50 FC5 [21428] On 22/09/2010 21:18, Jones, Susan wrote: > I'm using AUS with the default settings. Underlying tables vary in size. > 143,000, 8,500 and 1,500. Does that help? I believe small tables are run high > and larger tables are run medium. AAAAAAAAAAAAAAAAAAAAAAAAAAAAAARGH!!! I bloody hope not! Probably best if you make sure. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thanks Frank. I totally agree. I believe this view is used in many places. Not sure what the impact will be to change it. Will have to look into it further. Thanks for your reply. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FRANK Sent: Wednesday, September 22, 2010 4:28 PM To: ids@iiug.org Subject: Re: Question about Views - AIX 5.3/IDS 11.50 FC5 [21429] Not sure if you can rewrite the query that will only join the base tables together, NO view involved. Theoretically, the defined VIEW is for your convenience to query it directly. Personally, I always avoid joining view , I also inform the developers No View JOIN are allowed, If you think the view is not properly defined, we can redefine it for a simple and fast query ready. looks In your case, it could be lot better to join bases tables. Thanks, Frank On Wed, Sep 22, 2010 at 3:29 PM, SUSAN JONES <sjones@clerk.org> wrote: > I have noticed a large number of lock requests being initiated when > executing > this query against a view. > > The sqlexplain.out shows sequential scan on all tables involved in the > application query, which is using a view that also uses a view within > itself. > There are compound indices on all columns used to join and/or filter; > i_statute_number is the Primary Key in statutes, Foreign Key in > statutespeeds. > > Does there need to be separate indices on the individual columns or can the > view be forced to use a directive? > > This is the application query that accesses the view: > > SELECT vwstatutes.*, NVL(vwstatutes.dc_totalfine,0) fine , > NVL(SUM(statutespeeds.dc_totalfine),0) speedfine > FROM vwstatutes, OUTER statutespeeds > WHERE c_citation_type = 'C' > AND d_begindate <= '09/22/2010' > AND '09/22/2010' BETWEEN d_begindate AND NVL(d_enddate,TODAY) > AND vwstatutes.i_statute_number = statutespeeds.i_statute_number > GROUP BY 1,2,3,4,5,6,7 > > Here is the SQL for vwstatutes: > > create view vwstatutes (> > i_statute_number, > > c_statutenumber, > > c_description, > > d_begindate, > > d_enddate, > > c_citation_type, > > dc_totalfine > ) as > select distinct> > s.i_statute_number, > > s.c_statutenumber, > > s.c_description, > > s.d_begindate, > > s.d_enddate, > > vw.c_citation_type, > > s.dc_totalfine > from statute s, vwstatutecittype vw > where s.i_statute_number = vw.i_statute_number; > > Here is the SQL for vwstatutecittype: > > create view vwstatutecittype (> > i_statute_number, > > c_citation_type > ) as > select distinct> > s.i_statute_number, > > s.c_citation_type > from statute_dist_xref s; > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001485f42312fdc2b50490def931 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

On Wed, Sep 22, 2010 at 8:29 PM, SUSAN JONES <sjones@clerk.org> wrote: > I have noticed a large number of lock requests being initiated when > executing > this query against a view. > > In a later message you mention COMMITTED READ. What exactly are you doing and what kind of client are you using? COMMITTED READ will not establish locks in the rows it reads. REPEATABLE READ will do that. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --00c09f89966f4b29370490e0ab0d