Differerence acces path linked server Oracle 9/10

[blom0344]

We are experiencing huge performance issues with fetching data from a linked Oracle database after upgrading the receiving database to Oracle 10g.
It looks as though all data is first fetched at the receiving end, cause temp spaces are floaded. The query ran with no problem between 2 Oracle 9i databases.
Can anyone suggest a solution for enforcing the query at the backend.
We're looking into using a specific hint to enforce this.
So far , we expect that /*+driving_site*/ could be a solution, but we are not sure about its use.

Ties Blom

 

[hoinz]

Hello,

not sure if this will help, but one thing that might be useful to check:
Are there any other queries producing heavy loads, running on your Oracle 10g database only? And if so, do they run as fast as before?
Or aren't there any such queries, and so you can't be sure how fast they would run now?

In other words: Can we rule out that it is a general performance problem, not related to database links?

regards

 

[blom0344]

No, this is an extraction process from a transactional database on Oracle 9i to a datawarehouse server on Oracle10g.
On the last server the temp space is filled rapidly to the limit (and we have enlarged it quite a bit)
Extraction runs outside working hours, with no activity on the databases.
The problem occured directly after switching to Oracle 10g, but all other extraction jobs still work fine..

Ties Blom

 

[hoinz]

Hello Ties,

not sure if this answered my question; I was a bit vague it seems.
When I mentioned other queries producing heavy loads, I had in mind that you could compare various tasks in your Oracle 10g system, and find out if only data extraction via links suffers from poor performance, or if you can't be sure about other jobs too.
I was not talking about other tasks running at the same time and so affecting extraction.

And the reason I asked about a general performance problem, is because I had such problems after an upgrade to 10g. But I have no idea, if only database links are affected, sorry.

Ok, in case of a general problem, let me suggest to check if SGA and other memory areas have the same size as before. Otherwise just ignore my posting.

regards

 

[blom0344]

Well, so far there is just 1 single extraction that causes real problems. To be honest it is a very ugly one that performs joins on a set of remote tables with a temp set (an aggregated set to be precise).
We can rewrite it into more manageable pieces, but this would involve multiple steps (= a lot of additional work)
If we can enforce a solution that matches the original Oracle 9i performance we could be up and running within minutes.

Ties Blom

 

[SantaMufasa]

Ties,

My experience reveals that about 90% of performance issues trace back to either incorrect/missing indexes and/or stale statistics.

How did you populate your tables in your new 10g database from your 9i database? Can you confirm that you have gathered new statistics recently on your 10g objects?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[blom0344]

That is a good question. Not sure and can't ask one of our DBA's at the moment.
However, the datalink is used to fetch data from the 9i end only, so it is not a matter of combining data from the 2 sources.
I imagine that there is a standard update protocol for updating a version 9i database to 10g? I mean updating it with the data in place?

Ties Blom

 

[SantaMufasa]

Ties, Please tell us more about the specifics of your processing. It appears to me from your description, that you are using database links to harvest data from your 9i database, and then the harvested data ends up in tables on your 10g instance. Is that correct?

If so, then unless you are aware of the statistics being regathered, then the problem may arise from bad statistics. You can get an idea of how badly your statistics are out of date with this query:

select nvl(to_char(trunc(last_analyzed),'dd-MON-yyyy'),'never Analyzed')Analyzed,count(*)
from user_tables
group by trunc(last_analyzed)
order by trunc(last_analyzed);

ANALYZED         COUNT(*)
-------------- ----------
05-DEC-2006            93
never Analyzed         36

Notice in my data, above, for the schema against which I ran the script, that the statistics are extremely stale or non-existent.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[blom0344]

Yes, this is a correct assumption (I like the term ' harvesting').
Now, on gathering data about statistics,are you proposing the 9i or 10g side?
The opinions are divided on which optimizer is used (receiving or delivering side)

Ties Blom

 

[SantaMufasa]

It would be interesting to see both sides. If both sides have recently gathered statistics, then we have ruled out statistics as an issue altogether.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[dbtoo2001]

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements006.htm#BABEGIJC

 

[dbtoo2001]

It may help to see the query:

While cost-based optimization is performed transparently to the user, it is unable to improve the performance of several distributed query scenarios. Specifically, if your distributed query contains any of the following, cost-based optimization is not effective:

Aggregates

Subqueries

Complex SQL

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/ds_appdev.htm#i1007699

 

[blom0344]

I checked with one of our DBA's. All statistics are recent, no other performance issues. It is just this lone extraction that goes astray.

Ties Blom

 

[SantaMufasa]

Ties,

Not that I don't trust your DBA's, but I would prefer seeing the evidence of your recently gathered statistics.

You do not need a DBA to run the script I posted (on 19 Jun 07 11:54)...the script accesses USER_TABLES, to which you have full access. Please run the script and post the results here.

Also, if you could confirm that there are indexes in place for the columns that your SELECT's WHERE clause accesses, that is useful information, as well.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[blom0344]

Santa, I know. Though I am no Oracle DBA myself, I once worked as parttime DB2 DBA. Problem is that as a developer I cannot investigate at clients sites. I have to channel this through our DBA's and work my way slowly past their professional pride
I'll post the outcome in any case..
Thanks for your input..

Ties Blom

 

[taupirho]

Please also check your startup parameter optimizer_mode.
Get your DBA to run this query against both databases

select substr(name,0,512) "Parameter Name",
substr(value,0,512) "Value"
from v$parameter2
where name = 'optimizer_mode'

 

[blom0344]

Problem was finally solved by adding the 'all_rows' hint. This enforced the same execution plan as with Oracle9i.
Optimizer_mode for 10g is possibly set to a different value, due to the fact that when upgrading the setting for datawarehousing was chosen.

Thanks for all your input..

Ties Blom