473,898 Members | 2,759 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL re-parsing on query executed against a remote database usingdatabase links

Can anyone help to understand why re-parsing occurs on a remote database
(using database links), even though we are using a prepared statement on
the local database:

Scenario:
========

We have an schema (s1) on an Oracle 9i database with database links
pointing to a schema (s2) on another Oracle 9i database.

We have a SQL statement which joins tables from schemas s1 to tables in
schema s2 by the use of database links (tablename@link name). The query
is executed against schema s1.

The SQL statement is prepared and re-executed over many iterations
(using bind variables - Java/jdbc). The statement is closed only when we
have completed the iterations.

For both databases, the optimizer mode is CHOOSE and ALL the tables have
been analyzed.

Results:
=======

We have found that when we monitor / analyse the database, the parsing
rate is high, which is impacting the performance of the process.

NOTE: We also ran the same tests using Oracle 8i (for both databases)
instead of 9i. In these tests the parsing rate was very low (zero). This
is what I would have regarded as the expected behaviour.

The same high parsing rate is also seen when both schemas reside on the
same database, and database links are used to join tables from the 2
schemas. If however, the SQL query is amended to refer to the remote
tables in schema s2 by schema.tablenam e rather than tablename@linkn ame,
then the parsing rate is again very low (as expected) and the process
runs much more quickly.
Oct 14 '05 #1
0 4138

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4326
by: Nel | last post by:
I have a question related to the "security" issues posed by Globals ON. It is good programming technique IMO to initialise variables, even if it's just $foo = 0; $bar = ""; Surely it would be better to promote better programming than rely on PHP to compensate for lazy programming?
4
6437
by: Craig Bailey | last post by:
Anyone recommend a good script editor for Mac OS X? Just finished a 4-day PHP class in front of a Windows machine, and liked the editor we used. Don't recall the name, but it gave line numbers as well as some color coding, etc. Having trouble finding the same in an editor that'll run on OS X. -- Floydian Slip(tm) - "Broadcasting from the dark side of the moon"
4
18552
by: Alan Walkington | last post by:
Folks: How can I get an /exec'ed/ process to run in the background on an XP box? I have a monitor-like process which I am starting as 'exec("something.exe");' and, of course the exec function blocks until something.exe terminates. Just what I /don't/ want. (Wouldn't an & be nice here! Sigh) I need something.exe to disconnect and run in the background while I
1
3708
by: John Ryan | last post by:
What PHP code would I use to check if submitted sites to my directory actually exist?? I want to use something that can return the server code to me, ie HTTP 300 OK, or whatever. Can I do this with sockets??
10
4238
by: James | last post by:
What is the best method for creating a Web Page that uses both PHP and HTML ? <HTML> BLA BLA BLA BLA BLA
2
2385
by: Topspin | last post by:
I'm running PHP on Windows, but that's just the local test... in production it will be on Apache. I am not using PHP as a CGI. I want to perform redirection of the user's browser if the user isn't a valid user. I don't want to use the refresh meta tag or javascript to do this. I want to do this with some sort of server command. Does anyone know how to make this work? Here's my code... <?php
3
3497
by: presspley | last post by:
I have bought the book on advanced dreamweaver and PHP recently. I have installed MySQL and PHP server but am getting an error on the $GET statement show below. It says there is a problem with the variable $GET but $GET is not a variable, I thought it came from the page that calls the PHP file? if(($_GET)==""){ this gets an error
2
106633
by: sky2070 | last post by:
i have two file with jobapp.html calling jobapp_action.php <HTML> <!-- jobapp.html --> <BODY> <H1>Phop's Bicycles Job Application</H1> <P>Are you looking for an exciting career in the world of cyclery? Look no further! </P> <FORM NAME='frmJobApp' METHOD=post ACTION="jobapp_action.php"> Please enter your name:
1
13669
by: Brian | last post by:
I have an array like this: $events = array( array( '2003-07-01', 'Event Title 1', '1' //ID Number (not unique) ), array( '2003-07-02',
2
5320
by: JW | last post by:
I wanted have this as part of a flood control script: <? echo ("Flood control in place - please wait " . $floodinterval . " seconds between postings."); sleep(5); // go back two pages echo "<script>window.history.go(-2);</script>"; exit; ?>
0
9839
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11256
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10857
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10481
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7187
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5877
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6076
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4705
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4295
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.