| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query with a Remote Table over a DB-Link
You can 10046 level-8 trace the remote side of the query (an after logon
trigger works great for this), and then you can see exactly what
preparation steps the remote database is requested to perform by the
local one.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11
Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Next event: NCOAUG Training Day, Aug 16 Chicago
-----Original Message-----
Chitale
Sent: Thursday, August 15, 2002 10:14 AM
To: Multiple recipients of list ORACLE-L
It seems that I have been labouring under a misapprehension for some
years
now !
So it would seem as if each remote database handles it's own
portion of the query and returns only the result set to the
initiating database.
However, I cannot understand the part that says
"When choosing the execution plan for a distributed statement, the
optimizer considers the available indexes on remote databases just as it
does indexes on the local database. ....."
How does the local optimizer know about
1) Remote Indexes
2) Statistics on Remote Tables/Indexes
Hemant
At 12:43 AM 15-08-02 -0800, you wrote:
>Hemant,
>
>It looks like even 7.3 was able to treat a remote query differently -
cut
>and paste from 7.3 Tuning guide below. Although it does not
specifically
>state this, the 'fragmenting query' seems to indicate this....
>
>FWIW!
>John Kanagaraj
>Oracle Applications DBA
>DB Soft Inc
>Work : (408) 970 7002
>
>Listen to great, commercial-free christian music 24x7x365 at
>http://www.klove.com
>
>** The opinions and facts contained in this message are entirely mine
>and do not reflect those of my employer or customers **
>
>
>
>Optimizing Distributed Statements
>The optimizer chooses execution plans for SQL statements that access
>data on remote databases in much the same way it chooses executions
>for statements that access only local data:
>* If all the tables accessed by a SQL statement are collocated on the
>same remote database, Oracle sends the SQL statement to that
>remote database. The remote Oracle instance executes the
>statement and sends only the results back to the local database.
>* If a SQL statement accesses tables that are located on different
>databases, Oracle decomposes the statement into individual
>fragments, each of which accesses tables on a single database.
>Oracle then sends each fragment to the database it accesses. The
>remote Oracle instance for each of these databases executes its
>fragment and returns the results to the local database, where the
>local Oracle instance may perform any additional processing the
>statement requires.
>When choosing the execution plan for a distributed statement, the
>optimizer considers the available indexes on remote databases just as
it
>does indexes on the local database. If the statement uses the
cost-based
>approach, the optimizer also considers statistics on remote databases.
>Furthermore, the optimizer considers the location of data when
>estimating the cost of accessing it. For example, a full scan of a
remote
>table has a greater estimated cost than a full scan of an identical
local
>table.
>-----Original Message-----
>Sent: Wednesday, August 14, 2002 9:54 AM
>To: Multiple recipients of list ORACLE-L
>
>
>
>I had always understood that a query which joins a remote table
>to a local table would pull the entire remote table over and then
>do the join locally.
>
>However, the example in the Oracle9i Database Performance
>Tuning Guide and Reference (9.2), Chapter 2 Optimizer Operations
>"How the CBO evaluates remote operations"
>[the URL is
>http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/serve
r.920
>/a96533/opt_ops.htm#1004878]
>seems to show that the REMOTE operation actually does a query
>with a filter against the remote table before pulling data across :
>How the CBO Evaluates Remote Operations
>
>The remote operation indicates that there is a table from another
database
>being accessed through a database link. Example 2-10 has a remote
driving
>table:
>
>Example 2-10 How the CBO Evaluates a Query with a Remote Driving Table
>SELECT c.customer_name, count(*)
> FROM ra_customers c, so_headers_all_at_oe h
> WHERE c.customer_id = h.customer_id
> AND h.order_number = :b1
>GROUP BY c.customer_name;
>
>Plan
>--------------------------------------------------
>SELECT STATEMENT
> SORT GROUP BY
> NESTED LOOPS
> REMOTE
> TABLE ACCESS BY INDEX ROWID RA_CUSTOMERS
> INDEX UNIQUE SCAN RA_CUSTOMERS_U1
>
>
>Remote Database Query Obtained from the Library Cache
>SELECT "ORDER_NUMBER","CUSTOMER_ID"
> FROM "SO_HEADERS_ALL" "H"
> WHERE "ORDER_NUMBER"=:"SYS_B_0";
>
>
>The next example on how the CBO evaluates a query with
>a Local Driving table is similar -- it passes a WHERE clause
>to filter the Remote Table.
>The example even goes on to show how a Hint could be
>applied to drive the query on the Remote Table.
>
>
>Is this (that a WHERE clause is applied to the
>Remote table and that the full Remote table is not
>copied over) true ? Has this been the behaviour since 8i ?
>
>What about the Rule-Based Optimizer ? Would it behave
>the same way ?
>
>Hemant K Chitale
>Now using Eudora Email. Try it !
>
>My home page is : http://hkchital.tripod.com -- Please see the
official
>ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET:
>hkchital_at_singnet.com.sg Fat City Network Services -- (858) 538-5051
FAX:
>(858) 538-5051 San Diego, California -- Public Internet access /
Mailing
>Lists
Hemant K Chitale
Now using Eudora Email. Try it !
My home page is : http://hkchital.tripod.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET: hkchital_at_singnet.com.sg Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: cary.millsap_at_hotsos.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Aug 15 2002 - 11:13:40 CDT
 |
 |