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

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
LVL 8
ozlevanonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

haimaviCommented:
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
ozlevanonAuthor Commented:
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
haimaviCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ozlevanonAuthor Commented:
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
haimaviCommented:
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
ozlevanonAuthor Commented:
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
haimaviCommented:
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
ozlevanonAuthor Commented:
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
ozlevanonAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
haimaviCommented:
you welcome.
good luck.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.