Prev: Oracle sql completed with warnings...
Next: Client Connection Error (TNS-12518 TNS-12560 TNS-00530)
From: Vladimir M. Zakharychev on 12 Sep 2006 09:35

spremuta(a)gmail.com wrote:
> Vladimir M. Zakharychev ha scritto:
>
> > And what the application is doing when you're getting the error? Does
> > it happen to fetch something from a big result set in a loop and commit
> > inside this loop? Or does it happen that the query runs longer than
> > specified undo retention period (10800 seconds is 3 hours,) while
> > there's a lot of transactional activity in the db?
> >
> > Regards,
> > Vladimir M. Zakharychev
> > N-Networks, makers of Dynamic PSP(tm)
> > http://www.dynamicpsp.com
>
>
> Is there a fetch into a cursor declaration, in a sql script that
> somethimes is long 4-6 hours....
> in a table there are about 90milions of records.....

Well, if this script receives ORA-1555 on regular basis, then you
should increase undo_retention to cover the longest run time period of
this query so that Oracle retains undo needed by it for the whole
duration of the process (say, undo_retention=22000.) Or better try to
optimize that script so that it takes less time to complete, ideally
less than 3 hours.

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

From: spremuta on 12 Sep 2006 09:41

DA Morgan ha scritto:

> Post the PL/SQL that is causing the 1555.
> --
> Daniel Morgan


this should be the code...

DECLARE

/*-------------------------------------------------------------------------------*/
/*-- dichiarazione variabili
*/

/*-------------------------------------------------------------------------------*/
WORK_R_POLO
NETFLOW_IUM_EXT_H_DESTINATION.POLO%TYPE;
WORK_R_DESTINATION_ADDRESS
NETFLOW_IUM_EXT_H_DESTINATION.DESTINATION_ADDRESS%TYPE;
WORK_R_PROTOCOL
NETFLOW_IUM_EXT_H_DESTINATION.PROTOCOL%TYPE;
WORK_R_START_TIME
NETFLOW_IUM_EXT_H_DESTINATION.START_TIME%TYPE;
WORK_R_BYTES
NETFLOW_IUM_EXT_H_DESTINATION.BYTES%TYPE;
WORK_R_PACKETS
NETFLOW_IUM_EXT_H_DESTINATION.PACKETS%TYPE;

CONTATORE_COMMIT INTEGER(06) := 0;
FREQUENZA_COMMIT INTEGER(06) := 250000;

/*-------------------------------------------------------------------------------*/
/*-- dichiarazione cursori
*/

/*-------------------------------------------------------------------------------*/
CURSOR RNETFLOW_IUM_CUR1_DESTINATION IS
SELECT
POLO,
DESTINATION_ADDRESS,
PROTOCOL,
TRUNC(START_TIME,'dd'),
SUM(BYTES),
SUM(PACKETS)
FROM NETFLOW_IUM_EXT_H_DESTINATION
WHERE TRUNC(START_TIME,'dd') = TRUNC(SYSDATE-1,'dd')
GROUP BY
POLO,DESTINATION_ADDRESS,PROTOCOL,TRUNC(START_TIME,'dd');

BEGIN

/*-------------------------------------------------------------------------------*/
/*-- loop di lettura tabella tramite cursore
*/

/*-------------------------------------------------------------------------------*/
OPEN RNETFLOW_IUM_CUR1_DESTINATION;
LOOP
FETCH RNETFLOW_IUM_CUR1_DESTINATION
INTO
WORK_R_POLO,
WORK_R_DESTINATION_ADDRESS,
WORK_R_PROTOCOL,
WORK_R_START_TIME,
WORK_R_BYTES,
WORK_R_PACKETS;
EXIT WHEN RNETFLOW_IUM_CUR1_DESTINATION%NOTFOUND;

/*-------------------------------------------------------------------------------*/
/*-- insert NETFLOW_IUM_EXT_D_DESTINATION
*/

/*-------------------------------------------------------------------------------*/
INSERT INTO
NETFLOW_IUM_EXT_D_DESTINATION
(POLO,
DESTINATION_ADDRESS,
PROTOCOL,
START_TIME,
BYTES,
PACKETS
)
VALUES
(WORK_R_POLO,
WORK_R_DESTINATION_ADDRESS,
WORK_R_PROTOCOL,
WORK_R_START_TIME,
WORK_R_BYTES,
WORK_R_PACKETS
);
IF CONTATORE_COMMIT = FREQUENZA_COMMIT
THEN CONTATORE_COMMIT := 1;
COMMIT;
END IF;
END LOOP;
CLOSE RNETFLOW_IUM_CUR1_DESTINATION;
COMMIT;


INSERT_NETFLOW_IUM_LOG('INSERT_NETFLOW_IUM_DESTINATION_D.SQL','OK','ELABORAZIONE
OK');

EXCEPTION
WHEN OTHERS THEN

INSERT_NETFLOW_IUM_LOG('INSERT_NETFLOW_IUM_DESTINATION_d.SQL','KO',SQLCODE||'-'||SUBSTR(SQLERRM,
1, 100));
END;

From: spremuta on 12 Sep 2006 10:56

Brian Peasland ha scritto:

> > Well, if this script receives ORA-1555 on regular basis, then you
> > should increase undo_retention to cover the longest run time period of
> > this query so that Oracle retains undo needed by it for the whole
> > duration of the process (say, undo_retention=22000.) Or better try to
> > optimize that script so that it takes less time to complete, ideally
> > less than 3 hours.
>
> Additionally, make sure that your UNDO tablespace is sufficiently large
> to hold all of this undo.
>
>
> HTH,
> Brian
>


So i should extend the undo_retention parameter for example to 28800,
is correct?
extending undo_retention parameter need stop of oracle instance i
guess...right?
this change could cause overload on the db and the server?

then
do i have to change script in the lines:
IF CONTATORE_COMMIT = FREQUENZA_COMMIT
> THEN CONTATORE_COMMIT := 1;
> COMMIT;
> END IF;
?
deleting only commit line or all that lines in your opinion?

thanks for helping

From: Sybrand Bakker on 12 Sep 2006 12:57
On 12 Sep 2006 07:56:22 -0700, spremuta(a)gmail.com wrote:

>do i have to change script in the lines:
>IF CONTATORE_COMMIT = FREQUENZA_COMMIT
> > THEN CONTATORE_COMMIT := 1;
> > COMMIT;
> > END IF;
>?
>deleting only commit line or all that lines in your opinion?

ALL that lines.
Better still : get rid of the entire for loop (as it fetches record by
record) and convert this mess into a proper
INSERT
SELECT statement

and forget about it.

--
Sybrand Bakker, Senior Oracle DBA
From: joel garry on 12 Sep 2006 13:40

spremuta(a)gmail.com wrote:
> sjaffarhussain(a)gmail.com ha scritto:
>
> > What is the value of undo_retention parameter ?
> >
> > There is a link between this error and with your undo)retention value.
> >
> > Jaffar
> >
>
>
> undo_retention = 10800

OEM has an undo resource advisor that makes this all much more clear.
(In Oracle Enterprise Manager, Standalone), just open your instance,
then click on "instance," then click on any of the advisors, and close
the top window, then click on the undo tab.

I presume you meant database version 9.2.0 rather than 9.0.2?

jg
--
@home.com is bogus.
Tivo finally goes HD:
http://news.yahoo.com/s/ap/20060912/ap_on_hi_te/tivo_new_high_definition_dvr

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Oracle sql completed with warnings...
Next: Client Connection Error (TNS-12518 TNS-12560 TNS-00530)