Re: getting in a little over my head
- From: Tim Gorman <tim@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 08 Sep 2011 21:30:02 -0600
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@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@idm1> select count(*) from accountnotemview;
>
> COUNT(*)
> ----------
> 36474
>
> 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
> FROM accountnotemview acv
> 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@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@idm1> create index joe1 on accountnotemview (linktype);
>
> Index created.
>
> New explain is:
> SSS3@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.
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
