a collection of scripts to ease the day-to-day work of an Oracle DBA
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 
 
 

73 lines
2.7 KiB

/* =============================================================================
Creating a test environment and setup/schedule load tests
-----------------------------------------------------------------------------
This requires a user with CREATE ANY JOB (and CREATE TABLE) privileges.
Replace with your own definitions. This example uses:
* ERMADM as above described administrative user
* APPADM, APPUSER, APPBATCH, APPOTHER as example users of the different groups
Further, the following example values are set which you can adjust:
* maxrows: how many rows to insert on each job run (100000, in resman_load)
* jobs_per_user: how many jobs should be created per user (10, in DECLARE)
-----------------------------------------------------------------------------
Given the above example users, make sure you give your "master user" (the one
used to run THIS script) the required privilege to do so:
GRANT CREATE ANY JOB TO ERMADM;
Then run this as ERMADM. When done, you can of course drop the table
resman_test_temp again.
========================================================================== */
CREATE GLOBAL TEMPORARY TABLE resman_test_temp (
id NUMBER,
datum DATE
) ON COMMIT PRESERVE ROWS;
GRANT INSERT,UPDATE,DELETE ON resman_test_temp TO PUBLIC;
CREATE PUBLIC SYNONYM resman_test_temp ON ermadm.resman_test_temp; -- make it available to all by the same name
COMMENT ON TABLE resman_test_temp IS 'Global temporary table for ResourceManager Tests';
CREATE OR REPLACE PROCEDURE resman_load AS
/* Lasttest für ResourceManager
* WARNING: DOES NOT TERMINATE before 1 hour is up
*/
maxrows NUMBER := 100000;
i NUMBER := 0;
safeguard DATE;
BEGIN
safeguard := SYSDATE + 1/24;
WHILE SYSDATE < safeguard LOOP
i := 0;
WHILE i < maxrows LOOP
INSERT INTO resman_test_temp (id,datum) VALUES (i,SYSDATE);
i := i+1;
END LOOP;
COMMIT;
DELETE FROM resman_test_temp;
COMMIT;
END LOOP;
END;
/
DECLARE
TYPE array_u IS VARRAY(4) OF VARCHAR2(30);
job_users array_u := array_u('appadm','appuser','appbatch','appother');
jobs_per_user NUMBER := 10;
i NUMBER; u NUMBER;
BEGIN
FOR u IN 1 .. job_users.count LOOP
FOR i IN 1 .. jobs_per_user LOOP
dbms_scheduler.create_job(
job_name => job_users(u)||'.ResManTestJob_'||i, -- '[schema_name.]job_name'; requires CREATE [ANY] JOB privilege
job_type => 'STORED_PROCEDURE',
job_action => 'ermadm.resman_load',
comments => 'ResMan Load Test'
);
END LOOP;
END LOOP;
END;
/
--dbms_scheduler.run_job (job_name => 'run_load_sales');
--dbms_scheduler.stop_job (job_name => 'run_load_sales');
--dbms_scheduler.drop_job ('run_load_sales');