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@xxxxxxxx>
To: "Oracle L" <oracle-l@xxxxxxxxxxxxx>
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


Other related posts: