BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle ATP Swingbench Tips

2019-07-15

Oracle ATP Swingbench Tips

ATP Swingbench

Reference documents

Setting up Swingbench for Oracle Autonomous Transaction Processing (ATP)

Autonmous Transaction Processing

Connect with Oracle SQLcl Cloud Connection

Command Tips

./oewizard -cf ~/Wallet_ATPDEMO.zip \
		   -cs ATPDEMO_MEDIUM \
		   -ts DATA \
		   -dbap Welcome#2019 \
		   -dba admin \
		   -u soe \
		   -p Welcome#2019 \
		   -async_off \
		   -scale 5 \
		   -hashpart -create -cl -v

./sbutil -soe -cf ~/Wallet_ATPDEMO.zip -cs ATPDEMO_MEDIUM -u soe -p Welcome#2019 -tables

Sample:

It took 1:33 hours (Total Run Time) to complete…

[oracle@inst_demo bin]$ ./oewizard -cf ~/Wallet_ATPDEMO.zip \
>            -cs ATPDEMO_MEDIUM \
>            -ts DATA \
>            -dbap Welcome#2019 \
>            -dba admin \
>            -u soe \
>            -p Welcome#2019 \
>            -async_off \
>            -scale 5 \
>            -hashpart -create -cl -v
SwingBench Wizard
Author  :        Dominic Giles
Version :        2.6.0.1082

Running in Lights Out Mode using config file : ../wizardconfigs/oewizard.xml
Connecting to : jdbc:oracle:thin:@ATPDEMO_MEDIUM
Connected
Running script ../sql/soedgcreateuser.sql
The following statement failed : GRANT MANAGE SCHEDULER to soe : Due to : ORA-01031: insufficient privileges
The following statement failed : BEGIN
  $IF DBMS_DB_VERSION.VER_LE_11_2
  $THEN
	null;
  $ELSE
				-- The Following enables concurrent stats collection on Oracle Database 12c and Oracle Database 18c
				EXECUTE IMMEDIATE 'GRANT ALTER SYSTEM TO soe';
				DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(
						GRANTEE_NAME   => 'soe',
						PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',
						ADMIN_OPTION   => FALSE);
		$END
END; : Due to : ORA-06550: line 8, column 3:
PLS-00201: identifier 'DBMS_RESOURCE_MANAGER_PRIVS' must be declared
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 974 millisecond(s)
Starting run
Starting script ../sql/soedgdrop2.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 65 millisecond(s)
Starting script ../sql/soedgcreatetableshash2.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 762 millisecond(s)
Starting script ../sql/soedgviews.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 23 millisecond(s)
Starting script ../sql/soedgsqlset.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 716 millisecond(s)
Inserting data into table ADDRESSES_3750001
Inserting data into table ADDRESSES_2
Inserting data into table CUSTOMERS_2500001
Inserting data into table CUSTOMERS_2
Completed processing table CUSTOMERS_2 in 0:05:04
Inserting data into table ORDER_ITEMS_3574475
Inserting data into table ORDERS_3574476
Completed processing table CUSTOMERS_2500001 in 0:05:14
Completed processing table ADDRESSES_3750001 in 0:06:32
Inserting data into table ORDER_ITEMS_1
Inserting data into table ORDERS_2
Completed processing table ADDRESSES_2 in 0:06:44
Run time 0:12:18 : Running threads (4/4) : Percentage completed : 35.06
Completed processing table ORDERS_3574476 in 0:18:19
Inserting data into table CARD_DETAILS_3750001
Completed processing table ORDER_ITEMS_3574475 in 0:18:22
Inserting data into table CARD_DETAILS_2
Completed processing table ORDERS_2 in 0:17:08
Inserting data into table LOGON_5957461
Completed processing table ORDER_ITEMS_1 in 0:17:08
Inserting data into table LOGON_2
Inserting data into table PRODUCT_INFORMATION
Completed processing table CARD_DETAILS_3750001 in 0:06:00
Inserting data into table INVENTORIES
Completed processing table PRODUCT_INFORMATION in 0:00:00
Completed processing table CARD_DETAILS_2 in 0:05:59
Inserting data into table PRODUCT_DESCRIPTIONS
Completed processing table PRODUCT_DESCRIPTIONS in 0:00:00
Inserting data into table WAREHOUSES
Completed processing table WAREHOUSES in 0:00:00
Completed processing table INVENTORIES in 0:00:08
Completed processing table LOGON_2 in 0:07:21
Connection cache closed
Starting script ../sql/soedganalyzeschema2.sql
The following statement failed : begin
	declare
		   jobs_count number := 0;
	begin
		select value into jobs_count from v$parameter
		jobs_count where name='job_queue_processes';
		$IF DBMS_DB_VERSION.VER_LE_10_2
		$THEN
		-- Use the default stats collection approach
			dbms_stats.gather_schema_stats(
				OWNNAME=> 'SOE'
				,ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE
				,BLOCK_SAMPLE=>TRUE
				,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'
				,DEGREE=> 16
				,GRANULARITY=>'ALL'
				,CASCADE=>TRUE);
		$ELSIF DBMS_DB_VERSION.VER_LE_11_2
		$THEN
			 -- Oracle Database 11g release 2. Enable concurrent stats collection
			 dbms_output.put_line('database version is less than or equal to 11.2');
			 DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','WAREHOUSES','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','INVENTORIES','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','PRODUCT_INFORMATION','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','PRODUCT_DESCRIPTIONS','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','ORDERENTRY_METADATA','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','CUSTOMERS','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','ADDRESSES','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','ORDER_ITEMS','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','ORDERS','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','LOGON','INCREMENTAL','TRUE');
			 DBMS_STATS.GATHER_SCHEMA_STATS('SOE');
		$ELSE
			 -- Oracle Database 12c and Oracle Database 18c. Concurrent Stats collection work slightly different in this release
			 execute immediate q'[ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DEFAULT_PLAN']';
			 if jobs_count < 16 then
				execute immediate q'[ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 16 ]';
			 end if;
			 execute immediate q'[ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 16 ]';
			 DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','MANUAL');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','WAREHOUSES','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','INVENTORIES','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','PRODUCT_INFORMATION','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','PRODUCT_DESCRIPTIONS','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','ORDERENTRY_METADATA','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','CUSTOMERS','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','ADDRESSES','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','ORDER_ITEMS','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','ORDERS','INCREMENTAL','TRUE');
			 DBMS_STATS.SET_TABLE_PREFS('SOE','LOGON','INCREMENTAL','TRUE');
			 DBMS_STATS.GATHER_SCHEMA_STATS('SOE');
		$END
	end;
end; : Due to : ORA-01031: insufficient privileges
ORA-06512: at line 36
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 74 millisecond(s)
Starting script ../sql/soedgconstraints2.sql
Script completed in 0 hour(s) 20 minute(s) 31 second(s) 879 millisecond(s)
Starting script ../sql/soedgindexes2.sql
Script completed in 0 hour(s) 41 minute(s) 21 second(s) 639 millisecond(s)
Starting script ../sql/soedgsequences2.sql
Script completed in 0 hour(s) 0 minute(s) 20 second(s) 343 millisecond(s)
Starting script ../sql/soedgpackage2_header.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 419 millisecond(s)
Starting script ../sql/soedgpackage2_body.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 325 millisecond(s)
Starting script ../sql/soedgsetupmetadata.sql
Script completed in 0 hour(s) 0 minute(s) 2 second(s) 830 millisecond(s)

============================================
|           Datagenerator Run Stats        |
============================================
Connection Time                        0:00:00.002
Data Generation Time                   0:31:04.614
DDL Creation Time                      1:02:19.205
Total Run Time                         1:33:23.825
Rows Inserted per sec                       32,454
Data Generated (MB) per sec                    2.6
Actual Rows Generated                   61,228,985
Commits Completed                            3,082
Batch Updates Completed                    306,168

Connecting to : jdbc:oracle:thin:@ATPDEMO_MEDIUM
Connected

Post Creation Validation Report
===============================
The schema appears to have been created successfully.

Valid Objects
=============
Valid Tables : 'ORDERS','ORDER_ITEMS','CUSTOMERS','WAREHOUSES','ORDERENTRY_METADATA','INVENTORIES','PRODUCT_INFORMATION','PRODUCT_DESCRIPTIONS','ADDRESSES','CARD_DETAILS'
Valid Indexes : 'PRD_DESC_PK','PROD_NAME_IX','PRODUCT_INFORMATION_PK','PROD_SUPPLIER_IX','PROD_CATEGORY_IX','INVENTORY_PK','INV_PRODUCT_IX','INV_WAREHOUSE_IX','ORDER_PK','ORD_SALES_REP_IX','ORD_CUSTOMER_IX','ORD_ORDER_DATE_IX','ORD_WAREHOUSE_IX','ORDER_ITEMS_PK','ITEM_ORDER_IX','ITEM_PRODUCT_IX','WAREHOUSES_PK','WHS_LOCATION_IX','CUSTOMERS_PK','CUST_EMAIL_IX','CUST_ACCOUNT_MANAGER_IX','CUST_FUNC_LOWER_NAME_IX','ADDRESS_PK','ADDRESS_CUST_IX','CARD_DETAILS_PK','CARDDETAILS_CUST_IX'
Valid Views : 'PRODUCTS','PRODUCT_PRICES'
Valid Sequences : 'CUSTOMER_SEQ','ORDERS_SEQ','ADDRESS_SEQ','LOGON_SEQ','CARD_DETAILS_SEQ'
Valid Code : 'ORDERENTRY'
Schema Created
[oracle@inst_demo bin]$

Other tips: sqlcl

sql connect atp

[opc@inst_demo ~]$ which sql
~/sqlcl/bin/sql
[opc@inst_demo ~]$ sql /nolog

SQLcl: Release 19.1 Production on Mon Jul 15 07:32:41 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


SQL> !ls -tlr
total 48336
drwxr-xr-x.  4 opc opc       28 Apr  4 16:18 sqlcl
drwx------. 12 opc opc     4096 Jun  7 17:39 swingbench
-rw-rw-r--.  1 opc opc 27573881 Jul 10 05:10 swingbench.zip
drwxrwxr-x.  3 opc opc       18 Jul 10 08:13 oradiag_opc
drwxrwxr-x.  3 opc opc     4096 Jul 10 08:47 instance_client
-rw-rw-r--.  1 opc opc       13 Jul 10 08:54 password.txt
-rw-rw-r--.  1 opc opc    20033 Jul 10 09:00 Wallet_ATPDEMO.zip
-rw-rw-r--.  1 opc opc 21886970 Jul 15 07:31 sqlcl-19.1.0.094.1619.zip

SQL> set cloudconfig Wallet_ATPDEMO.zip
Operation is successfully completed.
Operation is successfully completed.
Using temp directory:/tmp/oracle_cloud_config6220806518546255371
SQL> connect admin/Welcome#2019@atpdemo_tp
Connected.
SQL>

About the size of soe tables/indexes

[opc@inst_demo bin]$ ora segsize soe

TABLESPACE_NAME                SEGMENT_NAME                                SIZE_MB
------------------------------ ---------------------------------------- ----------
DATA                           PROD_CATEGORY_IX[Idx]                             0
DATA                           WHS_LOCATION_IX[Idx]                              0
DATA                           PROD_SUPPLIER_IX[Idx]                             0
DATA                           WAREHOUSES[Tab]                                   0
DATA                           WAREHOUSES_PK[Idx]                                0
DATA                           PRODUCT_INFORMATION_PK[Idx]                       0
DATA                           ORDERENTRY_METADATA[Tab]                          0
DATA                           PRD_DESC_PK[Idx]                                  0
DATA                           PROD_NAME_IX[Idx]                                 0
DATA                           PRODUCT_INFORMATION[Tab]                          0
DATA                           PRODUCT_DESCRIPTIONS[Tab]                         0
DATA                           INV_WAREHOUSE_IX[Idx]                            15
DATA                           INV_PRODUCT_IX[Idx]                              15
DATA                           INVENTORY_PK[Idx]                                18
DATA                           INVENTORIES[Tab]                                 19
DATA                           CUST_ACCOUNT_MANAGER_IX[Idx]                    106
DATA                           CUSTOMERS_PK[Idx]                               112
DATA                           CUST_DOB_IX[Idx]                                128
DATA                           ORD_SALES_REP_IX[Idx]                           151
DATA                           CUST_FUNC_LOWER_NAME_IX[Idx]                    159
DATA                           ORDER_PK[Idx]                                   160
DATA                           ORD_CUSTOMER_IX[Idx]                            165
DATA                           ADDRESS_PK[Idx]                                 168
DATA                           CARD_DETAILS_PK[Idx]                            168
DATA                           CARDDETAILS_CUST_IX[Idx]                        173
DATA                           ADDRESS_CUST_IX[Idx]                            173
DATA                           ORD_WAREHOUSE_IX[Idx]                           173
DATA                           ORD_ORDER_DATE_IX[Idx]                          182
DATA                           CUST_EMAIL_IX[Idx]                              218
DATA                           ITEM_PRODUCT_IX[Idx]                            445
DATA                           ITEM_ORDER_IX[Idx]                              488
DATA                           LOGON                                           512
DATA                           CARD_DETAILS                                    512
DATA                           ORDER_ITEMS_PK[Idx]                             536
DATA                           ADDRESSES                                       768
DATA                           CUSTOMERS                                       768
DATA                           ORDERS                                          976
DATA                           ORDER_ITEMS                                    1536
[opc@inst_demo bin]$
[opc@inst_demo bin]$ ./sbutil -soe -cf ~/Wallet_ATPDEMO.zip -cs ATPDEMO_MEDIUM -u soe -p Welcome#2019 -val
Operation is successfully completed.
Operation is successfully completed.
The Order Entry Schema appears to be valid.
--------------------------------------------------
|Object Type    |     Valid|   Invalid|   Missing|
--------------------------------------------------
|Table          |        10|         0|         0|
|Index          |        26|         0|         0|
|Sequence       |         5|         0|         0|
|View           |         2|         0|         0|
|Code           |         1|         0|         0|
--------------------------------------------------

[opc@inst_demo bin]$
[opc@inst_demo bin]$ ./sbutil -soe -cf ~/Wallet_ATPDEMO.zip -cs ATPDEMO_MEDIUM -u soe -p Welcome#2019 -tables
Operation is successfully completed.
Operation is successfully completed.
Order Entry Schemas Tables
----------------------------------------------------------------------------------------------------------------------
|Table Name                  |                Rows|              Blocks|           Size|   Compressed?|  Partitioned?|
----------------------------------------------------------------------------------------------------------------------
|ORDER_ITEMS                 |                   0|                   0|          1.5GB|              |           Yes|
|ORDERS                      |                   0|                   0|        976.0MB|              |           Yes|
|CUSTOMERS                   |                   0|                   0|        768.0MB|              |           Yes|
|ADDRESSES                   |                   0|                   0|        768.0MB|              |           Yes|
|LOGON                       |                   0|                   0|        512.0MB|              |           Yes|
|CARD_DETAILS                |                   0|                   0|        512.0MB|              |           Yes|
|INVENTORIES                 |                   0|                   0|         19.0MB|      Disabled|            No|
|PRODUCT_DESCRIPTIONS        |                   0|                   0|          320KB|      Disabled|            No|
|PRODUCT_INFORMATION         |                   0|                   0|          256KB|      Disabled|            No|
|WAREHOUSES                  |                   0|                   0|           64KB|      Disabled|            No|
|ORDERENTRY_METADATA         |                   0|                   0|           64KB|      Disabled|            No|
----------------------------------------------------------------------------------------------------------------------
															Total Space           5.0GB
[opc@inst_demo bin]$

parameters in SwingBench

-cf tells oewizard the location of the credentials file
-cs is the connecting for the service of the ATP instance. It is based on the name of the instance and is of the form followed by one of the following _low, _medium,_high,_parallel
-ts is the name of the table space to install swingbench into. It is currently always “data”
-dba is the admin user, currently this is always admin
-dbap is the password you specified at the creation of the ATP instance
-u is the name you want to give to the user you are installing swingbench into (I’d recommend soe)
-p is the password for the user. It needs to follow the password complexity rules of ATP
-async_off you need to disable the wizards default behavior of using async commits. This is currently prohibited on ATP
-scale indicates the size of the schema you want to create where a scale of 1 will generate 1GB of data. The indexes will take an additional amount of space roughly half the size of the data. A scale of 10 will generate a 10GB of data and roughly of 5GB of indexes
-hashpart tells the wizard to use hash partitioning
-create tells swingbench to create the schema (-drop will delete the schema)
-cl tells swingbech to run in character mode
-v tells swingbench to output whats going on (verbose mode)

Test ScaleUp cpu cores

Test command:

./charbench -c ../configs/SOE_Server_Side_V2.xml \
-v users,tpm,tps \
-intermin 0 \
-intermax 0 \
-min 0 \
-max 0 \
-uc 32 \
-di SQ,WQ,WA \
-cf ~/Wallet_ATPDEMO.zip \
-cs atpdemo_tp \
-u soe \
-p Welcome#2019 \
-v user,tpm,tps \
-intermin 0 \
-intermax 0 \
-uc 32 \
-di SQ,WQ,WA \
-rt 0:10.30

Scale Up/Down: set CPU core count from 2 to 4.

ATP

Have a good work&life! 2019/07 via LinHong


Similar Posts

Comments