1

Topic: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

Hello, help please. E the cursor healthy, is in it unions and . OPEN cursor it is fulfilled for a second and FETCH for two hours. Besides every day time grows for 2 minutes though a record count approximately equally every day. Performance of the script in TOAD lasts some seconds. What can be not so with FETCH? Oracle 11g. Thanks.

2

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

To buy the ticket for a train and to take a place in a coach occupies 30 minutes, and to a place it goes two days - what not so with train?!

3

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

Fetch-ch-ch;
If the train goes 50 kilometers for two days that it any more a train and a cart.

4

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

Olegush;
Show an output for the problem cursor

select executions;
rows_processed;
fetches;
elapsed_time;
cpu_time;
plsql_exec_time;
user_io_wait_time;
concurrency_wait_time;
application_wait_time
from v$sql
where sql_id = ' your sql_id'

5

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

dbms_photoshop;
image

6

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

Olegush;
How much I understood on title it is a question about fetch from the cursor in a collection in PL/SQL.
In your case of any line not  and time performance of the order of 15-16 seconds.
If to look at statistics for the code of type such

declare
n numbers;
c sys_refcursor;
limit int: = 1e5;
begin
open c for
select - + querty
rownum
from dual
connect by rownum <= 1e6;
loop
fetch c
bulk collect into n
limit limit;
exit when n.count <limit;
end loop;
end;
/

the Number  will be equal to number of iterations + 1, and rows_processed - to number of the selected lines from the cursor.

set pages 150 lines 150
select executions;
rows_processed;
fetches;
elapsed_time;
cpu_time;
plsql_exec_time;
user_io_wait_time;
concurrency_wait_time;
application_wait_time
from v$sql
where sql_id = ' 26j00tx9fzd8d ';
EXECUTIONS ROWS_PROCESSED FETCHES ELAPSED_TIME CPU_TIME PLSQL_EXEC_TIME USER_IO_WAIT_TIME CONCURRENCY_WAIT_TIME APPLICATION_WAIT_TIME
---------- -------------- ---------- ------------ ---------- --------------- ----------------- --------------------- ---------------------
1 1000000 11 823582 819875 0 0 0 0
1 row selected.

Whether there are doubts that SQL_ID you selected.
Though business in dblink can, look that happens and on remote base.

7

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

dbms_photoshop;
I understood, thanks. I will change a little bit procedure and  that it gives tomorrow. Your scripts very useful. Thanks.

8

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

dbms_photoshop;
Good afternoon, here that I received today:

9

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

Olegush wrote:

Fetch-ch-ch;
If the train goes 50 kilometers for two days that it any more a train and a cart.

Olegush wrote:

E the cursor healthy, is in it unions and .

So  smile smile smile
Not, to make so that exec the cursor (to get on a train) occupied as much time, as well as full  can be (reached), of course. Only not aside "some seconds", and aside "two hours. Besides every day time grows for 2 minutes".
It is enough to materialize result.
If it would be desirable that all worked like a charm - that it is necessary to work qualitatively over "the cursor healthy, there are in it unions and "

10

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

I do not know how many records was yesterday (precisely to thousand) but time was equal for 120 seconds less.

11

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

Fetch-ch-ch wrote:

If it would be desirable that all worked like a charm - that it is necessary to work qualitatively over "the cursor healthy, there are in it unions and "

Result of performance of a script with / * + GATHER_PLAN_STATISTICS */the administrator out of procedure:

-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 0 | | | |
| 1 | HASH UNIQUE | | 1 | 269K | 0 |00:00:00.01 | 0 | 24M | 4367K | |
| 2 | VIEW | | 1 | 269K | 55 |00:00:00.01 | 61 | | | |
| 3 | UNION-ALL | | 1 | | 55 |00:00:00.01 | 61 | | | |
|* 4 | FILTER | | 1 | | 55 |00:00:00.01 | 61 | | | |
|* 5 | TABLE ACCESS FULL | XXX_XXXXXXXX_XXXX_XXXXXXX | 1 | 2 | 55 |00:00:00.01 | 61 | | | |
| 6 | SORT AGGREGATE | | 11 | 1 | 11 |00:00:01.06 | 0 | | | |
|* 7 | VIEW | | 11 | 1 | 0 |00:00:01.06 | 0 | | | |
|* 8 | WINDOW SORT PUSHED RANK | | 11 | 1 | 5 |00:00:01.06 | 0 | 2048 | 2048 | 2048 (0) |
| 9 | WINDOW SORT | | 11 | 1 | 12 |00:00:01.06 | 0 | 4096 | 4096 | 4096 (0) |
| 10 | NESTED LOOPS | | 11 | 1 | 12 |00:00:01.06 | 0 | | | |
| 11 | VIEW | XX_XXX_X | 11 | 1 | 1 |00:00:00.94 | 0 | | | |
| 12 | SORT UNIQUE | | 11 | 1 | 1 |00:00:00.94 | 0 | 2048 | 2048 | 2048 (0) |
| 13 | REMOTE | XXX_XX | 11 | 1 | 1 |00:00:00.94 | 0 | | | |
| 14 | REMOTE | XXX_XX | 1 | 1 | 12 |00:00:00.12 | 0 | | | |
| 15 | REMOTE | XXX_XX | 1 | 126K | 0 |00:00:00.13 | 0 | | | |
| 16 | REMOTE | XXX_XXXXXXXX | 1 | 142K | 0 |00:00:00.11 | 0 | | | |
|* 17 | FILTER | | 1 | | 0 |00:00:01.38 | 0 | | | |
|* 18 | HASH JOIN OUTER | | 1 | 1 | 690 |00:00:01.39 | 0 | 947K | 947K | 1231K (0) |
| 19 | VIEW | | 1 | 997 | 690 |00:00:00.42 | 0 | | | |
| 20 | REMOTE | | 1 | | 690 |00:00:00.42 | 0 | | | |
| 21 | REMOTE | XXX_XXXXXXXX | 1 | 571K | 574K|00:00:00.25 | 0 | | | |
|* 22 | FILTER | | 1 | | 0 |00:00:00.01 | 0 | | | |
| 23 | NESTED LOOPS OUTER | | 1 | 1 | 140 |00:00:12.58 | 3245 | | | |
|* 24 | HASH JOIN | | 1 | 2 | 141 |00:00:02.08 | 3245 | 1180K | 1180K | 1226K (0) |
| 25 | VIEW | XX_XXX_X | 1 | 1 | 317 |00:00:00.01 | 826 | | | |
| 26 | HASH UNIQUE | | 1 | 1 | 317 |00:00:00.01 | 826 | 1207K | 1207K | 1327K (0) |
| 27 | NESTED LOOPS | | 1 | | 317 |00:00:00.01 | 826 | | | |
| 28 | NESTED LOOPS | | 1 | 1 | 405 |00:00:00.01 | 596 | | | |
|* 29 | TABLE ACCESS BY INDEX ROWID | X_XXX_XXX | 1 | 22 | 405 |00:00:00.01 | 228 | | | |
|* 30 | INDEX SKIP SCAN | X_XXX_XXX_XXX_XXXXXX_X | 1 | 443 | 440 |00:00:00.01 | 35 | | | |
|* 31 | INDEX UNIQUE SCAN | X_XXXX_XXX_XX | 405 | 1 | 405 |00:00:00.01 | 368 | | | |
|* 32 | TABLE ACCESS BY INDEX ROWID | X_XXXX_XXX | 405 | 1 | 317 |00:00:00.01 | 230 | | | |
| 33 | VIEW | | 1 | 5016 | 5179 |00:00:01.63 | 2419 | | | |
|* 34 | HASH JOIN OUTER | | 1 | 5016 | 5179 |00:00:01.63 | 2419 | 1000K | 1000K | 1511K (0) |
| 35 | VIEW | | 1 | 3616 | 5786 |00:00:00.41 | 2419 | | | |
| 36 | CONCATENATION | | 1 | | 5786 |00:00:00.41 | 2419 | | | |
|* 37 | HASH JOIN OUTER | | 1 | 1410 | 3384 |00:00:00.39 | 1390 | 771K | 771K | 1239K (0) |
| 38 | MERGE JOIN | | 1 | 1009 | 999 |00:00:00.01 | 1390 | | | |
|* 39 | TABLE ACCESS BY INDEX ROWID | X_XXX | 1 | 1476 | 1474 |00:00:00.01 | 406 | | | |
| 40 | INDEX FULL SCAN | X_XXX_XX | 1 | 1477 | 1475 |00:00:00.01 | 6 | | | |
|* 41 | SORT JOIN | | 1474 | 1009 | 999 |00:00:00.01 | 984 | 115K | 115K | 102K (0) |
| 42 | TABLE ACCESS BY INDEX ROWID | X_XXXX | 1 | 1009 | 1001 |00:00:00.01 | 984 | | | |
|* 43 | INDEX RANGE SCAN | XXX_XXXXXXXXXX_X | 1 | 1009 | 1001 |00:00:00.01 | 18 | | | |
| 44 | REMOTE | XXX_XXXX | 1 | 585K | 593K|00:00:00.28 | 0 | | | |
|* 45 | HASH JOIN OUTER | | 1 | 2206 | 2402 |00:00:00.17 | 1029 | 776K | 776K | 1231K (0) |
| 46 | MERGE JOIN | | 1 | 1578 | 655 |00:00:00.01 | 1029 | | | |
|* 47 | TABLE ACCESS BY INDEX ROWID | X_XXX | 1 | 1476 | 1474 |00:00:00.01 | 406 | | | |
| 48 | INDEX FULL SCAN | X_XXX_XX | 1 | 1477 | 1475 |00:00:00.01 | 6 | | | |
|* 49 | SORT JOIN | | 1474 | 1579 | 655 |00:00:00.01 | 623 | 80896 | 80896 |71680 (0) |
| 50 | TABLE ACCESS BY INDEX ROWID | X_XXXX | 1 | 1579 | 655 |00:00:00.01 | 623 | | | |
|* 51 | INDEX RANGE SCAN | XXX_XXXXXXXXXX_X | 1 | 1579 | 655 |00:00:00.01 | 20 | | | |
| 52 | REMOTE | XXX_XXXX | 1 | 585K | 593K|00:00:00.11 | 0 | | | |
| 53 | REMOTE | XXX_XXXXXXXX | 1 | 546K | 410K|00:00:00.09 | 0 | | | |
| 54 | REMOTE | XXX_XXXXXXXX | 141 | 570K | 140 |00:00:10.59 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------

Where it is necessary to push that worked like a charm?
MATERIALIZED VIEW really helps?

12

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

Olegush wrote:

where it is necessary to push that worked like a charm?

replace on

select... from dual where 1 = 0

That that this request returns nothing yours.

Olegush wrote:

MATERIALIZED VIEW really helps?

to You A-Time clearly shows where time leaves.

13

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

dbms_photoshop wrote:

That that this request returns nothing yours.

Unless 165 rows_processed it of anything?

14

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

dbms_photoshop wrote:

to You A-Time clearly shows where time leaves.

Shows second and at me hours leave.

15

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

Olegush wrote:

it is passed...
Unless 165 rows_processed it of anything?

A-Rows = 0 for SELECT STATEMENT.
Not so coincides with statistics from v$sql.

Olegush wrote:

it is passed...
Shows second and at me hours leave.

Most likely because of NL the link in a cycle twitches and time for remote base does not get in A-Time.
Accordingly it makes sense to leave from NL.

16

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

dbms_photoshop wrote:

Most likely

Most likely the administrator pulled the report of seconds through 12-15 after request start, for  smile
And the HARDWARE it is possible to recommend three things:
1.  DRIVING_SITE
2. To process request, at least to remove distinct (well it is not pleasant to me), and it is better - to consider variants DI.
3. To be convinced that in April links do not depart according to a note considered today.

17

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

andrey_anonymous;
from DI?
.....
stax

18

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

andrey_anonymous wrote:

Most likely the administrator pulled the report of seconds through 12-15 after request start for  smile

At everyone the visibility, but I think the administrator took request from the code and simply substituted not those  or at all did not substitute. smile

19

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

dbms_photoshop wrote:

At everyone the visibility, but I think the administrator took request from the code and simply substituted not those  or at all did not substitute. smile

100 %

andrey_anonymous wrote:

1.  DRIVING_SITE

Directly in an apple

20

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

Stax wrote:

  from DI?

Products of class Data Integration - from ODI, ,  and others on most kommjuniti-edishn type of the same   (for today  like)

21

Re: FETCH BULK COLLECT INTO LIMIT it is long fulfilled

andrey_anonymous wrote:

it is passed...
Products of class Data Integration - from ODI, ,  and others on most kommjuniti-edishn type of the same   (for today  like)

Thanks

It is not assured that all understood sad((
.....
stax