Home » RDBMS Server » Performance Tuning » improve performance using partitioning (oracle 11g)
improve performance using partitioning [message #655132] Mon, 22 August 2016 17:55 Go to next message
tarundas2016
Messages: 2
Registered: August 2016
Junior Member
Problem definition:- Existing Java Web application which sends 300 insertion of records per Second from past 3 months in Oracle 11g database.This DB has no indexing on tables and performance is slow .So we need to use partitioning in using PL/SQL script in such a way that script takes input as date range of 3 days(i.e. current date + 2 days back data) data on one Main table and data older than 3 days in partition table

Now can some one please help in creating a PL-SQL script which can do fulfill above requirement

I have attached DDL file
--create the table--

	CREATE TABLE "OWNING_ROAD" 
   ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"EMP_RAILROAD_IDENTIFIER" VARCHAR2(4 BYTE), 
	"RAILROAD_NAME" VARCHAR2(50 BYTE), 
	"SCAC" VARCHAR2(4 BYTE) NOT NULL ENABLE,
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"UPDATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"VERSION" NUMBER(19,0),
	 PRIMARY KEY ("ID"),
	 UNIQUE ("RAILROAD_NAME"),
	 UNIQUE ("SCAC")
	 );
	 
CREATE TABLE "DIVISION" 
   (	"ID" NUMBER(19,0) NOT NULL ENABLE, 
	"AD_LINK" VARCHAR2(50 BYTE), 
	"CONTACT_EMAIL" VARCHAR2(50 BYTE), 
	"CONTACT_NAME" VARCHAR2(50 BYTE), 
	"CONTACT_PHONE_1" VARCHAR2(50 BYTE), 
	"CONTACT_PHONE_2" VARCHAR2(50 BYTE), 
	"DIVISION_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
	"OWNING_ROAD_ID" NUMBER(19,0),
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"UPDATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"VERSION" NUMBER(19,0),
	 PRIMARY KEY ("ID"),
  	 FOREIGN KEY ("OWNING_ROAD_ID")
	 REFERENCES  "OWNING_ROAD" ("ID") ENABLE
  	 );

CREATE TABLE "SUBDIVISION" 
   (	"ID" NUMBER(19,0) NOT NULL ENABLE, 
	"AD_LINK" VARCHAR2(50 BYTE), 
	"CONTACT_EMAIL" VARCHAR2(50 BYTE), 
	"CONTACT_NAME" VARCHAR2(50 BYTE), 
	"CONTACT_PHONE_1" VARCHAR2(50 BYTE), 
	"CONTACT_PHONE_2" VARCHAR2(50 BYTE), 
	"DEPLOYMENT_STATUS" VARCHAR2(255 BYTE), 
	"SUBDIVISION_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
	"DIVISION_ID" NUMBER(19,0),
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"UPDATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"VERSION" NUMBER(19,0),
	 PRIMARY KEY ("ID"),
	 FOREIGN KEY ("DIVISION_ID")
	 REFERENCES "DIVISION" ("ID") ENABLE
	 );
	 
CREATE TABLE "AREA" 
   	("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"AREA_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	"ATCS_ADDRESS" NUMBER(19,0), 
	"EMP_ADDRESS" VARCHAR2(63 BYTE),
	"AREA_TYPE" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"SUBDIVISION_ID" NUMBER(19,0),
	"OWNING_ROAD_ID" NUMBER(19,0) NOT NULL ENABLE, 
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"UPDATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"VERSION" NUMBER(19,0),
	 PRIMARY KEY ("ID"),
	 FOREIGN KEY ("SUBDIVISION_ID")
	 REFERENCES "SUBDIVISION" ("ID") ENABLE,
  	 FOREIGN KEY ("OWNING_ROAD_ID")
	 REFERENCES  "OWNING_ROAD" ("ID") ENABLE
	 );
	 
CREATE TABLE "CUSTOM_AREA_FIELDS" 
    ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"KEY" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	"VALUE" VARCHAR2(255 BYTE), 
	"AREA_ID" NUMBER(19,0),
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"UPDATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"VERSION" NUMBER(19,0),
	 PRIMARY KEY ("ID"),
	 FOREIGN KEY ("AREA_ID")
	 REFERENCES "AREA" ("ID") ENABLE
	 );

 CREATE TABLE "ASSET" 
   ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"ASSET_TYPE" NUMBER(10,0), 
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	"DEPLOYMENT_DATE" DATE, 
	"DEPLOYMENT_STATUS" VARCHAR2(255 BYTE), 
	"EMP_ADDRESS" VARCHAR2(63 BYTE) NOT NULL ENABLE, 
	"GPS_COORDINATES" SDO_GEOMETRY, 
	"HARDWARE_VERSION" VARCHAR2(255 BYTE), 
	"IN_SERVICE_DATE" DATE, 
	"LOCATION" NUMBER(10,0), 
	"MANUFACTURER" VARCHAR2(255 BYTE), 
	"PREVIOUS_ASSET_LINK" VARCHAR2(255 BYTE), 
	"SERIAL_NUMBER" NUMBER(19,2) NOT NULL ENABLE, 
	"SMID" VARCHAR2(32 BYTE) NOT NULL ENABLE, 
	"SOFTWARE_VERSION" VARCHAR2(255 BYTE), 
	"STATUS" NUMBER(10,0), 
	"STATE" NUMBER(10,0),
	"STATUS_STATE_UPDATED_DATE" DATE,
	"UPDATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	"AREA_ID" NUMBER(19,0), 
	"VERSION" NUMBER(19,0),
	 PRIMARY KEY ("ID"),
  	 UNIQUE ("SMID"),
     FOREIGN KEY ("AREA_ID")
	 REFERENCES "AREA" ("ID") ENABLE
   );

CREATE TABLE "CUSTOM_ASSET_FIELDS" 
    ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"KEY" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	"VALUE" VARCHAR2(255 BYTE), 
	"ASSET_ID" NUMBER(19,0),
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"UPDATED_DATE" DATE NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 BYTE) NOT NULL ENABLE,
	"VERSION" NUMBER(19,0),
	 PRIMARY KEY ("ID"),
	 FOREIGN KEY ("ASSET_ID")
	 REFERENCES "ASSET" ("ID") ENABLE
	 );

CREATE TABLE "ASSET_EVENT" 
   ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"CREATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"UPDATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"VERSION" NUMBER(19,0), 
	"ASSET_LOCATION" VARCHAR2(255 CHAR), 
	"COMPONENT_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"CONDITION_FLAG" NUMBER(10,0) NOT NULL ENABLE, 
	"DATE_TIME" TIMESTAMP (6) NOT NULL ENABLE, 
	"EVENT_INSTANCE_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"EVENT_TYPE_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"ASSET_ID" NUMBER(19,0) NOT NULL ENABLE, 
	 PRIMARY KEY ("ID"),
     FOREIGN KEY ("ASSET_ID")
	 REFERENCES "ASSET" ("ID") ENABLE
   );

   CREATE TABLE "ASSET_STATUS" 
   ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"CREATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"UPDATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"VERSION" NUMBER(19,0), 
	"ASSET_LOCATION" VARCHAR2(255 CHAR), 
	"COMPONENT_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"RESPONSE_CODE" NUMBER(10,0), 
	"STATE" NUMBER(10,0), 
	"STATUS" NUMBER(10,0), 
	"STATUS_STATE_UPDATED_DATE" TIMESTAMP (6), 
	"ASSET_ID" NUMBER(19,0) NOT NULL ENABLE, 
	 PRIMARY KEY ("ID"),
	 FOREIGN KEY ("ASSET_ID")
	 REFERENCES "ASSET" ("ID") ENABLE
   );
   
   CREATE TABLE "EVENT_VARIABLE" 
   ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"CREATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"UPDATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"VERSION" NUMBER(19,0), 
	"VALUE" BLOB, 
	"VARIABLE_ID" NUMBER(10,0), 
	"EVENT_ID" NUMBER(19,0) NOT NULL ENABLE, 
	 PRIMARY KEY ("ID"),
	 FOREIGN KEY ("EVENT_ID")
	 REFERENCES "ASSET_EVENT" ("ID") ENABLE
   );
   
   CREATE TABLE "STATUS_VARIABLE" 
   ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"CREATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"UPDATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"VERSION" NUMBER(19,0), 
	"VALUE" BLOB, 
	"VARIABLE_ID" NUMBER(10,0), 
	"STATUS_ID" NUMBER(19,0) NOT NULL ENABLE, 
	 PRIMARY KEY ("ID"),
	 FOREIGN KEY ("STATUS_ID")
	 REFERENCES "ASSET_STATUS" ("ID") ENABLE
   );
   
   CREATE TABLE "ASSET_VARIABLES" 
   ("ID" NUMBER(19,0) NOT NULL ENABLE, 
	"CREATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"CREATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"UPDATED_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"UPDATED_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"VERSION" NUMBER(19,0),
    "VARIABLE_ID" NUMBER(10,0) NOT NULL ENABLE,
    "VALUE" BLOB,
	"ASSET_ID" NUMBER(19,0)NOT NULL ENABLE, 
	 PRIMARY KEY ("ID"),
     CONSTRAINT ASSET_VARIABLE_ID_SMID UNIQUE ("VARIABLE_ID","ASSET_ID"),
	 FOREIGN KEY ("ASSET_ID")
	 REFERENCES "ASSET" ("ID") ENABLE
   );
   
   CREATE SEQUENCE  "HIBERNATE_SEQUENCE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOPARTITION;
   
  • Attachment: itcview.sql
    (Size: 7.36KB, Downloaded 55 times)

[Updated on: Mon, 22 August 2016 18:19] by Moderator

Report message to a moderator

Re: improve performance using partitioning [message #655134 is a reply to message #655132] Mon, 22 August 2016 18:25 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
partition will NOT improve performance if/when all the INSERTS continue to go into the same partition.
You have post no evidence to support you claim regarding the slowness and no proof that partitioning is what is required to make it (what ever it may be) faster.
If the code is doing 300 individual single rows every seconds, I am not sure there is any silver bullet to make things faster.

ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) ddl for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: improve performance using partitioning [message #655143 is a reply to message #655132] Tue, 23 August 2016 01:48 Go to previous messageGo to next message
John Watson
Messages: 7263
Registered: January 2010
Location: Global Village
Senior Member
Quote:
.This DB has no indexing on tables and performance is slow
This statement is not correct. You have indexes on all the tables, in some cases several. They will have been created implicitly when you defined the primary key and unique key constraints.

What is the SQL that you are trying to tune? You forgot to mention it.
Re: improve performance using partitioning [message #655144 is a reply to message #655143] Tue, 23 August 2016 02:39 Go to previous message
Roachcoach
Messages: 1513
Registered: May 2010
Location: UK
Senior Member
Rule #1 of tuning: find out what you are waiting on and fix that.

What checks have you done to see what you are waiting on and what evidence do you have that partitioning will help and not hinder?
Previous Topic: slow running sql
Next Topic: Tuning Multiple Not Exists
Goto Forum:
  


Current Time: Fri Feb 23 04:59:03 CST 2018

Total time taken to generate the page: 0.03563 seconds