[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

No Data shown in parts of AWR Report in Oracle 11g RAC

Posted on 2010-03-31
10
Medium Priority
?
7,518 Views
Last Modified: 2013-11-11
Hi. I'm trying to generate an AWR report on Oracle 11g RAC. The report is generated, but the db time is 0.00, and a lot of the sections are empty (showing "No data exists for this section of the report.") even though there is supposed to be data.

AFAIK the machine is set up without any special flags or configurations. It contains of two nodes, and running awrrpt on both them yields the same results.

I've attached a sample report. Even though it only spans one minute some actions were done on the database, so it should have shown some statistics. The same problem persists with a report generated over several hours with tens of thousands of select and update commands. Still, the DB time is 0 and the SQL section is empty, as well as most other sections.

Is there a flag I need to turn on (or anything else) to generate the required statistics?

Thanks,
rep3.txt
0
Comment
Question by:ozlevanon
  • 5
  • 5
10 Comments
 
LVL 1

Expert Comment

by:haimavi
ID: 29176234
from your report I can see that you used scope of only two snaps
1577-1578
try use larger range of snaps.
it should solve your hidden data and statistics..
0
 
LVL 8

Author Comment

by:ozlevanon
ID: 29180209
Thanks, but I have atried farther snaps (one of the reports I issued was 49 hours long), and it still showed DB time to be 0.
0
 
LVL 1

Expert Comment

by:haimavi
ID: 29187052
post the result of this query please -

     select
        sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
        dba_hist_active_sess_history
     where
        sample_time > sysdate - (&hours/24)
     group by
        sample_id,
        sample_time
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 8

Author Comment

by:ozlevanon
ID: 29187970
I ran the query with hours=10 and hours=90 and got "no rows selected" on both occasions (below is the full output).
I even ran it without the "where" clause and still got nothing, when I run "select count(*) from dba_hist_active_sess_history" the count seems to be 0.

It seems that nothing is registered the history, is there anyway to turn it on?

SQL>  select
  2          sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,  3    4  
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
  5    6    7    8          dba_hist_active_sess_history
     where
        sample_time > sysdate - (&hours/24)
     group by
        sample_id,  9   10   11   12  
 13          sample_time;
Enter value for hours: 10
old  10:         sample_time > sysdate - (&hours/24)
new  10:         sample_time > sysdate - (10/24)

no rows selected

SQL>  select
        sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
  2    3    4    5    6          count(*) as active_sessions
     from
        dba_hist_active_sess_history
     where
        sample_time > sysdate - (&hours/24)
     group by
        sample_id,
  7    8    9   10   11   12   13          sample_time
 14  ;
Enter value for hours: 90
old  10:         sample_time > sysdate - (&hours/24)
new  10:         sample_time > sysdate - (90/24)

no rows selected

Open in new window

0
 
LVL 1

Expert Comment

by:haimavi
ID: 29189970
Gathering database statistics using the AWR is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter.

should be:
SQL> show parameter STATISTICS_LEVEL

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

if the STATISTICS_LEVEL parameter is set to BASIC it's disabling the AWR gathering process.


0
 
LVL 8

Author Comment

by:ozlevanon
ID: 29190707
That doesn't seem to be it either, mine is set to TYPICAL:
SQL> show parameter STATISTICS_LEVEL

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL
0
 
LVL 1

Assisted Solution

by:haimavi
haimavi earned 1996 total points
ID: 29191390
how many rows you can find in DBA_HIST_SNAPSHOT?
take a manual snapshots every X minutes as well and try to produce a new AWR report.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

To generate an AWR report in an Oracle RAC environment:
@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

0
 
LVL 8

Author Comment

by:ozlevanon
ID: 29204225
Hi haimavi, thanks a lot for your help.
I ran the manual snapshot, and every time I do, two more records are created (I now have 430 records).
The only difference between the two records are that one has snap_flag 4 and one has snap_flag 5. According to a link I found http://kerryosborne.oracle-guy.com/2008/10/oracle-management-packs/ both 4 & 5 mean that data_collection (I assume it refers to the data I'm looking for) is OFF. All the records in my DBA_HIST_SNAPSHOT table have 4 or 5 in SNAP_FLAG column.

I've compared it with a different machine I have for which awr report does seem to work fine, and on that machine (which is 10g) the SNAP_FLAG column is always 0 (which indicates data collection on).

Do you have any idea why the data collection is off and what parameter (or other thing) controls it?
Thanks,
Oz
0
 
LVL 8

Accepted Solution

by:
ozlevanon earned 0 total points
ID: 29206631
Well, haimavi, I think I found the problem thanks to your note on DBA_HIST_SNAPSHOT and the help in http://kerryosborne.oracle-guy.com/2008/10/oracle-management-packs/
It seems that for some reason the system variable 'control_management_pack_access' was set to none, as was evidenced by running the command
select * from v$parameter where name='control_management_pack_access'

I've changed it using the command
alter system set CONTROL_MANAGEMENT_PACK_ACCESS = 'DIAGNOSTIC+TUNING'

And now the new snapshots seem to work much better, and report much more data.
Thanks a lot.
0
 
LVL 1

Expert Comment

by:haimavi
ID: 29913748
you welcome.
good luck.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month9 days, 19 hours left to enroll

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question