Re: getting in a little over my head
From: Tim Gorman <tim_at_evdbt.com>
Date: Thu, 08 Sep 2011 21:30:02 -0600
Message-ID: <4E69883A.8090502_at_evdbt.com>
Joe,
> I have a developer who came to me and asked me to make a certain sql statement 'as fast as possible'. Said statement was running against a view that was sort of nasty with sub-queries and a union thrown in. Cutting to the chase, I created a MV that looks like this:
> Not a lot of data....36,474 rows.
> SSS3_at_idm1> select count(*) from accountnotemview;
> COUNT(*)
> ----------
> 36474
> WHERE (linktype = 'Quote'
> AND objid IN
> (SELECT objectid FROM quote WHERE accountid = 128847
> ))
> OR (linktype = 'Policy'
> AND objid IN
> (SELECT objectid FROM pol_policy WHERE accountid = 128847 AND logid = 1
> ))
> /
> Explain plan is:
> SSS3_at_idm1> @/opt/app/oracle/product/10.2.0/rdbms/admin/utlxpls.sql
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------------------------------------
> Plan hash value: 3127273909
> I figured it might be a good idea to get rid of the full access of the MV so I created an index:
> SSS3_at_idm1> create index joe1 on accountnotemview (linktype);
> Index created.
> New explain is:
> SSS3_at_idm1> @/opt/app/oracle/product/10.2.0/rdbms/admin/utlxpls.sql
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------
> Plan hash value: 4264724585
> Here is where I get lost. The original clock time against the original view was about 1.5 seconds. The clock time against the materialized view is about 0.5 seconds, with or without the index. These numbers were garnered simply by 'set timing on' in
> sqlplus. I can see that it is accessing fewer rows but it is also accessing the MV twice by index (presumably once for each value
> of linktype).
> My question is this: does the 2nd explain plan look any "better" than the first? Since the timings are so close I am wondering if
> it's just a wash because the amount of data is relatively small.
> Any/all comments welcome.
> Thanks,
> -joe
> 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
Date: Thu, 08 Sep 2011 21:30:02 -0600
Message-ID: <4E69883A.8090502_at_evdbt.com>
Joe,
The 2nd explain-plan definitely looks better than the first. Look at line #3 in the first explain-plan, below...
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 41 | 87986 | 170 (1)| 00:00:03 | | 1 | COUNT | | | | | | |* 2 | FILTER | | | | | | | 3 | MAT_VIEW ACCESS FULL | ACCOUNTNOTEMVIEW | 40790 | 83M| 170 (1)| 00:00:03 | |* 4 | TABLE ACCESS BY INDEX ROWID | QUOTE | 1 | 15 | 4 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | QUOTE_BY_OBJECTID | 1 | | 3 (0)| 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID| POL_POLICY | 1 | 16 | 4 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | POL_POLICY_BY_OBJECTID | 3 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------
It is estimated to retrieve 40,790 rows totalling 83Mb. Now look at the two similar steps in the 2nd explain-plan, lines 4-5 and 9-10 below, and total up the same numbers...
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1246 | 2611K| 106 (0)| 00:00:02 | | 1 | COUNT | | | | | | | 2 | CONCATENATION | | | | | | |* 3 | FILTER | | | | | | | 4 | MAT_VIEW ACCESS BY INDEX ROWID| ACCOUNTNOTEMVIEW | 623 | 1305K| 49 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | JOE1 | 249 | | 44 (0)| 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID | POL_POLICY | 1 | 16 | 4 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | POL_POLICY_BY_OBJECTID | 3 | | 1 (0)| 00:00:01 | |* 8 | FILTER | | | | | | | 9 | MAT_VIEW ACCESS BY INDEX ROWID| ACCOUNTNOTEMVIEW | 623 | 1305K| 49 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | JOE1 | 249 | | 44 (0)| 00:00:01 | |* 11 | TABLE ACCESS BY INDEX ROWID | POL_POLICY | 1 | 16 | 4 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | POL_POLICY_BY_OBJECTID | 3 | | 1 (0)| 00:00:01 | |* 13 | TABLE ACCESS BY INDEX ROWID | QUOTE | 1 | 15 | 4 (0)| 00:00:01 | |* 14 | INDEX RANGE SCAN | QUOTE_BY_OBJECTID | 1 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------
Totalling lines 4-5 and 9-10, you see a total estimate of 1,246 table rows being retrieved after 498 index entries being retrieved (as opposed to 40,790 table rows being retrieved in the 1st explain-plan), and an estimated 2.61Mb of data being retrieved (as opposed to 83.00Mb of data being retrieved in the 1st explain-plan).
While I don't think the "cost" metric calculated by the Oracle optimizer means anything in *absolute* terms, I think it means something in *comparative* terms in a situation like this. Here, we have the same SQL statement using two different plans, and for the 1st explain-plan the metric is 170 and for the 2nd explain-plan the metric is 106. The purpose of the "cost" metric is to provide a summarized point of comparison between plans. That in itself indicates that the 2nd explain-plan is "better" (i.e. less expensive) than the 1st, at least according to the estimations calculated by the cost-based optimizer. One can argue about how close the optimizer's estimations approach reality, but that "cost" metric is what makes the cost-based optimizer choose one explain-plan over another, so it is certainly worth using in this situation.
Hope that makes sense?
Please let me know what you think?
Thanks!
-Tim
On 9/8/2011 3:53 PM, Sweetser, Joe wrote:
> SQL gurus,
>
> I have a developer who came to me and asked me to make a certain sql statement 'as fast as possible'. Said statement was running against a view that was sort of nasty with sub-queries and a union thrown in. Cutting to the chase, I created a MV that looks like this:
>
> SSS3_at_idm1> desc accountnotemview
> Name Null? Type
> ----------------------------------------- -------- ----------------------------
> CREATED_USERNAME VARCHAR2(100)
> CREATED_TIME TIMESTAMP(6)
> OBJID VARCHAR2(50)
> LINKTYPE VARCHAR2(100)
> TASKORDER NUMBER
> STATUS CHAR(7)
> MESSAGE VARCHAR2(4000)
>
> Not a lot of data....36,474 rows.
> SSS3_at_idm1> select count(*) from accountnotemview;
>
> COUNT(*)
> ----------
> 36474
>> FROM accountnotemview acv
> SQL statement is (ultimately, the accountID will be a bind variable):
> SELECT rownum AS pk,
> 128847 AS accountId,
> NULL AS logid,
> 0 AS deleted,
> CREATED_TIME AS CREATEDATE,
> CREATED_USERNAME AS USERNAME,
> 'GENERAL' AS CATEGORY,
> MESSAGE AS note
> WHERE (linktype = 'Quote'
> AND objid IN
> (SELECT objectid FROM quote WHERE accountid = 128847
> ))
> OR (linktype = 'Policy'
> AND objid IN
> (SELECT objectid FROM pol_policy WHERE accountid = 128847 AND logid = 1
> ))
> /
>
> Explain plan is:
> SSS3_at_idm1> @/opt/app/oracle/product/10.2.0/rdbms/admin/utlxpls.sql
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------------------------------------
> Plan hash value: 3127273909
>
> ---------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> ---------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 41 | 87986 | 170 (1)| 00:00:03 |
> | 1 | COUNT | | | | | |
> |* 2 | FILTER | | | | | |
> | 3 | MAT_VIEW ACCESS FULL | ACCOUNTNOTEMVIEW | 40790 | 83M| 170 (1)| 00:00:03 |
> |* 4 | TABLE ACCESS BY INDEX ROWID | QUOTE | 1 | 15 | 4 (0)| 00:00:01 |
> |* 5 | INDEX RANGE SCAN | QUOTE_BY_OBJECTID | 1 | | 3 (0)| 00:00:01 |
> |* 6 | TABLE ACCESS BY INDEX ROWID| POL_POLICY | 1 | 16 | 4 (0)| 00:00:01 |
> |* 7 | INDEX RANGE SCAN | POL_POLICY_BY_OBJECTID | 3 | | 1 (0)| 00:00:01 |
> ---------------------------------------------------------------------------------------------------------
>
> I figured it might be a good idea to get rid of the full access of the MV so I created an index:
> SSS3_at_idm1> create index joe1 on accountnotemview (linktype);
>
> Index created.
>
> New explain is:
> SSS3_at_idm1> @/opt/app/oracle/product/10.2.0/rdbms/admin/utlxpls.sql
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------
> Plan hash value: 4264724585
>
> ------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> ------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1246 | 2611K| 106 (0)| 00:00:02 |
> | 1 | COUNT | | | | | |
> | 2 | CONCATENATION | | | | | |
> |* 3 | FILTER | | | | | |
> | 4 | MAT_VIEW ACCESS BY INDEX ROWID| ACCOUNTNOTEMVIEW | 623 | 1305K| 49 (0)| 00:00:01 |
> |* 5 | INDEX RANGE SCAN | JOE1 | 249 | | 44 (0)| 00:00:01 |
> |* 6 | TABLE ACCESS BY INDEX ROWID | POL_POLICY | 1 | 16 | 4 (0)| 00:00:01 |
> |* 7 | INDEX RANGE SCAN | POL_POLICY_BY_OBJECTID | 3 | | 1 (0)| 00:00:01 |
> |* 8 | FILTER | | | | | |
> | 9 | MAT_VIEW ACCESS BY INDEX ROWID| ACCOUNTNOTEMVIEW | 623 | 1305K| 49 (0)| 00:00:01 |
> |* 10 | INDEX RANGE SCAN | JOE1 | 249 | | 44 (0)| 00:00:01 |
> |* 11 | TABLE ACCESS BY INDEX ROWID | POL_POLICY | 1 | 16 | 4 (0)| 00:00:01 |
> |* 12 | INDEX RANGE SCAN | POL_POLICY_BY_OBJECTID | 3 | | 1 (0)| 00:00:01 |
> |* 13 | TABLE ACCESS BY INDEX ROWID | QUOTE | 1 | 15 | 4 (0)| 00:00:01 |
> |* 14 | INDEX RANGE SCAN | QUOTE_BY_OBJECTID | 1 | | 3 (0)| 00:00:01 |
> ------------------------------------------------------------------------------------------------------------
>
> Here is where I get lost. The original clock time against the original view was about 1.5 seconds. The clock time against the materialized view is about 0.5 seconds, with or without the index. These numbers were garnered simply by 'set timing on' in
> sqlplus. I can see that it is accessing fewer rows but it is also accessing the MV twice by index (presumably once for each value
> of linktype).
>
> My question is this: does the 2nd explain plan look any "better" than the first? Since the timings are so close I am wondering if
> it's just a wash because the amount of data is relatively small.
>
> Any/all comments welcome.
>
> Thanks,
> -joe
> 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
> > > > >
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 08 2011 - 22:30:02 CDT