RE: getting in a little over my head
- From: "Sweetser, Joe" <JSweetser@xxxxxxxx>
- To: Oracle L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 9 Sep 2011 20:20:46 +0000
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxxxxxxxxxx>
To: "Oracle L" <oracle-l@xxxxxxxxxxxxx>
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.)
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
