Re: Long query, ask for progress
Date: Thu, 27 Jan 2011 12:06:40 +0100
Message-ID: <1296126400.32_at_user.newsoffice.de>
Michel Cadot schrieb am 26.01.2011 in
<4d404d7e$0$1413$426a74cc_at_news.free.fr>:
> "Andreas Mosmann" <mosmann_at_expires-31-01-2011.news-group.org> a écrit dans le
> message de news: 1296027840.2_at_user.newsoffice.de...
>| 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(VeryLongQuery); >| AnotherSession:=StartSynchronSession(); >| While AsynchronSession.IsRunning do begin >| PrintResult(AnotherSession.StartQuery(QueryToFindOutProgress)); >| Wait(60 sek); >| end; >| >| Thank you >| Andreas >| >| -- >| wenn email, dann AndreasMosmann <bei> web <punkt> 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
Thank you very much, but it doesn't seem to work as I want, I don't know
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> deReceived on Thu Jan 27 2011 - 05:06:40 CST