Toad World® Forums

Beta 13.2.0.156 - ADDM problem: ORA-13703 snapshots not found

Hi,

I've just downloaded latest Beta and tried running an ADDM report (Oracle 19.3) but it fails with the following message:

ORA-13703: The snapshot pair [6855, 6900] for database_id 3797924084 and instance_id [1] are not found in the current repository.
ORA-06512: at "SYS.DBMS_ADVISOR", line 226
ORA-06512: at "SYS.PRVT_ADVISOR", line 3546
ORA-06512: at "SYS.PRVT_ADVISOR", line 932
ORA-06512: at "SYS.PRVT_HDM", line 10
ORA-06512: at "SYS.WRI$_ADV_HDM_T", line 39
ORA-06512: at "SYS.PRVT_ADVISOR", line 915
ORA-06512: at "SYS.PRVT_ADVISOR", line 3451
ORA-06512: at "SYS.DBMS_ADVISOR", line 276
ORA-06512: at "SYS.DBMS_ADVISOR", line 221
ORA-06512: at line 10

In Oracle 19c new features, I could read the following:

##### Automatic Database Diagnostic Monitor (ADDM) Support for Pluggable Databases (PDBs)
You can now use ADDM analysis for PDBs in a multitenant environment.
ADDM analysis at a PDB level enables you to tune a PDB effectively for better performance.

Is this just some feature yet to be added to TOAD? In that case... should be possible to disable ADDM when connected to a PDB to avoid the above error meanwhile?

Regards,

Thanks. The snapshot pair clearly exists but I get the same error.

All of the other reports in that window work OK.

Will let you know when it is resolved.

Looking at the oracle docs, I see that it references dbms_addm with the comment about now having support for pluggables. Toad uses dbms_advisor. But interesting to note, in 18c and prior with pluggable databases, the error message was something along the lines of "you can't do this in a pluggable database"

Anyway, looking at dbms_addm, I see this in the documentation....

DBMS_ADDM.ANALYZE_INST (
   task_name                 IN OUT VARCHAR2,
   begin_snapshot            IN     NUMBER,
   end_snapshot              IN     NUMBER,
   cdb_type_override         IN     VARCHAR2,
   read_only_type_override   IN     VARCHAR2,
   instance_number           IN     NUMBER := NULL,
   db_id                     IN     NUMBER := NULL);

which looks promising because I can now tell it what kind of database I have (pluggable, container, etc), but when I try that, I get PLS-00306 "Wrong number or types of arguments in call to ANALYZE_INST. Doing an F4 on it, I see this:

...the parameters differ from what the documentation says!

Commenting out the parameters that don't exist and trying DBMS_ADDM.ANALYZE_INST again, I get the same error as in your original post.

So it seems that Oracle jumped the gun a bit on this one. I hesitate to just disable it in Toad incase Oracle fixes it in a patch. Although the error is a bit confusing because the snapshots do exist, the error will not cause any problems down the line in Toad.

Interesting... are you going to contact Oracle Support and open a SR about this? I can do it myself if you can't.

Regards,

Maybe we both should! I opened a case with them a year ago about dbms_tsdp_manage when 18c was newish...it was open for over a year and I never really did get straight answer about why what I was trying to do wasn't working.

OK, got it working after advised from Oracle Support, let's see:

Please see the note from https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/automatic-performance-diagnostics.html#GUID-B4B0899D-90A3-4E19-8654-1D5C34A48FD5

Note:

ADDM is enabled by default in a CDB root.
ADDM does not work in a PDB by default, because automatic AWR snapshots are disabled by default in a PDB. To use ADDM in a PDB, you must enable automatic AWR snapshots in the PDB.
A user whose current container is the CDB root can view ADDM results for the entire CDB. The ADDM results can include information about multiple PDBs. ADDM results related to a PDB are not included if the PDB is unplugged. The ADDM results stored on the CDB root cannot be viewed when the current container is a PDB.
ADDM results on a PDB provide only PDB-specific findings and recommendations. A user whose current container is a PDB can view ADDM results for the current PDB only. The ADDM results exclude findings that apply to the CDB as a whole, for example, I/O problems relating to the buffer cache size.
Enabling AWR snapshots on a PDB does not change the ADDM report on the CDB root.
AWR data on a PDB cannot be accessed from the CDB root.

Also please see the section:

7.1.3.1 Enabling ADDM in a Pluggable Database

ADDM does not work in a pluggable database (PDB) by default, because automatic AWR snapshots are disabled by default in a PDB. To use ADDM in a PDB, you must enable automatic AWR snapshots in the PDB by setting the AWR_PDB_AUTOFLUSH_ENABLED initialization parameter to TRUE and AWR snapshot interval greater than 0.

To enable ADDM in a PDB:

Set the AWR_PDB_AUTOFLUSH_ENABLED initialization parameter to TRUE in the PDB using the following command:

SQL> ALTER SYSTEM SET AWR_PDB_AUTOFLUSH_ENABLED=TRUE;

Set the AWR snapshot interval greater than 0 in the PDB using the command as shown in the following example:

SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>20);

Then I tried this and worked fine:
var tname VARCHAR2(60);
BEGIN
:tname := 'addm_test';
DBMS_ADDM.ANALYZE_INST(:tname, 1, 2);
END;
/

Now they're looking at the documentation mismatch issue, I'll keep you updated.

Regards,

Excellent! Thanks for the follow up.

At this moment, TOAD is not PDB-AWR capable... for a PDB, it should list the AWR snapshots attending to the PDB DBID (right now it's showing the CDB's snaps) and fails.

After running the SQL that you gave me above, I am getting 2 DBIDs (a DBID dropdown appears in the ADDM/AWR window's toolbar)

If I choose the 2nd one, I can choose a pair of snapshots belonging to the PDB and the ADDM report works.

I use this query to find the DBIDs in the database. Unfortunately I don't see a way to tell which one belongs to the pluggable.

select distinct dbid, db_name
from DBA_HIST_DATABASE_INSTANCE;

edit: CDB_HIST_DATABASE_INSTANCE holds the DBID for the pluggable.

The "DBID" dropdown in today's beta will make it more clear which DBID belongs to the container and which belongs to the pluggable (they both have the same DB Name in DBA/CDB_HIST_DATABASE_INSTANCE, which seems like a bug but that's on Oracle)

Maybe you could try using/joining with DBA_PDBS/CDB_PDBS views? They have both the DBID and the (correct) PDB_NAME.

Thanks. OK, I can get the pluggables from there and the container from DBA_HIST_DATABASE_INSTANCE.