Home » SQL & PL/SQL » SQL & PL/SQL » trigger impacting performance (11g32 linux(RHEL))
trigger impacting performance [message #666839] Tue, 28 November 2017 05:22 Go to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Hi Guys,

We have large table (14 million rows) call it source table,its partitioned and used for java based application and also to generate report/dashboard from it,since its become large generation was slow
so we created temp (staging table) -call it target table which should contain about 5000 records filtered from source table for reporting
to accomplish this we written trigger on souce table ,under few condition(for filter) it will insert or update rows in target table
for few days it worked fine,but now we are seeing that its impacting operation on source table application getting hanged ,on disabling trigger
found application working normal

in trigger nothing much just updating,inserting based on 2 checks(if conditions),and no where in the trigger select on source table
so why trigger should impact application

please suggest

Thanks

Report message to a moderator

Re: trigger impacting performance [message #666840 is a reply to message #666839] Tue, 28 November 2017 05:28 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
You are not going to like this, but....

Your approach of using a "temp" table is probably wrong. Instead, you need to tune the SQL that generates the reports from the "large" table. If the table, partitioning, and index structures are appropriate and the SQL is well written, the performance will not degrade as the number of rows increases.

Report message to a moderator

Re: trigger impacting performance [message #666841 is a reply to message #666840] Tue, 28 November 2017 06:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>We have large table (14 million rows)
14 Million rows is TINY; not Large.

It is rare that Oracle requires an "temp" table & "temp" table should be avoided.

Report message to a moderator

Re: trigger impacting performance [message #666842 is a reply to message #666839] Tue, 28 November 2017 06:24 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
In addition to the other comments, it sounds like your triggers are simply trying to reinvent functionality that Oracle already provides with materialized views.

Report message to a moderator

Re: trigger impacting performance [message #666844 is a reply to message #666842] Tue, 28 November 2017 08:11 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the temp table is aggregated data then most likely you're suffering from locks as multiple transactions cause the same the row(s) in the temp table to be modified.
As Ed mentioned, materialized views exist for this purpose.

Report message to a moderator

Re: trigger impacting performance [message #666845 is a reply to message #666844] Tue, 28 November 2017 08:12 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Past that - if you want to understand for sure why the triggers are slowing you down - trace the sessions.

Report message to a moderator

Re: trigger impacting performance [message #666853 is a reply to message #666844] Tue, 28 November 2017 22:24 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
No aggregate data temp table is indeoendent just trigger is inserting temp table while inserting main table and temp us small but why insert in temp should block or lock main table trigger is inserting with old new variable of trigger not touching main table directly000

Report message to a moderator

Re: trigger impacting performance [message #666854 is a reply to message #666842] Tue, 28 November 2017 22:27 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Both tables on same db still msterislised view advisable

Report message to a moderator

Re: trigger impacting performance [message #666855 is a reply to message #666853] Tue, 28 November 2017 22:45 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
while insert update main table ,trigger using :old value for checks and if condition is satisfied itse insert or update and :new for inserting in temp table(used for reporting)

Thanks

Report message to a moderator

Re: trigger impacting performance [message #666856 is a reply to message #666855] Tue, 28 November 2017 22:49 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
ALso when trigger is disables operation return to normal on using main table,so suspecting role of trigger in performance degradation

Report message to a moderator

Re: trigger impacting performance [message #666858 is a reply to message #666856] Wed, 29 November 2017 03:18 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you want any more help from us with the trigger you are going to have to show us the code.
Materialized views don't just exist to move data between DB's - their primary purpose is to store the data of a view query to improve performance.
Ideal solution is to improve the performance of the query so you don't need another table or materialized view - if you want help with that you'll need to post the query.
Next best solution is to turn the table into a materialized view.

Report message to a moderator

Re: trigger impacting performance [message #666859 is a reply to message #666858] Wed, 29 November 2017 05:12 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Hi,

Below is DDL of master table and trigger source

temp(staging) table is same as master table but no constraints,index,partition and no trigger attached to it




CREATE TABLE "schema1"."Master1"
( "IDENTIFIER" VARCHAR2(255 BYTE),
"SERIAL" NUMBER(*,0),
"NODE" VARCHAR2(64 BYTE) NOT NULL ENABLE,
"NODEALIAS" VARCHAR2(64 BYTE),
"MANAGER" VARCHAR2(64 BYTE),
"AGENT" VARCHAR2(64 BYTE),
"ALERTGROUP" VARCHAR2(255 BYTE),
"ALERTKEY" VARCHAR2(255 BYTE),
"SEVERITY" NUMBER(*,0),
"SUMMARY" VARCHAR2(255 BYTE),
"STATECHANGE" DATE,
"FIRSTOCCURRENCE" DATE,
"LASTOCCURRENCE" DATE,
"INTERNALLAST" DATE,
"POLL" NUMBER(*,0),
"TALLY" NUMBER(*,0),
"TYPE" NUMBER(*,0),
"CLASS" NUMBER(*,0),
"GRADE" NUMBER(*,0),
"LOCATION" VARCHAR2(64 BYTE),
"OWNERGID" NUMBER(*,0),
"OWNERUID" NUMBER(*,0),
"ACKNOWLEDGED" NUMBER(*,0),
"ACKNOWLEDGEDTIME" DATE,
"FLASH" NUMBER(*,0),
"EVENTID" VARCHAR2(255 BYTE),
"EXPIRETIME" NUMBER(*,0),
"SUPPRESSESCL" NUMBER(*,0),
"CUSTOMER" VARCHAR2(64 BYTE),
"SERVICE" VARCHAR2(256 BYTE),
"PHYSICALCARD" VARCHAR2(64 BYTE),
"PHYSICALPORT" NUMBER(*,0),
"PHYSICALSLOT" NUMBER(*,0),
"TASKLIST" NUMBER(*,0),
"LOCALNODEALIAS" VARCHAR2(64 BYTE),
"LOCALPRIOBJ" VARCHAR2(255 BYTE),
"LOCALROOTOBJ" VARCHAR2(255 BYTE),
"LOCALSECOBJ" VARCHAR2(255 BYTE),
"REMOTENODEALIAS" VARCHAR2(64 BYTE),
"REMOTEPRIOBJ" VARCHAR2(255 BYTE),
"REMOTEROOTOBJ" VARCHAR2(255 BYTE),
"REMOTESECOBJ" VARCHAR2(255 BYTE),
"X733CORRNOTIF" VARCHAR2(255 BYTE),
"X733EVENTTYPE" NUMBER(*,0),
"X733PROBABLECAUSE" NUMBER(*,0),
"X733SPECIFICPROB" VARCHAR2(64 BYTE),
"SERVERNAME" VARCHAR2(64 BYTE) NOT NULL ENABLE,
"SERVERNAME_SERVERSERIAL" VARCHAR2(255 BYTE) NOT NULL ENABLE,
"SERVERSERIAL" NUMBER(*,0),
"URL" VARCHAR2(1024 BYTE),
"EXTENDEDATTR" VARCHAR2(4000 BYTE),
"PROBESUBSECONDID" NUMBER(*,0),
"COLLECTIONFIRST" DATE,
"AGGREGATIONFIRST" DATE,
"DISPLAYFIRST" DATE,
"ALARMDETAILS" VARCHAR2(255 BYTE),
"ASSIGNEDTIME" DATE,
"ESCALATEDTALLY" NUMBER(*,0),
"ESCALATEDTIME" DATE,
"IMPACTSTATUS" NUMBER(*,0),
"PURGEFLAG" NUMBER(*,0),
"ALARMPRIORITY" NUMBER(*,0),
"REPORTINGUSER" VARCHAR2(64 BYTE),
"POSSIBLEROOTCAUSE" NUMBER(*,0),
"SUPPRESSTIME" DATE,
"WRITETOHEDB" NUMBER(*,0),
"CUSTOMERID" NUMBER(*,0),
"CUSTOMERDETAILS" VARCHAR2(2000 BYTE),
"ALERTSTATUS" NUMBER(*,0),
"ALERTTYPE" NUMBER(*,0),
"MANAGEDOBJCLASS" VARCHAR2(64 BYTE),
"FLAPCOUNT" NUMBER(*,0),
"FLAPWINDOW" NUMBER(*,0),
"FLAPINDICATOR" NUMBER(*,0),
"GEOINFO" VARCHAR2(255 BYTE),
"INHAND" VARCHAR2(2 BYTE),
"INHANDEXPIRETIME" DATE,
"PARENTPOINTER" VARCHAR2(512 BYTE),
"PROCESSINGTIME" NUMBER(*,0),
"SOURCESERVERNAME" VARCHAR2(64 BYTE),
"SOURCESERVERSERIAL" NUMBER(*,0),
"SOURCESTATECHANGE" DATE,
"NOTIFICATIONFLAG" NUMBER(*,0),
"NOTIFICATIONGROUP" VARCHAR2(100 BYTE),
"NOTIFICATIONRULEID" VARCHAR2(100 BYTE),
"NOTIFICATIONTIME" DATE,
"SVCAGGEVENT" NUMBER(*,0),
"CLEARTALLY" NUMBER(*,0),
"CLEARTIMESTAMP" DATE,
"NEFIRSTTIME" DATE,
"NELASTTIME" DATE,
"NETIMEMETHOD" NUMBER(*,0),
"ORIGINALSEVERITY" NUMBER(*,0),
"ACFLAG" NUMBER(*,0),
"RCASTATUS" NUMBER(*,0),
"RCAPROCESSINGTIME" NUMBER(*,0),
"RCATALLY" NUMBER(*,0),
"RCATIMESTAMP" DATE,
"ROOTCAUSEDESC" VARCHAR2(256 BYTE),
"SERVICEID" VARCHAR2(64 BYTE),
"SIASTATUS" NUMBER(*,0),
"SERVICESIMPACTED" NUMBER(*,0),
"SERVICETYPE" VARCHAR2(64 BYTE),
"SIAPROCESSINGTIME" NUMBER(*,0),
"SIATALLY" NUMBER(*,0),
"SIATIMESTAMP" DATE,
"SERVICESTATUS" NUMBER(*,0),
"SERVICEAFFECTINGALARMS" VARCHAR2(1024 BYTE),
"COMMISSIONEDSTATE" VARCHAR2(64 BYTE),
"EQUIPMENTROLE" VARCHAR2(64 BYTE),
"EQUIPMENTTYPE" VARCHAR2(64 BYTE),
"EQUIPMENTKEY" VARCHAR2(255 BYTE),
"SRCDOMAIN" VARCHAR2(64 BYTE),
"SRCSUBDOMAIN" VARCHAR2(64 BYTE),
"SRCEMSIDENTIFIER" VARCHAR2(255 BYTE),
"TOPOLOGYKEY" VARCHAR2(255 BYTE),
"CORRELATETOPOLOGYKEY" VARCHAR2(255 BYTE),
"TTDISASSOCIATE" NUMBER(*,0),
"TTFLAG" NUMBER(*,0),
"TTPRIORITY" VARCHAR2(8 BYTE),
"TTPROBLEMDESC" VARCHAR2(4000 BYTE),
"TTREQUESTTIME" DATE,
"TTSEQUENCE" VARCHAR2(64 BYTE),
"TTSERVER" VARCHAR2(64 BYTE),
"TTSERVICEAFFECTED" VARCHAR2(64 BYTE),
"TTSLACOMMIT" DATE,
"TTSTATUS" VARCHAR2(64 BYTE),
"TTUPDATE" DATE,
"TTUSER" VARCHAR2(64 BYTE),
"TTWAITTIME" NUMBER(*,0),
"CAUSETYPE" NUMBER(*,0),
"ADVCORRSERVERNAME" VARCHAR2(64 BYTE),
"ADVCORRSERVERSERIAL" NUMBER(*,0),
"ADVCORRCAUSETYPE" NUMBER(*,0),
"LOCALTERTOBJ" VARCHAR2(255 BYTE),
"LOCALOBJRELATE" NUMBER(*,0),
"REMOTETERTOBJ" VARCHAR2(255 BYTE),
"REMOTEOBJRELATE" NUMBER(*,0),
"CORRSCORE" NUMBER(*,0),
"CUSTOMATTR1" VARCHAR2(1024 BYTE),
"CUSTOMATTR2" VARCHAR2(255 BYTE),
"CUSTOMATTR3" VARCHAR2(255 BYTE),
"CUSTOMATTR4" VARCHAR2(255 BYTE),
"CUSTOMATTR5" VARCHAR2(255 BYTE),
"DELETEDAT" DATE,
"CUSTOMATTR6" VARCHAR2(255 BYTE),
"CUSTOMATTR7" VARCHAR2(255 BYTE),
"CUSTOMATTR8" VARCHAR2(255 BYTE),
"CUSTOMATTR9" VARCHAR2(255 BYTE),
"CUSTOMATTR10" VARCHAR2(255 BYTE),
"CUSTOMATTR11" VARCHAR2(255 BYTE),
"CUSTOMATTR12" VARCHAR2(255 BYTE),
"CUSTOMATTR13" VARCHAR2(255 BYTE),
"CUSTOMATTR14" VARCHAR2(255 BYTE),
"CUSTOMATTR15" VARCHAR2(255 BYTE),
"CUSTOMATTR16" VARCHAR2(255 BYTE),
"CUSTOMATTR17" VARCHAR2(255 BYTE),
"CUSTOMATTR18" VARCHAR2(255 BYTE),
"CUSTOMATTR19" VARCHAR2(255 BYTE),
"CUSTOMATTR20" VARCHAR2(255 BYTE),
"CUSTOMATTR21" VARCHAR2(255 BYTE),
"CUSTOMATTR22" VARCHAR2(255 BYTE),
"CUSTOMATTR23" VARCHAR2(255 BYTE),
"CUSTOMATTR24" VARCHAR2(255 BYTE),
"CUSTOMATTR25" VARCHAR2(255 BYTE),
"CUSTOMATTR26" VARCHAR2(255 BYTE),
"CUSTOMATTR27" NUMBER(*,0),
"CUSTOMATTR28" NUMBER(*,0),
"CUSTOMATTR29" DATE,
"CUSTOMATTR30" DATE,
"CLEAREDBY" VARCHAR2(256 BYTE),
"OUTAGEDURATION" NUMBER(*,0),
"TTCI" VARCHAR2(256 BYTE),
"INVID" NUMBER(*,0),
"INVLOCATIONID" NUMBER(*,0),
"VENDORNAME" VARCHAR2(64 BYTE),
"FLUCTUATIONCATEGORY" NUMBER,
"INVPROTECTION" NUMBER(*,0),
"RCAPARENTHISTORY" VARCHAR2(1000 BYTE),
"RCAWEIGHTFACTOR" NUMBER(*,0),
PRIMARY KEY ("SERVERNAME", "SERVERSERIAL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "schema1_STAT_IDX" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "schema1_STAT_DAT"
PARTITION BY RANGE ("LASTOCCURRENCE")
(PARTITION "STATUS_MDA_SEM_DAT_201709" VALUES LESS THAN (TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "schema1_STAT_DAT" ,
PARTITION "STATUS_MDA_SEM_DAT_201710" VALUES LESS THAN (TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "schema1_STAT_DAT" ,
PARTITION "STATUS_MDA_SEM_DAT_201711" VALUES LESS THAN (TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "schema1_STAT_DAT" ,
PARTITION "STATUS_MDA_SEM_DAT_201712" VALUES LESS THAN (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "schema1_STAT_DAT" ) ENABLE ROW MOVEMENT ;

CREATE INDEX "schema1"."IX1_Master1" ON "schema1"."Master1" ("CLEARTIMESTAMP", "FIRSTOCCURRENCE", "TTSEQUENCE", "PARENTPOINTER")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA_SMALL" ;

CREATE INDEX "schema1"."IX_Master1" ON "schema1"."Master1" ("FIRSTOCCURRENCE", "ALERTGROUP", "CUSTOMATTR10", "EQUIPMENTKEY", "MANAGER", "CUSTOMATTR3", "AGENT", "NODE", "SEVERITY", "ORIGINALSEVERITY", "X733EVENTTYPE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA_SMALL" ;

CREATE INDEX "schema1"."I_Master1" ON "schema1"."Master1" ("LASTOCCURRENCE", "CLASS", "NODE", "SERVERNAME_SERVERSERIAL", "SERIAL", "SRCSUBDOMAIN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "schema1_STAT_IDX" ;

CREATE OR REPLACE TRIGGER "schema1"."UPDATE_SIA_Master1" AFTER INSERT OR UPDATE ON Master1
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
DECLARE
cnt NUMBER(3,1);
BEGIN
IF INSERTING THEN
Begin
SELECT COUNT(SERVERSERIAL) INTO cnt from SIA_Master1 WHERE SERVERSERIAL = :old.SERVERSERIAL;
if (cnt = 0) then
if(:new.FIRSTOCCURRENCE > to_date('10/19/2017 12:45:00','MM/DD/YYYY HH24:MI:SS')) then
INSERT INTO SIA_Master1 (IDENTIFIER,SERIAL,NODE,NODEALIAS,MANAGER,AGENT,ALERTGROUP,ALERTKEY,SEVERITY,SUMMARY,STATECHANGE,FIRSTOCCURRENCE,LASTOCCURRENCE,INTER NALLAST,POLL,TALLY,TYPE,CLASS,GRADE,LOCATION,OWNERGID,OWNERUID,ACKNOWLEDGED,ACKNOWLEDGEDTIME,FLASH,EVENTID,EXPIRETIME,SUPPRESSESCL,CU STOMER,SERVICE,PHYSICALCARD,PHYSICALPORT,PHYSICALSLOT,TASKLIST,LOCALNODEALIAS,LOCALPRIOBJ,LOCALROOTOBJ,LOCALSECOBJ,REMOTENODEALIAS,RE MOTEPRIOBJ,REMOTEROOTOBJ,REMOTESECOBJ,X733CORRNOTIF,X733EVENTTYPE,X733PROBABLECAUSE,X733SPECIFICPROB,SERVERNAME,SERVERNAME_SERVERSERI AL,SERVERSERIAL,URL,EXTENDEDATTR,PROBESUBSECONDID,COLLECTIONFIRST,AGGREGATIONFIRST,DISPLAYFIRST,ALARMDETAILS,ASSIGNEDTIME,ESCALATEDTA LLY,ESCALATEDTIME,IMPACTSTATUS,PURGEFLAG,ALARMPRIORITY,REPORTINGUSER,POSSIBLEROOTCAUSE,SUPPRESSTIME,WRITETOHEDB,CUSTOMERID,CUSTOMERDE TAILS,ALERTSTATUS,ALERTTYPE,MANAGEDOBJCLASS,FLAPCOUNT,FLAPWINDOW,FLAPINDICATOR,GEOINFO,INHAND,INHANDEXPIRETIME,PARENTPOINTER,PROCESSI NGTIME,SOURCESERVERNAME,SOURCESERVERSERIAL,SOURCESTATECHANGE,NOTIFICATIONFLAG,NOTIFICATIONGROUP,NOTIFICATIONRULEID,NOTIFICATIONTIME,S VCAGGEVENT,CLEARTALLY,CLEARTIMESTAMP,NEFIRSTTIME,NELASTTIME,NETIMEMETHOD,ORIGINALSEVERITY,ACFLAG,RCASTATUS,RCAPROCESSINGTIME,RCATALLY ,RCATIMESTAMP,ROOTCAUSEDESC,SERVICEID,SIASTATUS,SERVICESIMPACTED,SERVICETYPE,SIAPROCESSINGTIME,SIATALLY,SIATIMESTAMP,SERVICESTATUS,SE RVICEAFFECTINGALARMS,COMMISSIONEDSTATE,EQUIPMENTROLE,EQUIPMENTTYPE,EQUIPMENTKEY,SRCDOMAIN,SRCSUBDOMAIN,SRCEMSIDENTIFIER,TOPOLOGYKEY,C ORRELATETOPOLOGYKEY,TTDISASSOCIATE,TTFLAG,TTPRIORITY,TTPROBLEMDESC,TTREQUESTTIME,TTSEQUENCE,TTSERVER,TTSERVICEAFFECTED,TTSLACOMMIT,TT STATUS,TTUPDATE,TTUSER,TTWAITTIME,CAUSETYPE,ADVCORRSERVERNAME,ADVCORRSERVERSERIAL,ADVCORRCAUSETYPE,LOCALTERTOBJ,LOCALOBJRELATE,REMOTE TERTOBJ,REMOTEOBJRELATE,CORRSCORE,CUSTOMATTR1,CUSTOMATTR2,CUSTOMATTR3,CUSTOMATTR4,CUSTOMATTR5,DELETEDAT,CUSTOMATTR6,CUSTOMATTR7,CUSTO MATTR8,CUSTOMATTR9,CUSTOMATTR10,CUSTOMATTR11,CUSTOMATTR12,CUSTOMATTR13,CUSTOMATTR14,CUSTOMATTR15,CUSTOMATTR16,CUSTOMATTR17,CUSTOMATTR 18,CUSTOMATTR19,CUSTOMATTR20,CUSTOMATTR21,CUSTOMATTR22,CUSTOMATTR23,CUSTOMATTR24,CUSTOMATTR25,CUSTOMATTR26,CUSTOMATTR27,CUSTOMATTR28, CUSTOMATTR29,CUSTOMATTR30,CLEAREDBY,OUTAGEDURATION,TTCI,INVID,INVLOCATIONID,VENDORNAME,FLUCTUATIONCATEGORY)
values (:new.IDENTIFIER,:new.SERIAL,:new.NODE,:new.NODEALIAS,:new.MANAGER,:new.AGENT,:new.ALERTGROUP,:new.ALERTKEY,:new.SEVERITY,:new.SUMMAR Y,:new.STATECHANGE,:new.FIRSTOCCURRENCE,:new.LASTOCCURRENCE,:new.INTERNALLAST,:new.POLL,:new.TALLY,:new.TYPE,:new.CLASS,:new.GRADE,:n ew.LOCATION,:new.OWNERGID,:new.OWNERUID,:new.ACKNOWLEDGED,:new.ACKNOWLEDGEDTIME,:new.FLASH,:new.EVENTID,:new.EXPIRETIME,:new.SUPPRESS ESCL,:new.CUSTOMER,:new.SERVICE,:new.PHYSICALCARD,:new.PHYSICALPORT,:new.PHYSICALSLOT,:new.TASKLIST,:new.LOCALNODEALIAS,:new.LOCALPRI OBJ,:new.LOCALROOTOBJ,:new.LOCALSECOBJ,:new.REMOTENODEALIAS,:new.REMOTEPRIOBJ,:new.REMOTEROOTOBJ,:new.REMOTESECOBJ,:new.X733CORRNOTIF ,:new.X733EVENTTYPE,:new.X733PROBABLECAUSE,:new.X733SPECIFICPROB,:new.SERVERNAME,:new.SERVERNAME_SERVERSERIAL,:new.SERVERSERIAL,:new. URL,:new.EXTENDEDATTR,:new.PROBESUBSECONDID,:new.COLLECTIONFIRST,:new.AGGREGATIONFIRST,:new.DISPLAYFIRST,:new.ALARMDETAILS,:new.ASSIG NEDTIME,:new.ESCALATEDTALLY,:new.ESCALATEDTIME,:new.IMPACTSTATUS,:new.PURGEFLAG,:new.ALARMPRIORITY,:new.REPORTINGUSER,:new.POSSIBLERO OTCAUSE,:new.SUPPRESSTIME,:new.WRITETOHEDB,:new.CUSTOMERID,:new.CUSTOMERDETAILS,:new.ALERTSTATUS,:new.ALERTTYPE,:new.MANAGEDOBJCLASS, :new.FLAPCOUNT,:new.FLAPWINDOW,:new.FLAPINDICATOR,:new.GEOINFO,:new.INHAND,:new.INHANDEXPIRETIME,:new.PARENTPOINTER,:new.PROCESSINGTI ME,:new.SOURCESERVERNAME,:new.SOURCESERVERSERIAL,:new.SOURCESTATECHANGE,:new.NOTIFICATIONFLAG,:new.NOTIFICATIONGROUP,:new.NOTIFICATIO NRULEID,:new.NOTIFICATIONTIME,:new.SVCAGGEVENT,:new.CLEARTALLY,:new.CLEARTIMESTAMP,:new.NEFIRSTTIME,:new.NELASTTIME,:new.NETIMEMETHOD ,:new.ORIGINALSEVERITY,:new.ACFLAG,:new.RCASTATUS,:new.RCAPROCESSINGTIME,:new.RCATALLY,:new.RCATIMESTAMP,:new.ROOTCAUSEDESC,:new.SERV ICEID,:new.SIASTATUS,:new.SERVICESIMPACTED,:new.SERVICETYPE,:new.SIAPROCESSINGTIME,:new.SIATALLY,:new.SIATIMESTAMP,:new.SERVICESTATUS ,:new.SERVICEAFFECTINGALARMS,:new.COMMISSIONEDSTATE,:new.EQUIPMENTROLE,:new.EQUIPMENTTYPE,:new.EQUIPMENTKEY,:new.SRCDOMAIN,:new.SRCSU BDOMAIN,:new.SRCEMSIDENTIFIER,:new.TOPOLOGYKEY,:new.CORRELATETOPOLOGYKEY,:new.TTDISASSOCIATE,:new.TTFLAG,:new.TTPRIORITY,:new.TTPROBL EMDESC,:new.TTREQUESTTIME,:new.TTSEQUENCE,:new.TTSERVER,:new.TTSERVICEAFFECTED,:new.TTSLACOMMIT,:new.TTSTATUS,:new.TTUPDATE,:new.TTUS ER,:new.TTWAITTIME,:new.CAUSETYPE,:new.ADVCORRSERVERNAME,:new.ADVCORRSERVERSERIAL,:new.ADVCORRCAUSETYPE,:new.LOCALTERTOBJ,:new.LOCALO BJRELATE,:new.REMOTETERTOBJ,:new.REMOTEOBJRELATE,:new.CORRSCORE,:new.CUSTOMATTR1,:new.CUSTOMATTR2,:new.CUSTOMATTR3,:new.CUSTOMATTR4,: new.CUSTOMATTR5,:new.DELETEDAT,:new.CUSTOMATTR6,:new.CUSTOMATTR7,:new.CUSTOMATTR8,:new.CUSTOMATTR9,:new.CUSTOMATTR10,:new.CUSTOMATTR1 1,:new.CUSTOMATTR12,:new.CUSTOMATTR13,:new.CUSTOMATTR14,:new.CUSTOMATTR15,:new.CUSTOMATTR16,:new.CUSTOMATTR17,:new.CUSTOMATTR18,:new. CUSTOMATTR19,:new.CUSTOMATTR20,:new.CUSTOMATTR21,:new.CUSTOMATTR22,:new.CUSTOMATTR23,:new.CUSTOMATTR24,:new.CUSTOMATTR25,:new.CUSTOMA TTR26,:new.CUSTOMATTR27,:new.CUSTOMATTR28,:new.CUSTOMATTR29,:new.CUSTOMATTR30,:new.CLEAREDBY,:new.OUTAGEDURATION,:new.TTCI,:new.INVID ,:new.INVLOCATIONID,:new.VENDORNAME,:new.FLUCTUATIONCATEGORY);
end if;
end if;
END;
END IF;
IF UPDATING('DELETEDAT')THEN
delete from SIA_Master1 where SERVERSERIAL = :old.SERVERSERIAL and :new.DELETEDAT is not null;
END IF;
IF UPDATING THEN
UPDATE SIA_Master1 a set
a.IDENTIFIER =:new.IDENTIFIER,a.SERIAL=:new.SERIAL,a.NODE=:new.NODE,a.NODEALIAS=:new.NODEALIAS,a.MANAGER=:new.MANAGER,a.AGENT=:new.AGENT,
a.ALERTGROUP=:new.ALERTGROUP,a.ALERTKEY=:new.ALERTKEY,a.SEVERITY=:new.SEVERITY,a.SUMMARY=:new.SUMMARY,a.STATECHANGE=:new.STATECHANGE, a.FIRSTOCCURRENCE=:new.FIRSTOCCURRENCE,
a.LASTOCCURRENCE=:new.LASTOCCURRENCE,a.INTERNALLAST=:new.INTERNALLAST,a.POLL=:new.POLL,a.TALLY=:new.TALLY,a.TYPE=:new.TYPE,a.CLASS=:n ew.CLASS,a.GRADE=:new.GRADE,a.LOCATION=:new.LOCATION,a.OWNERGID=:new.OWNERGID,a.OWNERUID=:new.OWNERUID,a.ACKNOWLEDGED=:new.ACKNOWLEDG ED,
a.ACKNOWLEDGEDTIME=:new.ACKNOWLEDGEDTIME,a.FLASH=:new.FLASH,a.EVENTID=:new.EVENTID,a.EXPIRETIME=:new.EXPIRETIME,a.SUPPRESSESCL=:new.S UPPRESSESCL,a.CUSTOMER=:new.CUSTOMER,a.SERVICE=:new.SERVICE,a.PHYSICALCARD=:new.PHYSICALCARD,a.PHYSICALPORT=:new.PHYSICALPORT,
a.PHYSICALSLOT=:new.PHYSICALSLOT,a.TASKLIST=:new.TASKLIST,a.LOCALNODEALIAS=:new.LOCALNODEALIAS,a.LOCALPRIOBJ=:new.LOCALPRIOBJ,a.LOCAL ROOTOBJ=:new.LOCALROOTOBJ,a.LOCALSECOBJ=:new.LOCALSECOBJ,a.REMOTENODEALIAS=:new.REMOTENODEALIAS,a.REMOTEPRIOBJ=:new.REMOTEPRIOBJ,
a.REMOTEROOTOBJ=:new.REMOTEROOTOBJ,a.REMOTESECOBJ=:new.REMOTESECOBJ,a.X733CORRNOTIF=:new.X733CORRNOTIF,a.X733EVENTTYPE=:new.X733EVENT TYPE,a.X733PROBABLECAUSE=:new.X733PROBABLECAUSE,a.X733SPECIFICPROB=:new.X733SPECIFICPROB,a.SERVERNAME=:new.SERVERNAME,a.SERVERNAME_SE RVERSERIAL=:new.SERVERNAME_SERVERSERIAL,a.SERVERSERIAL=:new.SERVERSERIAL,a.URL=:new.URL,a.EXTENDEDATTR=:new.EXTENDEDATTR,a.PROBESUBSE CONDID=:new.PROBESUBSECONDID,a.COLLECTIONFIRST=:new.COLLECTIONFIRST,a.AGGREGATIONFIRST=:new.AGGREGATIONFIRST,a.DISPLAYFIRST=:new.DISP LAYFIRST,a.ALARMDETAILS=:new.ALARMDETAILS,
a.ASSIGNEDTIME=:new.ASSIGNEDTIME,a.ESCALATEDTALLY=:new.ESCALATEDTALLY,a.ESCALATEDTIME=:new.ESCALATEDTIME,a.IMPACTSTATUS=:new.IMPACTST ATUS,a.PURGEFLAG=:new.PURGEFLAG,a.ALARMPRIORITY=:new.ALARMPRIORITY,a.REPORTINGUSER=:new.REPORTINGUSER,a.POSSIBLEROOTCAUSE=:new.POSSIB LEROOTCAUSE,a.SUPPRESSTIME=:new.SUPPRESSTIME,a.WRITETOHEDB=:new.WRITETOHEDB,a.CUSTOMERID=:new.CUSTOMERID,a.CUSTOMERDETAILS=:new.CUSTO MERDETAILS,a.ALERTSTATUS=:new.ALERTSTATUS,a.ALERTTYPE=:new.ALERTTYPE,a.MANAGEDOBJCLASS=:new.MANAGEDOBJCLASS,a.FLAPCOUNT=:new.FLAPCOUN T,a.FLAPWINDOW=:new.FLAPWINDOW,a.FLAPINDICATOR=:new.FLAPINDICATOR,a.GEOINFO=:new.GEOINFO,a.INHAND=:new.INHAND,a.INHANDEXPIRETIME=:new .INHANDEXPIRETIME,a.PARENTPOINTER=:new.PARENTPOINTER,
a.PROCESSINGTIME=:new.PROCESSINGTIME,a.SOURCESERVERNAME=:new.SOURCESERVERNAME,a.SOURCESERVERSERIAL=:new.SOURCESERVERSERIAL,a.SOURCEST ATECHANGE=:new.SOURCESTATECHANGE,a.NOTIFICATIONFLAG=:new.NOTIFICATIONFLAG,a.NOTIFICATIONGROUP=:new.NOTIFICATIONGROUP,a.NOTIFICATIONRU LEID=:new.NOTIFICATIONRULEID,a.NOTIFICATIONTIME=:new.NOTIFICATIONTIME,a.SVCAGGEVENT=:new.SVCAGGEVENT,a.CLEARTALLY=:new.CLEARTALLY,a.C LEARTIMESTAMP=:new.CLEARTIMESTAMP,a.NEFIRSTTIME=:new.NEFIRSTTIME,a.NELASTTIME=:new.NELASTTIME,a.NETIMEMETHOD=:new.NETIMEMETHOD,a.ORIG INALSEVERITY=:new.ORIGINALSEVERITY,a.ACFLAG=:new.ACFLAG,a.RCASTATUS=:new.RCASTATUS,a.RCAPROCESSINGTIME=:new.RCAPROCESSINGTIME,a.RCATA LLY=:new.RCATALLY,a.RCATIMESTAMP=:new.RCATIMESTAMP,a.ROOTCAUSEDESC=:new.ROOTCAUSEDESC,a.SERVICEID=:new.SERVICEID,a.SIASTATUS=:new.SIA STATUS,a.SERVICESIMPACTED=:new.SERVICESIMPACTED,a.SERVICETYPE=:new.SERVICETYPE,a.SIAPROCESSINGTIME=:new.SIAPROCESSINGTIME,a.SIATALLY= :new.SIATALLY,a.SIATIMESTAMP=:new.SIATIMESTAMP,a.SERVICESTATUS=:new.SERVICESTATUS,a.SERVICEAFFECTINGALARMS=:new.SERVICEAFFECTINGALARM S,a.COMMISSIONEDSTATE=:new.COMMISSIONEDSTATE,a.EQUIPMENTROLE=:new.EQUIPMENTROLE,a.EQUIPMENTTYPE=:new.EQUIPMENTTYPE,a.EQUIPMENTKEY=:ne w.EQUIPMENTKEY,a.SRCDOMAIN=:new.SRCDOMAIN,a.SRCSUBDOMAIN=:new.SRCSUBDOMAIN,
a.SRCEMSIDENTIFIER=:new.SRCEMSIDENTIFIER,a.TOPOLOGYKEY=:new.TOPOLOGYKEY,a.CORRELATETOPOLOGYKEY=:new.CORRELATETOPOLOGYKEY,a.TTDISASSOC IATE=:new.TTDISASSOCIATE,a.TTFLAG=:new.TTFLAG,a.TTPRIORITY=:new.TTPRIORITY,a.TTPROBLEMDESC=:new.TTPROBLEMDESC,a.TTREQUESTTIME=:new.TT REQUESTTIME,a.TTSEQUENCE=:new.TTSEQUENCE,a.TTSERVER=:new.TTSERVER,a.TTSERVICEAFFECTED=:new.TTSERVICEAFFECTED,a.TTSLACOMMIT=:new.TTSLA COMMIT,a.TTSTATUS=:new.TTSTATUS,a.TTUPDATE=:new.TTUPDATE,a.TTUSER=:new.TTUSER,a.TTWAITTIME=:new.TTWAITTIME,a.CAUSETYPE=:new.CAUSETYPE ,a.ADVCORRSERVERNAME=:new.ADVCORRSERVERNAME,a.ADVCORRSERVERSERIAL=:new.ADVCORRSERVERSERIAL,a.ADVCORRCAUSETYPE=:new.ADVCORRCAUSETYPE,a .LOCALTERTOBJ=:new.LOCALTERTOBJ,a.LOCALOBJRELATE=:new.LOCALOBJRELATE,a.REMOTETERTOBJ=:new.REMOTETERTOBJ,a.REMOTEOBJRELATE=:new.REMOTE OBJRELATE,a.CORRSCORE=:new.CORRSCORE,a.CUSTOMATTR1=:new.CUSTOMATTR1,a.CUSTOMATTR2=:new.CUSTOMATTR2,a.CUSTOMATTR3=:new.CUSTOMATTR3,a.C USTOMATTR4=:new.CUSTOMATTR4,a.CUSTOMATTR5=:new.CUSTOMATTR5,a.DELETEDAT=:new.DELETEDAT,a.CUSTOMATTR6=:new.CUSTOMATTR6,a.CUSTOMATTR7=:n ew.CUSTOMATTR7,a.CUSTOMATTR8=:new.CUSTOMATTR8,a.CUSTOMATTR9=:new.CUSTOMATTR9,a.CUSTOMATTR10=:new.CUSTOMATTR10,a.CUSTOMATTR11=:new.CUS TOMATTR11,a.CUSTOMATTR12=:new.CUSTOMATTR12,a.CUSTOMATTR13=:new.CUSTOMATTR13,a.CUSTOMATTR14=:new.CUSTOMATTR14,a.CUSTOMATTR15=:new.CUST OMATTR15,a.CUSTOMATTR16=:new.CUSTOMATTR16,a.CUSTOMATTR17=:new.CUSTOMATTR17,a.CUSTOMATTR18=:new.CUSTOMATTR18,a.CUSTOMATTR19=:new.CUSTO MATTR19,a.CUSTOMATTR20=:new.CUSTOMATTR20,a.CUSTOMATTR21=:new.CUSTOMATTR21,a.CUSTOMATTR22=:new.CUSTOMATTR22,a.CUSTOMATTR23=:new.CUSTOM ATTR23,a.CUSTOMATTR24=:new.CUSTOMATTR24,a.CUSTOMATTR25=:new.CUSTOMATTR25,a.CUSTOMATTR26=:new.CUSTOMATTR26,a.CUSTOMATTR27=:new.CUSTOMA TTR27,a.CUSTOMATTR28=:new.CUSTOMATTR28,a.CUSTOMATTR29=:new.CUSTOMATTR29,a.CUSTOMATTR30=:new.CUSTOMATTR30,a.CLEAREDBY=:new.CLEAREDBY,a .OUTAGEDURATION=:new.OUTAGEDURATION,a.TTCI=:new.TTCI,a.INVID=:new.INVID,a.INVLOCATIONID=:new.INVLOCATIONID,a.VENDORNAME=:new.VENDORNA ME,a.FLUCTUATIONCATEGORY=:new.FLUCTUATIONCATEGORY
WHERE a.SERVERSERIAL=:old.SERVERSERIAL;
END IF;
--IF DELETING THEN
--delete from SIA_Master1 sia where sia.IDENTIFIER = :old.IDENTIFIER;
--DBMS_OUTPUT.PUT_LINE('Record successfully inserted into emp_backup table');
END;
/
ALTER TRIGGER "schema1"."UPDATE_SIA_Master1" DISABLE;

Report message to a moderator

Re: trigger impacting performance [message #666860 is a reply to message #666859] Wed, 29 November 2017 05:25 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
You need to index "schema1"."UPDATE_SIA_Master1".SERVERSERIAL.

Alternatively, you could consider following the advice you have been given regarding not using this technique at all.

Report message to a moderator

Re: trigger impacting performance [message #666861 is a reply to message #666860] Wed, 29 November 2017 05:42 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please read and follow How to use [code] tags and make your code easier to read?

As John says you need an index on serverserial. Currently the select count(*), the delete and the update will all be doing full table scans.

In addition:
In the insert part you're check if a row exists in sia_master1 and then doing a date check. Those should be the other way round, there's no point doing SQL if you can tell from local values that it's pointless.
If deletedat is updated then not only is the delete run but so is the update, and of course the only thing the update will do is chew up CPU as the data it's looking for has just been deleted.

The pk of master1 is serverserial AND servername, but the trigger code acts like it's just serverserial. Are you sure that's right?

Report message to a moderator

Re: trigger impacting performance [message #666862 is a reply to message #666861] Wed, 29 November 2017 06:05 Go to previous message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Thanks for the replies ,i will review recommendations and test accordingly

Report message to a moderator

Previous Topic: Packages
Next Topic: switch data positions
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 06:24:34 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.