on 12-06-2011 10:58 PM
Hi Guys,
I have a statement running which is halting the system.
Host: hana
Port: 30003
Service: indexserver
Hierarchy: 0
Thread ID: 25529
Thread Type: SqlExecutor
Thread Method: ExecuteStatement
Thread Detail: ALTER TABLE "DM"."B_DIMM3" DISABLE DELTA LOG
Duration [MS]: 2129231 <----
Caller:
Calling:
User: SYSTEM
This should take seconds.
So, I want to Kill this process/thread.
Does anybody know how?
Best Regards,
Rasmus
Hello All,
I ran an expensive insert statement and due to this my HANA studio hanged. So I had to kill my HANA Studio and reopen it.
Now that I have reopened i guess this will have a new session and new thread Id. In this case how do I find out original session Id or thread id - in which I ran the expensive insert statement ?
Thanks,
Chandan S Murthy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I am using SPS07 and running the following statement to import a schema's all objects into another schema.
IMPORT "Schema_A"."*" FROM '/home/hanauser/hana_backup' with RENAME SCHEMA Schema_A TO Schema_B
The statement keeps in "running" state infinitely (already running for 3 hours).
I tried cancelling session using the option provided but the session remains unchanged and it shows same status ie 'RUNNING'. I also tried executing the "alter" statements but not able to kill the session.
In such situation is there anything we can try except resetting whole HANA DB Instance?
Hi Virendra
there is never a way to "force" the cancellation of a session, just a way to flag a session for "self-destruction".
It may be that the code part your import session was in just didn't check for this flag or that it actually was waiting for a lock on something.
In such a case, you either can just wait (eventually a timeout will stop the waiting for locks) or if you feel it's really urgent to stop this session, you have to restart the instance.
Usually this is a last measure that can be avoided by e.g. waiting out.
- Lars
Hi Lars,
that "self-destruction" flag to a session that you talked about, how exactly I can do that?
We're having a issue with some "ghost threads". Sometimes a XS process that should end quickly just stuck there doing, basically, nothing. But after hours it start to generate more and more sub-threads until hitting the limit and making a crashdump. It'd be good to have this "self-destruction" mode every few minutes or so.
Thx!
Rodrigo
Hi Rodrigo,
the way to set the flag is to use the ALTER SYSTEM CANCEL SESSION command:
That's the API that is available.
There is no way to interfere with the evaluation of the flag from a XS or SQL Script level.
To address your problem, I'd recommend to find out why the problem is occurring, instead of accepting it and shooting your solution in the head every now and then.
- Lars
Hello Lars,
I am running session cancellation tests on Rev70.
I have a SQL Statement which uses a CV and runs for 1 minutes using many cores.
I want to cancel this session after a couple seconds.
I uses alter system cancel/discceont session statements but the job is still running and reading your posts I was wondering if there is any thing else to do to not to wait till the process ends and rolls back.
Should I do something to make my SQL code aware ( runs on HANA Studio SQL Session) that a cancellation request has been made.
Thanks,
Ugur
Hi Ugur,
the cancellation of commands does not happen between two SQL commands, where the user could check for the flag.
Instead it happens during the execution of plan operators at a much lower SAP HANA internal level.
So there in fact is nothing you can do to your SQL code to make it easier to be cancelled.
- Lars
Hey Kyle,
thanks for that. Really nice of you to write that.
BTW: more words on SAP HANA from me and
can be found in our new book SAP HANA Administration. of Richard Bremer, Lars Breddemann - by SAP PRESS
cheers, Lars
It sure is And thankfully the good folks at SAP Press double and triple checked the language for us...
Hello All,
The original question regarding canceling thread has not been answered.
We need to cancel an XS code (without CONNECTION_ID) that might be stuck.
I can see the running thread and its THREAD_ID at the Performance query (Taken from the tab in administration)
But couldn’t find any canceling relevant syntax for this.
Please your opinion
BR,
Elad
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Guys,
>
> I have a statement running which is halting the system.
> Thread Method: ExecuteStatement
> Thread Detail: ALTER TABLE "DM"."B_DIMM3" DISABLE DELTA LOG
> Duration [MS]: 2129231 <----
> This should take seconds.
> So, I want to Kill this process/thread.
> Rasmus
HI Rasmus,
as the "how to kill the session?" question was already answered, I've another one:
Why do you think that disabling the delta log of the table should be a quick action?
Maybe a huge delta merge operation is necessary to complete the switch off? And such an operation may take a very considerable amount of time and resources.
Just killing the session that is performing this action might not be the solution to the problem...
best regards,
Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello,
The kill Question has not been answered as none of the statements actually kills the process.
Well.. you need to provide the feedback to our suggestions..
"ALTER SYSTEM CANCEL \[WORK IN\] SESSION session_id" gives a syntax error on WORK IN
Of course it does - \[ \] is there to highlight that this is just optional part...
What was the result of these commands:
SELECT CONNECTION_ID FROM "SYS"."M_CONNECTIONS" WHERE CREATOR_THREAD_ID=25529
ALTER SYSTEM CANCEL WORK IN SESSION '200012'
Also did you check log area?
Tomas
Hi all, Manoj,
I am running into the same issue.
Loading data through BODS on Windows Server using buld loader.
I can see that when the bulk loader option is enabled, the very first thing BODS does is that it executes the statement to disable the delta log.
BODS is stuck after this point. Even if you kill the BODS process, the lock on the table remains. No other process can write to the same table.
The only solution (I know of) is to restart Hana. It still gets stuck the next time I run BODS.
I also see that if I execute the same statement through studio, it never completes.
Wierd thing is I dont run into the same issue if the BODS job server is on the same box as Hana ( Suse Linux 11 )
Any solutions?
Hello,
there is following statement that might help...
ALTER SYSTEM CANCEL \[WORK IN\] SESSION session_id
Cancels the currently executed operation by the specified session, however, the session is not disconnected.
Tomas
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Tomas,
Thanks for the quick reply.
Though, I cant quite get it to work.
In "M_CONNECTIONS" i found the process there is no session_id but only connection_id and transaction_id
I went with the transaction id which was 104
The
"ALTER SYSTEM CANCEL [WORK IN] SESSION session_id" gives a syntax error on [WORK IN]
ALTER SYSTEM CANCEL [WORK IN] SESSION 104
If i remove it gives syntax error on the session_id.
ALTER SYSTEM CANCEL SESSION 104
Can you see what I am doing wrong ?
Best Regards,
Rasmus
Hello,
Now it executed, but it said 0 rows affected.
That is ok - it is always writing this... you can ignore - it is valid only when doing INSERT, UPDATE or DELETE...
Be sure you have correct session - try following:
SELECT CONNECTION_ID FROM "SYS"."M_CONNECTIONS" WHERE CREATOR_THREAD_ID=25529
ALTER SYSTEM CANCEL SESSION '200012'
Try also:
ALTER SYSTEM CANCEL WORK IN SESSION '200012'
Tomas
Hello Tomas,
I ran an expensive insert statement and due to this my HANA studio hanged. So I had to kill my HANA Studio and reopen it.
Now that I have reopened i guess this will have a new session and new thread Id. In this case how do I find out original session Id or thread id - in which I ran the expensive insert statement ?
Thanks,
Chandan S Murthy
I tried pressing "Cancel" in the progress bar in HDB Studio and the process just stayed in a state of waiting to cancel. The following did got work for me either:
ALTER SYSTEM CANCEL SESSION '400210'
However, when I did a disconnect, I at least saw it disappear from the progress bar/tab in HDB Studio and then was able to do other queries.
alter system disconnect session '400210'
It appears that under Performance --> Sessions, this particular session is still waiting to be canceled.
Well, actually you do see an improvement here (that had been requested by me and others):
You do have the confirmation that the cancellation request had "gone through".
The fact that the thread/command/session doesn't get stopped immediately is not a problem here in the first place. As soon as the thread code will be able to check for the flag, it will initiate the self-destruction, but this might take some time.
If you think that it takes too long, you might take some runtime snapshots in quick succession (you can trigger snapshot-series as well) to find which SAP HANA kernel function your thread is currently in. With this information it's not too difficult to get back to development and request for more checks on the cancel flag.
As mentioned before, there is a trade-off between cancellation responsiveness and performance.
The more SAP HANA has to check for a cancellation the less efficient it can work on your queries.
- Lars
Correct, and that might be due to cleanup/rollback activities as well.
Knowing that the "kill switch" had been acknowledged at least allows the DBA to not shoot the instance blindfolded and then have to wait for the recovery...
And sure enough the Dev colleagues spent a big deal of effort on making this more responsive. I like it as well.
It would be nice to have a mandatory check every 60 seconds. That would serve as a catch-all for when queries have gone into a tight loop that shouldn't have a check in it.
Would also be nice to have a check on object activation which fails after 60 seconds if the database lock isn't granted.
User | Count |
---|---|
86 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.