Q
Problem solve Get help with specific problems with your technologies, process and projects.

Reducing parse time for distributed query

I have a distributed query accessing data in one ORACLE 9.2.0.4 database from another 9.2.0.4 database. The parse time for the query seems excessive. Are there any techniques/parameters that can help reduce this? The following tkprof output illustrates the issue:

SELECT COL1, COL2 
FROM REMOTE_TABLE 
WHERE COL1=:K00000 AND COL2=:K00001 
call     count       cpu    elapsed 
------- ------  -------- ---------- 
Parse     2926     38.21      43.49 
Execute   2926      0.96       4.35 
Fetch     2926      0.42       0.39 
------- ------  -------- ---------- 
total     8778     39.59      48.25 

You're averaging about .013 seconds of CPU per parse, which is not excessive for hard parsing. I suspect that the hard parsing is necessitated by the fact that the table is remote. Have you considered creating a local materialized view of the remote table and querying that instead? Remote operations are always going to be more expensive than local ones.

Dig Deeper on Oracle database performance problems and tuning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close