Home » Other » General » Update Query Working very slow (Oracle 10g & 9i )
Update Query Working very slow [message #403629] Sun, 17 May 2009 14:51 Go to next message
raza10781
Messages: 6
Registered: May 2009
Location: Schenectady-New York
Junior Member
Hi All,
I have been messed up by one issue, so your urgent help.
I have a table "EPCT_TURBINE_SCHEDULE" in which there are about 33 columns. There is no primary key and this table have approx 2.9 lack data.
When I am trying to update data in this tables for around 25K records then it is taking time 13 mints.
The update is always on condition of three fields of table say c1,c2,c3 in where clause.

When i am creating index on c1&c2 then it is taking time around 13 mints and if I am creating index on c1,c2 and C3 then still it is taking time of 13 mints.
Please suggest what should i do to increase my performance. It is very critical for me.
Below is my actual update query:
UPDATE EPCT_TURBINE_SCHEDULE
SET --TURBINE_STATUS = upper(SCH(i).P_TURBINE_STATUS),
PLANNED_TURBINE_START_DATE =
TO_DATE (SCH(i).P_PLANNED_TURBINE_START_DATE, 'mm/dd/yyyy'),
ACTUAL_TURBINE_START_DATE =
TO_DATE (SCH(i).P_ACTUAL_TURBINE_START_DATE, 'mm/dd/yyyy'),
PLANNED_TURBINE_FINISH_DATE =
TO_DATE (SCH(i).P_PLANNED_TURBINE_FINISH_DATE, 'mm/dd/yyyy'),
ACTUAL_TURBINE_FINISH_DATE =
TO_DATE (SCH(i).P_ACTUAL_TURBINE_FINISH_DATE, 'mm/dd/yyyy'),
LAST_UPDATED_BY = SCH(i).P_UPDATED_BY,
LAST_UPDATION_DATE = SYSDATE,
POLE_ID = P_POLE_ID,
COUNTRY_ID = P_COUNTRY_ID,
CUSTOMER_ID = P_WMF_CUSTOMER_SEQ_ID,
SERVICE_AREA = upper(P_SERVICE_AREA),
SAM = SCH(i).P_SAM,
CODE = SCH(i).P_CODE,
TYPE_VALUE =P_TYPE,
USER1 = SCH(i).P_USER1,
USER2 = SCH(i).P_USER2,
USER3 = SCH(i).P_USER3,
USER4 = SCH(i).P_USER4,
USER5 = SCH(i).P_USER5,
LAT = P_LAT,
CPT = P_CPT,
PCT = SCH(i).P_PCT,
PROJECT_IPS_CMS_SEQ_ID = P_WMF_PROJECT_IPS_CMS_SEQ_ID
WHERE upper(SCOPE_ID) = upper(SCH(i).P_SCOPE_ID) AND TURBINE_SERIAL_NUMBER = SCH(i).P_TURBINE_SERIAL_NUMBER AND upper(LOGICAL_DELETE_FLAG) = 'N';

Thanks
Matloob
Re: Update Query Working very slow [message #403630 is a reply to message #403629] Sun, 17 May 2009 15:33 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
UPDATE epct_turbine_schedule
SET --TURBINE_STATUS = upper(SCH(i).P_TURBINE_STATUS),
 planned_turbine_start_date = To_date(Sch(i),'mm/dd/yyyy'),
       actual_turbine_start_date = To_date(Sch(i),'mm/dd/yyyy'),
       planned_turbine_finish_date = To_date(Sch(i),'mm/dd/yyyy'),
       actual_turbine_finish_date = To_date(Sch(i),'mm/dd/yyyy'),
       last_updated_by = Sch(i),
       last_updation_date = SYSDATE,
       pole_id = p_pole_id,
       country_id = p_country_id,
       customer_id = p_wmf_customer_seq_id,
       service_area = Upper(p_service_area),
       sam = Sch(i),
       code = Sch(i),
       type_value = p_type,
       user1 = Sch(i),
       user2 = Sch(i),
       user3 = Sch(i),
       user4 = Sch(i),
       user5 = Sch(i),
       lat = p_lat,
       cpt = p_cpt,
       pct = Sch(i),
       project_ips_cms_seq_id = p_wmf_project_ips_cms_seq_id
WHERE  Upper(scope_id) = Upper(Sch(i))
       AND turbine_serial_number = Sch(i)AND Upper(logical_delete_flag) = 'N'; 


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

>Upper(scope_id) = Upper(Sch(i))
The use of the UPPER() function precludes the use of any index

Why is it urgent for me to solve this problem for you?

Post output from tkprof showing results from ETL procedure.

[Updated on: Sun, 17 May 2009 15:57]

Report message to a moderator

Re: Update Query Working very slow [message #403631 is a reply to message #403630] Sun, 17 May 2009 16:22 Go to previous messageGo to next message
raza10781
Messages: 6
Registered: May 2009
Location: Schenectady-New York
Junior Member
SELECT COUNT (*)
				INTO P_TURBINE_COUNT
				FROM EPCT_TURBINE_SCHEDULE
				WHERE SCOPE_ID = SCH(i).P_SCOPE_ID
				AND TURBINE_SERIAL_NUMBER = SCH(i).P_TURBINE_SERIAL_NUMBER
				AND TO_DATE (PLANNED_TURBINE_START_DATE, 'mm/dd/yyyy') = TO_DATE (SCH(i).P_PLANNED_TURBINE_START_DATE, 'mm/dd/yyyy')
				AND TO_DATE (ACTUAL_TURBINE_START_DATE, 'mm/dd/yyyy') = TO_DATE (SCH(i).P_ACTUAL_TURBINE_START_DATE, 'mm/dd/yyyy')
				AND TO_DATE (PLANNED_TURBINE_FINISH_DATE, 'mm/dd/yyyy') = TO_DATE (SCH(i).P_PLANNED_TURBINE_FINISH_DATE, 'mm/dd/yyyy')
				AND TO_DATE (ACTUAL_TURBINE_FINISH_DATE, 'mm/dd/yyyy') = TO_DATE (SCH(i).P_ACTUAL_TURBINE_FINISH_DATE, 'mm/dd/yyyy')
				AND CODE = SCH(i).P_CODE
				AND PCT = SCH(i).P_PCT
				AND SAM = SCH(i).P_SAM
				AND USER1 = SCH(i).P_USER1
				AND USER2 = SCH(i).P_USER2
				AND	USER3 = SCH(i).P_USER3
				AND	USER4 = SCH(i).P_USER4
				AND	USER5 = SCH(i).P_USER5
				AND LOGICAL_DELETE_FLAG = 'N';
If (P_TURBINE_COUNT>0) then 

UPDATE EPCT_TURBINE_SCHEDULE
SET 
PLANNED_TURBINE_START_DATE =TO_DATE(SCHi).P_PLANNED_TURBINE_START_DATE, 'mm/dd/yyyy'),
ACTUAL_TURBINE_START_DATE =TO_DATE (SCH(i).P_ACTUAL_TURBINE_START_DATE, 'mm/dd/yyyy'),
PLANNED_TURBINE_FINISH_DATE =TO_DATE (SCH(i).P_PLANNED_TURBINE_FINISH_DATE, 'mm/dd/yyyy'),
ACTUAL_TURBINE_FINISH_DATE =TO_DATE (SCH(i).P_ACTUAL_TURBINE_FINISH_DATE, 'mm/dd/yyyy'),
LAST_UPDATED_BY = SCH(i).P_UPDATED_BY,
LAST_UPDATION_DATE = SYSDATE,
POLE_ID = P_POLE_ID,
COUNTRY_ID = P_COUNTRY_ID,
CUSTOMER_ID = P_WMF_CUSTOMER_SEQ_ID,
SERVICE_AREA = upper(P_SERVICE_AREA),
SAM = SCH(i).P_SAM,
CODE = SCH(i).P_CODE,
TYPE_VALUE =P_TYPE,
USER1 = SCH(i).P_USER1,
USER2 = SCH(i).P_USER2,
USER3 = SCH(i).P_USER3,
USER4 = SCH(i).P_USER4,
USER5 = SCH(i).P_USER5,
LAT = P_LAT,
CPT  =  P_CPT,
PCT = SCH(i).P_PCT,
PROJECT_IPS_CMS_SEQ_ID = P_WMF_PROJECT_IPS_CMS_SEQ_ID
  WHERE SCOPE_ID= SCH(i).P_SCOPE_ID AND 
        TURBINE_SERIAL_NUMBER = SCH(i).P_TURBINE_SERIAL_NUMBER  
        AND LOGICAL_DELETE_FLAG= 'N';
end if;



Thanks for suggestion, Now i have removed the upper functions i was using in where clause but still same time 13 mints it is taking for 25K recrods .

One more thing, Before update I am using the below query to check whether the update is required or not. Please suggest is it logical or not OR is there any other way to check this.

then I am updating the data.
Note: Index is created on all three fields of where clause.

I have been debugging this since last 2 weeks, please suggest any resolution for this.

Thanks
Matloob
Re: Update Query Working very slow [message #403632 is a reply to message #403629] Sun, 17 May 2009 16:28 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
post DDL for EPCT_TURBINE_SCHEDULE


Post output from tkprof showing results from ETL procedure.
Re: Update Query Working very slow [message #403633 is a reply to message #403632] Sun, 17 May 2009 16:58 Go to previous messageGo to next message
raza10781
Messages: 6
Registered: May 2009
Location: Schenectady-New York
Junior Member
CREATE TABLE EPCT_TURBINE_SCHEDULE
(
  SCOPE_ID                     VARCHAR2(10 BYTE),
  POLE_ID                      NUMBER(10),
  COUNTRY_ID                   NUMBER(10),
  CUSTOMER_ID                  NUMBER(10),
  SERVICE_AREA                 VARCHAR2(60 BYTE),
  SITE_ID                      NUMBER(10),
  TURBINE_SERIAL_NUMBER        VARCHAR2(30 BYTE),
  TURBINE_TYPE                 VARCHAR2(20 BYTE),
  ACTUAL_TURBINE_FINISH_DATE   DATE,
  ACTUAL_TURBINE_START_DATE    DATE,
  TURBINE_STATUS               VARCHAR2(20 BYTE),
  TURBINE_DOWN_TIME            VARCHAR2(20 BYTE),
  LOGICAL_DELETE_FLAG          CHAR(1 BYTE)     NOT NULL,
  CREATED_BY                   NUMBER(10)       NOT NULL,
  CREATION_DATE                DATE             DEFAULT SYSDATE               NOT NULL,
  LAST_UPDATED_BY              NUMBER(10)       NOT NULL,
  LAST_UPDATION_DATE           DATE             DEFAULT SYSDATE               NOT NULL,
  PLANNED_TURBINE_START_DATE   DATE,
  PLANNED_TURBINE_FINISH_DATE  DATE,
  SCHEDULE_TYPE_ID             NUMBER(5),
  SCHEDULE_RESPONSE_ID         NUMBER(5),
  SAM                          VARCHAR2(100 BYTE),
  CODE                         NUMBER(5),
  USER1                        VARCHAR2(100 BYTE),
  USER2                        VARCHAR2(100 BYTE),
  USER3                        VARCHAR2(100 BYTE),
  USER4                        VARCHAR2(100 BYTE),
  USER5                        VARCHAR2(100 BYTE),
  LAT                          NUMBER(5),
  CPT                          NUMBER(5),
  PCT                          NUMBER(5),
  PROJECT_IPS_CMS_SEQ_ID       NUMBER(10),
  TYPE_VALUE                   VARCHAR2(50 BYTE)
)
TABLESPACE EPIC_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX TURBINE_SCHEDULE_INDEX ON EPCT_TURBINE_SCHEDULE
(TURBINE_SERIAL_NUMBER, SCOPE_ID, LOGICAL_DELETE_FLAG)
LOGGING
TABLESPACE EPIC_INDEXES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE EPCT_TURBINE_SCHEDULE ADD (
  CONSTRAINT EPCT_TURBINE_SCHEDULE_R01 
 FOREIGN KEY (SCOPE_ID) 
 REFERENCES EPCT_TOP_ISSUE_SCOPE_MAP (SCOPE_ID));



Above are the DDL for table EPCT_TURBINE_SCHEDULE as you asked.
Sorry,I am not able to understand what do you means by "Post output from tkprof showing results from ETL procedure."
Apreciate for your help and ask me if any other info you require.

Thanks
Matloob
Re: Update Query Working very slow [message #403634 is a reply to message #403629] Sun, 17 May 2009 17:20 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Since PLANNED_TURBINE_START_DATE & PLANNED_TURBINE_FINISH_DATE are DATE datatypes,
TO_DATE() function should not be used on them (or any other element which is of type DATE) index.
Again functions within WHERE clause preclude use of indexes

Prior to starting ETL procedure enable SQL_TRACE; i.e. ALTER SESSION SET SQL_TRACE=TRUE.

This will generate a trace file which needs to be processed with tkprof utility including explain=username/password.

All of this is documented & can be found SEARCH this site or GOOGLE

All columns within WHERE clauses should be indexed.
Statistics for both table & indexes need to be current.

[Updated on: Sun, 17 May 2009 17:20]

Report message to a moderator

Re: Update Query Working very slow [message #403635 is a reply to message #403629] Sun, 17 May 2009 17:32 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
bcm@bcm-laptop:~$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
  print=integer    List only the first 'integer' SQL statements.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT statements.
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  waits=yes|no     Record summary for any wait events found in the trace file.
  sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor
Re: Update Query Working very slow [message #403636 is a reply to message #403634] Sun, 17 May 2009 17:32 Go to previous messageGo to next message
raza10781
Messages: 6
Registered: May 2009
Location: Schenectady-New York
Junior Member
Suggest me if I am wrong.

1.The values for date are coming in varchar2 types variabls that's why I am using to_date() to set the date type field value
2. Every thing whatever is in where clause should be indexed. what about field which a forien key column of another tabel.
should we create index on the field too for example in our scenario Scope_id is foreing key of another table.


Thanks
Matloob
Re: Update Query Working very slow [message #403637 is a reply to message #403629] Sun, 17 May 2009 17:45 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>PLANNED_TURBINE_START_DATE DATE,
> TO_DATE (PLANNED_TURBINE_START_DATE, 'mm/dd/yyyy')
Please explain what is accomplished above?
you should NOT apply TO_DATE() function to DATE datatypes!

> Every thing whatever in WHERE clause should be indexed
YES, unless you have valid reason to not do so.
Re: Update Query Working very slow [message #403638 is a reply to message #403637] Sun, 17 May 2009 17:53 Go to previous messageGo to next message
raza10781
Messages: 6
Registered: May 2009
Location: Schenectady-New York
Junior Member
Thanks for your expert comments.
1. let me know, my approach to check whether update is needed or not using selelct query is correct or not as there are all the updatable fields in where clause.
2. the values of date is in varchar variable, that's why converting that value into data type using to_date, as oracle was giving error.

thanks
Matloob
Re: Update Query Working very slow [message #403639 is a reply to message #403629] Sun, 17 May 2009 18:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>1. let me know, my approach to check whether update is needed or not using selelct query is correct or not as there are all the updatable fields in where clause.

I do not know your requirements, so have no way decide correct or not.

If the results meets the requirements, the procedure is OK.
If the results do not meet the requirements, then fix procedure.


>2. the values of date is in varchar variable, that's why converting that value into data type using to_date, as oracle was giving error.

using sqlplus along with CUT & PASTE of whole session to provide proof an error occurs.
Simply put any error would be of type PEBKAC & easily fixed.
Re: Update Query Working very slow [message #403750 is a reply to message #403639] Mon, 18 May 2009 08:18 Go to previous message
raza10781
Messages: 6
Registered: May 2009
Location: Schenectady-New York
Junior Member
Hi Swan,
Could you please let me know, if i want to delete my posted script query from below messages, how to do that...or if you can delete then please delete these query posted in my below messages.

Thanks
Matloob
Previous Topic: last time table was modified.
Next Topic: EMP TABLE IN ORACLE 10G
Goto Forum:
  


Current Time: Thu Dec 08 18:40:30 CST 2016

Total time taken to generate the page: 0.10001 seconds