Closed
Description
Issue Description:
While running the ResourceStresserBenchmark in Oracle Database 19c or Oracle Database 23c, the DBMS_OBFUSCATION_TOOLKIT package was not available, resulting in the following error during workload execution:
[INFO ] 2023-07-12 09:15:38,625 [main] com.oltpbenchmark.DBWorkload main (181) - ======================================================================
[INFO ] 2023-07-12 09:15:50,405 [main] com.oltpbenchmark.DBWorkload runWorkload (640) - Creating 4 virtual terminals...
[WARN ] 2023-07-12 09:15:50,406 [main] com.oltpbenchmark.benchmarks.resourcestresser.ResourceStresserBenchmark makeWorkersImpl (49) - numkeys=500, keyRange=125
[INFO ] 2023-07-12 09:16:02,593 [main] com.oltpbenchmark.DBWorkload runWorkload (644) - Launching the RESOURCESTRESSER Benchmark with 1 Phase...
[INFO ] 2023-07-12 09:16:02,601 [main] com.oltpbenchmark.ThreadBench runRateLimitedMultiPhase (128) - PHASE START :: [Workload=RESOURCESTRESSER] [Serial=false] [Time=300] [WarmupTime=0] [Rate=10000] [Arrival=REGULAR] [Ratios=[16.66, 16.66, 16.66, 16.66, 16.66, 16.7]] [ActiveWorkers=4]
[INFO ] 2023-07-12 09:16:02,604 [main] com.oltpbenchmark.ThreadBench runRateLimitedMultiPhase (280) - MEASURE :: Warmup complete, starting measurements.
[WARN ] 2023-07-12 09:16:03,725 [ResourceStresserWorker<000>] com.oltpbenchmark.api.Worker doWork (449) - SQLException occurred during [com.oltpbenchmark.benchmarks.resourcestresser.procedures.CPU1/01] and will not be retried... sql state [65000], error code [6575].
java.sql.SQLException: ORA-06575: Package or function MD5 is in an invalid state
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1230)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:511)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:162)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1009)
at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1270)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1148)
at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1660)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1469)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3760)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3935)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1101)
at com.oltpbenchmark.benchmarks.resourcestresser.procedures.CPU1.run(CPU1.java:55)
at com.oltpbenchmark.benchmarks.resourcestresser.ResourceStresserWorker.cpu1Transaction(ResourceStresserWorker.java:111)
at com.oltpbenchmark.benchmarks.resourcestresser.ResourceStresserWorker.executeWork(ResourceStresserWorker.java:69)
at com.oltpbenchmark.api.Worker.doWork(Worker.java:416)
at com.oltpbenchmark.api.Worker.run(Worker.java:282)
at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: Error : 6575, Position : 29, Sql = SELECT count(*) FROM (SELECT md5(concat(md5(concat(md5(concat(md5(concat(md5(concat('passwd',:1 )),:2 )),:3 )),:4 )),:5 )) FROM cputable WHERE empid >= 0 AND empid < 100) T1, OriginalSql = SELECT count(*) FROM (SELECT md5(concat(md5(concat(md5(concat(md5(concat(md5(concat('passwd',?)),?)),?)),?)),?)) FROM cputable WHERE empid >= 0 AND empid < 100) T1, Error Msg = ORA-06575: Package or function MD5 is in an invalid state
Description:
The DBMS_OBFUSCATION_TOOLKIT package has been deprecated and replaced by DBMS_CRYPTO. While running the ResourceStresserBenchmark in Oracle Database 19c or Oracle Database 23c, the following changes were made in the benchmarks/resourcestresser/ddl-oracle.sql file to resolve the error and make the MD5 function work correctly:
-- Before changes
create or replace function md5raw (text in varchar2) return varchar2 is hash_value varchar2(20);
begin
hash_value := dbms_obfuscation_toolkit.md5 (input_string => text);
return hash_value;
end;
create or replace function md5(text in varchar2) return varchar2 is hash_value varchar2(32);
begin
select lower(rawtohex(md5raw(text))) into hash_value from dual;
return hash_value;
end;
-- After changes
CREATE OR REPLACE FUNCTION md5raw (text IN VARCHAR2) RETURN RAW IS hash_value RAW(16);
BEGIN
hash_value := dbms_crypto.hash(src => UTL_I18N.STRING_TO_RAW(text, 'AL32UTF8'), typ => dbms_crypto.hash_md5);
RETURN hash_value;
END;
CREATE OR REPLACE FUNCTION md5(text IN VARCHAR2) RETURN VARCHAR2 IS hash_value VARCHAR2(32);
BEGIN
SELECT LOWER(RAWTOHEX(md5raw(text))) INTO hash_value FROM dual;
RETURN hash_value;
END;Also if you face any errors with CPU1/CPU2 procedure while executing the procedures, consider changing the cpuSelect statement in these procedures and adding them to the dialect file i.e. benchmarks/resourcestresser/ddl-oracle.sql.
-- Before changes
cpuSelect = new SQLStmt("SELECT count(*) FROM (SELECT " + complexClause + " FROM " + ResourceStresserConstants.TABLENAME_CPUTABLE + " WHERE empid >= 0 AND empid < 100) AS T1"); (CPU1)
cpuSelect = new SQLStmt("SELECT count(*) FROM (SELECT " + complexClause + " FROM " + ResourceStresserConstants.TABLENAME_CPUTABLE + " WHERE empid >= 0 AND empid < 100) AS T2"); (CPU2)
-- After changes
cpuSelect = new SQLStmt("SELECT count(*) FROM (SELECT " + complexClause + " FROM " + ResourceStresserConstants.TABLENAME_CPUTABLE + " WHERE empid >= 0 AND empid < 100) T1"); (CPU1)
cpuSelect = new SQLStmt("SELECT count(*) FROM (SELECT " + complexClause + " FROM " + ResourceStresserConstants.TABLENAME_CPUTABLE + " WHERE empid >= 0 AND empid < 100) T2"); (CPU2)