AWR snapshot taken for TPROC-C results #296

vikram-umachagi · 2021-12-14T05:48:11Z

vikram-umachagi
Dec 14, 2021

For Oracle 21c , when we are running latest HammerDB for a TPROC-C run using one hammerdb client per PDB and provide the PDB name as service name for each hammerDB instance. While the results are being collected we see that the AWR snapshot is being taken for the CDB database SID. How can we make sure that the results derived from awr snapshots are from individual PDB and not CDB within which multiple PDBs are running in parallel?

Answered by sm-shaw

Dec 15, 2021

Having revisited this, the underlying AWR is the same, ie. there is only one:

SQL> select text_vc from dba_views where view_name = 'AWR_CDB_SNAPSHOT';

TEXT_VC
--------------------------------------------------------------------------------
select snap_id, dbid, instance_number, startup_time,
       begin_interval_time, end_interval_time,
       flush_elapsed, snap_level, error_count, snap_flag, snap_timezone,
       begin_interval_time_tz, end_interval_time_tz,
       decode(con_dbid_to_id(dbid), 1, 0, con_dbid_to_id(dbid)) con_id
from WRM$_SNAPSHOT
where status = 0

SQL>  select text_vc from dba_views where view_name = 'AWR_PDB_SNAPSHOT'; 

TEXT_VC
--------------------------------------…

View full answer

sm-shaw · 2021-12-14T10:44:36Z

sm-shaw
Dec 14, 2021
Maintainer

Hi,
Firstly, is this HammerDB v4.3 the latest version?

There was an Issue for exactly this problem in 19c that was correctly resolved by a Pull Request. So the PDB shows both the CDB and PDB snapshots and HammerDB takes the most recent time interval (this was the change from previously ordering by snap id) - the assumption is that the PDB being tested gives the most recent one by time.

If you take a look at Issue #189, it provides detailed troubleshooting information to help diagnose the cause further. Please take a look at it and provide some further information on exactly what snapshots are showing and HammerDB is retrieving. The behaviour has changed on a number of occasions in Oracle releases, so it could be entirely possible that something has changed between 19c and 21c that requires this to be revisited.

1 reply

vikram-umachagi Dec 14, 2021
Author

Yes , Using HammerDB v4.3

Have 4 PDBs with TPCCTAB tablespace running from different terminals with HammerDB GUI.
CDB Name: DSS
PDB Name: DSSPDB1, DSSPDB2,DSSPDB3,DSSPDB4

Here is the output for one of the hammerdb.log file in /tmp.

Vuser 57:VU 57 : Assigning WID=8 based on VU count 56, Warehouses = 1000 (18 out of 18)
Vuser 56:Processing 10000000 transactions with output suppressed...
Vuser 57:Processing 10000000 transactions with output suppressed...
Vuser 1:Rampup 1 minutes complete ...
Vuser 1:Rampup 2 minutes complete ...
Vuser 1:Rampup complete, Taking start AWR snapshot.
Vuser 1:Start Snapshot 13 taken at 30 NOV 2021 10:43 of instance dss (1) of database DSS (485733441)
Vuser 1:Timing test period of 5 in minutes
Vuser 1:1 ...,
Vuser 1:2 ...,
Vuser 1:3 ...,
Vuser 1:4 ...,
Vuser 1:5 ...,
Vuser 1:Test complete, Taking end AWR snapshot.
Vuser 1:End Snapshot 14 taken at 30 NOV 2021 10:48 of instance dss (1) of database DSS (485733441)
Vuser 1:Test complete: view report from SNAPID 13 to 14
Vuser 1:56 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 46515 NOPM from 95253 Oracle TPM

sm-shaw · 2021-12-14T11:51:31Z

sm-shaw
Dec 14, 2021
Maintainer

In the driver script in the script editor window is both the systemconnect and user set to the PDB?

set systemconnect system/manager@oracle ;# Oracle connect string for system user
set connect tpcc/tpcc@oracle ;# Oracle connect string for tpc-c user

If yes, please take a look at Issue #189 and provide output of some of the troubleshooting queries shown there that show the contents of DBA_HIST_SNAPSHOT. This is the best way to diagnose the issue and see if something has changed in Oracle 21c.

2 replies

vikram-umachagi Dec 14, 2021
Author

Yes, both are set to the PDB.

set systemconnect system/*******@dsspdb1 ;# Oracle connect string for system user
set connect tpcc/tpcc@dsspdb1 ;# Oracle connect string for tpc-c user
set allwarehouses "true";# Use all warehouses to increase I/O

I will send the results for the debug commands in a while.

vikram-umachagi Dec 14, 2021
Author

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> select text_vc from dba_views where view_name = 'DBA_HIST_SNAPSHOT';

TEXT_VC

select "SNAP_ID","DBID","INSTANCE_NUMBER","STARTUP_TIME","BEGIN_INTERVAL_TIME","
END_INTERVAL_TIME","FLUSH_ELAPSED","SNAP_LEVEL","ERROR_COUNT","SNAP_FLAG","SNAP_
TIMEZONE","BEGIN_INTERVAL_TIME_TZ","END_INTERVAL_TIME_TZ","CON_ID" from AWR_CDB_
SNAPSHOT

SQL> select text_vc from dba_views where view_name = 'DBA_HIST_SYSSTAT';

TEXT_VC

select "SNAP_ID","DBID","INSTANCE_NUMBER","STAT_ID","STAT_NAME","VALUE","CON_DBI
D","CON_ID" from AWR_CDB_SYSSTAT

SQL> show user
USER is "SYS"
SQL> show con_name

CON_NAME

CDB$ROOT

SQL> alter session set container=dsspdb5;

Session altered.

SQL> show user
USER is "SYS"
SQL> show con_name

CON_NAME

DSSPDB5
SQL> select text_vc from dba_views where view_name = 'DBA_HIST_SNAPSHOT';

TEXT_VC

select "SNAP_ID","DBID","INSTANCE_NUMBER","STARTUP_TIME","BEGIN_INTERVAL_TIME","
END_INTERVAL_TIME","FLUSH_ELAPSED","SNAP_LEVEL","ERROR_COUNT","SNAP_FLAG","SNAP_
TIMEZONE","BEGIN_INTERVAL_TIME_TZ","END_INTERVAL_TIME_TZ","CON_ID" from AWR_CDB_
SNAPSHOT

SQL> select text_vc from dba_views where view_name = 'DBA_HIST_SYSSTAT';

TEXT_VC

select "SNAP_ID","DBID","INSTANCE_NUMBER","STAT_ID","STAT_NAME","VALUE","CON_DBI
D","CON_ID" from AWR_CDB_SYSSTAT

SQL> select rownum,con_id,instance_name,db_name,snap_id,to_char(end_interval_time, 'DD MON YYYY HH24:MI') from ( select di.instance_number,di.instance_name,di.db_name,di.dbid,ds.snap_id,ds.end_interval_time,di.con_id from dba_hist_snapshot ds,dba_hist_database_instance di where ds.dbid = di.dbid AND ds.instance_number = di.instance_number AND ds.startup_time = di.startup_time order by ds.snap_id DESC ) where ROWNUM <= 20;

ROWNUM     CON_ID INSTANCE_NAME    DB_NAME      SNAP_ID

TO_CHAR(END_INTERVAL_TIME,

     1          0 dss              DSS              347

14 DEC 2021 06:58

     2          0 dss              DSS              346

14 DEC 2021 05:58

     3          0 dss              DSS              345

14 DEC 2021 04:58

ROWNUM     CON_ID INSTANCE_NAME    DB_NAME      SNAP_ID

TO_CHAR(END_INTERVAL_TIME,

     4          0 dss              DSS              344

14 DEC 2021 04:05

     5          0 dss              DSS              343

14 DEC 2021 03:45

     6          0 dss              DSS              342

14 DEC 2021 02:14

ROWNUM     CON_ID INSTANCE_NAME    DB_NAME      SNAP_ID

TO_CHAR(END_INTERVAL_TIME,

     7          0 dss              DSS              341

14 DEC 2021 00:58

     8          0 dss              DSS              340

13 DEC 2021 23:58

     9          0 dss              DSS              339

13 DEC 2021 22:58

ROWNUM     CON_ID INSTANCE_NAME    DB_NAME      SNAP_ID

TO_CHAR(END_INTERVAL_TIME,

    10          0 dss              DSS              338

13 DEC 2021 21:58

    11          0 dss              DSS              337

13 DEC 2021 20:58

    12          0 dss              DSS              336

13 DEC 2021 19:58

ROWNUM     CON_ID INSTANCE_NAME    DB_NAME      SNAP_ID

TO_CHAR(END_INTERVAL_TIME,

    13          0 dss              DSS              335

13 DEC 2021 18:58

    14          0 dss              DSS              334

13 DEC 2021 17:58

    15          0 dss              DSS              333

13 DEC 2021 16:58

ROWNUM     CON_ID INSTANCE_NAME    DB_NAME      SNAP_ID

TO_CHAR(END_INTERVAL_TIME,

    16          0 dss              DSS              332

13 DEC 2021 15:58

    17          0 dss              DSS              331

13 DEC 2021 14:58

    18          0 dss              DSS              330

13 DEC 2021 13:58

ROWNUM     CON_ID INSTANCE_NAME    DB_NAME      SNAP_ID

TO_CHAR(END_INTERVAL_TIME,

    19          0 dss              DSS              329

13 DEC 2021 12:58

    20          0 dss              DSS              328

13 DEC 2021 11:58

20 rows selected.

SQL> select src_dbname, snap_interval, retention from dba_hist_wr_control;

SRC_DBNAME

SNAP_INTERVAL

RETENTION

CDB$ROOT
+00000 01:00:00.0
+00008 00:00:00.0

DSSPDB5
+40150 00:01:00.0
+00008 00:00:00.0

SRC_DBNAME

SNAP_INTERVAL

RETENTION

sm-shaw · 2021-12-14T12:08:59Z

sm-shaw
Dec 14, 2021
Maintainer

You can see the query used here 8748981 this is the commit that updated to the current functionality to list snapshots by time.

0 replies

sm-shaw · 2021-12-14T14:29:22Z

sm-shaw
Dec 14, 2021
Maintainer

OK - thanks for confirming 'DBA_HIST_SNAPSHOT' = AWR_CDB_SNAPSHOT - this will need looking at again as both the snapshot is taken in the CDB and the query. Feel free to submit a pull request with an update to resolve the issue. Otherwise, it will be looked into when time permits. I will create an issue from the discussion to tracke it.

0 replies

sm-shaw · 2021-12-15T12:25:31Z

sm-shaw
Dec 15, 2021
Maintainer

Having revisited this, the underlying AWR is the same, ie. there is only one:

SQL> select text_vc from dba_views where view_name = 'AWR_CDB_SNAPSHOT';

TEXT_VC
--------------------------------------------------------------------------------
select snap_id, dbid, instance_number, startup_time,
       begin_interval_time, end_interval_time,
       flush_elapsed, snap_level, error_count, snap_flag, snap_timezone,
       begin_interval_time_tz, end_interval_time_tz,
       decode(con_dbid_to_id(dbid), 1, 0, con_dbid_to_id(dbid)) con_id
from WRM$_SNAPSHOT
where status = 0

SQL>  select text_vc from dba_views where view_name = 'AWR_PDB_SNAPSHOT'; 

TEXT_VC
--------------------------------------------------------------------------------
select snap_id, dbid, instance_number, startup_time,
       begin_interval_time, end_interval_time,
       flush_elapsed, snap_level, error_count, snap_flag, snap_timezone,
       begin_interval_time_tz, end_interval_time_tz,
       decode(con_dbid_to_id(dbid), 1, 0, con_dbid_to_id(dbid)) con_id
from WRM$_SNAPSHOT
where status = 0

So if we run an example workload against a pluggable database it reports the snaphots as 60 to 61.

If we then run awrrpt then this shows the snapshots we have taken. If we then specify "Location of AWR Data Specified: AWR_PDB" then this gives us a report on the PDB as required. e.g. Container Name= RAZPDB1

SQL> @?/rdbms/admin/awrrpt


Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB
Enter value for awr_location: AWR_PDB

Location of AWR Data Specified: AWR_PDB

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num	DB Name      Instance	  Host
------------ ---------- ---------    ----------   ------
  171153594	 1	RAZCDB1      RAZCDB1	  razorbill.ho

Using 171153594 for database Id
Using	       1 for instance number


Listing all Completed Snapshots
Instance     DB Name	  Snap Id	Snap Started	Snap Level
------------ ------------ ---------- ------------------ ----------

RAZCDB1      RAZCDB1		 58  14 Dec 2021 09:06	  1
				 59  14 Dec 2021 09:09	  1
				 60  15 Dec 2021 06:48	  1
				 61  15 Dec 2021 06:50	  1

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

Enter value for end_snap: 61
End   Snapshot Id specified: 61


WORKLOAD REPOSITORY PDB report (PDB snapshots)

DB Name 	DB Id	 Unique Name DB Role	      Edition Release	 RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
RAZCDB1        171153594 RAZCDB1     PRIMARY	      EE      19.0.0.0.0 NO  YES

Instance     Inst Num Startup Time
------------ -------- ---------------
RAZCDB1 	    1 15-Dec-21 06:44

Container DB Id  Container Name       Open Time
--------------- --------------- ---------------
      171153594 RAZPDB1 	15-Dec-21 06:46


Service Statistics			DB/Inst: RAZCDB1/RAZCDB1  Snaps: 60-61
-> ordered by DB Time

							   Physical	 Logical
Service Name		      DB Time (s)   DB CPU (s)	  Reads (K)    Reads (K)
---------------------------- ------------ ------------ ------------ ------------
razpdb1 			      266	    26		 19	   1,965
			  ------------------------------------------------------

So this is working as intended in Issue #189 and should be sufficient for most use cases.
Of course enhancements can always be accepted by Pull Request if there is a fully tested, better way to do this for all use cases, single instance, RAC and multitenant. However, for now yes snapshots can be taken and reported at PDB level.

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

AWR snapshot taken for TPROC-C results #296

{{title}}

Replies: 5 comments 3 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

AWR snapshot taken for TPROC-C results #296

vikram-umachagi Dec 14, 2021

Replies: 5 comments · 3 replies

sm-shaw Dec 14, 2021 Maintainer

vikram-umachagi Dec 14, 2021 Author

sm-shaw Dec 14, 2021 Maintainer

vikram-umachagi Dec 14, 2021 Author

vikram-umachagi Dec 14, 2021 Author

TEXT_VC

TEXT_VC

CON_NAME

CON_NAME

TEXT_VC

TEXT_VC

TO_CHAR(END_INTERVAL_TIME,

TO_CHAR(END_INTERVAL_TIME,

TO_CHAR(END_INTERVAL_TIME,

TO_CHAR(END_INTERVAL_TIME,

TO_CHAR(END_INTERVAL_TIME,

TO_CHAR(END_INTERVAL_TIME,

TO_CHAR(END_INTERVAL_TIME,

SRC_DBNAME

SNAP_INTERVAL

RETENTION

SRC_DBNAME

SNAP_INTERVAL

RETENTION

sm-shaw Dec 14, 2021 Maintainer

sm-shaw Dec 14, 2021 Maintainer

sm-shaw Dec 15, 2021 Maintainer

vikram-umachagi
Dec 14, 2021

Replies: 5 comments 3 replies

sm-shaw
Dec 14, 2021
Maintainer

vikram-umachagi Dec 14, 2021
Author

sm-shaw
Dec 14, 2021
Maintainer

vikram-umachagi Dec 14, 2021
Author

vikram-umachagi Dec 14, 2021
Author

sm-shaw
Dec 14, 2021
Maintainer

sm-shaw
Dec 14, 2021
Maintainer

sm-shaw
Dec 15, 2021
Maintainer