Re: getting in a little over my head

We don't have enough information to judge - we only have enough information to know that the optimizer has made several mistakes. Can you please supply the predicate sections of the execution plans so we can check exactly what Oracle is doing
Did you collect stats on the MV after creating it - the plans suggest not. Is the account_id you chose representative, or is there a massive skew in the data that means it was a special case ?
Can you re-run the test, flushing the buffer_cache before each test and using the /*+ gather_plan_statistics */ hint
and dbms_xplan.display_cursor with the 'allstats last' option to report the results

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.)

Both

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

• Original Message ----- From: "Sweetser, Joe" <JSweetser_at_icat.com> To: "Oracle L" <oracle-l_at_freelists.org> Sent: Thursday, September 08, 2011 10:53 PM Subject: getting in a little over my head

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:

  COUNT(*)

     36474

| 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 |
---------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------
| 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.

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