How do I measure performance of a single sql execution in oracle
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I need to measure the duration of individual sql executions in Oracle DB.
I have the following query but it has two major problems.
SELECT
instance_name, host_name,
sql_id, sql_fulltext, plan_hash_value,
CASE
WHEN executions > 0
THEN ROUND(elapsed_time/executions,3)
ELSE NULL
END elap_per_exec,
elapsed_time,
executions,
PHYSICAL_READ_BYTES/1024 read_kb,
buffer_gets,
rows_processed
FROM v$sqlarea,user_users
CROSS JOIN (SELECT instance_name,host_name FROM v$instance)
WHERE v$sqlarea.PARSING_USER_ID = user_users.user_id AND executions > 5
It gives a sense of performance but this is the total duration for all executions divided by the number of executions so spikes in duration won't be noticeable
The duration is total of all CPUs that spent time on the task including paralleled tasks. A query that took 1 second may have 48 cores so it appears as 48 seconds.
Is the any way to get the performance for a single execution?
oracle
asked 2 mins ago
Brendan CooperBrendan Cooper
1
New contributor
Brendan Cooper is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I need to measure the duration of individual sql executions in Oracle DB.
I have the following query but it has two major problems.
SELECT
instance_name, host_name,
sql_id, sql_fulltext, plan_hash_value,
CASE
WHEN executions > 0
THEN ROUND(elapsed_time/executions,3)
ELSE NULL
END elap_per_exec,
elapsed_time,
executions,
PHYSICAL_READ_BYTES/1024 read_kb,
buffer_gets,
rows_processed
FROM v$sqlarea,user_users
CROSS JOIN (SELECT instance_name,host_name FROM v$instance)
WHERE v$sqlarea.PARSING_USER_ID = user_users.user_id AND executions > 5
It gives a sense of performance but this is the total duration for all executions divided by the number of executions so spikes in duration won't be noticeable
The duration is total of all CPUs that spent time on the task including paralleled tasks. A query that took 1 second may have 48 cores so it appears as 48 seconds.
Is the any way to get the performance for a single execution?
oracle
asked 2 mins ago
Brendan CooperBrendan Cooper
1
New contributor
Brendan Cooper is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I need to measure the duration of individual sql executions in Oracle DB.
I have the following query but it has two major problems.
SELECT
instance_name, host_name,
sql_id, sql_fulltext, plan_hash_value,
CASE
WHEN executions > 0
THEN ROUND(elapsed_time/executions,3)
ELSE NULL
END elap_per_exec,
elapsed_time,
executions,
PHYSICAL_READ_BYTES/1024 read_kb,
buffer_gets,
rows_processed
FROM v$sqlarea,user_users
CROSS JOIN (SELECT instance_name,host_name FROM v$instance)
WHERE v$sqlarea.PARSING_USER_ID = user_users.user_id AND executions > 5
It gives a sense of performance but this is the total duration for all executions divided by the number of executions so spikes in duration won't be noticeable
The duration is total of all CPUs that spent time on the task including paralleled tasks. A query that took 1 second may have 48 cores so it appears as 48 seconds.
Is the any way to get the performance for a single execution?
oracle
asked 2 mins ago
Brendan CooperBrendan Cooper
1
New contributor
Brendan Cooper is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I need to measure the duration of individual sql executions in Oracle DB.
I have the following query but it has two major problems.
SELECT
instance_name, host_name,
sql_id, sql_fulltext, plan_hash_value,
CASE
WHEN executions > 0
THEN ROUND(elapsed_time/executions,3)
ELSE NULL
END elap_per_exec,
elapsed_time,
executions,
PHYSICAL_READ_BYTES/1024 read_kb,
buffer_gets,
rows_processed
FROM v$sqlarea,user_users
CROSS JOIN (SELECT instance_name,host_name FROM v$instance)
WHERE v$sqlarea.PARSING_USER_ID = user_users.user_id AND executions > 5
It gives a sense of performance but this is the total duration for all executions divided by the number of executions so spikes in duration won't be noticeable
The duration is total of all CPUs that spent time on the task including paralleled tasks. A query that took 1 second may have 48 cores so it appears as 48 seconds.
Is the any way to get the performance for a single execution?
oracle
oracle
asked 2 mins ago
Brendan CooperBrendan Cooper
1
New contributor
Brendan Cooper is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 mins ago
Brendan CooperBrendan Cooper
1
New contributor
Brendan Cooper is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 mins ago
Brendan CooperBrendan Cooper
1
New contributor
Brendan Cooper is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 mins ago
Brendan CooperBrendan Cooper
1
asked 2 mins ago
Brendan CooperBrendan Cooper
1
1
New contributor
Brendan Cooper is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Brendan Cooper is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Brendan Cooper is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
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
});
}
});
Brendan Cooper is a new contributor. Be nice, and check out our Code of Conduct.
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%2f235110%2fhow-do-i-measure-performance-of-a-single-sql-execution-in-oracle%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Brendan Cooper is a new contributor. Be nice, and check out our Code of Conduct.
Brendan Cooper is a new contributor. Be nice, and check out our Code of Conduct.
Brendan Cooper is a new contributor. Be nice, and check out our Code of Conduct.
Brendan Cooper is a new contributor. Be nice, and check out our Code of Conduct.
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%2f235110%2fhow-do-i-measure-performance-of-a-single-sql-execution-in-oracle%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
