Slow Update [message #470415] |
Mon, 09 August 2010 17:01 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Michael Gaarde
Messages: 29 Registered: August 2010
|
Junior Member |
|
|
Hi,
have this statement in a sp, it is slow and runs in 48 seconds whereas the same job in a MS SQL database takes about a 1/4 of a second, I have tried to add index on the 3 WHERE fields in the table XBI_Finance.
Any suggestions will be appreciated.
Thanks, Michael
UPDATE XBI_Finance
SET
(COMPANYNUMBER
,FISCALYEAR
,BALANCE
,BALANCEENT
,OPENBALANCE
,OPENBALANCEENT
,CLOSINGBALANCE
,CLOSINGBALANCEENT)
=(
SELECT
p.COMPANYNUMBER
, p.PERIOD
, COALESCE(p.FULLYEARBASE, 0)
, COALESCE(p.FULLYEARENTERPRISE, 0)
, COALESCE(p.OPENBALANCEBASE, 0)
, COALESCE(p.OPENBALANCEENTERPRISE, 0)
, COALESCE(p.CLOSINGBALANCEBASE, 0)
, COALESCE(p.CLOSINGBALANCEENTERPRISE, 0)
FROM (SELECT
dp.*, fy.PERIOD
FROM vwDIMENSIONPERIOD_MCDK_PILOT dp INNER JOIN
vwFISCALYEAR_MCDK_PILOT fy
ON dp.FISCALYEAR = fy.FISCALYEARSTART
AND fy.FISCALYEARSTART = To_Date('01-01-2009','dd-MM-yyyy') AND dp.COMPANYNUMBER = '1100'
) p
WHERE p.ACCOUNTNUMBER = XBI_Finance.ACCOUNTNUMBER
AND p.LOCATIONNAME = XBI_Finance.LOCATIONNAME
AND p.ENTITYNAME = XBI_Finance.ENTITYNAME
|
|
|
|
|
|
|
Re: Slow Update [message #470422 is a reply to message #470421] |
Mon, 09 August 2010 17:52 ![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) |
Michael Gaarde
Messages: 29 Registered: August 2010
|
Junior Member |
|
|
Results in
SQL> dbms_stats.gather_table_stats('dhixplus','XBI_Finance',Cascade=>TRUE
SP2-0734: unknown command beginning "dbms_stats..." - rest of line ignored.
SQL>
|
|
|
|
Re: Slow Update [message #470424 is a reply to message #470423] |
Mon, 09 August 2010 18:01 ![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) |
Michael Gaarde
Messages: 29 Registered: August 2010
|
Junior Member |
|
|
And it's the update that is slow NOT the select.
I can rewrite SQL to contain more data then this data is included, performance is good but legibility goes down
|
|
|
|
|
|
|
Re: Slow Update [message #470472 is a reply to message #470415] |
Tue, 10 August 2010 01: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) |
Its_me_ved
Messages: 979 Registered: October 2009 Location: India
|
Senior Member |
|
|
Please have a look at each post and there is some advice given to you. Please follow the advice provided by the members.
[/code]
FROM vwDIMENSIONPERIOD_MCDK_PILOT dp INNER JOIN
vwFISCALYEAR_MCDK_PILOT fy
[/code]
I guess you are selecting data from two views been created.
How many rows are there in the table associated with the update statement you are providing including those two views created?
what are the indexes on those tables?
Make sure your statistics are upto date.
Please post the explain plan.
Regards
Ved
|
|
|
|
Re: Slow Update [message #470475 is a reply to message #470473] |
Tue, 10 August 2010 01:48 ![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) |
Its_me_ved
Messages: 979 Registered: October 2009 Location: India
|
Senior Member |
|
|
Michael Gaarde wrote on Tue, 10 August 2010 01:46Would love to post the explain plan, but I can't find the output, have seached both the client and the actual Oracle server.
Please have a look at this link
Are you unable to get the plan output? what happened when you ran
the statement following the steps mentioned in the link above?
Regards,
Ved
|
|
|
Re: Slow Update [message #470476 is a reply to message #470473] |
Tue, 10 August 2010 01:48 ![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) |
Michael Gaarde
Messages: 29 Registered: August 2010
|
Junior Member |
|
|
I have rewritten the sql to avoide the update, and performance is very good (<1 sec), but fundamentally I'd like to learn why it was slow, I will continue trying to retrieve the explain plan and post it
|
|
|
Re: Slow Update [message #470496 is a reply to message #470415] |
Tue, 10 August 2010 02:17 ![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) |
Michael Gaarde
Messages: 29 Registered: August 2010
|
Junior Member |
|
|
Got the Explain Plan, attached as csv
It looks like the main load is HASH JOINS
I Don't have the option to change the underlying tables.
Indexes are believed to be the same on the SQL server where the update performs instantly (< 1 sec)
|
|
|
Re: Slow Update [message #470499 is a reply to message #470496] |
Tue, 10 August 2010 02:20 ![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) |
Its_me_ved
Messages: 979 Registered: October 2009 Location: India
|
Senior Member |
|
|
Most of us can not or do not want to download files.
Please copy and paste the output here (Make sure the output is formatted)
Regards
Ved
|
|
|
|
Re: Slow Update [message #470503 is a reply to message #470499] |
Tue, 10 August 2010 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) |
Michael Gaarde
Messages: 29 Registered: August 2010
|
Junior Member |
|
|
Makes sense, but the output is quite large, here is a part of it
Are there other col that makes sense to read ?
CPU_COST IO_COST ACCESS_PREDICATES
103567066 360 "DP"."FISCALYEAR"=CASE "FISCALYEARSTART" WHEN ' ' THEN NULL ELSE TO_DATE("FISCALYEARSTART",'YYYY.MM.DD') END
103556953 358 "DP"."FISCALYEAR"=CASE "FISCALYEARSTART" WHEN ' ' THEN NULL ELSE TO_DATE("FISCALYEARSTART",'YYYY.MM.DD') END
103556953 358 "DP"."FISCALYEAR"=CASE "FISCALYEARSTART" WHEN ' ' THEN NULL ELSE TO_DATE("FISCALYEARSTART",'YYYY.MM.DD') END
54497719 357 "E"."ENTITYNAME"(+)="P"."ENTITYNAME"
54487606 355 "E"."ENTITYNAME"(+)="P"."ENTITYNAME"
54487606 355 "E"."ENTITYNAME"(+)="P"."ENTITYNAME"
38140931 355 "L"."LOCATIONNAME"(+)="P"."LOCATIONNAME"
38130818 353 "L"."LOCATIONNAME"(+)="P"."LOCATIONNAME"
38130818 353 "L"."LOCATIONNAME"(+)="P"."LOCATIONNAME"
21784143 353 "ACCOUNTNUMBER"="P"."ACCOUNTNUMBER"
21774030 351 "ACCOUNTNUMBER"="P"."ACCOUNTNUMBER"
21774030 351 "ACCOUNTNUMBER"="P"."ACCOUNTNUMBER"
|
|
|
Re: Slow Update [message #470504 is a reply to message #470415] |
Tue, 10 August 2010 02:27 ![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) |
Michael Gaarde
Messages: 29 Registered: August 2010
|
Junior Member |
|
|
PLAN_TABLE_OUTPUT
Plan hash value: 223619584
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 4474 | 262K| 68 (0)| 00:00:01 |
| 1 | UPDATE | XBI_FINANCE | | | | |
| 2 | TABLE ACCESS FULL | XBI_FINANCE | 4474 | 262K| 68 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 1 | 627 | 361 (1)| 00:00:05 |
|* 4 | TABLE ACCESS FULL | FISCALYEAR | 1 | 24 | 3 (0)| 00:00:01 |
| 5 | VIEW | VWDIMENSIONPERIOD_MCDK_PILOT | 3 | 1809 | 358 (1)| 00:00:05 |
| 6 | SORT GROUP BY | | 3 | 12417 | 358 (1)| 00:00:05 |
| 7 | VIEW | | 3 | 12417 | 357 (1)| 00:00:05 |
|* 8 | HASH JOIN OUTER | | 3 | 5679 | 357 (1)| 00:00:05 |
|* 9 | HASH JOIN OUTER | | 3 | 4905 | 354 (1)| 00:00:05 |
|* 10 | HASH JOIN | | 3 | 4131 | 352 (1)| 00:00:05 |
| 11 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 176 | 2 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | ACCOUNT01 | 1 | | 1 (0)| 00:00:01 |
|* 13 | VIEW | EXDIMENSIONPERIOD | 784 | 919K| 349 (0)| 00:00:05 |
| 14 | TABLE ACCESS BY INDEX ROWID| DIMENSIONPERIOD | 784 | 153K| 349 (0)| 00:00:05 |
|* 15 | INDEX SKIP SCAN | DIMENSIONPERIOD01 | 784 | | 8 (0)| 00:00:01 |
| 16 | VIEW | EXLOCATION | 1 | 258 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | LOCATION | 1 | 62 | 2 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | LOCATION01 | 1 | | 1 (0)| 00:00:01 |
| 19 | VIEW | EXENTITY | 1 | 258 | 2 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | ENTITY | 1 | 61 | 2 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | ENTITY01 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DP"."FISCALYEAR"=CASE "FISCALYEARSTART" WHEN ' ' THEN NULL ELSE
TO_DATE("FISCALYEARSTART",'YYYY.MM.DD') END )
4 - filter(CASE "FISCALYEARSTART" WHEN ' ' THEN NULL ELSE TO_DATE("FISCALYEARSTART",'YYYY.MM.DD') END
=TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
8 - access("E"."ENTITYNAME"(+)="P"."ENTITYNAME")
9 - access("L"."LOCATIONNAME"(+)="P"."LOCATIONNAME")
10 - access("ACCOUNTNUMBER"="P"."ACCOUNTNUMBER")
12 - access("ACCOUNTNUMBER"=:B1)
13 - filter("P"."LOCATIONNAME"=:B1 AND "P"."ENTITYNAME"=:B2 AND "P"."COMPANYNUMBER"='1100' AND
"P"."FISCALYEAR"=TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
15 - access("ACCOUNTNUMBER"=:B1)
filter("ACCOUNTNUMBER"=:B1)
18 - access("LOCATIONNAME"=:B1)
21 - access("ENTITYNAME"=:B1)
|
|
|
|
|
Re: Slow Update [message #470523 is a reply to message #470514] |
Tue, 10 August 2010 03:17 ![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) |
![](http://www.orafaq.com/forum/images/custom_avatars/146113.gif) |
rahulvb
Messages: 924 Registered: October 2009 Location: Somewhere Near Equator.
|
Senior Member |
|
|
Just use the Explain Plan for Select not for update.
SELECT
p.COMPANYNUMBER ,
p.PERIOD ,
COALESCE(p.FULLYEARBASE , 0) ,
COALESCE(p.FULLYEARENTERPRISE , 0) ,
COALESCE(p.OPENBALANCEBASE , 0) ,
COALESCE(p.OPENBALANCEENTERPRISE , 0) ,
COALESCE(p.CLOSINGBALANCEBASE , 0) ,
COALESCE(p.CLOSINGBALANCEENTERPRISE , 0)
FROM
(
SELECT
dp.* ,
fy.PERIOD
FROM
vwDIMENSIONPERIOD_MCDK_PILOT dp
INNER JOIN vwFISCALYEAR_MCDK_PILOT fy
ON
dp.FISCALYEAR = fy.FISCALYEARSTART
AND fy.FISCALYEARSTART = To_Date('01-01-2009' , 'dd-MM-yyyy')
AND dp.COMPANYNUMBER = '1100'
|
|
|
Re: Slow Update [message #470526 is a reply to message #470415] |
Tue, 10 August 2010 03:23 ![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) |
Michael Gaarde
Messages: 29 Registered: August 2010
|
Junior Member |
|
|
Here it is, it is very fast, sorry about the foprmatting, but I simple can't get SQL tools to format it
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1131 | 1030 (1)| 00:00:13 |
| 1 | HASH GROUP BY | | 1 | 1131 | 1030 (1)| 00:00:13 |
|* 2 | HASH JOIN | | 1 | 1131 | 1029 (1)| 00:00:13 |
|* 3 | TABLE ACCESS FULL | FISCALYEAR | 1 | 36 | 3 (0)| 00:00:01 |
| 4 | VIEW | | 790 | 844K| 1026 (1)| 00:00:13 |
|* 5 | HASH JOIN RIGHT OUTER | | 790 | 692K| 1026 (1)| 00:00:13 |
| 6 | VIEW | EXENTITY | 92 | 23736 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | ENTITY | 92 | 5612 | 3 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT OUTER | | 790 | 493K| 1022 (1)| 00:00:13 |
| 9 | VIEW | EXLOCATION | 92 | 23736 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | LOCATION | 92 | 5704 | 3 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 790 | 294K| 1019 (1)| 00:00:13 |
| 12 | TABLE ACCESS FULL | ACCOUNT | 248 | 43648 | 5 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID| DIMENSIONPERIOD | 980 | 197K| 1013 (1)| 00:00:13 |
|* 14 | INDEX RANGE SCAN | DIMENSIONPERIOD02 | 980 | | 556 (1)| 00:00:07 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."FISCALYEAR"=CASE "FISCALYEARSTART" WHEN ' ' THEN NULL ELSE
TO_DATE("FISCALYEARSTART",'YYYY.MM.DD') END )
3 - filter(CASE "FISCALYEARSTART" WHEN ' ' THEN NULL ELSE
TO_DATE("FISCALYEARSTART",'YYYY.MM.DD') END =TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
5 - access("E"."ENTITYNAME"(+)="ENTITYNAME")
8 - access("L"."LOCATIONNAME"(+)="LOCATIONNAME")
11 - access("ACCOUNTNUMBER"="ACCOUNTNUMBER")
14 - access("COMPANYNUMBER"='1100')
filter(CASE "FISCALYEAR" WHEN ' ' THEN NULL ELSE TO_DATE("FISCALYEAR",'YYYY.MM.DD') END
=TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
|
|
|
|
|
|
Re: Slow Update [message #470534 is a reply to message #470530] |
Tue, 10 August 2010 03:35 ![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) |
Michael Gaarde
Messages: 29 Registered: August 2010
|
Junior Member |
|
|
But the SELECT is fast using the same queries, I have now rewritte the sp to avoid the updates, this causes loss of legibility and flexibility (not all parts of the statement are required at all time), but it performs really well
The same views are i use on MSSQL and here they perform well.
Leave it, I don't have the time (or skill) to persue it further.
Thanks again for you time
/Michael
|
|
|
|
Re: Slow Update [message #470561 is a reply to message #470535] |
Tue, 10 August 2010 04:07 ![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) |
Michael Gaarde
Messages: 29 Registered: August 2010
|
Junior Member |
|
|
No, these are the cols i have
STATEMENT_ID, PLAN_ID,
TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID, DEPTH, POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID, OTHER, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES, PROJECTION, TIME, QBLOCK_NAME, OTHER_XML
using
SELECT * FROM PLAN_TABLE
|
|
|
|
Re: Slow Update [message #470585 is a reply to message #470567] |
Tue, 10 August 2010 04:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Use the method rahulvb showed you for getting the explain plan:
EXPLAIN PLAN FOR
< Your Query >;
SELECT * FROM TABLE(dbms_xplan.display);
Do that in sqlplus and you'll get it correctly formatted.
TABLE ACCESS FULL is data that appears in the plan. It's not a column.
|
|
|