Case Description

Out of curiosity and better answers to other people's questions - "How many TPS can oracle support?" The following tests were carried out.

Two, problems

How much is the performance difference between Oracle's high concurrent Insert transaction partition table and regular table?

Three, design

3.1 Data Design

Designs the common table and partition table with the same structure, two indexes, as follows:

• Regular table

create table qq_sale (so_no varchar(10),product_name varchar(10),qty number) tablespace cbs;
alter table qq_sale add constraint pk_so_no primary key(so_no) using index tablespace cbsinx;
create index idx_qq_sale_02 on qq_sale(last_updated_date) tablespace cbsinx;
create sequence seq_qq_sale maxvalue 99999999999999 cache 1000 noorder;

• The partition table is divided into 20 zones using single HASH partition.

CREATE TABLE DBAHQQ.qq_sale_part
(
  so_no              VARCHAR2(10),
  product_name       VARCHAR2(10),
  qty                NUMBER,
  last_updated_date  DATE
)
NOCOMPRESS 
RESULT_CACHE (MODE DEFAULT)
STORAGE    (
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING
PARTITION BY HASH (so_no)
(  
  PARTITION qq_sale_part_P1
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P2
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P3
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P4
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P5
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P6
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P7
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P8
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P9
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P10
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P11
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P12
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P13
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P14
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P15
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P16
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P17
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P18
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P19
    TABLESPACE CBS,  
  PARTITION qq_sale_part_P20
    TABLESPACE CBS
)
NOCACHE
NOPARALLEL
NOMONITORING;


ALTER TABLE DBAHQQ.qq_sale_part ADD (
  CONSTRAINT qq_sale_part_PK
  PRIMARY KEY
  (so_no)
  ENABLE VALIDATE);
  


CREATE INDEX DBAHQQ.IDX_QQ_SALE_PART_02 ON DBAHQQ.QQ_SALE_PART
(LAST_UPDATED_DATE)
  STORAGE    (
              BUFFER_POOL      DEFAULT
              FLASH_CACHE      DEFAULT
              CELL_FLASH_CACHE DEFAULT
             )
LOGGING
LOCAL (  
  PARTITION QQ_SALE_PART_P1
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P2
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P3
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P4
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P5
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P6
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P7
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P8
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P9
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P10
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P11
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P12
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P13
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P14
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P15
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P16
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P17
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P18
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P19
    TABLESPACE CBSINX,  
  PARTITION QQ_SALE_PART_P20
    TABLESPACE CBSINX
)
NOPARALLEL;
  
create index idx_qq_sale_02 on qq_sale_part(last_updated_date) tablespace cbsinx ;

3.2 Test scenario design

• The test tool is python's self-compiled and concurrent pressure measurement tool. The code is as follows:

• Scenario Description: 300 concurrent users, each user completes 10,000 insert transactions, without setting think_time, respectively, to pressure the conventional table and partition table.
• SQL statement: insert into qqq_sale values (seq_qq_sale. nextval,'toothpaste', 1,sysdate());

3.3 Pressure measurement results

• Partition table results
  • A total of 300w transactions were completed
  • Time: 0:04:14.432000
  • TPS: 11800
  • Main waiting events (from high to low): log file sync, latch: enqueue hash chains, library cache: mutex X, latch: ges resources Hash list
• Regular table
  • A total of 300w transactions were completed
  • Time: 0:08:31.432000
  • TPS: 5870
  • Main waiting events (from high to low): buffer busy waits, enq: TX - index contention, latch: ges resources hash list, log file sync

Four. Conclusion

• Single transaction, partitioned tables with the same business design and regular tables, using partitioned tables performance is twice as high as conventional tables.
• Common problems with high concurrent transaction databases:
  • Firstly, memory contention is a problem. Partitioned tables are used to effectively disperse the distribution of tables and index data blocks, and reduce memory conflicts when high concurrency occurs.
  • The second is IO problem. Using high performance IO devices can effectively reduce IO response time, such as SSD, PCIE SSD and so on.
  • Third, application design distribution. Although RAC and single instance are not tested in this case, there is a well-known convention that GC competition will occur when multiple instances of RAC are used. When designing, it is necessary to distribute the partition tables on different instances according to the average distribution of partition tables, and read by service. It is necessary to avoid one partition data appearing on two instances at the same time.
• local index partitions must be used when partitioning tables to design indexes.