Home » RDBMS Server » Performance Tuning » performance tuning (oracle 10g)
performance tuning [message #496926] Thu, 03 March 2011 03:42 Go to next message
manidotkandan
Messages: 16
Registered: February 2011
Junior Member
Hi,

I have a table (CONTEXT_UT) in a remote oracle DB. I attached the structure.
Here the primary key is
START_DATE, CONTEXTID, UTID.
Each day we have 80000 records roughly.
It has partitioned on START_DATE

I need to log the change in column values (from TCELL to HCSUSG_IDLEMODE) for each CONTEXTID, UTID comparing the previous day's values. I need the column name also in the target (refer target table).
Please note if any CONTEXTID and UTID don't find data on the previous day then we have to go further (day - 2). We can go up to 3 days before. But we should take the latest previous value to compare

I attached the query also to get this. Is there any possibility to increase the performance. Currently it takes 7 to 8 mins to insert 19000 records. I mean i found 19000 changes.
To my knowledge i explained the issue. Reply me for any doubt.
  • Attachment: scr.txt
    (Size: 14.48KB, Downloaded 120 times)
Re: performance tuning [message #496937 is a reply to message #496926] Thu, 03 March 2011 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58903
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data.

Before, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: performance tuning [message #496949 is a reply to message #496937] Thu, 03 March 2011 04:55 Go to previous messageGo to next message
manidotkandan
Messages: 16
Registered: February 2011
Junior Member
Hi,

I attached the latest script with test data and the result i need.

Thanks
Manikandan
  • Attachment: scr.txt
    (Size: 23.51KB, Downloaded 96 times)
Re: performance tuning [message #496968 is a reply to message #496949] Thu, 03 March 2011 06:00 Go to previous messageGo to next message
manidotkandan
Messages: 16
Registered: February 2011
Junior Member
Hi,

Sorry. I forgot to include this. The following are the types i used in the query.

create or replace type Name_Value_Pair as object (param_name varchar2(30), param_value varchar2(255), old_param_value varchar2(255))
create or replace type Name_Value_varray as varray(275) of Name_Value_Pair
Re: performance tuning [message #496973 is a reply to message #496968] Thu, 03 March 2011 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 58903
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many of us can't or don't want to download files.
So please post it inline without forgetting to format it as explained in the guide.

Regards
Michel
Re: performance tuning [message #497145 is a reply to message #496973] Fri, 04 March 2011 03:05 Go to previous messageGo to next message
manidotkandan
Messages: 16
Registered: February 2011
Junior Member
Hi,

Formatted script as per the guide

Source Table
============

CREATE TABLE CONTEXT_UT
(
   START_DATE            DATE,
   CONTEXTID             VARCHAR2 (64 BYTE),
   UTID                  VARCHAR2 (64 BYTE),
   TCELL                 VARCHAR2 (64 BYTE),
   CELLRESERVED          VARCHAR2 (64 BYTE),
   TRESELECTION          VARCHAR2 (64 BYTE),
   QUALMEASQTY           VARCHAR2 (64 BYTE),
   QHYST1                VARCHAR2 (64 BYTE),
   QHYST2                VARCHAR2 (64 BYTE),
   QQUALMIN              VARCHAR2 (64 BYTE),
   QRXLEVMIN             VARCHAR2 (64 BYTE),
   INDIVOFFSET           VARCHAR2 (64 BYTE),
   PWRADM                VARCHAR2 (64 BYTE),
   PWRADMOFFSET          VARCHAR2 (64 BYTE),
   PWROFFSET             VARCHAR2 (64 BYTE),
   PWRHYST               VARCHAR2 (64 BYTE),
   TMCONGACTION          VARCHAR2 (64 BYTE),
   RELEASEASEDL          VARCHAR2 (64 BYTE),
   ASEDLADM              VARCHAR2 (64 BYTE),
   DLCODEADM             VARCHAR2 (64 BYTE),
   ASEULADM              VARCHAR2 (64 BYTE),
   SF8ADM                VARCHAR2 (64 BYTE),
   SF32ADM               VARCHAR2 (64 BYTE),
   ASEULADMOFFSET        VARCHAR2 (64 BYTE),
   MINPWRRL              VARCHAR2 (64 BYTE),
   MAXRATE               VARCHAR2 (64 BYTE),
   INTERRATE             VARCHAR2 (64 BYTE),
   MINIMUMRATE           VARCHAR2 (64 BYTE),
   MAXPWRMAX             VARCHAR2 (64 BYTE),
   INTERPWRMAX           VARCHAR2 (64 BYTE),
   MINPWRMAX             VARCHAR2 (64 BYTE),
   COMPMODEADM           VARCHAR2 (64 BYTE),
   IFOFFSET              VARCHAR2 (64 BYTE),
   IFHYST                VARCHAR2 (64 BYTE),
   IFCONG                VARCHAR2 (64 BYTE),
   INTFQFDDMEAIND        VARCHAR2 (64 BYTE),
   SRATSEARCH            VARCHAR2 (64 BYTE),
   SINTRASEARCH          VARCHAR2 (64 BYTE),
   SINTERSEARCH          VARCHAR2 (64 BYTE),
   FACHMEAOCACYLNCOEF    VARCHAR2 (64 BYTE),
   ACCESSCLSNBARR        VARCHAR2 (64 BYTE),
   MAXTXPOWERUL          VARCHAR2 (64 BYTE),
   BEMARGINASEUL         VARCHAR2 (64 BYTE),
   BEMARGINASEDL         VARCHAR2 (64 BYTE),
   SIB1PLMNSPVALTAG      VARCHAR2 (64 BYTE),
   SF16ADM               VARCHAR2 (64 BYTE),
   BEMARGINDLPWR         VARCHAR2 (64 BYTE),
   BEMARGINDLCODE        VARCHAR2 (64 BYTE),
   HOTYPE                VARCHAR2 (64 BYTE),
   USEDFRQTHR2DECNO      VARCHAR2 (64 BYTE),
   USEDFRQTHR2DRSCP      VARCHAR2 (64 BYTE),
   ADMINSTATE            VARCHAR2 (64 BYTE),
   LDSHARGSMTHRES        VARCHAR2 (64 BYTE),
   LDSHARGSMFRACT        VARCHAR2 (64 BYTE),
   SNDIRRETRYTGT         VARCHAR2 (64 BYTE),
   NINSYNCIND            VARCHAR2 (64 BYTE),
   RLFAILURET            VARCHAR2 (64 BYTE),
   NOUTSYNCIND           VARCHAR2 (64 BYTE),
   SF4ADMUL              VARCHAR2 (64 BYTE),
   HARDIFHOCORR          VARCHAR2 (64 BYTE),
   HSDPAUSERSADM         VARCHAR2 (64 BYTE),
   LDSHARMARG            VARCHAR2 (64 BYTE),
   RELASEDLGHS           VARCHAR2 (64 BYTE),
   TMCONGACTGHS          VARCHAR2 (64 BYTE),
   TMINITIALGHS          VARCHAR2 (64 BYTE),
   SF4ULPATHLOSSTHRES    VARCHAR2 (64 BYTE),
   ULPATHLOSSCHECKENA    VARCHAR2 (64 BYTE),
   SHCSRAT               VARCHAR2 (64 BYTE),
   SF16GADM              VARCHAR2 (64 BYTE),
   RELEASEASEDLNG        VARCHAR2 (64 BYTE),
   TMCONGACTIONNG        VARCHAR2 (64 BYTE),
   TMINITIALG            VARCHAR2 (64 BYTE),
   SF16ADMUL             VARCHAR2 (64 BYTE),
   SF8ADMUL              VARCHAR2 (64 BYTE),
   POS                   VARCHAR2 (2000 BYTE),
   RESERVEDBY            VARCHAR2 (20 BYTE),
   EULNOSRVCELLUSRADM    VARCHAR2 (64 BYTE),
   ACCCLBARREDCS         VARCHAR2 (64 BYTE),
   ACCCLBARREDPS         VARCHAR2 (64 BYTE),
   AGPSENABLED           VARCHAR2 (64 BYTE),
   ASLDTHUSP_AMR12200    VARCHAR2 (64 BYTE),
   ASLDTHUSP_AMR7950     VARCHAR2 (64 BYTE),
   ASLDTHUSP_AMR5900     VARCHAR2 (64 BYTE),
   CDLDTHRDLSF128        VARCHAR2 (64 BYTE),
   EULSRVCEUSRADM        VARCHAR2 (64 BYTE),
   IUBLINKREF            VARCHAR2 (256 BYTE),
   PWRLOADTHRDLSP        VARCHAR2 (512 BYTE),
   RTSELPSINT_CHANTYP    VARCHAR2 (64 BYTE),
   RTSELPSINT_ULPRFRT    VARCHAR2 (64 BYTE),
   RTSELPSINT_DLPRFRT    VARCHAR2 (64 BYTE),
   SF8GADMUL             VARCHAR2 (64 BYTE),
   AMRWBRATEDLMAX        NUMBER,
   AMRWBRATEULMAX        NUMBER,
   HCSSIB3CON_SSRCHHCS   NUMBER,
   HCSUSG_CONECTEDMODE   NUMBER,
   HCSUSG_IDLEMODE       NUMBER
)


Sample Data
============
INSERT INTO CONTEXT_UT
    VALUES (TO_DATE ('02/17/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
            'BRHMALXSCR0R03',
            'ALBHU1034A',
            '0',
            '1',
            '1',
            '2',
            '2',
            '2',
            '-20',
            '-107',
            '0',
            '75',
            '15',
            '300',
            '2000',
            '1',
            '500',
            '85',
            '500',
            '2',
            '32',
            '-180',
            '40690',
            '7760',
            '1590',
            '42',
            '38',
            '0',
            '15',
            '6000',
            '621',
            '1',
            '6',
            '23',
            '22',
            '4',
            '0',
            '24',
            '1',
            '16',
            '1',
            '-12',
            '-106',
            '1',
            '100',
            '100',
            '3',
            '10',
            '10',
            '6',
            '3',
            '32',
            '0',
            '-105',
            '16',
            '3',
            '800',
            '3000',
            '50',
            '8',
            '-4025014',
            '10',
            '0',
            '0',
            '1',
            '0',
            '0',
            '100',
            '0',
            '16',
            '',
            '',
            '0',
            '64',
            '64',
            '8',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            'SubNetwork=ONRM_RootMo_R,SubNetwork=BRHMALXSCR0R03,MeContext=BRHMALXSCR0R03,ManagedElement=1,RncFunction=1,IubLink=Iub_ALBHU1034',
            '<es:pwrLoadThresholdDlSpeech>  <es:amr12200>50</es:amr12200>  <es:amr5900>100</es:amr5900>  <es:amr7950>0</es:amr7950></es:pwrLoadThresholdDlSpeech>',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '');

INSERT INTO CONTEXT_UT
    VALUES (TO_DATE ('02/17/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
            'BRHMALXSCR0R03',
            'ALBHU1034B',
            '0',
            '1',
            '1',
            '2',
            '2',
            '2',
            '-20',
            '-107',
            '0',
            '75',
            '15',
            '300',
            '2000',
            '1',
            '500',
            '85',
            '500',
            '2',
            '32',
            '-180',
            '40690',
            '7760',
            '1590',
            '42',
            '38',
            '0',
            '15',
            '6000',
            '621',
            '1',
            '6',
            '23',
            '22',
            '4',
            '0',
            '24',
            '1',
            '16',
            '1',
            '-12',
            '-106',
            '1',
            '100',
            '100',
            '3',
            '10',
            '10',
            '6',
            '3',
            '32',
            '0',
            '-105',
            '16',
            '3',
            '800',
            '3000',
            '50',
            '8',
            '-4025014',
            '10',
            '0',
            '0',
            '1',
            '0',
            '0',
            '100',
            '0',
            '16',
            '',
            '',
            '0',
            '64',
            '64',
            '8',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            'SubNetwork=ONRM_RootMo_R,SubNetwork=BRHMALXSCR0R03,MeContext=BRHMALXSCR0R03,ManagedElement=1,RncFunction=1,IubLink=Iub_ALBHU1034',
            '<es:pwrLoadThresholdDlSpeech>  <es:amr12200>50</es:amr12200>  <es:amr5900>100</es:amr5900>  <es:amr7950>0</es:amr7950></es:pwrLoadThresholdDlSpeech>',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '');

INSERT INTO CONTEXT_UT
    VALUES (TO_DATE ('02/17/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
            'BRHMALXSCR0R04',
            'ALBHU1034T',
            '0',
            '1',
            '1',
            '2',
            '2',
            '2',
            '-20',
            '-107',
            '0',
            '75',
            '15',
            '300',
            '2000',
            '1',
            '500',
            '85',
            '500',
            '2',
            '32',
            '-180',
            '40690',
            '7760',
            '1590',
            '42',
            '38',
            '0',
            '15',
            '6000',
            '621',
            '1',
            '6',
            '23',
            '22',
            '4',
            '0',
            '24',
            '1',
            '16',
            '1',
            '-12',
            '-106',
            '1',
            '100',
            '100',
            '3',
            '10',
            '10',
            '6',
            '3',
            '32',
            '0',
            '-105',
            '16',
            '3',
            '800',
            '3000',
            '50',
            '8',
            '-4025014',
            '10',
            '0',
            '0',
            '1',
            '0',
            '0',
            '100',
            '0',
            '16',
            '',
            '',
            '0',
            '64',
            '64',
            '8',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            'SubNetwork=ONRM_RootMo_R,SubNetwork=BRHMALXSCR0R03,MeContext=BRHMALXSCR0R03,ManagedElement=1,RncFunction=1,IubLink=Iub_ALBHU1034',
            '<es:pwrLoadThresholdDlSpeech>  <es:amr12200>50</es:amr12200>  <es:amr5900>100</es:amr5900>  <es:amr7950>0</es:amr7950></es:pwrLoadThresholdDlSpeech>',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '');

INSERT INTO CONTEXT_UT
    VALUES (TO_DATE ('02/17/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
            'BRHMALXSCR0R04',
            'ALBHU1034U',
            '0',
            '1',
            '1',
            '2',
            '2',
            '2',
            '-20',
            '-107',
            '0',
            '75',
            '15',
            '300',
            '2000',
            '1',
            '500',
            '85',
            '500',
            '2',
            '32',
            '-180',
            '40690',
            '7760',
            '1590',
            '42',
            '38',
            '0',
            '15',
            '6000',
            '621',
            '1',
            '6',
            '23',
            '22',
            '4',
            '0',
            '24',
            '1',
            '16',
            '1',
            '-12',
            '-106',
            '1',
            '100',
            '100',
            '3',
            '10',
            '10',
            '6',
            '3',
            '32',
            '0',
            '-105',
            '16',
            '3',
            '800',
            '3000',
            '50',
            '8',
            '-4025014',
            '10',
            '0',
            '0',
            '1',
            '0',
            '0',
            '100',
            '0',
            '16',
            '',
            '',
            '0',
            '64',
            '64',
            '8',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            'SubNetwork=ONRM_RootMo_R,SubNetwork=BRHMALXSCR0R03,MeContext=BRHMALXSCR0R03,ManagedElement=1,RncFunction=1,IubLink=Iub_ALBHU1034',
            '<es:pwrLoadThresholdDlSpeech>  <es:amr12200>50</es:amr12200>  <es:amr5900>100</es:amr5900>  <es:amr7950>0</es:amr7950></es:pwrLoadThresholdDlSpeech>',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '');

INSERT INTO CONTEXT_UT
    VALUES (TO_DATE ('02/16/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
            'BRHMALXSCR0R03',
            'ALBHU1034A',
            '0',
            '1',
            '1',
            '1',
            '2',
            '2',
            '-20',
            '-106',
            '0',
            '75',
            '15',
            '300',
            '2001',
            '1',
            '500',
            '85',
            '502',
            '2',
            '32',
            '-180',
            '40691',
            '7760',
            '1590',
            '42',
            '38',
            '0',
            '12',
            '6000',
            '621',
            '1',
            '7',
            '23',
            '22',
            '4',
            '0',
            '22',
            '1',
            '16',
            '1',
            '-12',
            '-106',
            '1',
            '100',
            '100',
            '3',
            '10',
            '11',
            '6',
            '3',
            '32',
            '',
            '-105',
            '16',
            '3',
            '800',
            '3000',
            '50',
            '8',
            '-4025014',
            '10',
            '0',
            '0',
            '1',
            '0',
            '0',
            '100',
            '0',
            '16',
            '',
            '',
            '0',
            '64',
            '64',
            '8',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            'SubNetwork=ORM_RootMo_R,SubNetwork=BRHMALXSCR0R03,MeContext=BRHMALXSCR0R03,ManagedElement=1,RncFunction=1,IubLink=Iub_ALBHU1034',
            '<es:pwrLodThresholdDlSpeech>  <es:amr12200>50</es:amr12200>  <es:amr5900>100</es:amr5900>  <es:amr7950>0</es:amr7950></es:pwrLoadThresholdDlSpeech>',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '');

INSERT INTO CONTEXT_UT
    VALUES (TO_DATE ('02/15/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
            'BRHMALXSCR0R03',
            'ALBHU1034B',
            '0',
            '1',
            '1',
            '2',
            '2',
            '2',
            '-20',
            '-10S',
            '0',
            '75',
            '15',
            '300',
            '2000',
            '1',
            '500',
            '85',
            '500',
            '2',
            '32',
            '-180',
            '40690',
            '7760',
            '1590',
            '42',
            '38',
            '0',
            '12',
            '6000',
            '621',
            '1',
            '6',
            '23',
            '22',
            '4',
            '0',
            '23',
            '1',
            '16',
            '1',
            '-12',
            '-102',
            '1',
            '100',
            '100',
            '3',
            '10',
            '10',
            '6',
            '3',
            '32',
            '1',
            '-105',
            '16',
            '3',
            '802',
            '3000',
            '50',
            '8',
            '-4025014',
            '10',
            '0',
            '0',
            '1',
            '0',
            '3',
            '100',
            '0',
            '16',
            '',
            '',
            '0',
            '64',
            '64',
            '8',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            'SubNetwork=ONRM_RootMo_R,SubNetwork=BRHMALXSCR0R03,MeContext=BRHMALXSCR0R03,ManagedElement=1,RncFunction=1,IubLink=Iub_ALBHU1034',
            '<es:pwrLoadThresholdDlSpeech>  <es:amr12200>50</es:amr12200>  <es:amr5900>100</es:amr5900>  <es:amr7950>0</es:amr7950></es:pwrLoadThresholdDlSpeech>',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '');

INSERT INTO CONTEXT_UT
    VALUES (TO_DATE ('02/16/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
            'BRHMALXSCR0R04',
            'ALBHU1034T',
            '0',
            '1',
            '1',
            '2',
            '2',
            '2',
            '-20',
            '-107',
            '0',
            '75',
            '15',
            '300',
            '2000',
            '1',
            '500',
            '85',
            '500',
            '2',
            '32',
            '-180',
            '40690',
            '7760',
            '1590',
            '42',
            '38',
            '0',
            '15',
            '6000',
            '621',
            '1',
            '6',
            '23',
            '22',
            '4',
            '0',
            '24',
            '1',
            '16',
            '1',
            '-12',
            '-106',
            '1',
            '100',
            '100',
            '3',
            '10',
            '10',
            '6',
            '3',
            '32',
            '0',
            '-105',
            '16',
            '3',
            '800',
            '3000',
            '50',
            '8',
            '-4025014',
            '10',
            '0',
            '0',
            '1',
            '0',
            '0',
            '100',
            '0',
            '16',
            '',
            '',
            '0',
            '64',
            '64',
            '8',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            'SubNetwork=ONRM_RootMo_R,SubNetwork=BRHMALXSCR0R03,MeContext=BRHMALXSCR0R03,ManagedElement=1,RncFunction=1,IubLink=Iub_ALBHU1034',
            '<es:pwrLoadThresholdDlSpeech>  <es:amr12200>50</es:amr12200>  <es:amr5900>100</es:amr5900>  <es:amr7950>0</es:amr7950></es:pwrLoadThresholdDlSpeech>',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '');

INSERT INTO CONTEXT_UT
    VALUES (TO_DATE ('02/16/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
            'BRHMALXSCR0R04',
            'ALBHU1034U',
            '0',
            '1',
            '1',
            '1',
            '2',
            '2',
            '-20',
            '-107',
            '0',
            '75',
            '15',
            '300',
            '2000',
            '1',
            '500',
            '85',
            '500',
            '2',
            '32',
            '-180',
            '40690',
            '7760',
            '1590',
            '42',
            '38',
            '0',
            '15',
            '6000',
            '621',
            '1',
            '6',
            '23',
            '22',
            '4',
            '0',
            '24',
            '1',
            '16',
            '1',
            '-12',
            '-106',
            '1',
            '100',
            '100',
            '3',
            '10',
            '10',
            '6',
            '3',
            '32',
            '0',
            '-105',
            '16',
            '3',
            '800',
            '3000',
            '50',
            '8',
            '-4025014',
            '10',
            '0',
            '0',
            '1',
            '0',
            '0',
            '100',
            '0',
            '16',
            '',
            '',
            '0',
            '64',
            '64',
            '8',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            'SubNetwork=ONRM_RootMo_R,SubNetwork=BRHMALXSCR0R03,MeContext=BRHMALXSCR0R03,ManagedElement=1,RncFunction=1,IubLink=Iub_ALBHU1034',
            '<es:pwrLoadThresholdDlSpeech>  <es:amr12200>50</es:amr12200>  <es:amr5900>100</es:amr5900>  <es:amr7950>0</es:amr7950></es:pwrLoadThresholdDlSpeech>',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '',
            '');

COMMIT;


Target Table
==================
CREATE TABLE STG
(
   CONTEXTID        VARCHAR2 (32 BYTE),
   UTID             VARCHAR2 (32 BYTE),
   START_TSTAMP     DATE,
   ATTRIBUTE_NAME   VARCHAR2 (50 BYTE),
   NEW_VALUE        VARCHAR2 (500 BYTE),
   OLD_VALUE        VARCHAR2 (500 BYTE)
)


My Query
========

INSERT /* +append */ INTO STG (CONTEXTID, UTID, START_TSTAMP, ATTRIBUTE_NAME, NEW_VALUE,
                               OLD_VALUE)
WITH lst_qry
        AS (SELECT                              /*+ DRIVING_SITE(d) full(d) */ START_DATE, CONTEXTID, UTID, 
                  HCSSIB3CON_SSRCHHCS, HCSUSG_CONECTEDMODE, HCSUSG_IDLEMODE, AMRWBRATEDLMAX, AMRWBRATEULMAX,
                   ASLDTHUSP_AMR12200, ASLDTHUSP_AMR5900, ASLDTHUSP_AMR7950, EULNOSRVCELLUSRADM, FACHMEAOCACYLNCOEF, 
                   RTSELPSINT_CHANTYP, RTSELPSINT_DLPRFRT, RTSELPSINT_ULPRFRT, SF4ULPATHLOSSTHRES, SIB1PLMNSPVALTAG,
                   ULPATHLOSSCHECKENA, USEDFRQTHR2DECNO, USEDFRQTHR2DRSCP, ACCCLBARREDCS, ACCCLBARREDPS,
                   ACCESSCLSNBARR, ADMINSTATE, AGPSENABLED, ASEDLADM, ASEULADM,
                   ASEULADMOFFSET, BEMARGINASEDL, BEMARGINASEUL, BEMARGINDLCODE, BEMARGINDLPWR,
                   CDLDTHRDLSF128, CELLRESERVED, COMPMODEADM, DLCODEADM, EULSRVCEUSRADM,
                   HARDIFHOCORR, HOTYPE, HSDPAUSERSADM, IFCONG, IFHYST,
                   IFOFFSET, INDIVOFFSET, INTERPWRMAX, INTERRATE, INTFQFDDMEAIND,
                   IUBLINKREF, LDSHARGSMFRACT, LDSHARGSMTHRES, LDSHARMARG, MAXPWRMAX,
                   MAXRATE, MAXTXPOWERUL, MINIMUMRATE, MINPWRMAX, MINPWRRL,
                   NINSYNCIND, NOUTSYNCIND, PWRADM, PWRADMOFFSET, PWRHYST,
                   PWRLOADTHRDLSP, PWROFFSET, QHYST1, QHYST2, QQUALMIN,
                   QRXLEVMIN, QUALMEASQTY, RELASEDLGHS, RELEASEASEDL, RELEASEASEDLNG,
                   RESERVEDBY, RLFAILURET, SF16ADM, SF16ADMUL, SF16GADM,
                   SF32ADM, SF4ADMUL, SF8ADM, SF8ADMUL, SF8GADMUL,
                   SHCSRAT, SINTERSEARCH, SINTRASEARCH, SNDIRRETRYTGT, SRATSEARCH,
                   TCELL, TMCONGACTGHS, TMCONGACTION, TMCONGACTIONNG, TMINITIALG,
                   TMINITIALGHS, TRESELECTION, POS
              FROM CONTEXT_UT@remote_DB d
             WHERE start_date BETWEEN TO_DATE ('02/15/2011', 'MM/DD/YYYY') - 3 AND  TO_DATE ('02/15/2011', 'MM/DD/YYYY'))
SELECT c_d.CONTEXTID, c_d.UTID, c_d.START_DATE, vals.param_name ATTRIBUTE_NAME, REPLACE (vals.param_value, 'NULL', '') new_value,
       REPLACE (vals.old_param_value, 'NULL', '') old_value
  FROM 
       (SELECT * FROM LST_QRY WHERE START_DATE = TO_DATE ('02/15/2011', 'MM/DD/YYYY')) C_d,
       (SELECT *
          FROM (SELECT RANK () OVER (PARTITION BY CONTEXTID, UTID ORDER BY start_date DESC) sl, b.* FROM LST_QRY b WHERE START_DATE < TO_DATE ('02/15/2011', 'MM/DD/YYYY'))
         WHERE sl = 1) P_d,
       TABLE(Name_Value_varray (
                Name_Value_Pair('HCSSIB3CON_SSRCHHCS', c_d.HCSSIB3CON_SSRCHHCS, p_d.HCSSIB3CON_SSRCHHCS), 
                Name_Value_Pair('HCSUSG_CONECTEDMODE', c_d.HCSUSG_CONECTEDMODE, p_d.HCSUSG_CONECTEDMODE), 
                Name_Value_Pair('HCSUSG_IDLEMODE', c_d.HCSUSG_IDLEMODE, p_d.HCSUSG_IDLEMODE), 
                Name_Value_Pair('AMRWBRATEDLMAX', c_d.AMRWBRATEDLMAX, p_d.AMRWBRATEDLMAX),
                Name_Value_Pair('AMRWBRATEULMAX', c_d.AMRWBRATEULMAX, p_d.AMRWBRATEULMAX), 
                Name_Value_Pair('ASLDTHUSP_AMR12200', c_d.ASLDTHUSP_AMR12200, p_d.ASLDTHUSP_AMR12200), 
                Name_Value_Pair('ASLDTHUSP_AMR5900', c_d.ASLDTHUSP_AMR5900, p_d.ASLDTHUSP_AMR5900), 
                Name_Value_Pair('ASLDTHUSP_AMR7950', c_d.ASLDTHUSP_AMR7950, p_d.ASLDTHUSP_AMR7950), 
                Name_Value_Pair('EULNOSRVCELLUSRADM', c_d.EULNOSRVCELLUSRADM, p_d.EULNOSRVCELLUSRADM),
                Name_Value_Pair('FACHMEAOCACYLNCOEF', c_d.FACHMEAOCACYLNCOEF, p_d.FACHMEAOCACYLNCOEF), 
                Name_Value_Pair('RTSELPSINT_CHANTYP', c_d.RTSELPSINT_CHANTYP, p_d.RTSELPSINT_CHANTYP), 
                Name_Value_Pair('RTSELPSINT_DLPRFRT', c_d.RTSELPSINT_DLPRFRT, p_d.RTSELPSINT_DLPRFRT), 
                Name_Value_Pair('RTSELPSINT_ULPRFRT', c_d.RTSELPSINT_ULPRFRT, p_d.RTSELPSINT_ULPRFRT), 
                Name_Value_Pair('SF4ULPATHLOSSTHRES', c_d.SF4ULPATHLOSSTHRES, p_d.SF4ULPATHLOSSTHRES),
                Name_Value_Pair('SIB1PLMNSPVALTAG', c_d.SIB1PLMNSPVALTAG, p_d.SIB1PLMNSPVALTAG), 
                Name_Value_Pair('ULPATHLOSSCHECKENA', c_d.ULPATHLOSSCHECKENA, p_d.ULPATHLOSSCHECKENA), 
                Name_Value_Pair('USEDFRQTHR2DECNO', c_d.USEDFRQTHR2DECNO, p_d.USEDFRQTHR2DECNO), 
                Name_Value_Pair('USEDFRQTHR2DRSCP', c_d.USEDFRQTHR2DRSCP, p_d.USEDFRQTHR2DRSCP), 
                Name_Value_Pair('ACCCLBARREDCS', c_d.ACCCLBARREDCS, p_d.ACCCLBARREDCS),
                Name_Value_Pair('ACCCLBARREDPS', c_d.ACCCLBARREDPS, p_d.ACCCLBARREDPS), 
                Name_Value_Pair('ACCESSCLSNBARR', c_d.ACCESSCLSNBARR, p_d.ACCESSCLSNBARR), 
                Name_Value_Pair('ADMINSTATE', c_d.ADMINSTATE, p_d.ADMINSTATE), 
                Name_Value_Pair('AGPSENABLED', c_d.AGPSENABLED, p_d.AGPSENABLED), 
                Name_Value_Pair('ASEDLADM', c_d.ASEDLADM, p_d.ASEDLADM), 
                Name_Value_Pair('ASEULADM', c_d.ASEULADM, p_d.ASEULADM),
                Name_Value_Pair('ASEULADMOFFSET', c_d.ASEULADMOFFSET, p_d.ASEULADMOFFSET), 
                Name_Value_Pair('BEMARGINASEDL', c_d.BEMARGINASEDL, p_d.BEMARGINASEDL), 
                Name_Value_Pair('BEMARGINASEUL', c_d.BEMARGINASEUL, p_d.BEMARGINASEUL), 
                Name_Value_Pair('BEMARGINDLCODE', c_d.BEMARGINDLCODE, p_d.BEMARGINDLCODE), 
                Name_Value_Pair('BEMARGINDLPWR', c_d.BEMARGINDLPWR, p_d.BEMARGINDLPWR),
                Name_Value_Pair('CDLDTHRDLSF128', c_d.CDLDTHRDLSF128, p_d.CDLDTHRDLSF128), 
                Name_Value_Pair('CELLRESERVED', c_d.CELLRESERVED, p_d.CELLRESERVED), 
                Name_Value_Pair('COMPMODEADM', c_d.COMPMODEADM, p_d.COMPMODEADM), 
                Name_Value_Pair('DLCODEADM', c_d.DLCODEADM, p_d.DLCODEADM), 
                Name_Value_Pair('EULSRVCEUSRADM', c_d.EULSRVCEUSRADM, p_d.EULSRVCEUSRADM),
                Name_Value_Pair('HARDIFHOCORR', c_d.HARDIFHOCORR, p_d.HARDIFHOCORR), 
                Name_Value_Pair('HOTYPE', c_d.HOTYPE, p_d.HOTYPE), 
                Name_Value_Pair('HSDPAUSERSADM', c_d.HSDPAUSERSADM, p_d.HSDPAUSERSADM), 
                Name_Value_Pair('IFCONG', c_d.IFCONG, p_d.IFCONG), 
                Name_Value_Pair('IFHYST', c_d.IFHYST, p_d.IFHYST), 
                Name_Value_Pair('IFOFFSET', c_d.IFOFFSET, p_d.IFOFFSET),
                Name_Value_Pair('INDIVOFFSET', c_d.INDIVOFFSET, p_d.INDIVOFFSET), 
                Name_Value_Pair('INTERPWRMAX', c_d.INTERPWRMAX, p_d.INTERPWRMAX), 
                Name_Value_Pair('INTERRATE', c_d.INTERRATE, p_d.INTERRATE), 
                Name_Value_Pair('INTFQFDDMEAIND', c_d.INTFQFDDMEAIND, p_d.INTFQFDDMEAIND), 
                Name_Value_Pair('IUBLINKREF', c_d.IUBLINKREF, p_d.IUBLINKREF), 
                Name_Value_Pair('LDSHARGSMFRACT', c_d.LDSHARGSMFRACT, p_d.LDSHARGSMFRACT),
                Name_Value_Pair('LDSHARGSMTHRES', c_d.LDSHARGSMTHRES, p_d.LDSHARGSMTHRES), 
                Name_Value_Pair('LDSHARMARG', c_d.LDSHARMARG, p_d.LDSHARMARG), 
                Name_Value_Pair('MAXPWRMAX', c_d.MAXPWRMAX, p_d.MAXPWRMAX), 
                Name_Value_Pair('MAXRATE', c_d.MAXRATE, p_d.MAXRATE), 
                Name_Value_Pair('MAXTXPOWERUL', c_d.MAXTXPOWERUL, p_d.MAXTXPOWERUL), 
                Name_Value_Pair('MINIMUMRATE', c_d.MINIMUMRATE, p_d.MINIMUMRATE),
                Name_Value_Pair('MINPWRMAX', c_d.MINPWRMAX, p_d.MINPWRMAX), 
                Name_Value_Pair('MINPWRRL', c_d.MINPWRRL, p_d.MINPWRRL), 
                Name_Value_Pair('NINSYNCIND', c_d.NINSYNCIND, p_d.NINSYNCIND), 
                Name_Value_Pair('NOUTSYNCIND', c_d.NOUTSYNCIND, p_d.NOUTSYNCIND), 
                Name_Value_Pair('PWRADM', c_d.PWRADM, p_d.PWRADM), 
                Name_Value_Pair('PWRADMOFFSET', c_d.PWRADMOFFSET, p_d.PWRADMOFFSET),
                Name_Value_Pair('PWRHYST', c_d.PWRHYST, p_d.PWRHYST), 
                Name_Value_Pair('PWRLOADTHRDLSP', c_d.PWRLOADTHRDLSP, p_d.PWRLOADTHRDLSP), 
                Name_Value_Pair('PWROFFSET', c_d.PWROFFSET, p_d.PWROFFSET), 
                Name_Value_Pair('QHYST1', c_d.QHYST1, p_d.QHYST1), 
                Name_Value_Pair('QHYST2', c_d.QHYST2, p_d.QHYST2), 
                Name_Value_Pair('QQUALMIN', c_d.QQUALMIN, p_d.QQUALMIN),
                Name_Value_Pair('QRXLEVMIN', c_d.QRXLEVMIN, p_d.QRXLEVMIN), 
                Name_Value_Pair('QUALMEASQTY', c_d.QUALMEASQTY, p_d.QUALMEASQTY), 
                Name_Value_Pair('RELASEDLGHS', c_d.RELASEDLGHS, p_d.RELASEDLGHS), 
                Name_Value_Pair('RELEASEASEDL', c_d.RELEASEASEDL, p_d.RELEASEASEDL), 
                Name_Value_Pair('RELEASEASEDLNG', c_d.RELEASEASEDLNG, p_d.RELEASEASEDLNG), 
                Name_Value_Pair('RESERVEDBY', c_d.RESERVEDBY, p_d.RESERVEDBY),
                Name_Value_Pair('RLFAILURET', c_d.RLFAILURET, p_d.RLFAILURET), 
                Name_Value_Pair('SF16ADM', c_d.SF16ADM, p_d.SF16ADM), 
                Name_Value_Pair('SF16ADMUL', c_d.SF16ADMUL, p_d.SF16ADMUL), 
                Name_Value_Pair('SF16GADM', c_d.SF16GADM, p_d.SF16GADM), 
                Name_Value_Pair('SF32ADM', c_d.SF32ADM, p_d.SF32ADM), 
                Name_Value_Pair('SF4ADMUL', c_d.SF4ADMUL, p_d.SF4ADMUL),
                Name_Value_Pair('SF8ADM', c_d.SF8ADM, p_d.SF8ADM), 
                Name_Value_Pair('SF8ADMUL', c_d.SF8ADMUL, p_d.SF8ADMUL), 
                Name_Value_Pair('SF8GADMUL', c_d.SF8GADMUL, p_d.SF8GADMUL), 
                Name_Value_Pair('SHCSRAT', c_d.SHCSRAT, p_d.SHCSRAT), 
                Name_Value_Pair('SINTERSEARCH', c_d.SINTERSEARCH, p_d.SINTERSEARCH), 
                Name_Value_Pair('SINTRASEARCH', c_d.SINTRASEARCH, p_d.SINTRASEARCH),
                Name_Value_Pair('SNDIRRETRYTGT', c_d.SNDIRRETRYTGT, p_d.SNDIRRETRYTGT), 
                Name_Value_Pair('SRATSEARCH', c_d.SRATSEARCH, p_d.SRATSEARCH), 
                Name_Value_Pair('TCELL', c_d.TCELL, p_d.TCELL), 
                Name_Value_Pair('TMCONGACTGHS', c_d.TMCONGACTGHS, p_d.TMCONGACTGHS), 
                Name_Value_Pair('TMCONGACTION', c_d.TMCONGACTION, p_d.TMCONGACTION), 
                Name_Value_Pair('TMCONGACTIONNG', c_d.TMCONGACTIONNG, p_d.TMCONGACTIONNG),
                Name_Value_Pair('TMINITIALG', c_d.TMINITIALG, p_d.TMINITIALG), 
                Name_Value_Pair('TMINITIALGHS', c_d.TMINITIALGHS, p_d.TMINITIALGHS), 
                Name_Value_Pair('TRESELECTION', c_d.TRESELECTION, p_d.TRESELECTION), 
                Name_Value_Pair('POS', c_d.POS, p_d.POS)
                )) vals
 WHERE     C_d.CONTEXTID = P_d.CONTEXTID
       AND c_d.UTID = P_d.UTID
       AND vals.param_value IS NOT NULL
       AND REPLACE (vals.param_value, 'NULL', '') <> REPLACE (vals.old_param_value, 'NULL', '')


Result-Needed
=============
CONTEXTID UTID START_TSTA ATTRIBUTE_NAM New Old
=========================================================================
BRHMALXSCR0R03 ALBHU1034A 2/17/2010 QUALMEASQTY 2 1
BRHMALXSCR0R03 ALBHU1034A 2/17/2010 SINTERSEARCH 24 22
BRHMALXSCR0R03 ALBHU1034B 2/17/2010 USEDFRQTHR2DECNO 10 11
BRHMALXSCR0R03 ALBHU1034B 2/17/2010 BEMARGINASEDL -106 -102
BRHMALXSCR0R03 ALBHU1034B 2/17/2010 INTERPWRMAX 15 12
BRHMALXSCR0R03 ALBHU1034B 2/17/2010 LDSHARGSMFRACT 0 1
BRHMALXSCR0R03 ALBHU1034B 2/17/2010 NOUTSYNCIND 800 802
BRHMALXSCR0R03 ALBHU1034B 2/17/2010 QRXLEVMIN -107 -10S
BRHMALXSCR0R03 ALBHU1034B 2/17/2010 SHCSRAT 0 3
BRHMALXSCR0R03 ALBHU1034B 2/17/2010 SINTERSEARCH 24 23
BRHMALXSCR0R04 ALBHU1034T 2/17/2010 BEMARGINASEDL -106 -102

Regards,
Manikandan
Re: performance tuning [message #497146 is a reply to message #497145] Fri, 04 March 2011 03:07 Go to previous messageGo to next message
manidotkandan
Messages: 16
Registered: February 2011
Junior Member
The types used here are

create or replace type Name_Value_Pair as object (param_name varchar2(30), param_value varchar2(255), old_param_value varchar2(255))
create or replace type Name_Value_varray as varray(275) of Name_Value_Pair


Regards,
Manikandan
Re: performance tuning [message #497158 is a reply to message #497146] Fri, 04 March 2011 03:43 Go to previous message
cookiemonster
Messages: 10912
Registered: September 2008
Location: Rainy Manchester
Senior Member
Post an explain plan for your insert statement.
Previous Topic: Tunning the query and 4000-5000 parameter in IN clause
Next Topic: inserting data using FOR ALL
Goto Forum:
  


Current Time: Wed Aug 27 08:11:18 CDT 2014

Total time taken to generate the page: 0.08085 seconds