Sample Transaction

	Location : TOP > Usage > Sample Transaction

	Step1 Preparation Step2 Load "SMPL_TRANS_OLTPvX.X.tgp" Step3 Create a sample schema and dummy data Step4 Start transactions Step5 Check performance Step6 Stop transactions Step7 Drop a sample schema

	>> TOP

This chapter explains how to use the sample configuration for ORACLE - smpl_oltp_oracle_vX.X.tgp - which is attached with TG version 0.60 later. This sample is composed by a very simple object and SQL statements considering the transaction of the OLTP system.

		Step1	- Preparation -

In the begining, verify that your target database server meets the following requirements. If there is something not satisfied, change your database parameter or correct the sample configration for your system.

* CONFIGRATION FOR THE SAMPLE TRANSACTION
Database server IP address	192.168.100.100
SID name	testdb
Listener port number	1521
Password for SYSTEM database user	manager
Specified tablespace exists and has a enough space	USERS
Specified temporary tablespace exists and has a enough space	TEMP

		Step2	- Load the configration "SMPL_TRANS_OLTPvX.X.tgp" -

Startup TGC.

* COMMAND-LINE SAMPLE
C:\> cd tg08X C:\tg08X> tgc

Load the sample configration file. Click "File" menu and select "Open". To the next, choose "smpl_oltp_oracle_vX.X.tgp" file on the "Open" window.

		Step3	- Create a sample schema and dummy data -

Create a sample schema for the sample transaction. Click "Jobs" menu and select "Execute specified Job", execute "1. BUILD_SCHEMA" Job.

The result of "1.BUILD_SCHEMA" job.

TG> execute job 1

>> Execute job No : 1 [ 1.BUILD_SCHEMA ]

SQL No.0 : create user TEST identified by test
default tablespace USERS
temporary tablespace TEMP
System altered.

SQL No.1 : grant CONNECT,RESOURCE to TEST
System altered.

SQL No.2 : create table TEST.ITEMLIST(
ITEM_ID number,
CATEGORY number,
ITEM_NAME varchar2(32),
DESCRIPTION varchar2(255))
nologging
System altered.

SQL No.3 : create table TEST.ORDERCOUNT(
ITEM_ID number,
ORDER_COUNT number)
nologging
System altered.

SQL No.4 : create table TEST.ORDERLIST(
ORDER_ID number,
ITEM_ID number,
ORDER_DATE date)
System altered.

SQL No.5 : create sequence TEST.SEQ_ORDER_ID start with 1 increment by 1
nomaxvalue nominvalue nocycle nocache
System altered.

SQL No.6 : select object_name, object_type from dba_objects
where owner = 'TEST' order by object_type
SEQ_ORDER_ID SEQUENCE
ORDERLIST TABLE
ORDERCOUNT TABLE
ITEMLIST TABLE
4 rows selected.
Specified job successfully executed.

TG>

When specified job has completed successfully, following objects are created.

※OBJECTS
database user	TEST
Table x 3	ITEMLIST ORDERCOUNT ORDERLIST
Sequence	SEQ_ORDER_ID

To the next, create a dummy data. Click "Jobs" menu and select "Execute specified Job", execute "2. GENERATE_DATA" Job.

The result of "2.GENERATE_DATA" job.

TG> execute job 2

>> Execute job No : 2 [ 2.GENERATE_DATA ]
declare

ITEM_ID number;

CATEGORY number;
begin

ITEM_ID := 1;

CATEGORY := 1;

for i in 1..1000 loop

for ii in 1..50 loop

insert /*+APPEND*/ into ITEMLIST values(

ITEM_ID,

CATEGORY,

'N2345678901234567890123456789012',

'D2345678901234567890123456789012345678901234567890123

insert /*+APPEND*/ into ORDERCOUNT values(ITEM_ID, 0);

ITEM_ID := ITEM_ID + 1;

end loop;

CATEGORY := CATEGORY + 1;

commit;

end loop;
end;

Specified job successfully executed.

TG>

When specified job has completed successfully, 50000 dummy records are inserted into the "ITEMLIST" and "ORDERCOUNT" table. It may take a few minutes depending on your database server.

Finally, Create a indexes. Click "Jobs" menu and select "Execute specified Job", execute "3. BUILD_INDEXES" Job. It may also take a few minutes.

The result of "3.BUILD_INDEXES" job.

TG> execute job 3

>> Execute job No : 3 [ 3.BUILD_INDEXES ]

SQL No.0 : alter table TEST.ITEMLIST
add constraint PK_ITEMLIST_ITEM_ID primary key(ITEM_ID)
System altered.

SQL No.1 : create index TEST.IDX_ITEMLIST_CATEGORY on TEST.ITEMLIST(CATEGORY)
System altered.

SQL No.2 : alter table TEST.ORDERCOUNT
add constraint PK_ORDERCOUNT_ITEM_ID primary key(ITEM_ID)
System altered.

SQL No.3 : alter table TEST.ORDERLIST
add constraint PK_ORDERLIST_ORDER_ID primary key(ORDER_ID)
System altered.

SQL No.4 : create index TEST.IDX_ORDERLIST_ITEM_ID on TEST.ORDERLIST(ITEM_ID)
System altered.

SQL No.5 : create index TEST.IDX_ORDERLIST_ORDER_DATE on TEST.ORDERLIST(ORDER_DATE)
System altered.

SQL No.6 : alter table TEST.ITEMLIST logging
System altered.

SQL No.7 : alter table TEST.ORDERCOUNT logging
System altered.

SQL No.8 : select object_name, object_type from dba_objects
where owner = 'TEST' order by object_type
PK_ITEMLIST_ITEM_ID INDEX
IDX_ORDERLIST_ITEM_ID INDEX
PK_ORDERLIST_ORDER_ID INDEX
PK_ORDERCOUNT_ITEM_ID INDEX
IDX_ORDERLIST_ORDER_DATE INDEX
IDX_ITEMLIST_CATEGORY INDEX
SEQ_ORDER_ID SEQUENCE
ORDERLIST TABLE
ITEMLIST TABLE
ORDERCOUNT TABLE
10 rows selected.
Specified job successfully executed.

TG>

		Step4	- Start transactions -

Click "Agent" menu on the main window of TGC and select "Make agnets connect to database". You can make agents connect to the target database.

To the next, Click "Agent" menu and select "Activate agents". You can begin to put load (agents send SQL requests) on the database server.

The sample transaction is composed with following two kinds of SQL statements. Transaction "VIEW_ITEMS" is executed at 90% and "ORDER" is at 10%.

TRANSACTION NAME	RATIO	SQL
VIEW_ITEMS	90%	select * from ITEMLIST where CATEGORY = :VAL_CATEGORY;
ORDER	10%	insert into ORDERLIST values (SEQ_ORDER_ID.nextval, :VAL_ITEM_ID, sysdate); update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = :VAL_ITEM_ID; commit;

The above ":VAL_CATEGORY" and ":VAL_ITEM_ID" are random variables. The range of the value is as follows.

VARIABLE NAME	RANGE
:VAL_CATEGORY	1 - 1000
:VAL_ITEM_ID	1 - 50000

		Step5	- Check performance -

You can confirm the transaction statistics. Click "Monitor" menu and select "Monitoring start > show summary".

The status of transaction execution is displayed on the "TG Console" window. The value of "TPS:" on the righ edge is a number of execution of the transaction per second.

TG> Monitoring start. [ 600 Seconds ]
21:23:09 TRGT:ACTV CONN:10/10 DISC:0/10 TERM:0/10 ERR:0 TPS:10
21:23:10 TRGT:ACTV CONN:10/10 DISC:0/10 TERM:0/10 ERR:0 TPS:10
21:23:11 TRGT:ACTV CONN:10/10 DISC:0/10 TERM:0/10 ERR:0 TPS:10
21:23:12 TRGT:ACTV CONN:10/10 DISC:0/10 TERM:0/10 ERR:0 TPS:10
21:23:13 TRGT:ACTV CONN:10/10 DISC:0/10 TERM:0/10 ERR:0 TPS:10
21:23:14 TRGT:ACTV CONN:10/10 DISC:0/10 TERM:0/10 ERR:0 TPS:10
21:23:15 TRGT:ACTV CONN:10/10 DISC:0/10 TERM:0/10 ERR:0 TPS:10

The monitoring will end after five minutes by default. The value of "Average TPS:" is average of TPS in 10 minutes.

21:33:07 TRGT:ACTV CONN:10/10 DISC:0/10 TERM:0/10 ERR:0 TPS:10
21:33:08 TRGT:ACTV CONN:10/10 DISC:0/10 TERM:0/10 ERR:0 TPS:10
---- TRANSACTION REPORT ( SUMMARY ) ------------------

MONITORING PERIOD
~~~~~~~~~~~~~~~~~
- Begin : Sat May 10 21:23:09 JST 2008
- End: Sat May 10 21:33:08 JST 2008

THROUGHPUT
~~~~~~~~~~
Transaction nameMinMaxAverage
================ ==== ===== =======
VIEW_ITEMS0ms109ms 4.48ms
ORDER0ms266ms 8.62ms

TPS
~~~
- Total transaction count : 5975
- Monitoring time: 600 seconds
- Average TPS: 9.96

TG>

When you stop the monitoring forcely, Click "Monitor" menu and select "Monitoring stop forcely". When you change the monitoring period, select "Specify monitoring period" and input (in millisecond) the value.

		Step6	- Stop transactions -

When you stop the transaction, Click "Agent" menu and select "Freeze agents".

And, Click "Agent" menu and select "Make agents disconnect from database".

		Step7	- Drop a sample schema -

If you want to drop sample data and objects, Click "Jobs" menu and select "Execute specified Job", execute "4. DROP_SCHEMA" Job.


	>> TOP > Usage > Sample transaction