Database Administrators
Q&A for database professionals who wish to improve their database skills
Latest Questions
0
votes
1
answers
207
views
AWR dbms_workload_repository ash_global_report_text extract text from output
I want to build a scheduled job in Enterprise Manager to trigger every hour the following query: SELECT output FROM TABLE(dbms_workload_repository.ash_global_report_text(584958394, Null, SYSDATE-1/24, SYSDATE, l_wait_class=>'Scheduler')); I want to capture Events and the percentage only if I get one...
I want to build a scheduled job in Enterprise Manager to trigger every hour the following query:
SELECT output FROM TABLE(dbms_workload_repository.ash_global_report_text(584958394, Null, SYSDATE-1/24, SYSDATE, l_wait_class=>'Scheduler'));
I want to capture Events and the percentage only if I get one with Scheduler resmgr:cpu quantum
And then I want to parse this output only out.
I get now the big varchar(320) column output when I hit the query, but I want also to build a PL/SQL package where I can add variables inside and also to parse out only the important percentage of the resmgr: cpu quantum event. Other events are not important to me.
Any help would be appreciated.
Kind regards.
ultimo_frogman
(31 rep)
Aug 5, 2020, 01:22 PM
• Last activity: Jun 14, 2025, 08:00 PM
0
votes
0
answers
18
views
In which sections of AWR and Statspack can we identify how much CPU the database usually consumes?
We need to migrate a database, but we need to evaluate the possibility of reducing the number of resources (CPU) to decrease the number of licenses. In which sections of AWR and Statspack can we identify how much CPU the database usually consumes? I mean, if we have 16 cores in the server and usuall...
We need to migrate a database, but we need to evaluate the possibility of reducing the number of resources (CPU) to decrease the number of licenses. In which sections of AWR and Statspack can we identify how much CPU the database usually consumes? I mean, if we have 16 cores in the server and usually it consumes only 12 cores, how can we check it?
Astora
(841 rep)
Dec 13, 2024, 01:42 AM
2
votes
4
answers
59285
views
Finding all sql id, sql text, number of executions, execution time between two AWR snap ids in oracle
The AWR report provides the SQL information for top 10-20 SQL statements. Is it possible to get information on SQL statements that do not make it to the top list? How do we find all sql id, sql text, number of executions, execution time between two AWR snap ids in oracle?
The AWR report provides the SQL information for top 10-20 SQL statements. Is it possible to get information on SQL statements that do not make it to the top list? How do we find all sql id, sql text, number of executions, execution time between two AWR snap ids in oracle?
Deven
(194 rep)
Feb 12, 2014, 07:01 AM
• Last activity: Jul 1, 2021, 09:54 AM
0
votes
1
answers
1910
views
Oracle DB not generating AWR
Sysaux tablespace is almost full . Could this be the reason AWR snapshot is not generated ? Although I do have the AutoExtend ON for SYSAUX tablespace.
Sysaux tablespace is almost full . Could this be the reason AWR snapshot is not generated ? Although I do have the AutoExtend ON for SYSAUX tablespace.
Karan Gondalia
(3 rep)
Jul 31, 2020, 06:50 AM
• Last activity: Jul 31, 2020, 11:02 AM
0
votes
1
answers
3750
views
How to find historical sql_id executed by users
I'm trying to find historical sql_id executed by users within interval between snapshot_id from AWR. I'm using the view `DBA_HIST_ACTIVE_SESS_HISTORY`,`DBA_USERS` and `DBA_HIST_SNAPSHOTS` to try find the `username`,`sql_id`,`sample_time` etc.. but after some tests neither all sql executed was listed...
I'm trying to find historical sql_id executed by users within interval between snapshot_id from AWR. I'm using the view
DBA_HIST_ACTIVE_SESS_HISTORY,DBA_USERS and DBA_HIST_SNAPSHOTS to try find the username,sql_id,sample_time etc.. but after some tests neither all sql executed was listed in output of the following query
col username for a30;
col BEGIN_INTERVAL_TIME for a30;
col END_INTERVAL_TIME for a30;
COL SAMPLE_TIME FOR A30;
set linesize 2000;
select * from (
select b.BEGIN_INTERVAL_TIME,b.END_INTERVAL_TIME,u.username,a.SESSION_ID,a.SESSION_SERIAL#,
a.INSTANCE_NUMBER,a.sql_id,A.SAMPLE_TIME from DBA_HIST_ACTIVE_SESS_HISTORY a
inner join dba_users u
on u.user_id=a.user_id
inner join dba_hist_snapshot b
on b.snap_id=a.snap_id
where b.snap_id >= 40 and b.snap_id <= 46
order by BEGIN_INTERVAL_TIME desc) where rownum <= 50;
Why is this happening? Is there some other way to do it?
Astora
(841 rep)
Feb 26, 2020, 03:12 PM
• Last activity: Feb 26, 2020, 03:30 PM
1
votes
1
answers
72
views
Best way to know if my environment has workload repository license
Usually, when I need to use something that use some resource from awr, I look for `LAST_USAGE_DATE` at `dba_feature_usage_statistics` with feature name `Automatic Workload Repository`, but I'm almost sure this is not a good way. How I know if my environment has workload repository license?
Usually, when I need to use something that use some resource from awr, I look for
LAST_USAGE_DATE at dba_feature_usage_statistics with feature name Automatic Workload Repository, but I'm almost sure this is not a good way.
How I know if my environment has workload repository license?
Astora
(841 rep)
Jan 24, 2020, 04:36 AM
• Last activity: Jan 24, 2020, 08:59 AM
5
votes
1
answers
1274
views
Oracle AWR shows missing stats for SQL ID as top elapsed statement
Why SQL ID has null number of executions and elapsed time / execution in AWR report. SQL ID is among top elapsed command on AWR but no details stats whoing time / execution. Would it be because shared pool got flushed or what else can cause this?
Why SQL ID has null number of executions and elapsed time / execution in AWR report. SQL ID is among top elapsed command on AWR but no details stats whoing time / execution. Would it be because shared pool got flushed or what else can cause this?
user1332
Mar 29, 2011, 03:45 AM
• Last activity: Sep 30, 2016, 12:33 PM
1
votes
1
answers
250
views
ORA-13506 An attempt was made to perform operation with an invalid Start/End Snapshot Pair during AWR report creation
When I create an AWR Report, the following error occurs: >ORA-13506 Cause: An attempt was made to perform operation with an invalid Start/End Snapshot Pair. What does this mean, and what should I do about it?
When I create an AWR Report, the following error occurs:
>ORA-13506
Cause: An attempt was made to perform operation with an
invalid Start/End Snapshot Pair.
What does this mean, and what should I do about it?
Sunny
(45 rep)
Feb 6, 2016, 03:29 PM
• Last activity: Mar 2, 2016, 06:05 AM
1
votes
0
answers
810
views
DB CPU parameter in AWR - Oracle
What is the significance of DB CPU paramater in Automatic Workload Repository? Say in my case it's always above 100. Does it have benchmark metric as such to say that so and so number is the right value? How does it work? [![enter image description here][1]][1] [![enter image description here][2]][2...
What is the significance of DB CPU paramater in Automatic Workload Repository? Say in my case it's always above 100. Does it have benchmark metric as such to say that so and so number is the right value? How does it work?
Thanks!!
Thanks!!
tesla747
(1910 rep)
Aug 13, 2015, 11:23 AM
0
votes
1
answers
5441
views
Oracle average active sessions high threshold
I am trying make sense of the DB load from the AWR report for a period handed over to me by DBA. Researching a bit, I see the "Average active sessions" is a metric widely used to measure DB load. It is calculated using (Total DB time/Total wall clock elapsed time). From the AWR, in my case it came a...
I am trying make sense of the DB load from the AWR report for a period handed over to me by DBA. Researching a bit, I see the "Average active sessions" is a metric widely used to measure DB load. It is calculated using (Total DB time/Total wall clock elapsed time). From the AWR, in my case it came a little over 7. Does this number indicate high DB activity? Or do I need to take into account anything else?
toddlermenot
(113 rep)
Jul 11, 2014, 07:21 PM
• Last activity: Jul 11, 2014, 07:32 PM
1
votes
2
answers
2336
views
What useful information can I get from ASH report which I cannot get from AWR report?
What useful information can I get from ASH report which I cannot get from AWR report? What are the common scenarios in which its better to get information from ASH than from AWR?
What useful information can I get from ASH report which I cannot get from AWR report?
What are the common scenarios in which its better to get information from ASH than from AWR?
Deven
(194 rep)
Feb 14, 2014, 06:28 AM
• Last activity: Apr 25, 2014, 09:43 PM
0
votes
1
answers
4694
views
How to interpret undo statistics in Oracle AWR report?
![Extract from AWR Report][1] [1]: https://i.sstatic.net/Hs0dq.jpg How can I use undo statistics in oracle AWR report for tuning Oracle? which indicators are important to identify that size of undo segments needs to be increased?
How can I use undo statistics in oracle AWR report for tuning Oracle? which indicators are important to identify that size of undo segments needs to be increased?
Deven
(194 rep)
Mar 14, 2014, 07:38 AM
• Last activity: Mar 14, 2014, 02:35 PM
2
votes
1
answers
3622
views
In Oracle AWR report how to use "Latch statistics" section in analysis
Latch statistics in AWR report contain following sections 1.Latch Activity 2.Latch Sleep Breakdown 3.Latch Miss Sources 4.Parent Latch Statistics 5.Child Latch Statistics How can I use these sections of AWR report for useful insights to improve Oracle performance?
Latch statistics in AWR report contain following sections
1.Latch Activity
2.Latch Sleep Breakdown
3.Latch Miss Sources
4.Parent Latch Statistics
5.Child Latch Statistics
How can I use these sections of AWR report for useful insights to improve Oracle performance?
Deven
(194 rep)
Mar 7, 2014, 08:04 AM
• Last activity: Mar 13, 2014, 10:16 AM
1
votes
1
answers
342
views
How to measure improvement after splitting tablespaces into multiple disk volumes? (Oracle 10g AWR)
I have a recently released 10 000 user analytical application that is suffering from performance issues because of excessive amount of transactions. Apart from re writing many of the SQLs in the application for better performance we are also on top down approach where we are adding more volumes and...
I have a recently released 10 000 user analytical application that is suffering from performance issues because of excessive amount of transactions. Apart from re writing many of the SQLs in the application for better performance we are also on top down approach where we are adding more volumes and tweeks to memory etc. An Oracle specialist redesigned our sever from single tablespace with 2 volumes to 2 tablespaces with 4 volumes where the indexes will have their own volume and tablespace. Each volume is a separate disk array so they do not compete for IO.
We have executed this change in development env and we are about to move into acceptance but before paying for the change the business would like to see measurable gains.
**Where in the AWR report can I make comparisons to measure such data on the before and after the change is executed?**
Melara
(98 rep)
Nov 4, 2012, 06:51 PM
• Last activity: Nov 13, 2012, 10:30 PM
2
votes
1
answers
3036
views
When is a row added to DBA_HIST_SQLSTAT?
I don't understand the criteria that entails an additional record in the `dba_hist_sqlstat` view. I have expected there to be at most one entry per `sql_id` and `snap_id`, that is, the following statement to return no record: select count(*), snap_id, sql_id from dba_hist_sqlstat group by snap_id, s...
I don't understand the criteria that entails an additional record in the
dba_hist_sqlstat view. I have expected there to be at most one entry per sql_id and snap_id, that is, the following statement to return no record:
select
count(*),
snap_id,
sql_id
from
dba_hist_sqlstat
group by
snap_id,
sql_id
having
count(*) > 1
order by
count(*) desc;
*Yet*, it returns many records, indicating that it is not possible, but the normality, that the same sql statement is captured multiple times per snap_id.
Additionaly, with more than one record per sql and snap period, how do I interpret the *delta* columns within this view? The documentation reads:
> The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.
This doesn't really make sense to me.
**Update** as per *Justin's* comment: this is not in a RAC environment: so even if I group by snap_id, sql_id, dbid, instance_number, the query returns multiple records per snap_id and sql_id.
René Nyffenegger
(3763 rep)
Jul 5, 2012, 04:57 AM
• Last activity: Jul 5, 2012, 07:49 AM
Showing page 1 of 15 total questions