Home » RDBMS Server » Performance Tuning » Query is taking longer duration (Oracle 11g 11.2.0.1.0)
Query is taking longer duration [message #660153] Fri, 10 February 2017 03:05 Go to next message
manoj12
Messages: 196
Registered: March 2008
Location: India
Senior Member
Hi Sir,

The below is the update statement. This update is very slow in performance.
The below is the execution plan of the above update statement.

  update ITDB.SOURCE_EMPLOYEE s set S.LAST_PROMOTION_DATE= ( select max( DE.LAST_PROMOTION_DATE)   from 
HRBO.DIM_EMPLOYEE de where DE.LAST_PROMOTION_DATE is not null and S.CENTRAL_ID_CPID= DE.CENTRAL_ID_CPID
) where S.LAST_PROMOTION_DATE is null;

commit;

The cost of the query is 356K and it is taking longer duration of time.



Appreciate your help

Re: Query is taking longer duration [message #660154 is a reply to message #660153] Fri, 10 February 2017 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 65199
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The below is the execution plan of the above update statement.
What execution plan?

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

And format your statement.
If you don't know how to do it, learn it using SQL Formatter.

[Updated on: Fri, 10 February 2017 03:08]

Report message to a moderator

Re: Query is taking longer duration [message #660155 is a reply to message #660154] Fri, 10 February 2017 03:15 Go to previous messageGo to next message
manoj12
Messages: 196
Registered: March 2008
Location: India
Senior Member
Hi Sir,

The below is the cost of the query

15663 rows updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 1321977470

--------------------------------------------------------------------------------

--------

| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Ti

me     |

--------------------------------------------------------------------------------

--------

|   0 | UPDATE STATEMENT     |                 | 15663 | 31326 | 64524   (1)| 00

:12:55 |

|   1 |  UPDATE              | SOURCE_EMPLOYEE |       |       |            |
       |

|*  2 |   TABLE ACCESS FULL  | SOURCE_EMPLOYEE | 15663 | 31326 |  1094   (1)| 00

:00:14 |

|   3 |   SORT AGGREGATE     |                 |     1 |    21 |            |
       |

|*  4 |    HASH JOIN         |                 | 62486 |  1281K| 63430   (1)| 00

:12:42 |

|   5 |     TABLE ACCESS FULL| SOURCE_EMPLOYEE | 16810 |   147K|  1101   (1)| 00

:00:14 |

|*  6 |     TABLE ACCESS FULL| DIM_EMPLOYEE    |   194K|  2279K| 62326   (1)| 00

:12:28 |

--------------------------------------------------------------------------------

--------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("S"."LAST_PROMOTION_DATE" IS NULL)
   4 - access("S"."CENTRAL_ID_CPID"="DE"."CENTRAL_ID_CPID")
   6 - filter("DE"."LAST_PROMOTION_DATE" IS NOT NULL)
Re: Query is taking longer duration [message #660159 is a reply to message #660155] Fri, 10 February 2017 03:54 Go to previous messageGo to next message
manoj12
Messages: 196
Registered: March 2008
Location: India
Senior Member
Hi Sir,

The below is the query and the execution plan .

 update ITDB.SOURCE_EMPLOYEE s set S.LAST_PROMOTION_DATE= ( select max( DE.LAST_PROMOTION_DATE)   from 
HRBO.DIM_EMPLOYEE de where DE.LAST_PROMOTION_DATE is not null and S.CENTRAL_ID_CPID= DE.CENTRAL_ID_CPID
) where S.LAST_PROMOTION_DATE is null;



15663 rows updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 1321977470

--------------------------------------------------------------------------------

--------

| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Ti

me     |

--------------------------------------------------------------------------------

--------

|   0 | UPDATE STATEMENT     |                 | 15663 | 31326 | 64524   (1)| 00

:12:55 |

|   1 |  UPDATE              | SOURCE_EMPLOYEE |       |       |            |
       |

|*  2 |   TABLE ACCESS FULL  | SOURCE_EMPLOYEE | 15663 | 31326 |  1094   (1)| 00:00:14 |

|   3 |   SORT AGGREGATE     |                 |     1 |    21 |            |
       |
|*  4 |    HASH JOIN         |                 | 62486 |  1281K| 63430   (1)| 00:12:42 |

|   5 |     TABLE ACCESS FULL| SOURCE_EMPLOYEE | 16810 |   147K|  1101   (1)| 00:00:14 |

|*  6 |     TABLE ACCESS FULL| DIM_EMPLOYEE    |   194K|  2279K| 62326   (1)| 00:12:28 |

--------------------------------------------------------------------------------

--------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("S"."LAST_PROMOTION_DATE" IS NULL)
   4 - access("S"."CENTRAL_ID_CPID"="DE"."CENTRAL_ID_CPID")
   6 - filter("DE"."LAST_PROMOTION_DATE" IS NOT NULL)


This update is taking longer duration in updating only 15k records.Appreciate your help in resturcting as this is a correlated subquery.

Appreciate your help sir.
Re: Query is taking longer duration [message #660161 is a reply to message #660159] Fri, 10 February 2017 04:20 Go to previous messageGo to next message
Roachcoach
Messages: 1498
Registered: May 2010
Location: UK
Senior Member
You probably want to index S.CENTRAL_ID_CPID and DE.CENTRAL_ID_CPID

The initial FTS is unavoidable so you might as well speed up the other part.


Or rewrite it as a merge, which handles that type of access path a lot better in my experience.

[Updated on: Fri, 10 February 2017 04:21]

Report message to a moderator

Re: Query is taking longer duration [message #660226 is a reply to message #660161] Mon, 13 February 2017 02:25 Go to previous messageGo to next message
manoj12
Messages: 196
Registered: March 2008
Location: India
Senior Member
Hi Sir,


I have already indexed on S.CENTRAL_ID_CPID and DE.CENTRAL_ID_CPID. But still the update is taking 4 hrs to process only 15k rows.

The biggest problem which I see is filtering last_promotion_date is NULL.Even If I Index on the last_promotion_date attribute it is not going to use the index as index ignores NULL Data.

Appreciate your help.?

Regards,
Re: Query is taking longer duration [message #660227 is a reply to message #660226] Mon, 13 February 2017 02:51 Go to previous messageGo to next message
Roachcoach
Messages: 1498
Registered: May 2010
Location: UK
Senior Member
>>Or rewrite it as a merge, which handles that type of access path a lot better in my experience.
Re: Query is taking longer duration [message #660228 is a reply to message #660227] Mon, 13 February 2017 03:40 Go to previous messageGo to next message
manoj12
Messages: 196
Registered: March 2008
Location: India
Senior Member
Hi Sir,

The below is the query rewritten in MERGE

MERGE /*+ NDEX (s IDX_CENTRAL_ID_CPID) USE_HASH(s p)  */  INTO itdb.source_employee s
   USING (SELECT /*+  INDEX(de INDX_DIM_EMPLOYEE )  IUSE_HASH(p de) PARALLEL(8)  */
                 MAX (de.last_promotion_date) OVER (PARTITION BY de.central_id_cpid)
                                                          last_promotion_date,
                 de.central_id_cpid
            FROM hrbo.dim_employee de
           WHERE de.last_promotion_date IS NOT NULL
           ) p
   ON (s.central_id_cpid = p.central_id_cpid)
   WHEN MATCHED THEN
      UPDATE
         SET last_promotion_date = p.last_promotion_date
         WHERE s.last_promotion_date IS NULL
      ;

This query is still taking 4 hrs to execute.
Re: Query is taking longer duration [message #660229 is a reply to message #660228] Mon, 13 February 2017 03:40 Go to previous messageGo to next message
manoj12
Messages: 196
Registered: March 2008
Location: India
Senior Member
Appreciate your help on the below
Re: Query is taking longer duration [message #660230 is a reply to message #660229] Mon, 13 February 2017 03:47 Go to previous messageGo to next message
cookiemonster
Messages: 12927
Registered: September 2008
Location: Rainy Manchester
Senior Member
So again - post the execution plan.

Do you have any update triggers on the table?
Re: Query is taking longer duration [message #660231 is a reply to message #660230] Mon, 13 February 2017 03:50 Go to previous messageGo to next message
Roachcoach
Messages: 1498
Registered: May 2010
Location: UK
Senior Member
Post the plan without the hints too. A bunch of the are malformed anyway, but you should let the database try first.

Ideally actually a plan with the runtime statistics

[Updated on: Mon, 13 February 2017 03:50]

Report message to a moderator

Re: Query is taking longer duration [message #660232 is a reply to message #660229] Mon, 13 February 2017 04:38 Go to previous messageGo to next message
manoj12
Messages: 196
Registered: March 2008
Location: India
Senior Member
Hi Sir,

The below is the execution plan of the below Merge statement. There are no trigger on any of the tables created.
PLAN_TABLE_OUTPUT

Plan hash value: 3327090124
 
---------------------------------------------------------------------------------------------------
| Id  | Operation               | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT         |                 | 70040 |    99M|       | 81494   (1)| 00:16:18 |
|   1 |  MERGE                  | SOURCE_EMPLOYEE |       |       |       |            |          |
|   2 |   VIEW                  |                 |       |       |       |            |          |
|*  3 |    HASH JOIN            |                 | 70040 |   102M|    15M| 81494   (1)| 00:16:18 |
|   4 |     VIEW                |                 |   221K|    12M|       | 78460   (1)| 00:15:42 |
|   5 |      SORT UNIQUE        |                 |   221K|  2600K|  4360K| 78460   (1)| 00:15:42 |
|   6 |       WINDOW SORT       |                 |   221K|  2600K|  4360K| 78460   (1)| 00:15:42 |
|*  7 |        TABLE ACCESS FULL| DIM_EMPLOYEE    |   221K|  2600K|       | 76419   (1)| 00:15:18 |
|   8 |     TABLE ACCESS FULL   | SOURCE_EMPLOYEE | 16820 |    23M|       |  1087   (1)| 00:00:14 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("S"."CENTRAL_ID_CPID"="P"."CENTRAL_ID_CPID")
   7 - filter("DE"."LAST_PROMOTION_DATE" IS NOT NULL)

When I am running this query from Procedure this is taking 4 hrs. But when I am running manually it is taking 30 minutes to execute.

Appreciate your help sir
Re: Query is taking longer duration [message #660233 is a reply to message #660232] Mon, 13 February 2017 04:40 Go to previous messageGo to next message
manoj12
Messages: 196
Registered: March 2008
Location: India
Senior Member
Hi Sir,

The below plan given is without using HINTS.

Appreciate your help sir

Regards,
Re: Query is taking longer duration [message #660234 is a reply to message #660233] Mon, 13 February 2017 04:46 Go to previous messageGo to next message
cookiemonster
Messages: 12927
Registered: September 2008
Location: Rainy Manchester
Senior Member
having it in a procedure shouldn't cause it to take 8 times as long.

Are you manually running it against the same user/db as the procedure?
Are you sure that it isn't something else in the procedure that's taking all the time?
Re: Query is taking longer duration [message #660235 is a reply to message #660234] Mon, 13 February 2017 04:56 Go to previous messageGo to next message
manoj12
Messages: 196
Registered: March 2008
Location: India
Senior Member
Hi Sir,

But While running this procedure this update is running in the background from sys user.

I have checked it myself that this update is taking longer duration in Production Instance.

Regards,
Re: Query is taking longer duration [message #660236 is a reply to message #660235] Mon, 13 February 2017 04:58 Go to previous messageGo to next message
Roachcoach
Messages: 1498
Registered: May 2010
Location: UK
Senior Member
That doesn't make a lot of sense.

What is your session waiting on when it runs long?
Re: Query is taking longer duration [message #660237 is a reply to message #660236] Mon, 13 February 2017 05:04 Go to previous messageGo to next message
cookiemonster
Messages: 12927
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why on earth is it running as sys?

You should never, ever run application code as sys.
Re: Query is taking longer duration [message #660239 is a reply to message #660237] Mon, 13 February 2017 05:18 Go to previous messageGo to next message
manoj12
Messages: 196
Registered: March 2008
Location: India
Senior Member
Hi Sir,

The application code is not running on SYS user. It is running under application related Database schema. The Schema User is not having DBA Privileges to monitor what is running in the background. So I am monitoring from the SYS User.

I am checking whether it waiting for any lock in the background.

Regards,
Re: Query is taking longer duration [message #660240 is a reply to message #660239] Mon, 13 February 2017 05:33 Go to previous messageGo to next message
cookiemonster
Messages: 12927
Registered: September 2008
Location: Rainy Manchester
Senior Member
Ok.

Any generating the explain plans on production?
Re: Query is taking longer duration [message #660618 is a reply to message #660153] Wed, 22 February 2017 16:03 Go to previous message
mikek
Messages: 29
Registered: January 2017
Junior Member
Here is something to consider to improve the Update Command.
Add a new Composite Index to the table "hrbo.dim_employee"
which will include All Values including Nulls for the
Columns "central_id_cpid" and "last_promotion_date".
This Composite Index should provide the data needed
by the Query of the from Table "hrbo.dim_employee"
without the need to read and load the table data into
the SGA to process the Query.

As far as I know there the Update of "source_employee" Table will
still require the "TABLE ACCESS FULL".

Since this a change to the Physical Structure I would encourage
you to have it reviewed by someone on your team and if possible
perform test. This may aid in finding any issues that the
change may create.

You will need to edit the CREATE INDEX to provide the correct
Tablespace to the command.



CREATE INDEX hrbo.dim_employee_i ON hrbo.dim_employee
  (central_id_cpid, last_promotion_date)
TABLESPACE ???;



ANALYZE TABLE hrbo.dim_employee COMPUTE STATISTICS;


Reformatted Query so I can better understand it.

UPDATE itdb.source_employee s
SET    s.last_promotion_date =
         (SELECT MAX( de.last_promotion_date)
          FROM   hrbo.dim_employee de
          WHERE  de.central_id_cpid     = s.central_id_cpid
          AND    de.last_promotion_date IS NOT NULL
         )
WHERE  s.last_promotion_date IS NULL;

Previous Topic: Query became very slow
Next Topic: IM Column Store ( indexes,lob data)
Goto Forum:
  


Current Time: Mon Nov 20 22:36:47 CST 2017

Total time taken to generate the page: 0.02873 seconds