From: "Sweetser, Joe" <JSweetser@icat.com>
To: Oracle L <oracle-l@freelists.org>
Subject: RE: getting in a little over my head
Thread-Topic: getting in a little over my head
Date: Fri, 9 Sep 2011 20:20:46 +0000
Message-ID: <D18D6513433DF04394041EA42B53E91C57884BF5@ICATEXCH2.ICAT.com>
References: <D18D6513433DF04394041EA42B53E91C57884719@ICATEXCH2.ICAT.com>
 <8C70DF85B6294ED0A15FAAD932821796@Primary>
 <7CF8509D95504B2892B6A21ED96E2163@Primary>
In-Reply-To: <7CF8509D95504B2892B6A21ED96E2163@Primary>
Accept-Language: en-US
Content-Language: en-US
Content-type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: JSweetser@icat.com

Issue has been resolved, as it were.  The responses from Tim, Stephane and Jonathan provided me with an excellent starting point for a conversation with the developer.  After going over each of their responses, we recreated the materialized view and included the accountid field in the MV.  We also converted that field from a char to a number.  Those changes allowed the SQL statement to be changed from the rather ugly first one of:

SELECT rownum      AS pk,
  92968            AS accountId,
  NULL             AS logid,
  0                AS deleted,
  CREATED_TIME     AS CREATEDATE,
  CREATED_USERNAME AS USERNAME,
  'GENERAL'        AS CATEGORY,
  MESSAGE          AS note
FROM accountnotemview acv
WHERE (linktype = 'Quote'
AND objid      IN
  (SELECT objectid FROM quote WHERE accountid = 92968
  ))
OR (linktype = 'Policy'
AND objid   IN
  (SELECT objectid FROM pol_policy WHERE accountid = 92968 AND logid = 1
  ))
/

To:

select * from accountnotemview where accountid=92968;

That alone had the statement executing is less than 0.1 seconds.  And after adding an index on the accountid and gathering stats on the table the time got down to 0.01 seconds or less!  I say that because I know some time had to pass even if 'set timing on' is showing:

Elapsed: 00:00:00.00

The original timing I was trying to improve on was about 1.5 seconds.

All in all, this exercise was a great lesson in why DBA's and developers should work together and also affirmed YET AGAIN what a great resource oracle-l is for the oracle community.

Off to crack a cold one very shortly,
-joe

Here are the explain plans before and after indexing:

SSS3@idm1> explain plan for select * from accountnotemview where accountid=92968;

Explained.

Elapsed: 00:00:00.01
SSS3@idm1> @/opt/app/oracle/product/10.2.0/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 1582430164

-----------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |     8 | 16816 |   160   (1)| 00:00:02 |
|*  1 |  MAT_VIEW ACCESS FULL| ACCOUNTNOTEMVIEW |     8 | 16816 |   160   (1)| 00:00:02 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

   1 - filter("ACCOUNTID"=92968)

Note
-----
   - dynamic sampling used for this statement

17 rows selected.

Elapsed: 00:00:00.01
SSS3@idm1> create index joe1 on accountnotemview(accountid);

Index created.

Elapsed: 00:00:00.08

<<Also gathered stats in another window: exec dbms_stats.gather_table_stats (ownname=>'SSS3', tabname=>'ACCOUNTNOTEMVIEW', estimate_percent=>100, method_opt => 'for all indexed columns size auto',degree => dbms_stats.default_degree);>>

SSS3@idm1> explain plan for select * from accountnotemview where accountid=92968;

Explained.

Elapsed: 00:00:00.00
SSS3@idm1> @/opt/app/oracle/product/10.2.0/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 3112908439

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |     2 |   260 |     2   (0)| 00:00:01 |
|   1 |  MAT_VIEW ACCESS BY INDEX ROWID| ACCOUNTNOTEMVIEW |     2 |   260 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN             | JOE1             |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   2 - access("ACCOUNTID"=92968)

14 rows selected.


-----Original Message-----
From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Jonathan Lewis
Sent: Friday, September 09, 2011 2:48 AM
To: Oracle L
Subject: Re: getting in a little over my head


> In the second plan
> you do the work of accessing the pol_policy table twice for each row in the
> materialized view rather than once per row

That should have been "for each row selected from"
And the doubling only occurs for one of the two link_types.


Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com


----- Original Message -----
From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
To: "Oracle L" <oracle-l@freelists.org>
Sent: Friday, September 09, 2011 8:27 AM
Subject: Re: getting in a little over my head


Tim's given you an argument for the second plan looking better, here's an
argument for the first looking better: In the second plan
you do the work of accessing the pol_policy table twice for each row in the
materialized view rather than once per row - the work done
in the subqueries may be the largest resource user.  (And that may also be the
case in the original query - so adding the account_id
to the quote index and both the account_id and logid to the policy index may be
the best way to improve performance.)


--
http://www.freelists.org/webpage/oracle-l


Confidentiality Note: This message contains information that may be confidential and/or privileged. If you are not the intended recipient, you should not use, copy, disclose, distribute or take any action based on this message. If you have received this message in error, please advise the sender immediately by reply email and delete this message. Although ICAT Managers, LLC, Underwriters at Lloyd's, Syndicate 4242, scans e-mail and attachments for viruses, it does not guarantee that either are virus-free and accepts no liability for any damage sustained as a result of viruses. Thank you.

--
http://www.freelists.org/webpage/oracle-l