Slow SQL when using dblink to a remote db
Hi,
I have the attached SQL which when run in a remote db takes about 30s but when executed directly, it takes less than 500ms! Both database are Oracle 9.2.0.1. A TNSPing to the remote database takes only 80ms.
Can someone advise how I can make the SQL run just as fast on the remote db? Could this also be a Oracle bug? Because I notice if I change to decode, the timing improves dramatically and performs almost the same as remote.
I have the attached SQL which when run in a remote db takes about 30s but when executed directly, it takes less than 500ms! Both database are Oracle 9.2.0.1. A TNSPing to the remote database takes only 80ms.
Can someone advise how I can make the SQL run just as fast on the remote db? Could this also be a Oracle bug? Because I notice if I change to decode, the timing improves dramatically and performs almost the same as remote.
SELECT vitemgroupdesc,
SUM (CASE
WHEN (a.nsalesyear * 100 + a.nsalesmonth) BETWEEN 200701 AND 200704
THEN DECODE (i.vbaseuom, 'LB', a.nbaseqty * 0.453592, 'GAL', a.nbaseqty * 3.7854, a.nbaseqty)
ELSE 0
END
) qty1
FROM monthendsales a, itemmst i, itemclassificationmst ic
WHERE (a.vitemcode = i.vitemcode AND i.vitemclassificationcode = ic.vitemclassificationcode)
AND a.vdivcode IN ('123')
GROUP BY vitemgroupdesc
Explain Plan From the Remote Database (Time Taken 39 s)
SELECT STATEMENT CHOOSECost: 126 Bytes: 274,833 Cardinality: 3,393
6 SORT GROUP BY Cost: 126 Bytes: 274,833 Cardinality: 3,393
5 NESTED LOOPS Cost: 101 Bytes: 274,833 Cardinality: 3,393
3 HASH JOIN Cost: 101 Bytes: 74,921 Cardinality: 973
1 REMOTE Cost: 2 Bytes: 45,731 Cardinality: 973
2 REMOTE Cost: 97 Bytes: 444,570 Cardinality: 14,819
4 REMOTE Cost: 4 Bytes: 12 Cardinality: 3
Explain Plan from Actual DB (Time Taken 234 ms)
SELECT STATEMENT CHOOSECost: 390 Bytes: 274,833 Cardinality: 3,393
9 SORT GROUP BY Cost: 390 Bytes: 274,833 Cardinality: 3,393
8 NESTED LOOPS Cost: 322 Bytes: 274,833 Cardinality: 3,393
6 MERGE JOIN Cost: 322 Bytes: 74,921 Cardinality: 973
3 SORT JOIN Cost: 43 Bytes: 45,731 Cardinality: 973
2 TABLE ACCESS BY INDEX ROWID NFSDBA.MONTHENDSALES Cost: 2 Bytes: 45,731 Cardinality: 973
1 INDEX RANGE SCAN NON-UNIQUE NFSDBA.IND_MTHENDSALS_DIVCODE_DIVMST Cost: 1 Cardinality: 389
5 SORT JOIN Cost: 280 Bytes: 444,570 Cardinality: 14,819
4 TABLE ACCESS FULL NFSDBA.ITEMMST Cost: 97 Bytes: 444,570 Cardinality: 14,819
7 INDEX UNIQUE SCAN UNIQUE NFSDBA.PK_ITEMCLASSIFICATIONMST Bytes: 12 Cardinality: 3
Yes, but additionally you have to compute the statistics over the tables and indexes.
ASKER
I can add an index on the vitemgroupdesc column in the NFSDBA.itemmst table, but even without additional index, the performance of the SQL is already ok at a fast 234ms when executed directly. The problem just happen when I execute remotely from another database using dblinks.
As mentioned, I also tried changing the SELECT CASE to DECODE and the remote execution becomes just as fast??
As mentioned, I also tried changing the SELECT CASE to DECODE and the remote execution becomes just as fast??
i had this too once, sometimes the processing is done on the local side.
Like when using TO_CHAR and dates the processing is done local, not remote...
maybe same thing
my problem was
SELECT * FROM LOGS
WHERE TO_CHAR(DT_CREATED, 'YYYY') >= '2007'
This was processed on the local side (processing 1.4 million records -> 2 minutes)
SELECT * FROM LOGS
WHERE DT_CREATED > TO_DATE('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
This was processed on the remote side (processing 1.4 million records -> 4 seconds )
Like when using TO_CHAR and dates the processing is done local, not remote...
maybe same thing
my problem was
SELECT * FROM LOGS
WHERE TO_CHAR(DT_CREATED, 'YYYY') >= '2007'
This was processed on the local side (processing 1.4 million records -> 2 minutes)
SELECT * FROM LOGS
WHERE DT_CREATED > TO_DATE('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
This was processed on the remote side (processing 1.4 million records -> 4 seconds )
> The problem just happen when I execute remotely from another database using dblinks.
try the hint "driving_site"
Forces query execution to be done at a user selected site rather than at a site selected by the database. This hint is useful if you are using distributed query optimization.
/*+ DRIVING_SITE([@queryblock]
SELECT /*+ DRIVING_SITE(departments) */ *
FROM employees, departments@rsite
WHERE employees.department_id = departments.department_id;
try the hint "driving_site"
Forces query execution to be done at a user selected site rather than at a site selected by the database. This hint is useful if you are using distributed query optimization.
/*+ DRIVING_SITE([@queryblock]
SELECT /*+ DRIVING_SITE(departments) */ *
FROM employees, departments@rsite
WHERE employees.department_id = departments.department_id;
To speed up the dblink create a view based on the query.
Place this view on the remote site.
Create dblink on the local site.
Query the view from the local site using the dblink.
I have just experienced the same problem and this
approach speed up the query 8-10 times.
Using view on the remote site you force the remote site to do all
process steps decreasing the transfer of the data.
Also take in account that you use Sort operation - GROUB BY.
This will force the sort to be done at the local site.
This also increases the volume of the transfered data.
To avoid this - use remote view.
Place this view on the remote site.
Create dblink on the local site.
Query the view from the local site using the dblink.
I have just experienced the same problem and this
approach speed up the query 8-10 times.
Using view on the remote site you force the remote site to do all
process steps decreasing the transfer of the data.
Also take in account that you use Sort operation - GROUB BY.
This will force the sort to be done at the local site.
This also increases the volume of the transfered data.
To avoid this - use remote view.
ASKER
Driving Site hints have no impact. Schwertner, I am also unable to use that suggested option as I can only query from the remote database.
Anymore ideas?
Anymore ideas?
If you can not create a view on the remote site use Donald Burleson suggestion:
Instead
SELECT a,b,c FROM gh@klm;
Use
SELECT * FROM (SELECT a,b,c FROM gh@klm);
Burleson says that this is the same, but my experiments this week
shows that this is not so.
Creating views on the remote site is the best option for me.
Also have a look here:
http://www.dba-oracle.com/t_sql_dblink_performance.htm
Instead
SELECT a,b,c FROM gh@klm;
Use
SELECT * FROM (SELECT a,b,c FROM gh@klm);
Burleson says that this is the same, but my experiments this week
shows that this is not so.
Creating views on the remote site is the best option for me.
Also have a look here:
http://www.dba-oracle.com/t_sql_dblink_performance.htm
it's hard to tell but if ALL of the tables are on the remote site, try adding a HINT
/*+ DRIVING_SITE(a) */
that will make the query run on the remote database, and just return the results.
/*+ DRIVING_SITE(a) */
that will make the query run on the remote database, and just return the results.
SELECT /*+ DRIVING_SITE(a) */
vitemgroupdesc,
SUM (CASE
WHEN (a.nsalesyear * 100 + a.nsalesmonth) BETWEEN 200701 AND 200704
THEN DECODE (i.vbaseuom, 'LB', a.nbaseqty * 0.453592, 'GAL', a.nbaseqty * 3.7854, a.nbaseqty)
ELSE 0
END
) qty1
FROM monthendsales a, itemmst i, itemclassificationmst ic
WHERE (a.vitemcode = i.vitemcode AND i.vitemclassificationcode = ic.vitemclassificationcode)
AND a.vdivcode IN ('123')
GROUP BY vitemgroupdesc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
9.2.0.1 has many bugs.
You can easily upgrade to 9.2.0.8.
Driving site doesn't impact the speed even in Oracle 11g.
Only creating and using a view is a working solution.
You can easily upgrade to 9.2.0.8.
Driving site doesn't impact the speed even in Oracle 11g.
Only creating and using a view is a working solution.
with an index on the table should solve the issue
4 TABLE ACCESS FULL NFSDBA.ITEMMST Cost: 97 Bytes: 444,570 Cardinality: 14,819