Query is taking longer duration [message #660153] |
Fri, 10 February 2017 03:05 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manoj12
Messages: 210 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 #660155 is a reply to message #660154] |
Fri, 10 February 2017 03:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manoj12
Messages: 210 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manoj12
Messages: 210 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 #660226 is a reply to message #660161] |
Mon, 13 February 2017 02:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manoj12
Messages: 210 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 #660228 is a reply to message #660227] |
Mon, 13 February 2017 03:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manoj12
Messages: 210 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 #660232 is a reply to message #660229] |
Mon, 13 February 2017 04:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manoj12
Messages: 210 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 #660234 is a reply to message #660233] |
Mon, 13 February 2017 04:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manoj12
Messages: 210 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 #660239 is a reply to message #660237] |
Mon, 13 February 2017 05:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manoj12
Messages: 210 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 #660618 is a reply to message #660153] |
Wed, 22 February 2017 16:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/9c1adc7946408f642b8150b56a4bc043?s=64&d=mm&r=g) |
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;
|
|
|