?
Solved

wrong result (or cached result) is returning when a query is run on a dblink!

Posted on 2010-01-08
53
Medium Priority
?
654 Views
Last Modified: 2013-12-19
Hi oracle experts,

I have a strange issue here and I could not solve for a few days...

I have a link, OR9X on a oracle db 8.1.7.4.1 , OR8X, to a db, OR9X, version 9.2.x

when I run a query on OR8X like

select * from users@OR9X where userid='XXXXXX'

i get a record, then when I modify the query as

select * from users@OR9X where userid='XXXXXX some garbage data'

still I get the same data where as it should return nothing...

I tested this on TOAD (using ODP.Net, Oracle 9.2 client) and sqlplus (Oracle 9.2 client) and on a web server using ODP.Net client...
same issue... oracle does not care my new query and returns wrong data/or cached data somehow...

if I run the query on linked db, as

select * from users where userid='XXXXXX some garbage data'

I dont get anything as expected...

why this is happening? how can I fix the issue?

this is happening on other linked db's as well, so the issue is not on the linked db, or dblink, or the table/index...

something else, which I need to figure it out...

thanks...
0
Comment
Question by:HainKurt
  • 31
  • 10
  • 8
  • +2
53 Comments
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 26213264
It looks like there is a bug. Did you check with Oracle by opening TAR?
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26213267
after running this

select * from users@OR9X where userid='XXXXXX some garbage data'

and running

select * from users@OR9X where userid='XXXXXX'

I dont get anything ;) which I should get one record... and modify query, add one space before * for example

select  * from users@OR9X where userid='XXXXXX'

I start getting one record as expected...

0
 
LVL 61

Author Comment

by:HainKurt
ID: 26213276
I dont have access to oracle support right now ;(
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 61

Author Comment

by:HainKurt
ID: 26213280
and I guess they dont support 8.x db anymore...
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26213337
and this

select first_name || ' ' || last_name as NAME, to_char(dob, 'YYYY-MM-DD') as DOB, base_ID, eft_account, addr1_addr_line1, addr1_addr_line2, addr1_addr_line3, addr1_addr_line4, addr1_city, addr1_state_cd, addr1_postal_cd, addr1_country_cd, ssn from t_1111_person_table@OR9X where client_code = 1111 and user_ID='11111111'

is returning one same record no matter what I put to user_ID, and strange thing is it is returning

"HAIN xxxx KURT" as NAME ;) it should be "HAIN KURT", this "xxxx" comes from the query I tested 2 days ago ;) I had modified the query to

select first_name || ' xxxx ' || last_name as NAME,...

and I am not using same session, just reconnected to the db with toad...
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 26213607
I fond similar bug between  
1. Wrong Results For Query Using A Dblink [ID 730599.1] (Version: 9.2.0.1 to 10.2.0.4)
2. Bug 2448615  Wrong results from HASH JOIN over DBLINK with multibyte data (9.2.0.1, 8.1.7.4)
3. Bug 2533766  Wrong results from HASH JOIN over DBLINK between multibyte databases ( 9.2.0.2
, 8.1.7.4 )

What is your full Oracle version?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26213641
I had a theory about what might be causing the original problem.  I have no idea about the name concatenation posted above.

I suggest we eliminate TOAD and the web server.  Let's stick with sqlplus.

from a brand new sqlplus window, logged into the the version 8 database do you get a row back with:
select * from users@OR9X where userid='XXXXXX some garbage data';

Just a WAG (Wild Arse Guess):
In the version 9 database, is userid a char(6)?  I'm thinking there might be an issue with how Oracle 8 is handling the data and it might just be looking at the first 6 characters of the literal string.
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26213667
it is happening on sqlplus too ;)

userid is varchar(20)

I thought the same way and checked the size, it is 20 and we wre using just first 8...
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26213707
Oh well.  It was a long shot.  

With the bug summarys virdi_ds posted, my guess is a bug.
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26213719
it is : 8.1.7.4.1

linked db is : 9.2.0.7.0

the issue is between these servers...
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26213826
I didn't read the bugs posted to see when they were fixed or for possible work arounds but based on the description it appears to be with hash joins.

Try not using hash joins:

select /*+ NO_USE_HASH */ * from users@OR9X where userid='XXXXXX some garbage data';

0
 
LVL 61

Author Comment

by:HainKurt
ID: 26213856
/*+ NO_USE_HASH */  did not help... looks like it is ignored...
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26213876
explain plan says

Plan
SELECT STATEMENT REMOTE  CHOOSE Cost: 6  Bytes: 200  Cardinality: 1        
      1 TABLE ACCESS FULL DC_DBA.T_XXXX_PERSON_TABLE XXXX Cost: 6  Bytes: 200  Cardinality: 1  

with  /*+ NO_USE_HASH */ used...
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26213889
duh...Single table query doesn't need joins.

It's been a long week.  I need a beer!!!
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 26213951
Are you getting the same wrong result while connecting to 9i and use db link for 8i?
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26213983
this result is only when I connect to 8i and run a query that involves a dblink to 9i db (not sure just 9i, maybe 10 too, just I dont have any other db to test)

I have one 8i and many 9i db, and issue is happening on 8i db...

I tried to run similar queries on 9i db with links to other 9i db's... no issue here...
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26213992
and the thing is we cannot upgrade this monster since we have many third party app that runs only on 8i db ;(
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 26214066
Since you are not able to upgrade 8i, Can you do one test:-

Create 10g database with one user. Create db link between 10g and 9i. Then create synonyms for all table view to 9i using 10g dblink.
Now Instead of using 9i directly, call 9i table via 10g.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26214121
well...  if it's a bug and you can't patch (even if you could find it)/upgrade, let's try to trick the bug:

select * from (select * from users@OR9X) where userid='XXXXXX some garbage data';
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26214173
26214066 is too complicated that involves many resources (dba/hardware/software/testing all other app) which I dont see it is feasible right now...
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26214189
same issues here:

select * from (select * from users@OR9X) where userid='XXXXXX some garbage data';

we could not trick oracle ;)
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26214212
also tried

select * from (select * from users@OR9X where userid='XXXXXX some garbage data');

no success!...
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26214269
strange thing is I tried this:

select sysdate sd2, sd1, t.* from (select sysdate sd1, t.* from users@OR9X t where userid='XXXXXX some garbage data');

date time parts sd1 & sd2 are correct, always changing, the rest is not ;)
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 26214333
I am nout sure if this work, create synonym over dblink and use this synonym in select statement.

create synonym user_1 for users@OR9X;

select * from user_1 where userid ='xxxxx';

What about if you use function on userid ie lower(userid) = 'xxxxx';
Create function based index on lower(userid) as well in 9i
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26214387
the problem with

create synonym user_1 for users@OR9X;

is table name & dblink name is dynamically passed to sp, and it is actually something like this

t_client_users@OR9X, we pass client to sp, and it is converted to t_1111_users@ORA0

when we pass client=1111, dblink=ORA0... this solution if it works, we need to create max 9999 client, and 100 tables (min), so 1M synonyms ;) not feasible but I will try with one...
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26214409
create synonym user_1 for users@OR9X;

DID NOT WORK, SAME ISSUES ;(
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26214452
lower(userid) = 'xxxxx' did not work either (without function index)...
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26214465
also

converting to query to multiple line (I tgought maybe there is a problem parsing one line query) did not work...
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26214541
strange thing

select * from user_1 where userid ='xxxxx';

works, I add some garbage char at the end of base id, i get same result, I keep adding antil 30-40 characters in total, then I start getting nothing, then I run original query I get nothing ;)

works : select * from user_1 where userid ='xxxxx';
works : select * from user_1 where userid ='xxxxxA';
works : select * from user_1 where userid ='xxxxxAA';
...
!works : select * from user_1 where userid ='xxxxxAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
!works : select * from user_1 where userid ='xxxxx';
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 26214707
What about if we change datatype from varchar to char or vice versa between 8i and 9i. Again  not sure if it works.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26215824
>>we could not trick oracle ;)

Never give up.  Many bugs have a work around since there are almost always a few different ways to do the same thing in Oracle.

While we try to think of other tricks to try...

It's been a long time sine I dealt with 8i and am not sure if we could pull this off but, if we could locate any old 8.1.7.4 patches, would the apps support them?

I'm not sure what the last patch was but I find references up to 8.1.7.4.6.  Who knows, you might get lucky...  I know you can't download them from Oracle any more but I bet someone out there has the patches still lying around somewhere.  I also doubt Oracle would care if you acquire it since it's desupported.

0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26237348
can u check V$SQLTEXT after running the query for any query transformation or re-write happening ?
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26237694
May be your query running on 9i to be highly parallelized based on the init parametrs and  Oracle has re-written the query and passed it out to  slaves to complete.
0
 
LVL 21

Expert Comment

by:flow01
ID: 26271525
The statement is executed remote,
did or can you trick it to use the current database ?

select * from users@OR9X,
dual -- use table in current database to change the driving site
where userid='XXXXXX some garbage data'
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26276058
rewrite thing:

I run this

select 'HainKurt' as HainKurt, first_name || ' ' || last_name as NAME from t_1111_users@U9XA where client_code = 1111 and lower(base_ID)='XXXXXX'

and

select * from V$SQLTEXT where sql_text like '%HainKurt%'

I get

piece, sql_text
0 select :"SYS_B_0" as HainKurt, first_name || :"SYS_B_1" || last
1 _name as NAME from t_1111_users@U9XA where client_code =
2 :"SYS_B_2" and lower(base_ID)=:"SYS_B_3"

on 8i db, on 9i I get nothing...

on 8i
query_rewrite_enabled FALSE
optimizer_index_caching 60
optimizer_features_enable 8.1.7
optimizer_index_cost_adj 15
optimizer_max_permutations 2000
optimizer_mode CHOOSE
optimizer_percent_parallel 0
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26276087
I tried

select /*+driving_site(rt)*/ ... t_1111_users@U9XA rt ...

I get explain plan as:

SELECT STATEMENT REMOTE  CHOOSE Cost: 6  Bytes: 57  Cardinality: 1        
      1 TABLE ACCESS FULL xxxx.T_1111_USERS U9XA Cost: 6  Bytes: 57  Cardinality: 1  

still similar issues... I dont get anything on linked db, 9x db when I run this

select * from V$SQLTEXT where sql_text like '%HainKurt%'

my statement is never seen on remote... but I get something on 8i, but not the latest query I run, but previous ones ???
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26276093
!!!! some progress !!!!

select * from users@OR9X,
dual -- use table in current database to change the driving site
where userid='XXXXXX some garbage data'

looks like working fine ;) will test more, apply to application and I will let you know the results...

now, why and how this trick solved the issue?
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26276127
I tested it on our web server by changing the sp, and web app is working fine too...

but when I checked the package(s), we have similar issues everywhere, but we catched the problem only in one sp... I am scared that we will have similar issues any time in other places too ;( scary,  we need to find a permenant solution without changing the queries...
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 26278329
Where these packages are in 8i or 9i?
Quick review of packages If I am not wrong:-
there are two users USER1 and USER2. USER1 is the owner of all tables and packages, USER2 is being granted to execute PKG1. This package is creating one temp table.
When USER2 will execute this package, temp table will be created in USER1 not in USER2 even though, it is executed from USER2.
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26278488
all are in 8i
sql statements are created dynamically and used in cursors as

open out_cursor for vSQL

where out_cursor is ref cursor, output parameter of sp

I could not get any use of comments about user1 & user2 for my issue...
0
 
LVL 21

Expert Comment

by:flow01
ID: 26279234
now, why and how this trick solved the issue?
Why, i don't know : when upgrading or oracle databases troubles where mostly on the links
: probably some of the caching mechanisms of the 9  does not cooperate with the 8 mechanisms.
How ,  by shifting some of the processing to the 8 another mechanism is used that does not give the troubles you mentioned. (but be sure to test, you might introduce others)

The aim should probably be (apart from upgrading all db)  to shift the work to the 8.

If the  owner of the tables on OR9X is not the CONNECT BY USER of the databaselink or  you use databaselinks without fixed user and the executing user is not the owner of the tables on OR9X
you can try to use synonyms and views and in the view return back to OR8X as DRIVING SITE.

-- create a table  just like the table dual :
create table one_row_table (varchar2(1);
insert into one_row_table select '?' from dual;
-- you will need it because using dual itself does not work (i checked an execution plan)

create view table1_remote
is
select /*+ DRIVING_SITE (o8)*/ ' table1.* from table1, one_row_table@OR8X
;
create synonym table1 on table1_remote;

On v8 executing
select * from table1@OR9X
will be started from  v8  (in the example a made for myself)

If 't works you might spare changing the packages , but I don't know what it does to performance.
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26284565
I checked the dblinks in Ora8x

CREATE PUBLIC DATABASE LINK OR9X
 CONNECT TO ORA_DBA
 IDENTIFIED BY <PWD>
 USING 'OR9X';

and tables belongs to ORA_DBA in 9X db...

and I am connecting to 8x using same ORA_DBA (probbaly they are not the same thing, even if name & password is the same)

I have 5-10 links, with 100s of tables in each linked db, and the queries are created dynamically in sp, we pass table name, dblinkname to sp's, sp creates the statement and opens the cursor and pass to our web application... 10 dblink, 250 tables, we cannot create 2500 alias ;) and maintain this... and we have many similar queries in different packages... I am looking for a solution whic does not involve code change, if possible... right now only solution I have is adding ", dual" to from section of queries...

so what will be the better option other than adding dual (this one works fine) to all queries in this format

select ... from some_table@some_link ....
->
select ... from dual, some_table@some_link ....

We have just applied some patches to 9X db's, and rebooted 8X db, and these patches did not fix the problem... issue still exists between 8x -> dblink to 9X, so probnlem must be on 8X db...
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26286177
I still never saw a response to:
It's been a long time sine I dealt with 8i and am not sure if we could pull this off but, if we could locate any old 8.1.7.4 patches, would the apps support them?
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26287078
I am not sure, but it should support it. is there any release notes that you can send or post here
<Email address deleted by slightwv>
I want to read what is fixed in these patches so I can talk to managers & dba's...
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26287158
First: I removed your email address from the post above.  The experts here cannot take questions outside Experts-Exchange.

I just wanted to see if the legacy apps would support a patched 8.1.7 before we go on a scavenger hunt.

Sorry but I'm not even sure if anyone can even locate the patches in question.  I'll go through some old CDs and see if I might have any old patches.

I'm thinking that if this is a possibility you'll need post another question to see if anyone still has these and will be willing to upload them (after getting permission from Experts-Exchange to upload this type of information).

0
 
LVL 61

Author Comment

by:HainKurt
ID: 26287572
ok, thanks, I just checked my Oracle 8 with TOAD session Info

it says

Oracle8i : 8.1.7.4.1 Production
NLSRTL : 3.4.1.0.0
TNS for 32-bit Windows: 8.1.7.4.0

and our dbas says 8.1.7.4.1 is the latest patch that we have...
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26287589
whats the latest patch number maybe I can find something on internet since rigth now I dont have metalink access... and with free subscription, I cannot get much from Oracle... looks like all links are dead or not accessible (at least from my account)
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26287641
>>maybe I can find something on internet since

I don't know.  I doubt you'll find Oracle patches just sitting around out there and if you did, I probably wouldn't trust them.

I surfed a little before and saw some references to 8.1.7.4.6.

You don't have any active support contracts?  That kills patching in it's tracks.  All this hinges on Oracle blessing someone here providing patches for a de-supported version.  If you don't have a current  service contract, I doubt they'll bless it.
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26288455
I got some feedback from one of DBA's and he says

"there is no patch beyond 8.1.7.4.1"

if this is the case I am stuck!
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 26288726
Just for testing purpose, Install Oracle 10g (your computer) create synonym to 9i using dblink, create 8i synonym pointing to 10g and then test. If it works, we probably don't need very huge SGA and resources.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26288925
>>create 8i synonym pointing to 10g

I've killed a lot of brain cells over the years but I vaguely remember 8i connecting to 10g not being allowed.

>>I got some feedback from one of DBA's and he says

What O/S? You might need new DBAs.

From an Oracle document:
http://download.oracle.com/docs/cd/B11454_01/11.5.9/html/iop110817.html

"The above 5 patches are included in the 8.1.7.4.6 patch set on Windows"
0
 
LVL 61

Author Comment

by:HainKurt
ID: 26293807
we are using standard version on Windows, and right now 8.1.7.4.1
I found the same document

From an Oracle document:
http://download.oracle.com/docs/cd/B11454_01/11.5.9/html/iop110817.html

but dont have access to read the release notes/bug fix...

please note that the problem still exists immediately after a shutdown/reboot process...

I guess our dbas found a patch for bug bug 2533766, which is 8.1.7.4.29, we will install this and test again, hopefully it fixes... o/w we need to move/port all applications to Ora9x databases if we can...
0
 
LVL 61

Accepted Solution

by:
HainKurt earned 0 total points
ID: 26293822
some progress ;)

alter session set cursor_sharing=exact;

on Ora8i, looks it fixes the issues, I will make some more testing on this...

right now it is set to : force
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month15 days, 21 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question