Re: Yes, this is probably a stupid question but...

From: Pat <pat.casey_at_service-now.com>
Date: Sun, 13 Apr 2008 15:18:37 -0700 (PDT)
Message-ID: <394e6ddb-24ca-4013-81ba-aa3e2de49491@m44g2000hsc.googlegroups.com>

On Apr 13, 2:52 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Pat" <pat.ca..._at_service-now.com> wrote in message
>
> news:b1ed3a55-a7f3-4ba1-a6fd-53c0f0548bd5_at_b64g2000hsa.googlegroups.com...
>
>
>
> > On Apr 13, 11:20 am, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> >> On Apr 12, 5:55 pm, Pat <pat.ca..._at_service-now.com> wrote:
>
> >> Another option to consider is looking at is using/creating a
> >> materialized view to support the count query.
>
> >> Going down the posts in the other part of this thread, if you force
> >> the count query to use a hash join, this might help the queries that
> >> return a high count ( 200k ) but could ( possibly severely ) impact
> >> the queries that ( running now with a nested loop ) return a small
> >> count.
>
> > I'm going to show my ignorance here, but would a materialized view
> > help if there's a lot of transactional volume on the table e.g. if
> > there are, say, 500 or so updates per hour across that table? Wouldn't
> > I need to refresh the view every few seconds to keep it current? Or is
> > this the part I'm missing?
>
> There is an option to "refresh on commit".
>
> It's not a good idea to use it on a very busy table as the overheads
> are dramatic (a single row update resulted in about 45 statement
> executions on the commit the last time I tested it). However, at
> one update every 7 seconds, you may find that the overheads are
> acceptable.
>
> There are a couple of oddities with read-consistency, though.
>
> Check what happens if you update the table, then (from the same session)
> run the query BEFORE committing. Your version of the query has to go to
> the base tables to see your update rather than using the materialized
> view -
> and you may then be caught in a trap where other users start sharing the
> cursor that visited the base tables rather than the view. Unless you have
> very good control over how the updates and queries synchronise, you
> could get caught in the trap of randomly changing performance.
>
> A completely different thought - this may be a case where you want to
> get literal values into your query and build histograms to support the
> query. You had 150,000 out of 180,000 rows with status 'active'.
> If you create a histogram on the column, and use literals in your SQL
> you may find that the optimizer uses the 'damage-limiting' hash join on
> the worst case, and the nested loop on the other cases. The cost of
> the extra optimisation stages may be insignificant compared to the benefit
> of doing the right thing every time.
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

In this case, looks like having a histogram didn't help. The active column has two potential values (0 or 1), so it's essentially a boolean. Even with a 2 columns histogram on it, the optimizer is still going down nested loops.

I may take a crack at putting in an a refreshing materialized view though. Need to sit down and do a little match on relative transaction frequency though, want to make sure I end up saving cycles rather than burning them.

Thanks again!

SQL> analyze table task compute statistics for columns "active" size 2;

Table analyzed.

SQL> explain plan for select count(*) from incident inner join task on incident."sys_id" = task."sys_id" where task."active" = 1;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---

Plan hash value: 1023873955

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | 1 | 68 | 32 (7)| 00:00:01 |

|   1 |  SORT AGGREGATE        |		|     1 |    68 |	     |		|
|   2 |   NESTED LOOPS	       |		|   155K|    10M|    32   (7)|
00:00:01 |
|*  3 |    INDEX FAST FULL SCAN| ACTIVE_TASK	|   197K|  6755K|    29
(0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN   | INCIDENT_SYSID |     1 |    33 |

1 (0)| 00:00:01 |

---

PLAN_TABLE_OUTPUT

---

Predicate Information (identified by operation id):

---

   3 - filter("TASK"."active"=1)
   4 -
access(NLSSORT(INTERNAL_FUNCTION("sys_id"),'nls_sort=''BINARY_CI''')=NLSSO         

RT(INTERNAL_FUNCTION("TASK"."sys_id"),'nls_sort=''BINARY_CI'''))

18 rows selected. Received on Sun Apr 13 2008 - 17:18:37 CDT