OraFAQ Forum: Performance Tuning » improve performance using partitioning

Home » RDBMS Server » Performance Tuning » improve performance using partitioning (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

improve performance using partitioning [message #655132]

Mon, 22 August 2016 17:55

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 1500 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

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: improve performance using partitioning [message #655143 is a reply to message #655132]

Tue, 23 August 2016 01:48

John Watson
Messages: 8928
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.

Report message to a moderator

Re: improve performance using partitioning [message #655144 is a reply to message #655143]

Tue, 23 August 2016 02:39

Roachcoach
Messages: 1576
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?

Report message to a moderator

Previous Topic:	slow running sql
Next Topic:	Tuning Multiple Not Exists

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 05:33:24 CDT 2024