HammerDB says about 0 Oracle TPM

Question

Have a good day, guys and girls!)

Using HammerDB for Oracle benchmarking, I have this connection settings:

diset connection instance IP_of_server:1521/XEPDB1

... this messages from start and end snapshots:

Vuser 1:Start Snapshot 30 taken at 06 JUL 2022 16:58 of instance xe (1) of database XE (691444369) Vuser 1:End Snapshot 31 taken at 06 JUL 2022 17:00 of instance xe (1) of database XE (691444369)

... and this result message:

Vuser 1:TEST RESULT : System achieved 12248 NOPM from 0 Oracle TPM

I use HammerDB 4.4, Oracle XE 21c and pluggable database XEPDB1. According to Oracle docs, starting from version 12.2 there are separated AWR snapshots for CDB and PDB - and AWR PDB snapshots are default disabled. Well, I've executed the code below:

alter session set container=XEPDB1;
alter system set awr_pdb_autoflush_enabled=true;
conn system/<password>;
alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000 scope=both;

... and then restart all 5 Oracle services. No effect.

Then I've tried to modify HammerDB oraoltp.tcl line 2533: from this:

SELECT INSTANCE_NUMBER, INSTANCE_NAME, DB_NAME, DBID, 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 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.END_INTERVAL_TIME DESC) WHERE ROWNUM=1"

to this:

SELECT INSTANCE_NUMBER, INSTANCE_NAME, DB_NAME, DBID, 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 FROM AWR_PDB_SNAPSHOT DS, AWR_PDB_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.END_INTERVAL_TIME DESC) WHERE ROWNUM=1"

So, I've replaced DBA_HIST_SNAPSHOT with AWR_PDB_SNAPSHOT and DBA_HIST_DATABASE_INSTANCE with AWR_PDB_DATABASE_INSTANCE - as follows from this Oracle 12.2 feature. As result it has got 0 NOPM and 0 TPM as well.

So what have I done wrong and how to fix this 0 TPM? Any ideas, please?)(

Answer 1

There is an Issue on the HammerDB GitHub site that discusses this topic in detail here: Oracle 19C Incorrect AWR Snapshots Reported in Multitenant DB #189

Try connecting to the CDB with the system user to monitor the workload and report the TPM.

Answer 2

This is an example from 21c where it was shown that container databases are working as expected oracle 21c with default container database is having issues with Hammerdb 4.4 #330

You shouldn't modify the HammerDB source, the previous studies have shown that there is one AWR repository upon which there are multiple views.

Please verify your Oracle Licensing information for Oracle XE. This feature of HammerDB queries the AWR repository. It may be that the AWR is not enabled in this version of Oracle, for example previously it has not been enabled in Standard Edition. In this case you need to manually switch to statspack if this is still available.