| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: RBO/CBO question
Rick - Thanks for the tip. I will keep that in mind the next time we =
have a
large data migration with a tight timeframe. This is why I answer =
questions,
I often learn more myself.
=A0
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>=20
-----Original Message-----
From: Rick_Cale_at_teamhealth.com [mailto:Rick_Cale_at_teamhealth.com]
Sent: Wednesday, September 11, 2002 4:13 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: RBO/CBO question
Kirti,
If performance is an issue you could probably convert this routine to =
using
BULK BINDS and COLLECTS.
Rick
=20
"Deshpande, Kirti"
<kirti.deshpande_at_ve To: Multiple =
recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com> =20
rizon.com> cc:
Sent by: Subject: RE: RBO/CBO
question =20
root_at_fatcity.com
=20
=20
09/11/2002 04:18 PM
Please respond to
ORACLE-L
=20
=20
Dennis,
Thanks for the code. If it were my process & database, I would have =
done it
the same way :)
The Vendor app dynamically generates and runs the COPY commands. I can =
not
control the generation part of it as it is a vendor supplied tool.
I might be able to convince the responsible DBA of this system to =
consider
a
logon trigger to change optimizer_goal for sessions initiated by a
particular user id and use that id to run the COPY commands. Other =
than
that I am out of ideas.
Thanks again.
-----Original Message-----
Sent: Wednesday, September 11, 2002 1:55 PM
To: 'Deshpande, Kirti'; 'ORACLE-L_at_fatcity.com'
Kirti
I use this COPY quite a bit, and it generally works well. However, =
as I
said, I think that Oracle development thought at one time they were =
going
to
drop it. Their recommendation for when it didn't work so well was to =
create
a PL/SQL program that would move the data in a cursor loop. In answer =
to
your original question, since COPY is so old, it probably doesn't have =
a
concept of CBO. Here is the PL/SQL code I wrote, if it is of benefit. =
Since
it is PL/SQL, you can probably use more sophisticated tuning methods, =
get
some help from people on this list. You can modify the program so the
program runs on the side of the database link that suits your purposes
better.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>
DECLARE
orig prcheck_orig%rowtype;
CURSOR prcheck_cursor
IS
SELECT *
FROM prcheck_orig;
BEGIN
OPEN prcheck_cursor;
LOOP
FOR x IN 1..100 LOOP
FETCH prcheck_cursor
INTO orig;
EXIT WHEN prcheck_cursor%NOTFOUND;
INSERT INTO prcheck
VALUES (
orig.print_type,
orig.check_nbr,
orig.pchset3_ss_sw,
0
);
END LOOP;
COMMIT;
EXIT WHEN prcheck_cursor%NOTFOUND;
END LOOP;
-----Original Message-----
Sent: Wednesday, September 11, 2002 1:48 PM
To: DENNIS WILLIAMS; 'ORACLE-L_at_fatcity.com'
Dennis,
Yes, that's the one.
The Vendor loves it. They use it to copy from/to whatever data from 50
databases anywhere on a set of 10 servers.
-----Original Message-----
Sent: Wednesday, September 11, 2002 1:37 PM
To: 'ORACLE-L_at_fatcity.com'
Cc: 'kirti.deshpande_at_verizon.com'
Kirti
Is that the venerable SQL*Net COPY command? I say venerable because =
it
is little-documented, hasn't been enhanced in years, and when I called
Oracle support several years ago I was told that it would go away soon.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>
-----Original Message-----
Sent: Wednesday, September 11, 2002 2:08 PM
To: Multiple recipients of list ORACLE-L
Listers,
How do you set optimizer_goal to 'choose' to take effect for sessions
connecting using COPY command?
The database runs with optimizer_mode=3DRULE. I want to test if CBO =
would be
better for queries used for data selection via COPY.
Thanks.
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: kirti.deshpande_at_verizon.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --=20 Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20 Author:=20 INET: Rick_Cale_at_teamhealth.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inReceived on Wed Sep 11 2002 - 16:33:52 CDT
 |
 |