Quarterly SingingSQL Newsletter #5
April 13, 2007
Introduction
If you wish to unsubscribe at any time, just drop me a line
at dantow@singingsql.com. I’ll likely
eventually get around to setting up a proper email group, through some third
party, so that eventually people can subscribe and unsubscribe without
communicating with me, but this is simplest for a start, I think.
I’m taking a lowest-common-denominator approach to the
format of this newsletter, sending it as a straight-ASCII note so anyone at all
can access it securely, but if you’d rather read something more formatted, go
to OnlineNewsletter05.
I’ll also have links scattered throughout for supplementary materials.
Although this material is copyrighted, feel free to forward
this newsletter, as is, to any friends who you think might be interested.
If you have any comments, ideas for future newsletter
content, or suggestions, feel free to drop me a line, at dantow@singingsql.com – I’m always
happy to hear from you!
SingingSQL News of Public Events
Old Old News
To see a cumulative listing of all news, including old news
from past newsletters, see all
news.
Old news
- I
presented my old seminar, Fixing Broken SQL at
the Northern California Oracle Users Group (NoCOUG)
conference in Redwood Shores,
California, in February.
- I
presented my new seminar, Getting
SQL Performance Right the First Try (Most of the Time)
at the HOTSOS conference in Dallas
in March. To my knowledge, this was the first-ever sing-along HOTSOS presentation! (There is a recurring “chorus”
in this presentation: “If you don’t
know enough to… (something you ought to know
before starting to write the SQL)… You
don’t know enough to write the SQL!” The HOTSOS crowd, good sports
all, sang the chorus along with me, each time this chorus came up! I was,
however, merciful enough to my very tolerant audience to speak the rest of the presentation.
Here is the whitepaper that
goes with it.
New news
- I will
present my new seminar, A Taxonomy
of Problematic SQL at the coming Collaborate07 OAUG
conference in Las Vegas
at 9:45AM, April 17. I will be
posting this presentation and its whitepaper on singingsql.com shortly
after the conference. If you’ll be there, and you’d like to connect, give
me a buzz on my cell, at 650-704-9228!
I’ll just be there on Monday evening and Tuesday.
- I plan
to expand my class offerings outside of the San Francisco Bay Area, but
I’d like to do this mainly through partners who
already know prospective students in their area, likely partners who
receive this newsletter. If you think you can find 6 or more paying
students in your area, and you’d be interested in a chance to share in the
profit from the class, drop me a line, and we can work out a deal for a
class in your area. (If you work for a company, and your company wants a
class just for its own people, I already have an offering for classes
presented on demand for company-internal audiences.)
- I’ve
had a few people ask about coming class offerings, and it occurred to me
that I don’t have a good way to announce them if they come up suddenly,
because I want to keep my promise that this newsletter only comes just
four times a year. Therefore, I’m starting a new list for anyone
interested in announcements of classes and other offerings (such as
presentations to local OAUGs) that might come up
without enough warning to make it into this newsletter. If you are
interested (and there is certainly no commitment in this, on your part)
just drop me a line at dantow@singingsql.com,
and I’ll add you to this new list. Please be sure to include your location
and some range of area within which you’d consider attending something if
I happened by. The area can be as broad as a continent or as narrow as a
town – “Let me know if anything comes up in Asia.” to “Let me know if you
have a class coming to Darwin, Australia.” Even if you’re off the beaten
path, we might get lucky. I won’t send notes for this second list except
to the people in the area where I’m offering something, and I doubt you’ll
see more than a note or two from this new list per year, even if your area
is broad.
Links
Old Links
To see a cumulative listing of all links, including links
already listed in past newsletters, see all links.
New Links
Here you can see my new seminar, Getting SQL
Performance Right the First Try (Most of the Time)
presented at the HOTSOS conference in Dallas
in March, and here is the whitepaper
that goes with it.
.
Featured Article
Remote Links Case Study, a Query Crossing
Links, with a 3000-Plus-Fold Speedup
I recently ran into a slow query containing remote links that
typifies many of the problems I’ve seen in this class of problems, so I thought
it would make a good case study for this newsletter. I’ve stripped away some
distracting detail, to clarify the main points, and also to preserve client
confidentiality. First, here’s a little background:
The problem appeared when a monthly batch job for a large
Oracle Application at a very large company running failed with a “snapshot too
old” error. Before diving into how I fixed the query, I’ll digress with a
discussion of this type of error.
Oracle automatically presents query answers that are
consistent as of a single moment in time, usually the moment the query begins.
This is called a “snapshot.” Many non-Oracle databases achieve read-consistency
(when they bother) by locking (to prevent writes, not to prevent other reads)
just-read rows until the end of the query, so those databases really achieve
read consistency as-of the end of the
query, by preventing changes in already-read rows until the end. Even with
row-level locking, locking out writes during large reads is pretty well a
disaster for concurrency under high read-write load, and in my opinion is a
much more important problem than the old debate about whether the database
should lock at the block level or the row level. Oracle, however, uses rollback
data kept on all write activity to unfailingly reconstruct each read block,
during the course of the query, as-of the snapshot point in time (usually at
the beginning of the query, never later, though occasionally earlier, when you
call for multiple queries to reflect the same point in time). Oracle uses a
fixed amount of space for rollback, an amount chosen by the DBAs,
and the newest rollback data overwrites the oldest, continually. Usually, there
is plenty of rollback space to see any given block as-of at least an hour
earlier (to record at least an hour’s worth of changes, that is), so queries
that run in less than an hour have little danger of seeing this error, unless
the rollback space is undersized. Oracle queries running much longer than an
hour or so, though, may see this error if write activity is high and the query
happens to hit a block toward the end of the query that must roll back a
transaction that happened toward the start of the query.
For many queries, this time-consistent-read feature isn’t
important, but for some it is absolutely critical, as an inconsistent picture
of the data (some rows as-of 8AM, some as-of 9AM, et cetera) would potentially
lead to terrible business actions, such as acting on a false impression that
money moved between accounts (represented by different rows) had been lost, or,
perhaps worse, could lead to complacency about inconsistencies that are real
(money has really been lost, for
example), but that are instead assumed to be the result of an inconsistent data
picture. On some non-Oracle databases, such inconsistent results can appear
when you enable “dirty reads” on that database (usually in order to prevent the
earlier-mentioned horrible concurrency problems for those nasty read locks),
but dirty reads aren’t even possible on Oracle – Oracle doesn’t even offer the
option. Since rolling back to a read-consistent picture isn’t free – it takes
time and can result in those rollback-too-old errors on long-running queries –
we might question whether Oracle ought to at least offer the option of dirty
reads. In my own experience, though, Oracle made the right choice. It is hard
to grasp all the subtle problems that turning off read-consistency can cause,
so that having the dirty-reads option amounts to offering the developers a lot
of rope to hang themselves with – few will fully appreciate the danger, and the
chances of correctly using the hypothetical Oracle dirty-reads feature every
time would simply be too low. When it would be misused (and when it is misused, on non-Oracle databases),
the application defects would tend to be very hard to uncover with testing,
very subtle, very likely to make it into production use for long periods, and,
ultimately, very expensive. On the other hand, in my experience, there is
little real need for this
hypothetical feature on Oracle, because it is always possible (and useful,
anyway!) to
tune the query to be fast enough to avoid the error. Therefore, I really think
of the snapshot-too-old error as really being a “query too slow” error,
although many too-slow queries will not see this error, because many queries
that run in less than an hour are still too slow, and some queries that run
much longer than an hour miss the error.
So, all the defect said was that a certain named module
regularly got this snapshot-too-old error, including once on January 24, and
that this was unacceptable. Fortunately, the team had in place proprietary
scripts that I run at all my Oracle clients that regularly snapshot load-producing
SQL, including with each snapshot the identity of the module (among other
things) that produced the load. With these snapshots, it was a simple matter to
find the text of the slow SQL, and, as a bonus, to see what it was doing during
its long run:
select to_char(snap_date_time, 'HH24:MI:SS') time,
SQL_HASH_VALUE, PLAN_HASH, event_type
from my_snapshots
where module =
'FOOBAR' and snap_date_time between
'24-JAN-2007' and '25-JAN-2007' order by snap_date_time;
Which yielded the results (The repeated SQL_HASH_VALUE, here
is how I found the bad SQL, which was stored in another of our scripts’
tables.):
TIME SQL_HASH_VALUE PLAN_HASH EVENT_TYPE
--------
-------------- ---------- ------------------------------
11:21:52 1362564538 274635492
db file sequential read
11:28:31 1362564538 274635492
SQL*Net message from dblink
11:35:27 1362564538 274635492
SQL*Net message from dblink
11:40:06 1362564538 274635492
SQL*Net message from dblink
11:43:01 1362564538 274635492
SQL*Net message from dblink
11:46:44 1362564538 274635492
SQL*Net message from dblink
11:53:37 1362564538 274635492
SQL*Net message from dblink
12:00:15 1362564538 274635492
SQL*Net message from dblink
12:04:42 1362564538 274635492 SQL*Net
message from dblink
12:09:02 1362564538 274635492
SQL*Net message from dblink
12:13:10 1362564538 274635492
SQL*Net message from dblink
12:20:59 1362564538 274635492
SQL*Net message from dblink
12:25:39 1362564538 274635492
SQL*Net message from dblink
12:26:45 1362564538 274635492
SQL*Net message from dblink
12:33:57 1362564538 274635492
SQL*Net message from dblink
12:36:17 1362564538 274635492
SQL*Net message from dblink
12:44:11 1362564538 274635492
SQL*Net message from dblink
12:50:28 1362564538 274635492
SQL*Net message from dblink
12:51:18 1362564538 274635492
SQL*Net message from dblink
12:58:01 1362564538 274635492
SQL*Net message from dblink
13:03:53 1362564538 274635492
SQL*Net more data from dblink
13:06:20 1362564538 274635492
SQL*Net message from dblink
13:11:26 1362564538 274635492
SQL*Net message from dblink
13:16:51 1362564538 274635492
SQL*Net message from dblink
13:22:28 1362564538 274635492
SQL*Net message from dblink
13:30:20 1362564538 274635492
SQL*Net message from dblink
13:32:12 1362564538 274635492
SQL*Net message from dblink
13:37:42 1362564538 274635492
SQL*Net message from dblink
13:41:27 1362564538 274635492
SQL*Net message from dblink
13:48:07 1362564538 274635492
SQL*Net message from dblink
13:54:12 1362564538 274635492
SQL*Net message from dblink
13:57:04 1362564538 274635492
SQL*Net message from dblink
14:03:21 1362564538 274635492
SQL*Net message from dblink
14:10:05 1362564538 274635492
SQL*Net message from dblink
14:11:11 1362564538 274635492
SQL*Net message from dblink
14:18:36 1362564538 274635492
SQL*Net message from dblink
14:21:07 1362564538 274635492
SQL*Net message from dblink
14:29:33 1362564538 274635492
SQL*Net message from dblink
14:35:48 1362564538 274635492
SQL*Net message from dblink
14:38:14 1362564538 274635492
SQL*Net message from dblink
14:41:27 1362564538 274635492
SQL*Net message from dblink
14:50:53 1362564538 274635492
SQL*Net message from dblink
14:55:29 1362564538 274635492
SQL*Net more data from dblink
14:58:21 1362564538 274635492
SQL*Net message from dblink
15:04:25 1362564538 274635492
SQL*Net message from dblink
15:06:24 1362564538 274635492
SQL*Net message from dblink
15:11:47 1362564538 274635492
SQL*Net message from dblink
15:21:00 1362564538 274635492
SQL*Net message from dblink
15:24:54 1362564538 274635492
SQL*Net message from dblink
15:26:17 1362564538 274635492
SQL*Net message from dblink
15:35:22 1362564538 274635492
SQL*Net message from dblink
15:38:39 1362564538 274635492
SQL*Net message from dblink
15:43:28 1362564538 274635492
SQL*Net message from dblink
15:47:49 1362564538 274635492
SQL*Net message from dblink
15:51:28 1362564538 274635492
SQL*Net message from dblink
15:56:38 1362564538 274635492
SQL*Net message from dblink
16:04:42 1362564538 274635492
SQL*Net message from dblink
16:08:27 1362564538 274635492 SQL*Net message from dblink
16:14:25 1362564538 274635492
SQL*Net message from dblink
16:17:06 1362564538 274635492
SQL*Net message from dblink
16:21:34 1362564538 274635492
SQL*Net message from dblink
16:29:51 1362564538 274635492
SQL*Net message from dblink
16:32:22 1362564538 274635492
SQL*Net message from dblink
16:40:39 1362564538 274635492
SQL*Net message from dblink
16:43:39 1362564538 274635492
SQL*Net message from dblink
16:50:14 1362564538 274635492
SQL*Net message from dblink
16:55:57 1362564538 274635492
SQL*Net message from dblink
16:56:43 1362564538 274635492
SQL*Net message from dblink
17:05:36 1362564538 274635492
SQL*Net message from dblink
17:10:27 1362564538 274635492
SQL*Net message from dblink
17:15:25 1362564538 274635492
SQL*Net message from dblink
17:18:01 1362564538 274635492
SQL*Net message from dblink
17:22:12 1362564538 274635492
SQL*Net message from dblink
17:26:32 1362564538 274635492
SQL*Net message from dblink
17:32:22 1362564538 274635492
SQL*Net message from dblink
17:36:11 1362564538 274635492
SQL*Net message from dblink
17:45:07 1362564538 274635492
SQL*Net message from dblink
17:47:27 1362564538 274635492
SQL*Net message from dblink
17:51:26 1362564538 274635492
SQL*Net message from dblink
17:59:20 1362564538 274635492
SQL*Net message from dblink
18:03:17 1362564538 274635492
SQL*Net message from dblink
18:10:19 1362564538 274635492
SQL*Net message from dblink
18:14:38 1362564538 274635492
SQL*Net message from dblink
18:20:56 1362564538 274635492
SQL*Net message from dblink
18:24:28 1362564538 274635492
SQL*Net message from dblink
18:27:15 1362564538 274635492
SQL*Net message from dblink
18:34:47 1362564538 274635492
SQL*Net message from dblink
18:38:16 1362564538 274635492
SQL*Net message from dblink
18:41:28 1362564538 274635492
SQL*Net message from dblink
18:49:55 1362564538 274635492
SQL*Net message from dblink
18:52:53 1362564538 274635492
SQL*Net message from dblink
18:58:13 1362564538 274635492
SQL*Net message from dblink
19:01:55 1362564538 274635492
SQL*Net message from dblink
19:07:16 1362564538 274635492
SQL*Net message from dblink
19:14:42 1362564538 274635492
SQL*Net message from dblink
19:16:51 1362564538 274635492
SQL*Net message from dblink
19:25:20 1362564538 274635492
SQL*Net message from dblink
19:27:25 1362564538 274635492 SQL*Net
message from dblink
19:35:13 1362564538 274635492
SQL*Net message from dblink
19:37:50 1362564538 274635492
SQL*Net message from dblink
19:44:48 1362564538 274635492
SQL*Net more data from dblink
19:49:41 1362564538 274635492
SQL*Net message from dblink
19:52:39 1362564538 274635492
SQL*Net message from dblink
20:00:41 1362564538 274635492
SQL*Net message from dblink
20:05:11 1362564538 274635492
SQL*Net message from dblink
20:10:22 1362564538 274635492
SQL*Net message from dblink
20:12:23 1362564538 274635492
SQL*Net message from dblink
20:16:20 1362564538 274635492
SQL*Net message from dblink
20:21:46 1362564538 274635492
SQL*Net message from dblink
109
rows selected.
From these snapshots, we can see that virtually the entire
9-hour runtime of the module up to the error came from a single SQL statement
(which in our case took 9 hours to get the snapshot-too-old error), getting a
single execution plan, and that virtually all of that runtime came from waits
for data to return across a database link, presumably work going on in another
instance to return data requested from across a database link.
This tuning problem includes the opportunity to take
advantage of a number of shortcuts while working toward the solution:
The slow query (found through the SQL hash value) was a
12-part UNION query, but boiling away a lot of distracting, unimportant, and
proprietary detail, I can describe the problem with an analogous 2-part UNION:
SELECT
XXX_APPS_PKG.xxx_some_fn(M1.org_id) org_name,
mm1.col1, mmm1.col2 , SUM(d.amount*<function using
columns of mm2 and mm3>) amount
FROM
details d , master1 m1 ,
Master_master1
mm1 , master_master2 mm2 , master_master3 mm3 ,
Master_master_master1
mmm1
WHERE
1 = 1
AND d.type IN (SELECT t.type
FROM
type_sets s ,types t
WHERE
s.set_id = t.set_id
AND s.SET_NAME =
'A_SET_NAME')
AND d.filter_col = :value
AND m1.pkey_id = d.fkey_id
AND m1.fkey1_id = mm1.pkey_id
AND m1.fkey2_id = mm2.pkey_id
AND m1.fkey3_id = mm3.pkey_id
AND mm1.fkey4_id = mmm1.pkey_id GROUP BY
XXX_APPS_PKG.xxx_some_fn(M1.org_id), mm1.col1, mmm1.col2
UNION
SELECT
XXX_APPS_PKG.xxx_some_fn(M1.org_id) org_name,
mm1.col1, mmm1.col2 , SUM(d.amount) amount
FROM
details@REMOTE_LINK.XXXX.COM d
, master1@REMOTE_LINK.XXXX.COM m1 ,
Master_master1@REMOTE_LINK.XXXX.COM mm1
,
Master_master_master1@REMOTE_LINK.XXXX.COM mmm1
WHERE
1 = 1
AND d.type IN (SELECT t.type
FROM
type_sets s ,types t
WHERE
s.set_id = t.set_id
AND s.SET_NAME =
'A_SET_NAME')
AND d.filter_col = :value
AND m1.pkey_id = d.fkey_id
AND m1.fkey1_id = mm1.pkey_id
AND mm1.fkey4_id = mmm1.pkey_id GROUP BY
XXX_APPS_PKG.xxx_some_fn(M1.org_id), mm1.col1, mmm1.col2
The first thing to notice, though, is that the first part
does not involve database links, and virtually the entire 9-hour runtime was
spent waiting on some database-link-related delay, so it is reasonable to
suspect that the first part (really the first 6 parts, which were almost
identical, in the real problem) will run quickly, and if this is true we can safely
ignore it. I therefore stripped out the first part and ran it separately, and
found that the resulting query ran in around 1 second, clearly no tuning issue
for a monthly batch process.
The next shortcut comes from noticing that the second UNION’d part (really the second 6 parts, in the real
problem) essentially reprises the first part (actually is simpler, avoiding a
couple of joins!), except that it reaches the tables of the main-query block
across the database link @REMOTE_LINK.XXXX.COM,
instead of locally. Database links are notorious sources of performance-tuning
headaches for a number of reasons:
- It is
expensive, in general, to combine data across links into a single query
result, where, for example, the equivalent of a NESTED LOOPS path for a
join between two local tables would become hideously expensive if
performed across a high-latency network link between two databases located
thousands of miles apart. (Oracle knows this, and tends to instead create
plans that require far fewer round trips across the link but these
generally lack the benefits that a NESTED LOOPS plan enjoys on a
local-database plan for most queries.)
- Oracle
doesn’t generally look across the link (last I heard, anyway – anyone want
to research this – see below?) to see the linked-to database’s stats on
the linked-to tables, and vice-versa, the linked-to database can’t see the
local database’s statistics, so we have something like two defending
bridge players who must try to coordinate their play for a good result,
but cannot see each others’ hands - the optimizer on each side of the link
is at a substantial disadvantage, here, working out its part of the plan
without seeing the whole picture.
- Efforts
at manual tuning also leave the manual tuner with disadvantages, as
compared to the usual exercise of tuning a query of all local tables:
- We
can’t execute the query or see an execution plan from a normal SELECT ANY
tuning account, but must get access to the account that owns the links,
for semi-normal tuning, since SELECT ANY does not extend to links.
- Even
when we can see the execution plan, it tends to have steps like “REMOTE
ACCESS” that leave a guess as to what’s going on at the other end of the
link, so the resulting execution plans are comparatively uninformative.
- Control
of execution plans involving links is poor, without a good set of hints
for this purpose.
Often these problems are so formidable that the best advice
when considering queries involving links is to avoid them as much as possible,
either keeping local copies of the data, or at most running the link-using
queries as infrequently as possible. The next bit of advice, which bears on the
current problem, is to try to create efficient, isolated parts of the query
that can execute entirely on the remote database – in this case, Oracle will
generally pass the entire isolated remote query block across the link and let
the other end parse it as a local query, with the same well-informed execution
plan it would get if it ran locally on that end. (You can actually test and tune
that query block while logged on at the other end, if you have an account,
there.)
Here, since we already know that the local query block runs
fast, the closely parallel link-using block (which runs on a closely-parallel
database) ought to run fast (or at the worst ought to be easy to tune, by
making the other end get the same result we get at the local end), too, if we
can get it to parse on the other end as if it was a local query, there.
Consider the first link-using block:
SELECT
XXX_APPS_PKG.xxx_some_fn(M1.org_id) org_name,
mm1.col1, mmm1.col2 , SUM(d.amount) amount
FROM
details@REMOTE_LINK.XXXX.COM d
, master1@REMOTE_LINK.XXXX.COM m1 ,
Master_master1@REMOTE_LINK.XXXX.COM mm1
,
Master_master_master1@REMOTE_LINK.XXXX.COM mmm1
WHERE
1 = 1
AND d.type IN (SELECT t.type
FROM
type_sets s ,types t
WHERE
s.set_id = t.set_id
AND s.SET_NAME =
'A_SET_NAME')
AND d.filter_col = :value
AND m1.pkey_id = d.fkey_id
AND m1.fkey1_id = mm1.pkey_id
AND mm1.fkey4_id = mmm1.pkey_id GROUP BY
XXX_APPS_PKG.xxx_some_fn(M1.org_id), mm1.col1, mmm1.col2
There are just two parts of this that reference the local
database, so it can almost be passed
as-is across the link to execute entirely on the other end – the SELECT and GROUP
BY clauses both contain reference to the local function XXX_APPS_PKG.xxx_some_fn(), and the subquery
condition
d.type IN (SELECT t.type
FROM
type_sets s ,types t
WHERE
s.set_id = t.set_id
AND s.SET_NAME =
'A_SET_NAME')
currently must execute locally.
That non-correlated subquery, by the way, read just a
single row at test time, providing a single-value IN list.
I was able to show that the query
SELECT
M1.org_id org_name, mm1.col1, mmm1.col2
, SUM(d.amount) amount
FROM
details@REMOTE_LINK.XXXX.COM d
, master1@REMOTE_LINK.XXXX.COM m1 ,
Master_master1@REMOTE_LINK.XXXX.COM mm1
,
Master_master_master1@REMOTE_LINK.XXXX.COM mmm1
WHERE
1 = 1
AND d.type IN ('THE SINGLE
VALUE')
AND d.filter_col = :value
AND m1.pkey_id = d.fkey_id
AND m1.fkey1_id = mm1.pkey_id
AND mm1.fkey4_id = mmm1.pkey_id GROUP BY
M1.org_id,
mm1.col1, mmm1.col2
ran fast (around a second) when run
locally on the REMOTE_LINK database. The
REMOTE_LINK database did not have a local
row with s.SET_NAME = 'A_SET_NAME', so I couldn’t
test something that reads that type locally on REMOTE_LINK,
but I thought the optimal answer was probably to create a local type set on
REMOTE_LINK that mirrored that set on the
local instance, so I tested an analogous alternative that returned the same
single-row result, taking advantage of the fact that REMOTE_LINK
was a very similar schema, just having different rows:
SELECT
decode(t.type,
'ANOTHER_TYPE', 'THE SINGLE_VALUE',NULL)
FROM
type_sets@REMOTE_LINK.XXXX.COM s
,types@REMOTE_LINK.XXXX.COM t
WHERE
s.set_id = t.set_id
AND s.SET_NAME =
'ANOTHER_SET_NAME_WITH_A_SINGLE_TYPE'
The REMOTE_LINK-local
query (to test on REMOTE_LINK, since I
didn’t have ready access to the production link-owning account on the local
instance), then, was:
SELECT
M1.org_id org_name, mm1.col1, mmm1.col2
, SUM(d.amount) amount
FROM
details d , master1 m1 ,
Master_master1
mm1 ,
Master_master_master1
mmm1
WHERE
1 = 1
AND d.type IN (SELECT decode(t.type, 'ANOTHER_TYPE',
'THE
SINGLE_VALUE',NULL)
FROM
type_sets s ,types t
WHERE
s.set_id = t.set_id
AND s.SET_NAME =
'ANOTHER_SET_NAME_WITH_A_SINGLE_TYPE')
AND d.filter_col = :value
AND m1.pkey_id = d.fkey_id
AND m1.fkey1_id = mm1.pkey_id
AND mm1.fkey4_id = mmm1.pkey_id GROUP BY
M1.org_id,
mm1.col1, mmm1.col2
And this, too, ran very fast locally on REMOTE_LINK,
so it looked like the right approach. The only remaining problems, then, were
how to create the same functional result as the original query and to verify
that this all worked with links. For the functional result, the new query block
would almost be right as long as we created (and kept in sync with the local
instance LOCAL) a new flex_value_set with SET_NAME =
'A_SET_NAME' retuning
the same single-item list 'THE SINGLE_VALUE' as the set on LOCAL. The final remaining
trick was to select and group by the locally-executed function result XXX_APPS_PKG.xxx_some_fn(M1.org_id) without letting this
bit of local activity trigger Oracle to fail to pass the query block across to
the REMOTE_LINK end. I could have tried
leaving that local function call on (someone want to try this, with an
analogous query, and let us know if Oracle does the smart thing? – I’ll credit
you in the next newsletter with the answer, if you get it) – the smart thing
for Oracle to do would be to execute a raw-rows query of
SELECT
M1.org_id, mm1.col1, mmm1.col2 , d.amount
FROM
details d , master1 m1 ,
Master_master1
mm1 ,
Master_master_master1
mmm1
WHERE
1 = 1
AND d.type IN (SELECT t.type
FROM
type_sets s ,types t
WHERE
s.set_id = t.set_id
AND s.SET_NAME =
'A_SET_NAME')
AND d.filter_col = :value
AND m1.pkey_id = d.fkey_id
AND m1.fkey1_id = mm1.pkey_id
AND mm1.fkey4_id = mmm1.pkey_id
on the REMOTE_LINK
end, passing the individual rows resulting across the link to LOCAL, then to
evaluate XXX_APPS_PKG.xxx_some_fn(M1.org_id) on the LOCAL end and
perform the GROUP BY on the LOCAL end, and this might perform reasonably well,
if Oracle took this alternative. I didn’t bother testing this alternative,
though (anyone want to try?), since an even better alternative I was sure would
leave the execution of the remote blocks to cleanly parse on the REMOTE_LINK
end was:
SELECT
XXX_APPS_PKG.xxx_some_fn(M1.org_id) org_name,
mm1.col1, mmm1.col2 , SUM(d.amount*<function using
columns of mm2 and mm3>) amount
FROM
details d , master1 m1 ,
Master_master1
mm1 , master_master2 mm2 , master_master3 mm3 ,
Master_master_master1
mmm1
WHERE
1 = 1
AND d.type IN (SELECT t.type
FROM
type_sets s ,types t
WHERE
s.set_id = t.set_id
AND s.SET_NAME =
'A_SET_NAME')
AND d.filter_col = :value
AND m1.pkey_id = d.fkey_id
AND m1.fkey1_id = mm1.pkey_id
AND m1.fkey2_id = mm2.pkey_id
AND m1.fkey3_id = mm3.pkey_id
AND mm1.fkey4_id = mmm1.pkey_id GROUP BY
XXX_APPS_PKG.xxx_some_fn(M1.org_id), mm1.col1, mmm1.col2
UNION
select XXX_APPS_PKG.xxx_some_fn(org_id)
org_name,
col1,
col2,
sum(amount) amount
FROM
(
SELECT
M1.org_id, mm1.col1, mmm1.col2 , SUM(d.amount) amount
FROM
details@REMOTE_LINK.XXXX.COM d
, master1@REMOTE_LINK.XXXX.COM m1 ,
Master_master1@REMOTE_LINK.XXXX.COM mm1
,
Master_master_master1@REMOTE_LINK.XXXX.COM mmm1
WHERE
1 = 1
AND d.type IN (SELECT t.type
FROM
type_sets@REMOTE_LINK.XXXX.COM s
,types@REMOTE_LINK.XXXX.COM t
WHERE
s.set_id = t.set_id
AND s.SET_NAME =
'A_SET_NAME')
AND d.filter_col = :value
AND m1.pkey_id = d.fkey_id
AND m1.fkey1_id = mm1.pkey_id
AND mm1.fkey4_id = mmm1.pkey_id
GROUP
BY M1.org_id, mm1.col1, mmm1.col2)
group by XXX_APPS_PKG.xxx_some_fn(org_id),
col1, col2;
This had the advantage of being less likely to present
Oracle with difficulty achieving parsing of the problematic parts on the
REMOTE_LINK end, and it had the advantage that most of the grouping was done on the
REMOTE_LINK end, before sending rows across the link, so potentially far fewer rows
would need to cross the link and would need to have the function XXX_APPS_PKG.xxx_some_fn() performed. (In fact,
making a similar change to the top half of the query might help just a tiny
bit, saving function calls for so many rows, but since the top half of the
query runs already in seconds, and runs once a month, no one cares.) Therefore,
I didn’t bother exploring the alternatives. I couldn’t test this on LOCAL
without access to the applications account on LOCAL, so I tested on a LOCAL_DEV
applications account, which we could
reach. Even there, though, I didn’t feel free to create a new type set, so I
tested with the analogous
SELECT
decode(t.type,
'ANOTHER_TYPE',
'THE
SINGLE_VALUE',NULL)
FROM
type_sets s ,types t
WHERE
s.set_id = t.set_id
AND s.SET_NAME = 'ANOTHER_SET_NAME_WITH_A_SINGLE_TYPE'
substituted into the subquery just for testing purposes. The resulting second
half of the original 12-part query ran in about 5 seconds, even across the
link, essentially as fast as the first half, making a full 12-part query that
should run in about 10 seconds.
Research Problems
Old Research Problems
To see a cumulative listing of all
research problems (the one below, and the earlier ones from the earlier
newsletters), see all
research problems. There has been no solution submitted yet for the
earlier-published research problems.
Does Oracle
in its very latest incarnations “look across the link” to see statistics from
the other end of the link when optimizing the execution plan of a query that
combines data on more than one database? Does any non-Oracle RDBMS do this?
Will any
version of Oracle (or any other RDBMS) parse and optimize a raw (PL/SQL-function-free)
query remotely, then pass the raw rows to the local database to perform any
function calls and any GROUP BY operation that might depend on those function
calls?
©2007 Dan Tow, All rights reserved
Home