Hi,

I work on Oracle 11 and sometimes there are queries, that need hours to
be performed.
is there a way to find out the progress of a special query within
another session?

pseudo:

AsynchronSession:=StartSessionAsynchron(VeryLongQu ery);
AnotherSession:=StartSynchronSession();
While AsynchronSession.IsRunning do begin
PrintResult(AnotherSession.StartQuery(QueryToFindO utProgress));
Wait(60 sek);
end;

Thank you
Andreas

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

"Andreas Mosmann" <mosmann (AT) expires-31-01-2011 (DOT) news-group.org> a écrit dans le message de news: 1296027840.2 (AT) user (DOT) newsoffice.de...
SQL> desc V$SESSION_LONGOPS
Name Null? Type
-------------------------------- -------- ----------------------
SID NUMBER
SERIAL# NUMBER
OPNAME VARCHAR2(64)
TARGET VARCHAR2(64)
TARGET_DESC VARCHAR2(32)
SOFAR NUMBER
TOTALWORK NUMBER
UNITS VARCHAR2(32)
START_TIME DATE
LAST_UPDATE_TIME DATE
TIMESTAMP DATE
TIME_REMAINING NUMBER
ELAPSED_SECONDS NUMBER
CONTEXT NUMBER
MESSAGE VARCHAR2(512)
USERNAME VARCHAR2(30)
SQL_ADDRESS RAW(4)
SQL_HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
QCSID NUMBER

Regards
Michel

On Jan 25, 11:44*pm, Andreas Mosmann <mosm... (AT) expires-31-01-2011 (DOT) news-
group.org> wrote:
v$session_longops is about it as far as built-in, I think if it
doesn't do that for you you'll have to write your own.

The creaky old 4GL I work on has always had an automatic progress bar
that would come up for reports running longer than some minimum, but
that's because it figures out beforehand how many rows it will deal
with, then figures the rate while running. It confuses users when
there is a multistep process and each has a progress bar (and the
programmer forgets to turn it off).

jg
--
@home.com is bogus.
http://news.techworld.com/applicatio...ommittee-spot/

Michel Cadot schrieb am 26.01.2011 in
<4d404d7e$0$1413$426a74cc (AT) news (DOT) free.fr>:


why.
There is a query I start, that takes several minutes to be answered. But
all the time there is no long op of this query listed in the view above.
Maybe, there are lot of "short ops" that makes the query slow. Maybe
there is another reason for this.
What can I do?

Example (very simplified):
There is a big table "TBIG" containing
ID_F START STOP TYPE ID_TYPE
001 0 500 APPLE 12345
001 500 1000 APPLE 23456
001 1000 1500 APPLE 34567
001 0 300 PEACH 45678
001 300 600 PEACH 56789
001 600 900 PEACH 67890
001 900 1200 PEACH 78901
001 0 800 BREAD 89012
001 800 1600 BREAD 90123

there are for each ID_F many entries

There are smaller tables like
TAPPLE
ID ID_COLOR
12345 1
23456 2
34567 1

TAPPLE_COLOR
1 RED
2 YELLOW

and some like this for peach, bread and so on

I Need
START STOP PEACH APPLE BREAD
0 300 JUICY YELLOW SOFT
300 500 DRY YELLOW SOFT
500 600 DRY RED SOFT
600 800 JUICY RED SOFT
800 900 JUICY RED HARD
900 1000 SWEET RED HARD
1000 1200 SWEET GREEN HARD
1200 1500 GREEN HARD
1500 1600 HARD

So I first extract all "Breaks" from FIRST table like
inline VIEW BREAKS AS SELECT ID_F, STOP FROM TBIG GROUP BY ID_F

after this I connect this by analytic functions as
SELECT
ID_F,
nvl(lag(STOP) over (partitioned by ID_F order by STOP),0) START,
STOP
FROM
BREAKS

and at last I have a query like

with
BREAKS as (
SELECT ID_F, STOP FROM TBIG GROUP BY ID_F
),
RANGES as (
SELECT
ID_F,
nvl(lag(STOP) over (partitioned by ID_F order by STOP),0) START,
STOP
FROM
BREAKS
)
SELECT R.ID_F,R.START,R.STOP,
AC.COLOR,PX.XXX,BS.SOFT
FROM RANGES R

JOIN TBIG BA on R.ID_F=BA.ID_F AND BA.START<R.STOP and R.START<BA.STOP
JOIN TAPPLE A ON BA.ID_TYPE=A.ID
JOIN TAPPLE_COLOR AC ON A.ID_COLOR=AC.ID

JOIN TBIG BP on R.ID_F=BP.ID_F AND BP.START<R.STOP and R.START<BP.STOP
JOIN TPEACH P ON BP.ID_TYPE=P.ID
JOIN TPEACH_XXX PX ON P.ID_XXX=PX.ID

JOIN TBIG BB on R.ID_F=BB.ID_F AND BB.START<R.STOP and R.START<BB.STOP
JOIN TBREAD B ON BB.ID_TYPE=B.ID
JOIN TBREAD_SOFT BS ON B.ID_SOFT=BS.ID

and so on.

You see, that TBIG is queried many times and so the query takes some
time. But I can not find my session in the longops. What to do?

the execution plan has about 40 lines, lots of nested loops, some sort
operations, index- scans of TBIG- indexes and Access on TBIF by INDEX
ROW ID

some of them must to be seen anywhere, isn't it?

Andreas

btw: I afraid there is no way to optimize query or database, the query
probably will always look like this.

--
wenn email, dann AndreasMosmann <bei> web <punkt> de