Rick,
Thanks.
Yes, performance is one of the major issues. But so is any changes to
Vendor's design of this "tool". They do not use PL/SQL :(
- Kirti
-----Original Message-----
Sent: Wednesday, September 11, 2002 4:13 PM
To: Multiple recipients of list ORACLE-L
Kirti,
If performance is an issue you could probably convert this routine to using
BULK BINDS and COLLECTS.
Rick
"Deshpande, Kirti"
<kirti.deshpande@ve To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
rizon.com> cc:
Sent by: Subject: RE: RBO/CBO
question
[EMAIL PROTECTED]
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.
- Kirti
-----Original Message-----
Sent: Wednesday, September 11, 2002 1:55 PM
To: 'Deshpande, Kirti'; '[EMAIL PROTECTED]'
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.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
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;
END;
/
-----Original Message-----
Sent: Wednesday, September 11, 2002 1:48 PM
To: DENNIS WILLIAMS; '[EMAIL PROTECTED]'
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.
- Kirti
-----Original Message-----
Sent: Wednesday, September 11, 2002 1:37 PM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
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.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
-----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.
- Kirti
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
