Oracle TPM reporting 0 with HammerDB

[tieonster]

Hi all,

I've been running a small test using HammerDB 4.7 with Oracle 19, and have been obtaining 0 TPM. NOPM seems to be displaying a value. I understand that this issue has been discussed here and has been fixed quite some time ago. (##189)

However, I am still facing the same problem and getting 0 TPM. Can I check how I can go about resolving this issue on my end? Any help would be greatly appreciated!

[sm-shaw]

This issue is almost always associated with pluggable databases and the user being used not having access to see the transaction statistics. Try connecting system to the container database or use a common user #330

[tieonster]

Hi thanks @sm-shaw for the prompt response. However, my oracle database is just a standalone database before pluggable/containers were introduced in Oracle, but I'm still running into the 0 TPM issue. Any idea on why this may be the case?

[sm-shaw]

In this case it could be a licensing issue with the diagnostics pack depending on the version you are using.
There are discussions of this here https://sourceforge.net/p/hammerdb/discussion/292313/thread/99dc5c61/
https://sourceforge.net/p/hammerdb/discussion/292313/thread/84dc50002c/

and to generate the report do:
@?/rdbms/admin/spcreate.sql.
Replace "BEGIN dbms_workload_repository.create_snapshot(); END;" in driver script with "BEGIN statspack. snap; END;"
comment out AWR SQL queries.
For report do statspack report incl TPM sql> @? \rdbms \admin \spreport.sql

[tieonster]

@sm-shaw thanks for the prompt response. Will investigate this further and post my solution here when I solve this.

[tieonster]

Hi Steve,

@sm-shaw you mentioned that in order to use the statspack report, we need to comment out the AWR SQL queries. Can I check how I can go about doing this?

Are these queries the same ones found in the driver script?

[sm-shaw]

Yes in the driver script, so the line shown here:

   set sql1 "BEGIN dbms_workload_repository.create_snapshot(); END;"

switch $myposition {
    1 { 
        if { $mode eq "Local" || $mode eq "Primary" } {
            set lda [ OracleLogon $systemconnect lda $timesten ]
            set curn1 [oraopen $lda ] 
            set lda1 [ OracleLogon $connect lda1 $timesten ]
            set curn2 [oraopen $lda1 ]
            if { $timesten } {
                puts "For TimesTen use external ttStats utility for performance reports"
                set sql1 "select (xact_commits + xact_rollbacks) from sys.monitor"
            } else {
                set sql1 "BEGIN dbms_workload_repository.create_snapshot(); END;"
                oraparse $curn1 $sql1

[tieonster]

Hi all,

I've figured out a solution to this without having to modify the code, and I'll pen my thoughts down here just in case someone in the future sees this thread. Here are a list of things to check if you've noticed that you are getting a 0 TPM.

1. Check if your Oracle database is a pluggable database. If it is, you can either 1) Connect the system to the container database or 2) Use common user with "c##" as prefix (See oracle 21c with default container database is having issues with Hammerdb 4.4 #330)

2. If your Oracle database is not a pluggable database and just a standard one, check if it is Standard or Enterprise Edition. In my case, it was the enterprise edition so it was much easier for me. All I had to do was to enable the AWR by setting one of the configuration parameters. This is because HammerDB makes use of the AWR snapshots taken to generate TPM values, and if you had it disabled previously then of course you would not be getting any TPM results.

To enable the AWR, it was done using the sqlplus command line and running the command below:

alter system set CONTROL_MANAGEMENT_PACK_ACCESS = 'DIAGNOSTIC + TUNING'

Unfortunately, for those running the Standard Edition of Oracle, you may have to obtain the TPM from the statspack report. I did not go through this process but @sm-shaw had already mentioned how to do so in this thread. Please see above for instructions.

I hope this comment helps someone out there facing this issue.

Cheers!