OraFAQ Forum: Performance Tuning » Snap IDs not found for awr reports generation

Home » RDBMS Server » Performance Tuning » Snap IDs not found for awr reports generation (Oracle 11gR2, RHEL 5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Snap IDs not found for awr reports generation [message #528427]

Mon, 24 October 2011 19:35

User01
Messages: 9
Registered: October 2011
Location: California

Junior Member

Hi,

When I want to generate AWR report for database, I dont see any Snap IDs. What was disabled?

Is it possible to generate awr reports for unseen snap IDs?

Appreciate your help!

Thanks.

Here below is the log:
Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
49472052 WPSDBSTG 1 wpsdbstg

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified: html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 49472052 1 WPSDBSTG wpsdbstg rcolnx88700

Using 49472052 for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day's Completed Snapshots

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:

Report message to a moderator

Re: Snap IDs not found for awr reports generation [message #528428 is a reply to message #528427]

Mon, 24 October 2011 19:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311

NOTE: 436386.1,
"Package for disabling AWR without a Diagnostic Pack license in Oracle"

is "time_statistics" TRUE?

Report message to a moderator

Re: Snap IDs not found for awr reports generation [message #528429 is a reply to message #528428]

Mon, 24 October 2011 19:53

User01
Messages: 9
Registered: October 2011
Location: California

Junior Member

Thanks! This is my first posting, sure to follow it going forward.

Well, Metalink ID looks to be working until 10.2

timed_statistics is set TRUE.

Report message to a moderator

Re: Snap IDs not found for awr reports generation [message #528430 is a reply to message #528429]

Mon, 24 October 2011 19:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

set lines 100 pages 999
select	snap_id
,	snap_level
,	to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
from	dba_hist_snapshot 
order by 1
/

Report message to a moderator

Re: Snap IDs not found for awr reports generation [message #528431 is a reply to message #528430]

Mon, 24 October 2011 20:00

User01
Messages: 9
Registered: October 2011
Location: California

Junior Member

It did not generate any snap ID

SQL> set lines 100 pages 999
SQL> select snap_id
2 , snap_level
3 , to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
4 from dba_hist_snapshot
5 order by 1
6 /

no rows selected

Thanks

Report message to a moderator

Re: Snap IDs not found for awr reports generation [message #528432 is a reply to message #528431]

Mon, 24 October 2011 20:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I suspect job not enabled

Enable/Disable automatic snapshots
Note. This job is enabled by default

exec dbms_scheduler.enable('GATHER_STATS_JOB')

and to disable...

exec dbms_scheduler.disable('GATHER_STATS_JOB')

Report message to a moderator

Re: Snap IDs not found for awr reports generation [message #528433 is a reply to message #528432]

Mon, 24 October 2011 20:09

User01
Messages: 9
Registered: October 2011
Location: California

Junior Member

Will I be able to generate reports for the time intervals when it was disabled, any ways to get the awr reports?

Thanks for your help

Report message to a moderator

Re: Snap IDs not found for awr reports generation [message #528434 is a reply to message #528433]

Mon, 24 October 2011 20:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Will I be able to generate reports for the time intervals when it was disabled, any ways to get the awr reports?
no, no data means no reports

Report message to a moderator

Re: Snap IDs not found for awr reports generation [message #528435 is a reply to message #528434]

Mon, 24 October 2011 20:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Change the snapshot interval
Note. This example changes it to 15 minutes

exec dbms_workload_repository.modify_snapshot_settings (interval => 15)

Manually take a snapshot

exec dbms_workload_repository.create_snapshot

Report message to a moderator

Re: Snap IDs not found for awr reports generation [message #528437 is a reply to message #528434]

Mon, 24 October 2011 20:13

User01
Messages: 9
Registered: October 2011
Location: California

Junior Member

Okay, Thanks

Desperately needed to see the database reports as we upgrade database to 11gR2. Since, Application team reported slowness.

Thank you for you time

Report message to a moderator

Re: Snap IDs not found for awr reports generation [message #528438 is a reply to message #528435]

Mon, 24 October 2011 20:15

User01
Messages: 9
Registered: October 2011
Location: California

Junior Member

SQL> show parameter GATHER_STATS_JOB
SQL> exec dbms_workload_repository.create_snapshot;
BEGIN dbms_workload_repository.create_snapshot; END;

*
ERROR at line 1:
ORA-13509: error encountered during updates to a AWR table
ORA-01654: unable to extend index ORA-01654: unable to extend index
SYS.WRM$_SNAPSHOT_DETAILS_INDEX by 128 in tablespace SYSAUX
. by in tablespace
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 99
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 122
ORA-06512: at line 1

SQL> exec dbms_workload_repository.modify_snapshot_settings (interval => 15);

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Snap IDs not found for awr reports generation [message #528439 is a reply to message #528438]

Mon, 24 October 2011 20:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

The job likely errored out when it ran out of disk space.

01654, 00000, "unable to extend index %s.%s by %s in tablespace %s"
// *Cause:  Failed to allocate an extent of the required number of blocks for
//          an index segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//          files to the tablespace indicated.

Report message to a moderator

Re: Snap IDs not found for awr reports generation [message #528440 is a reply to message #528439]

Mon, 24 October 2011 20:38

User01
Messages: 9
Registered: October 2011
Location: California

Junior Member

Hi,

Am unable to do so.

can you provide me any other options/queries to extend the index.

Thanks

Report message to a moderator

Re: Snap IDs not found for awr reports generation [message #528441 is a reply to message #528440]

Mon, 24 October 2011 20:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>can you provide me any other options/queries to extend the index.
When tablespace is full only two alternatives exist.
1) add more space to tablespace
2) free up space within tablespace

Since SYSAUX tablespace is owned by Oracle, you are ill advised to manually do DML (DELETE) against it.

Report message to a moderator

Re: Snap IDs not found for awr reports generation [message #528450 is a reply to message #528441]

Tue, 25 October 2011 01:18

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

It may help to find out what is in your SYSAUX tablespace: query V$SYSAUX_OCCUPANTS to find out. Some of the occupants can be moved to anoter tablespace, the view shows the procedures to do this. You will in any case have to make the tablespace bigger before you upgrade.
Rgds, John.

Report message to a moderator

Re: Snap IDs not found for awr reports generation [message #528459 is a reply to message #528432]

Tue, 25 October 2011 02:02

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

GATHER_STATS_JOB & AWR? Do you really see any relation???

Report message to a moderator

Previous Topic:	Optimize MERGE statement
Next Topic:	Index on Range-List Subpartitions Table using template

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 21:26:08 CDT 2024