Re: getting in a little over my head
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "Oracle L" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 9 Sep 2011 08:27:18 +0100
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
