Yes, this is probably a stupid question but...
Pat
We have an application that stores operational data as a single master
table and a set of child tables. Based on the class of data, a
different child table gets joined in e.g.
master inner_join pets -> gives us pet data
master inner_join cars -> gives us car data
It works reasonably well, and it makes the developer's lives easier.
Normally the working set of records is quite small as well e.g. < 1k
records at a time so the actual queries being issued are something
like:
select <some fields> from a inner join b on a .sys_id = b.sys_id where
a.<some indexed column> = <some fairly selective criteria>
There's one part of the application though that computes counts via
the not terribly complex:
select count(*) from a inner join b on a sys_id = b.syss_id where
a.<some indexed column> = <some selective criteria>
Recently though we've had some customers with some unusual data shape
such that the particular count query (above) is joining 200k records
or so together to implement the count.
Looking at profile on the database, that count alone is something like
68% of the server CPU load. Not surprisingly, I'd dearly love to do
something about this. Problem is, I'm stumped.
Question I have is:
Short of constructing a summary table (which is probably a non starter
in this environment), is there anything I could to to speed up the
return time of a count query like the above?
Apologies if this is a stupid question, but as you can tell from the
batch of posts I've been putting on the newsgroup I'm on something of
a jihad to clean up some of the Oracle performance bottlenecks in this
app, that that is definately one of them.
Ana C. Dent
55e038...@f63g2000hsf.googlegroups.com:
>
> Question I have is:
>
> Short of constructing a summary table (which is probably a non starter
> in this environment), is there anything I could to to speed up the
> return time of a count query like the above?
>
Ready, Fire, Aim!
If you don't know specifically where the time is being spent,
you'd only be guessing what change is most optimal.
Run your query with SQL_TRACE=TRUE & run the results thru tkprof.
Post the results & EXPLAIN PLAN back here is you still need more asisstance
Pat
> Pat <pat.ca...@service-now.com> wrote in news:0653963d-1d32-4e85-9bae-
> 55e038b9e...@f63g2000hsf.googlegroups.com:
>
>
>
> > Question I have is:
>
> > Short of constructing a summary table (which is probably a non starter
> > in this environment), is there anything I could to to speed up the
> > return time of a count query like the above?
>
> Ready, Fire, Aim!
>
> If you don't know specifically where the time is being spent,
> you'd only be guessing what change is most optimal.
>
> Run your query with SQL_TRACE=TRUE & run the results thru tkprof.
>
> Post the results & EXPLAIN PLAN back here is you still need more asisstance
I'm working over a remote desktop at the moment so I can't cut and
paste the plan so I'll transcribe:
select count(*) as recordcount from (a inner join b on a.sys_id =
b.sys_id) where a.active = 1
Table has aprox 180k rows, of which 151k have active = true
SELECT STATEMENT
_SORT AGGREGATE
__NESTED LOOPS
___INDEX RANGE SCAN (index on a.active, a.sys_id)
___INDEX UNIQUE SCAN (index on b.sys_id)
Total Query time: 2.39 seconds
I've got a covering index on the primary table and an index on the
child table's join condition, so there's no actual table reads going
on here e.g. it's just an index join. I have current stats on the
table (as of last night). From what I can see this is not an absurdly
bad optimizer plan, but then I suspect there's something I'm not
seeing, hence my post here.
hpuxrac
>
> - Show quoted text -
The nested loops technique works pretty well with a limited volume of
matching records but if you are really getting counts of 200k at times
well it's a non-performer from the get go.
A hash join might perfom a ( little ) better ... but still may not be
adequate.
What release level and patchset are you running on exactly? What kind
of hardware platform?
Does the application really require an absolutely accurate count in
these circumstances and if so how is this accurate count going to be
used?
Re-design may be called for here or at least some bypass type
techniques.
If absolutely correct counts are not required one way of thinking
about a bypass is limiting the count to some base number of rows via
rownum ( select count(*) from whatever the tables are with join
condition AND rownum <= 10,100,1000 ).
Another bypass technique might be to create a table that has counts
already calculated and refresh that table on a periodic basis. Then
point the query against the pre-aggregated table.
Pat
Unfortunately (from a database efficiency standpoint), exact counts
are required, or at least expected by our current user base. Summary
tables are probably not going to work either since I can't predict
what the count is going to be over. In this case, this user is
hammering the subset active=1, but tomorrow it may be based on some
other specifier.
Switching this to a hash join sounds like it has promise (or at least
be worth a shot).
Is there an optimizer hint I can add to force a hash join in this
case? Be worth a shot.
I have to admit I am a bit surprised this is all that slow though.
It's a modern box with 3.0 ghz CPUs in it (intel) and virtually no
load apart from this query. He can basically monopolize an entire core
(and he does according to vmstat), and it's still taking him about 2.2
seconds to finish. Seems like that's an aweful lot of CPU time just to
join a couple of hundred thousand index nodes together and count the
result. Virtually no physical IOs involved here either. Far as I can
tell, this is pure CPU load.
10.2.0.4 64 bit on RHEL 5.0.
Charles Hooper
> On Apr 12, 5:51 pm, hpuxrac <johnbhur...@sbcglobal.net> wrote:
> > The nested loops technique works pretty well with a limited volume of
> > matching records but if you are really getting counts of 200k at times
> > well it's a non-performer from the get go.
>
> > A hash join might perfom a ( little ) better ... but still may not be
> > adequate.
>
> are required, or at least expected by our current user base. Summary
> tables are probably not going to work either since I can't predict
> what the count is going to be over. In this case, this user is
> hammering the subset active=1, but tomorrow it may be based on some
> other specifier.
>
> Switching this to a hash join sounds like it has promise (or at least
> be worth a shot).
>
> Is there an optimizer hint I can add to force a hash join in this
> case? Be worth a shot.
>
> I have to admit I am a bit surprised this is all that slow though.
> It's a modern box with 3.0 ghz CPUs in it (intel) and virtually no
> load apart from this query. He can basically monopolize an entire core
> (and he does according to vmstat), and it's still taking him about 2.2
> seconds to finish. Seems like that's an aweful lot of CPU time just to
> join a couple of hundred thousand index nodes together and count the
> result. Virtually no physical IOs involved here either. Far as I can
> tell, this is pure CPU load.
>
> 10.2.0.4 64 bit on RHEL 5.0.
I set up a simple test case to test the suggestion to try a hash join,
rather than a nested loops join. Your results may vary slightly from
these results.
First, two tables that have sufficiently wide enough columns so that
Oracle does not try a full table scan:
CREATE TABLE T1 (
SYS_ID NUMBER(10) NOT NULL,
ACTIVE NUMBER(1) NOT NULL,
C3 VARCHAR2(255),
C4 VARCHAR2(255),
C5 VARCHAR2(255),
C6 VARCHAR2(255),
C7 VARCHAR2(255),
C8 VARCHAR2(255),
C9 VARCHAR2(255),
C10 VARCHAR2(255));
CREATE UNIQUE INDEX T1_IND1 ON T1(ACTIVE,SYS_ID);
CREATE TABLE T2 (
SYS_ID NUMBER(10) NOT NULL,
C2 VARCHAR2(255),
C3 VARCHAR2(255),
C4 VARCHAR2(255),
C5 VARCHAR2(255),
C6 VARCHAR2(255),
C7 VARCHAR2(255),
C8 VARCHAR2(255),
C9 VARCHAR2(255),
C10 VARCHAR2(255));
CREATE UNIQUE INDEX T2_IND1 ON T2(SYS_ID);
Now, insert rows into the T1 table (note that the DECODE contains an
error, reverse the last 1 and 0 in the DECODE, but this will work as
is by slightly adjusting the SQL statement):
INSERT INTO
T1
SELECT
ROWNUM,
DECODE(SIGN(MOD(ROWNUM,100)-84),1,1,0),
DBMS_RANDOM.STRING('A',255),
DBMS_RANDOM.STRING('A',255),
DBMS_RANDOM.STRING('A',255),
DBMS_RANDOM.STRING('A',255),
DBMS_RANDOM.STRING('A',255),
DBMS_RANDOM.STRING('A',255),
DBMS_RANDOM.STRING('A',255),
DBMS_RANDOM.STRING('A',255)
FROM
DUAL
CONNECT BY
LEVEL<=180000;
Now to save time, we will use the T1 table to build the T2 table:
INSERT INTO
T2
SELECT
SYS_ID,
C3,
C3,
C4,
C5,
C6,
C7,
C8,
C9,
C10
FROM
T1;
COMMIT;
Now, collect statistics on the tables and indexes:
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE);
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE);
Your query, rewritten to work with the above (note that a.active is
specified as 0 in this case):
select count(*) as recordcount from (T1 a inner join T2 b on a.sys_id
=
b.sys_id) where a.active = 0;
The DBMS_XPLAN showing actual timing:
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows
| A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
00:00:02.45 | 3000 |
| 2 | NESTED LOOPS | | 1 | 90000 | 153K|
00:00:02.14 | 3000 |
| 3 | INDEX FAST FULL SCAN| T2_IND1 | 1 | 180K| 180K|
00:00:00.18 | 382 |
|* 4 | INDEX UNIQUE SCAN | T1_IND1 | 180K| 1 | 153K|
00:00:00.81 | 2618 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."ACTIVE"=0 AND "A"."SYS_ID"="B"."SYS_ID")
The above shows that the T1_IND1 index was probed 180,000 times. Note
that the plan order is not the same as what you posted, likely because
your table B (T2) contains more than 1 row per SYS_ID.
Plan statistics from a 10046 trace, 3000 consistent reads:
(Rows 1) SORT AGGREGATE (cr=3000 pr=0 pw=0 time=0 us)
(Rows 153000) NESTED LOOPS (cr=3000 pr=0 pw=0 time=2255352 us
cost=121 size=1170000 card=90000)
(Rows 180000) INDEX FAST FULL SCAN T2_IND1 (cr=382 pr=0 pw=0
time=266066 us cost=104 size=900000 card=180000)
(Rows 153000) INDEX UNIQUE SCAN T1_IND1 (cr=2618 pr=0 pw=0
time=0 us cost=0 size=8 card=1)
Slightly modifying your query with a hint to force a hash join:
select /*+ use_hash(a b) */
count(*) as recordcount from (T1 a inner join T2 b on a.sys_id =
b.sys_id) where a.active = 0;
The DBMS_XPLAN showing actual timing:
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows
| A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
00:00:02.42 | 818 | | | |
|* 2 | HASH JOIN | | 1 | 90000 | 153K|
00:00:02.13 | 818 | 3624K| 1381K| 5510K (0)|
|* 3 | INDEX FAST FULL SCAN| T1_IND1 | 1 | 90000 | 153K|
00:00:00.16 | 436 | | | |
| 4 | INDEX FAST FULL SCAN| T2_IND1 | 1 | 180K| 180K|
00:00:00.18 | 382 | | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."SYS_ID"="B"."SYS_ID")
3 - filter("A"."ACTIVE"=0)
The above executed 0.03 seconds faster, and the consistent reads
dropped to 818.
(Rows 1) SORT AGGREGATE (cr=818 pr=0 pw=0 time=0 us)
(Rows 153000) HASH JOIN (cr=818 pr=0 pw=0 time=1352636 us
cost=458 size=1170000 card=90000)
(Rows 153000) INDEX FAST FULL SCAN T1_IND1 (cr=436 pr=0 pw=0
time=238965 us cost=120 size=720000 card=90000)
(Rows 180000) INDEX FAST FULL SCAN T2_IND1 (cr=382 pr=0 pw=0
time=278655 us cost=104 size=900000 card=180000)
With just an ORDERED hint to force the same plan order as what you
posted:
| Id | Operation | Name | Starts | E-Rows | A-Rows
| A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
00:00:02.24 | 3075 |
| 2 | NESTED LOOPS | | 1 | 90000 | 153K|
00:00:01.99 | 3075 |
|* 3 | INDEX FAST FULL SCAN| T1_IND1 | 1 | 90000 | 153K|
00:00:00.16 | 436 |
|* 4 | INDEX UNIQUE SCAN | T2_IND1 | 153K| 1 | 153K|
00:00:00.72 | 2639 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."ACTIVE"=0)
4 - access("A"."SYS_ID"="B"."SYS_ID")
0.21 seconds faster than the first execution, even though the
consistent reads increased to 3075
(Rows 1) SORT AGGREGATE (cr=3075 pr=0 pw=0 time=0 us)
(Rows 153000) NESTED LOOPS (cr=3075 pr=0 pw=0 time=2026659 us
cost=128 size=1170000 card=90000)
(Rows 153000) INDEX FAST FULL SCAN T1_IND1 (cr=436 pr=0 pw=0
time=239888 us cost=120 size=720000 card=90000)
(Rows 153000) INDEX UNIQUE SCAN T2_IND1 (cr=2639 pr=0 pw=0
time=0 us cost=0 size=5 card=1)
In the plans, look closely at the timings of each step in the plan.
The timings of a child step will roll up into the parent step
(indicated by the indentation in the plan). The actual scan of the
indexes happens reasonably fast, it is the join that takes the
majority of the elapsed time.
Interesting fun with mathematics, which may not be entirely relevant.
On a computer with a computer marketed as having a 1333MHz bus speed,
using 333MHz quad pumped dual channel memory chips, each memory clock
cycle retrieves up to 32 bytes in 0.000000003003003 seconds (maximum
transfer speed of 10,162.35 MB per second), and the CPU core will wait
for this duration on every memory access. A standard 8KB block
requires a minimum of 256 memory clock cycles to be read, resulting in
a minimum delay of 0.000000768768768 seconds to read an 8KB block from
system memory. If you require the computer to perform 180,000 8KB
reads (assuming the data is not cached in the CPU registers, L1, L2,
or L3 caches), it will take a minimum of 0.138 seconds (consistent
reads of 8KB blocks might take 5-10 times longer). What seems like a
simple problem becomes a bit complicated when you dig into the
details.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Pat
Thanks! I ran a test case with a slightly different (and larger)
dataset and I'm getting 3.9 seconds with nested loops and 2.2 seconds
with the hash join, even though the optimizer plan tells me nested
loops are cheaper.
Think I'm going to see about putting the hint into practise.
hpuxrac
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.
Pat
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?
Jonathan Lewis
"Pat" <pat....@service-now.com> wrote in message
news:b1ed3a55-a7f3-4ba1...@b64g2000hsa.googlegroups.com...
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 Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Pat
wrote:
> "Pat" <pat.ca...@service-now.com> wrote in message
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.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.
Jonathan Lewis
Pat,
The costing in this plan are a little surprising - but there are
two possibilities that I can think of might explain them
What's your value for optimizer_index_caching - has it been
set to something close to 100 ?
Is this (very specifically) running 10.2.0.1 ?
I guess it's is also possible that the NLS index is introducing a side
effect that I've not come across before - can you show
us the plan you get (including costs and predicates as above)
when you hint the hash join suggested by Charles Hooper.
--
Regards
Jonathan Lewis
Robert Klemme
An alternative approach may be to use a trigger and a counter table.
You might face locking issues though. That depends on how much
concurrency you have on that table, how much original rows fold down
into one row and on the duration of your transactions.
> 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.
That's always good to do some math beforehand.
Kind regards
robert
Shakespeare
Unfortunaltely, my testdatabase could not handle the test scripts (it's
sized too small I think) but would reversing the columns in
CREATE UNIQUE INDEX T1_IND1 ON T1(ACTIVE,SYS_ID);
to
CREATE UNIQUE INDEX T1_IND1 ON T1(SYS_ID,ACTIVE);
do some good here? (Since active is not very discriminating and the join is
on SYS_ID)? Would like to have tested this myself, but no success...
Shakespeare
Pat
You'd think it might help a little bit, but I've got an index on just
sys_id as well (for other reasons, not for this query), and the
optimizer seems to not want to use it so he thinks at least that he
gets enough discrimination out of active to make it worthwhile.
In case anyone is curious though, I did run down some more information
here and it makes sense (to me at least).
It was kind of weird that the optimizer kept choosing nested loop
joins even when a hash join was demonstrably much faster. Turns out
one of my colleages (in an effort to work around an unrelated issue)
had decided to set:
optimizer_index_caching=90
optimizer_index_cost_adj=10
With those settings in place, he ran nested loops consistently.
With the default
optimizer_index_caching=0
optimizer_index_cost_adj=100
He runs a hash join across this same data set.
Gonna run some more tests, but if that looks good I'm going to look at
adjusting those parameters on production box. Issue they were
implemented to work around was resolved another way in any event.
Thanks for all the helps,
--- Pat