Home » RDBMS Server » Server Administration » Long running SELECT that should be erroneous (11.2.0.3.0, WIN 2008 R2)
icon5.gif  Long running SELECT that should be erroneous [message #621630] Fri, 15 August 2014 03:11 Go to next message
OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany
Junior Member
Hello,

first I have to say, that I'm totally new to Oracle DB. I've seen it the first time last week...
Yesterday I stumbled on a behaviour of the software, that I don't really understand and I hope, you can help me.
At the EM performance folder, there is an activity, that runs since a long time (at least since yesterday). Please see the attached screenshots. And there's my first question: Where can I see, at which time it started?
But what I really want to know: Why does it run so long? Normally it has to quit with an error. If I copy the statement to sqlplus it says:
SQL> select version from sys.product_component_version@ods where upper(product) like '%ORACLE%';
select version from sys.product_component_version@ods where upper(product) like '%ORACLE%'
                                                  *
ERROR at line 1:
ORA-02019: connection description for remote database not found


SQL>

And that's what I expect (because 'ods' is not set in tnsnames.ora). What can I do now? Can I abort the statement in any way?

Thank you and best regards

Fabain
Re: Long running SELECT that should be erroneous [message #621631 is a reply to message #621630] Fri, 15 August 2014 03:15 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
v$session holds SQL_EXEC_START, you'll need to find the sid you're looking for, obviously.

As to why the db link doesnt work, make certain you are using the same account, if it is not the same account you've a good chance of getting unexpected behaviour.
Re: Long running SELECT that should be erroneous [message #621632 is a reply to message #621631] Fri, 15 August 2014 03:27 Go to previous messageGo to next message
OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany
Junior Member
Thank you for the answer! What do I wrong?
SQL> select sql_exec_start from v$session where sql_id='3hbam82tyc3h2';

SQL_EXEC
--------


SQL>




Relating to the second issue: The statement is running with 'SYS', which is the user I'm working in sqlplus!
Re: Long running SELECT that should be erroneous [message #621633 is a reply to message #621632] Fri, 15 August 2014 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The statement is running with 'SYS', which is the user I'm working in sqlplus!


NEVER do that.

Read Read SYS is special.

Re: Long running SELECT that should be erroneous [message #621635 is a reply to message #621633] Fri, 15 August 2014 03:42 Go to previous messageGo to next message
OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany
Junior Member
Michel Cadot wrote on Fri, 15 August 2014 10:32

Quote:
The statement is running with 'SYS', which is the user I'm working in sqlplus!


NEVER do that.

Read Read SYS is special.



Yes, I know. But it's the only user that was given me by the DBA and I better do nothing like creating users etc... Confused
Re: Long running SELECT that should be erroneous [message #621636 is a reply to message #621635] Fri, 15 August 2014 04:20 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
So you were given sys but not allowed to create users? Wow...I dont even...just wow.


Anyway, v$session is transient real time data. If you've killed it, you'll lose the data. Depending on your options you might get a rough idea from ASH.

There could be a number of reasons why that has stopped working, though you dont need the tns entry on the server, you can push it into the link description. To be honest though, I'd start by asking the person who ran it in the first place.

Looking at your image, the wait events are extremely unexpected for a query like that. I'd not expect a CPU wait on the issuing side - was the host starved for CPU perhaps? I don't really trust what grid is telling you there. On the remote side, maybe, but not the issuing side.
Re: Long running SELECT that should be erroneous [message #621637 is a reply to message #621636] Fri, 15 August 2014 04:35 Go to previous messageGo to next message
OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany
Junior Member
Ok, thanks. So there is essentially nothing I can do? The selection was started by the program "Toad", so there is no one I can ask.
Can I kill the selection in any way or will it run until the server will be rebootet?

Roachcoach wrote on Fri, 15 August 2014 11:20

Looking at your image, the wait events are extremely unexpected for a query like that. I'd not expect a CPU wait on the issuing side - was the host starved for CPU perhaps? I don't really trust what grid is telling you there. On the remote side, maybe, but not the issuing side.


Yes, that's another point I wondered about, too...
Re: Long running SELECT that should be erroneous [message #621638 is a reply to message #621637] Fri, 15 August 2014 04:40 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Wait, it is still running? If so, you will be able to locate it in v$session - that will give you machine names and osuer.

Furthermore someone ran it and SYS users should be more than rare (though from what you've said I'm going to guess that might not hold true here). Ask around. Failing that, ASH gives machine (as I recall) if you've got the options.

Yes, you can kill it if it is still running (check the destination as it may not die gracefully there) but if it were me, I'd go round up who was running it and see what is going on.
Re: Long running SELECT that should be erroneous [message #621639 is a reply to message #621638] Fri, 15 August 2014 05:01 Go to previous messageGo to next message
OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany
Junior Member
Yes, it is still running! How can I locate it in v$session?
It was run by Toad, a management and monitoring tool for Oracle databases. So nobody ran it deliberately.
I can start an ASH Report. But I don't know, what I can read out of it relating my problem. Sorry, I'm really new to Oracle!
Re: Long running SELECT that should be erroneous [message #621640 is a reply to message #621639] Fri, 15 August 2014 05:08 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
TOAD is a gui tool for developers.

Try this

select username, osuser, machine, sid, serial#, program,logon_time, s.sql_id,event, substr(sql_text,1,39) sql_txt from v$session s
left join v$sqlarea sq on s.sql_hash_value=sq.hash_value
where status='ACTIVE'
and s.sid != (select sid from v$mystat where rownum=1)
order by 6,7


You'll need to eyeball it, obviously.
Re: Long running SELECT that should be erroneous [message #621641 is a reply to message #621640] Fri, 15 August 2014 06:07 Go to previous messageGo to next message
OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany
Junior Member
Thanks for the informative statement, but I'm afraid we talk at cross purposes.
I know the machine, the program and the user. But what can I do now? The program (toad.exe) is not running anymore, I terminated it already yesterday! How can I kill the selection on Oracle?
Re: Long running SELECT that should be erroneous [message #621642 is a reply to message #621641] Fri, 15 August 2014 06:13 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I meant find the user to establish what DB is being used and how.

Get your DBA to kill it.
Re: Long running SELECT that should be erroneous [message #621643 is a reply to message #621642] Fri, 15 August 2014 06:20 Go to previous messageGo to next message
OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany
Junior Member
I know the user and the DB.
The DBA is in holiday... Wink
Re: Long running SELECT that should be erroneous [message #621644 is a reply to message #621643] Fri, 15 August 2014 06:22 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Then I suggest you be very careful and read this

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2014.htm#SQLRF53115
Re: Long running SELECT that should be erroneous [message #621645 is a reply to message #621644] Fri, 15 August 2014 07:00 Go to previous messageGo to next message
OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany
Junior Member
Thank you again. That's what I was looking for.

Result:
SQL> alter system kill session '274, 61841';
alter system kill session '274, 61841'
*
ERROR at line 1:
ORA-00031: session marked for kill


SQL>


And it's still running... Has it become stuck? Is that possible?
Re: Long running SELECT that should be erroneous [message #621646 is a reply to message #621645] Fri, 15 August 2014 07:06 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Marked for kill means it'll kill it asap.

Usually one sees that because DML is rolling back.

Very occasionally you need to nuke the process at the OS but as you're on windows....I'm out Smile
Re: Long running SELECT that should be erroneous [message #621647 is a reply to message #621646] Fri, 15 August 2014 07:18 Go to previous messageGo to next message
OfflineVIP
Messages: 9
Registered: August 2014
Location: Germany
Junior Member
Ok, perhaps somebody else can help from here?

I did the following:

SQL> select sid, spid from v$process p, v$session s where paddr = addr and s.sid in 274 order by s.sid;

       SID SPID
---------- ------------------------
       274 4796



Now I use the SPID for orakill? Is the following usage right?
orakill SID 4796

(In that case, SID is the instance and not the Session ID, right?)

[Updated on: Fri, 15 August 2014 07:19]

Report message to a moderator

Re: Long running SELECT that should be erroneous [message #621649 is a reply to message #621630] Fri, 15 August 2014 07:53 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
OfflineVIP wrote on Fri, 15 August 2014 03:11
Hello,

<snip>

If I copy the statement to sqlplus it says:
SQL> select version from sys.product_component_version@ods where upper(product) like '%ORACLE%';
select version from sys.product_component_version@ods where upper(product) like '%ORACLE%'
                                                  *
ERROR at line 1:
ORA-02019: connection description for remote database not found


SQL>

And that's what I expect (because 'ods' is not set in tnsnames.ora). What can I do now? Can I abort the statement in any way?



Actually that reference to "ods" in the sql is NOT a reference to tnsnames.ora, it is a reference to a database link (query DBA_DB_LINKS). The definition of the database link ODS may then reference an entry in tnsnames, or it may bypass tnsnames by giving the full address spec directly. If it (the db link) references a tns entry (more correctly, a 'net service name') that entry may or may not be "ods", but for sanity's sake in naming conventions, it probably should be.

Re: Long running SELECT that should be erroneous [message #621652 is a reply to message #621635] Fri, 15 August 2014 08:20 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
OfflineVIP wrote on Fri, 15 August 2014 04:42

Yes, I know. But it's the only user that was given me by the DBA and I better do nothing like creating users etc... Confused


What legitimate DBA would give out the SYS password? You now have full access to destroy the database. For fun, I would do it then blame it on the DBA. He will be fired and you will now be the DBA. Fastest way to move up in the company.
Re: Long running SELECT that should be erroneous [message #621657 is a reply to message #621637] Fri, 15 August 2014 10:23 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Yes, I know. But it's the only user that was given me by the DBA and I better do nothing like creating users etc..


So you didn't understand what is in the link.
Read it again and again until you understand all what it implies.
ANYTHING is better than using SYS.

Previous Topic: status of the v$session
Next Topic: Help about Log file Oracle
Goto Forum:
  


Current Time: Thu Mar 28 05:10:27 CDT 2024