| 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
Kirti,
If performance is an issue you could probably convert this routine to using BULK BINDS and COLLECTS.
Rick
"Deshpande, Kirti"
<kirti.deshpande_at_ve To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
rizon.com> cc:
Sent by: Subject: RE: RBO/CBO question
root_at_fatcity.com
09/11/2002 04:18 PM
Please respond to
ORACLE-L
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=RULE. 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 Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L Received on Wed Sep 11 2002 - 15:09:30 CDT
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |