470,863 Members | 1,152 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,863 developers. It's quick & easy.

RELICATION QUESTIONS

I have two databases (8.1.7), one master and one replicated. I'm getting different results in my explain plan from the same query. Both dbs have the same indexes and all tables have been analyzed.

I'm using all the indexes I should on the master db but not on the replicated db. Does anyone have any ideas why the optimizer is behaving differently in the two databases?

Thank you.

--
Paul
Jul 19 '05 #1
6 2829
Is Oracle using the same optimizer in both instances?
"Paul" <pa*****@yahoo.com> wrote in message news:CL*********************@news4.srv.hcvlny.cv.n et...
I have two databases (8.1.7), one master and one replicated. I'm getting different results in my explain plan from the same query. Both dbs have the same indexes and all tables have been analyzed.

I'm using all the indexes I should on the master db but not on the replicated db. Does anyone have any ideas why the optimizer is behaving differently in the two databases?

Thank you.

--
Paul
Jul 19 '05 #2
Yes, I forgot to mention that both databases are using the cost based optimizer.

--
Paul S. LaBarbera
University of Phoenix On Line Faculty
pa*****@email.uophx.edu

"Laconic2" <la******@comcast.net> wrote in message news:0a********************@comcast.com...
Is Oracle using the same optimizer in both instances?
"Paul" <pa*****@yahoo.com> wrote in message news:CL*********************@news4.srv.hcvlny.cv.n et...
I have two databases (8.1.7), one master and one replicated. I'm getting different results in my explain plan from the same query. Both dbs have the same indexes and all tables have been analyzed.

I'm using all the indexes I should on the master db but not on the replicated db. Does anyone have any ideas why the optimizer is behaving differently in the two databases?

Thank you.

--
Paul
Jul 19 '05 #3
And do you get the same results from the query in both databases? Are they loaded with the same data?

"Paul" <pa*****@yahoo.com> wrote in message news:CR*********************@news4.srv.hcvlny.cv.n et...
Yes, I forgot to mention that both databases are using the cost based optimizer.

--
Paul S. LaBarbera
University of Phoenix On Line Faculty
pa*****@email.uophx.edu

"Laconic2" <la******@comcast.net> wrote in message news:0a********************@comcast.com...
Is Oracle using the same optimizer in both instances?
"Paul" <pa*****@yahoo.com> wrote in message news:CL*********************@news4.srv.hcvlny.cv.n et...
I have two databases (8.1.7), one master and one replicated. I'm getting different results in my explain plan from the same query. Both dbs have the same indexes and all tables have been analyzed.

I'm using all the indexes I should on the master db but not on the replicated db. Does anyone have any ideas why the optimizer is behaving differently in the two databases?

Thank you.

--
Paul
Jul 19 '05 #4
Yes:

The tables have the same data and I get the same results back. The production database returns data faster because it is using all indexes. The replication database is slower because it is not using indexes. However the indexes are all there. All tables have been analyzed also.

--
Paul S. LaBarbera
University of Phoenix On Line Faculty
pa*****@email.uophx.edu

"Laconic2" <la******@comcast.net> wrote in message news:MO********************@comcast.com...
And do you get the same results from the query in both databases? Are they loaded with the same data?

"Paul" <pa*****@yahoo.com> wrote in message news:CR*********************@news4.srv.hcvlny.cv.n et...
Yes, I forgot to mention that both databases are using the cost based optimizer.

--
Paul S. LaBarbera
University of Phoenix On Line Faculty
pa*****@email.uophx.edu

"Laconic2" <la******@comcast.net> wrote in message news:0a********************@comcast.com...
Is Oracle using the same optimizer in both instances?
"Paul" <pa*****@yahoo.com> wrote in message news:CL*********************@news4.srv.hcvlny.cv.n et...
I have two databases (8.1.7), one master and one replicated. I'm getting different results in my explain plan from the same query. Both dbs have the same indexes and all tables have been analyzed.

I'm using all the indexes I should on the master db but not on the replicated db. Does anyone have any ideas why the optimizer is behaving differently in the two databases?

Thank you.

--
Paul
Jul 19 '05 #5
"Laconic2" <la******@comcast.net> wrote in message news:<0a********************@comcast.com>...
Is Oracle using the same optimizer in both instances?
If they are both using the cost-based optimizer, are the cost-based
optimizer parameters (i.e. optimizer_index_cost_adjustment) identical?
What about things like multi-block read count?

When you analyze the tables on the two databases, are you using the
same process to gather statistics? Or are you doing things
differently-- different histograms, for example?

Justin Cave <jc***@ddbcinc.com>
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
"Paul" <pa*****@yahoo.com> wrote in message
news:CL*********************@news4.srv.hcvlny.cv.n et...
I have two databases (8.1.7), one master and one replicated. I'm
getting different results in my explain plan from the same query. Both
dbs have the same indexes and all tables have been analyzed.

I'm using all the indexes I should on the master db but not on the
replicated db. Does anyone have any ideas why the optimizer is behaving
differently in the two databases?

Thank you.

--
Paul
--

Jul 19 '05 #6
Justin:

I don't know but you just gave me some good things to look at.

Thanks.

--
Paul
Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by softwareengineer2006 | last post: by
reply views Thread by connectrajesh | last post: by
8 posts views Thread by Krypto | last post: by
reply views Thread by ramu | last post: by
1 post views Thread by ramu | last post: by
reply views Thread by ramu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.