Current Active Session on a Oracle Table
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I'm running a query which updates an Oracle table through Datastage. The query is taking a long time to execute so I want to examine the query/session on that Oracle table in the database and determine how the query is being executed.
Could someone please advise how I can examine that active query/session in DB?
I'm using DB visualizer tool.
oracle execution-plan session
edited Sep 7 '17 at 7:05
hot2use
8,59452458
asked Aug 24 '17 at 5:54
Syed Mohammed MehdiSyed Mohammed Mehdi
1
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I'm running a query which updates an Oracle table through Datastage. The query is taking a long time to execute so I want to examine the query/session on that Oracle table in the database and determine how the query is being executed.
Could someone please advise how I can examine that active query/session in DB?
I'm using DB visualizer tool.
oracle execution-plan session
edited Sep 7 '17 at 7:05
hot2use
8,59452458
asked Aug 24 '17 at 5:54
Syed Mohammed MehdiSyed Mohammed Mehdi
1
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
What do you want to see? Explain plan? How much longer it will run? Session history? SQL stats? There are many scripts for these. Or you could use OEM (Oracle Enterprise Manager).
– sandman
Aug 24 '17 at 9:18
I want to look what session is running on a particular table and it's explain plan
– Syed Mohammed Mehdi
Aug 25 '17 at 8:34
Quite a few steps to do this with scripts, and a bit of copy/pasting of SQL_ID. I suggest OEM or some visual monitoring tool that can drill down into active SQL from session lists.
– sandman
Aug 25 '17 at 9:30
In my company db visualizer tool is being used but I don't know how to find out using it :(
– Syed Mohammed Mehdi
Aug 25 '17 at 14:20
add a comment |
I'm running a query which updates an Oracle table through Datastage. The query is taking a long time to execute so I want to examine the query/session on that Oracle table in the database and determine how the query is being executed.
Could someone please advise how I can examine that active query/session in DB?
I'm using DB visualizer tool.
oracle execution-plan session
edited Sep 7 '17 at 7:05
hot2use
8,59452458
asked Aug 24 '17 at 5:54
Syed Mohammed MehdiSyed Mohammed Mehdi
1
I'm running a query which updates an Oracle table through Datastage. The query is taking a long time to execute so I want to examine the query/session on that Oracle table in the database and determine how the query is being executed.
Could someone please advise how I can examine that active query/session in DB?
I'm using DB visualizer tool.
oracle execution-plan session
oracle execution-plan session
edited Sep 7 '17 at 7:05
hot2use
8,59452458
asked Aug 24 '17 at 5:54
Syed Mohammed MehdiSyed Mohammed Mehdi
1
edited Sep 7 '17 at 7:05
hot2use
8,59452458
asked Aug 24 '17 at 5:54
Syed Mohammed MehdiSyed Mohammed Mehdi
1
edited Sep 7 '17 at 7:05
hot2use
8,59452458
edited Sep 7 '17 at 7:05
hot2use
8,59452458
edited Sep 7 '17 at 7:05
hot2use
8,59452458
8,59452458
asked Aug 24 '17 at 5:54
Syed Mohammed MehdiSyed Mohammed Mehdi
1
asked Aug 24 '17 at 5:54
Syed Mohammed MehdiSyed Mohammed Mehdi
1
asked Aug 24 '17 at 5:54
Syed Mohammed MehdiSyed Mohammed Mehdi
1
1
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
What do you want to see? Explain plan? How much longer it will run? Session history? SQL stats? There are many scripts for these. Or you could use OEM (Oracle Enterprise Manager).
– sandman
Aug 24 '17 at 9:18
I want to look what session is running on a particular table and it's explain plan
– Syed Mohammed Mehdi
Aug 25 '17 at 8:34
Quite a few steps to do this with scripts, and a bit of copy/pasting of SQL_ID. I suggest OEM or some visual monitoring tool that can drill down into active SQL from session lists.
– sandman
Aug 25 '17 at 9:30
In my company db visualizer tool is being used but I don't know how to find out using it :(
– Syed Mohammed Mehdi
Aug 25 '17 at 14:20
add a comment |
What do you want to see? Explain plan? How much longer it will run? Session history? SQL stats? There are many scripts for these. Or you could use OEM (Oracle Enterprise Manager).
– sandman
Aug 24 '17 at 9:18
I want to look what session is running on a particular table and it's explain plan
– Syed Mohammed Mehdi
Aug 25 '17 at 8:34
Quite a few steps to do this with scripts, and a bit of copy/pasting of SQL_ID. I suggest OEM or some visual monitoring tool that can drill down into active SQL from session lists.
– sandman
Aug 25 '17 at 9:30
In my company db visualizer tool is being used but I don't know how to find out using it :(
– Syed Mohammed Mehdi
Aug 25 '17 at 14:20
What do you want to see? Explain plan? How much longer it will run? Session history? SQL stats? There are many scripts for these. Or you could use OEM (Oracle Enterprise Manager).
– sandman
Aug 24 '17 at 9:18
What do you want to see? Explain plan? How much longer it will run? Session history? SQL stats? There are many scripts for these. Or you could use OEM (Oracle Enterprise Manager).
– sandman
Aug 24 '17 at 9:18
I want to look what session is running on a particular table and it's explain plan
– Syed Mohammed Mehdi
Aug 25 '17 at 8:34
I want to look what session is running on a particular table and it's explain plan
– Syed Mohammed Mehdi
Aug 25 '17 at 8:34
Quite a few steps to do this with scripts, and a bit of copy/pasting of SQL_ID. I suggest OEM or some visual monitoring tool that can drill down into active SQL from session lists.
– sandman
Aug 25 '17 at 9:30
Quite a few steps to do this with scripts, and a bit of copy/pasting of SQL_ID. I suggest OEM or some visual monitoring tool that can drill down into active SQL from session lists.
– sandman
Aug 25 '17 at 9:30
In my company db visualizer tool is being used but I don't know how to find out using it :(
– Syed Mohammed Mehdi
Aug 25 '17 at 14:20
In my company db visualizer tool is being used but I don't know how to find out using it :(
– Syed Mohammed Mehdi
Aug 25 '17 at 14:20
add a comment |
2 Answers
2
active
oldest
votes
you can try this query once. This query displays SQL currently active databases
select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
answered Aug 24 '17 at 7:20
Sunil NepaliSunil Nepali
121118
add a comment |
There's a feature of the Tuning Pack called Real Time SQL Monitoring. It's available in SQL Developer, OEM, or theoretically via some package calls - but you really want a GUI for this report.
You can see each step of the plan as it executes and see what the wait events are. You'll also see right away if it's not running with the degree of parallelization that you're expecting.
Our resident database performance expert Maria Colgan has an excellent write-up on the topic here.
answered Aug 31 '17 at 15:33
thatjeffsmiththatjeffsmith
79936
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f184228%2fcurrent-active-session-on-a-oracle-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
you can try this query once. This query displays SQL currently active databases
select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
answered Aug 24 '17 at 7:20
Sunil NepaliSunil Nepali
121118
add a comment |
you can try this query once. This query displays SQL currently active databases
select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
answered Aug 24 '17 at 7:20
Sunil NepaliSunil Nepali
121118
add a comment |
you can try this query once. This query displays SQL currently active databases
select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
answered Aug 24 '17 at 7:20
Sunil NepaliSunil Nepali
121118
you can try this query once. This query displays SQL currently active databases
select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
answered Aug 24 '17 at 7:20
Sunil NepaliSunil Nepali
121118
answered Aug 24 '17 at 7:20
Sunil NepaliSunil Nepali
121118
answered Aug 24 '17 at 7:20
Sunil NepaliSunil Nepali
121118
answered Aug 24 '17 at 7:20
Sunil NepaliSunil Nepali
121118
121118
add a comment |
add a comment |
There's a feature of the Tuning Pack called Real Time SQL Monitoring. It's available in SQL Developer, OEM, or theoretically via some package calls - but you really want a GUI for this report.
You can see each step of the plan as it executes and see what the wait events are. You'll also see right away if it's not running with the degree of parallelization that you're expecting.
Our resident database performance expert Maria Colgan has an excellent write-up on the topic here.
answered Aug 31 '17 at 15:33
thatjeffsmiththatjeffsmith
79936
add a comment |
There's a feature of the Tuning Pack called Real Time SQL Monitoring. It's available in SQL Developer, OEM, or theoretically via some package calls - but you really want a GUI for this report.
You can see each step of the plan as it executes and see what the wait events are. You'll also see right away if it's not running with the degree of parallelization that you're expecting.
Our resident database performance expert Maria Colgan has an excellent write-up on the topic here.
answered Aug 31 '17 at 15:33
thatjeffsmiththatjeffsmith
79936
add a comment |
There's a feature of the Tuning Pack called Real Time SQL Monitoring. It's available in SQL Developer, OEM, or theoretically via some package calls - but you really want a GUI for this report.
You can see each step of the plan as it executes and see what the wait events are. You'll also see right away if it's not running with the degree of parallelization that you're expecting.
Our resident database performance expert Maria Colgan has an excellent write-up on the topic here.
answered Aug 31 '17 at 15:33
thatjeffsmiththatjeffsmith
79936
There's a feature of the Tuning Pack called Real Time SQL Monitoring. It's available in SQL Developer, OEM, or theoretically via some package calls - but you really want a GUI for this report.
You can see each step of the plan as it executes and see what the wait events are. You'll also see right away if it's not running with the degree of parallelization that you're expecting.
Our resident database performance expert Maria Colgan has an excellent write-up on the topic here.
answered Aug 31 '17 at 15:33
thatjeffsmiththatjeffsmith
79936
answered Aug 31 '17 at 15:33
thatjeffsmiththatjeffsmith
79936
answered Aug 31 '17 at 15:33
thatjeffsmiththatjeffsmith
79936
answered Aug 31 '17 at 15:33
thatjeffsmiththatjeffsmith
79936
79936
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f184228%2fcurrent-active-session-on-a-oracle-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What do you want to see? Explain plan? How much longer it will run? Session history? SQL stats? There are many scripts for these. Or you could use OEM (Oracle Enterprise Manager).
– sandman
Aug 24 '17 at 9:18
I want to look what session is running on a particular table and it's explain plan
– Syed Mohammed Mehdi
Aug 25 '17 at 8:34
Quite a few steps to do this with scripts, and a bit of copy/pasting of SQL_ID. I suggest OEM or some visual monitoring tool that can drill down into active SQL from session lists.
– sandman
Aug 25 '17 at 9:30
In my company db visualizer tool is being used but I don't know how to find out using it :(
– Syed Mohammed Mehdi
Aug 25 '17 at 14:20