Update table runs too low. Please help [message #433325] |
Tue, 01 December 2009 10:10  |
lamnguyen14
Messages: 119 Registered: March 2007 Location: Virginia
|
Senior Member |
|
|
Good Afternoon.
I tried to update my table and it ran too slow. Please show me how to make it run faster. I would appreciate that.
Here is my procedure:
create or replace procedure upd_tb
is
mmdd_var VARCHAR2 (4) := TO_CHAR (sysdate_var, 'MMDD');
begin
UPDATE request
SET rqdate = mmdd_var
WHERE dept = '216'
AND rqdate = '0000'
AND (rq_id) IN (
SELECT rq_id
FROM request_update
WHERE NVL(rq_code, 'x') IN ('06', '24')
AND NVL(rq_form, 'x') NOT IN
('01', '02', '03', '04', '05', '06', '07',
'08', '201', '202', '275', '276'));
end;
Thanks much.
LN
|
|
|
|
Re: Update table runs too low. Please help [message #433327 is a reply to message #433325] |
Tue, 01 December 2009 10:18   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Unless you've got function based indexes on Request_Update then you'll be doing a FTS of that table.
You don't need the NVLs on RQ_CODE and RQ_FORM
Show us the Explain plabn that the query uses and the indexes that you've got on the two tables.
|
|
|
|
|
Re: Update table runs too low. Please help [message #433330 is a reply to message #433327] |
Tue, 01 December 2009 10:25   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
JRowbottom wrote on Tue, 01 December 2009 16:18
You don't need the NVLs on RQ_CODE and RQ_FORM
That's definitely true for rq_code but not necessarily true for rq_form. With the nvl any rows where rq_form is null will be picked up. Without it they won't.
|
|
|
Re: Update table runs too low. Please help [message #433334 is a reply to message #433325] |
Tue, 01 December 2009 12:00   |
lamnguyen14
Messages: 119 Registered: March 2007 Location: Virginia
|
Senior Member |
|
|
I have table REQUEST:
rq_id number(9),
rqdate varchar2(4)
primary key and index on RQ_ID column.
I have table REQUEST_UPDATE
rq_id number(9),
rq_code varchar2(3),
rq_form varchar2(3)
primary key and index on RQ_ID column.
I just tried my update without the NVL. It ran faster.
|
|
|
|
Re: Update table runs too low. Please help [message #433338 is a reply to message #433325] |
Tue, 01 December 2009 12:14   |
lamnguyen14
Messages: 119 Registered: March 2007 Location: Virginia
|
Senior Member |
|
|
I am so sorry. I tried that but I did not work for me. I don't know why since I am very new to Oracle. I am using TOAD 9.6.1
I tried :
SET AUTOTRACE ON explain
and got this error message:
ORA-00922: missing or invalid option
Thanks.
|
|
|
Re: Update table runs too low. Please help [message #433339 is a reply to message #433338] |
Tue, 01 December 2009 12:28   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
I really don't know much about TOAD other than it is a lazy mans way to do real work, but you'll want to do the autotrace in SQL*Plus.
Everyone was once new to Oracle but you have been over 2.5 years. Everything is relative, but I wouldn't consider that still "new" to Oracle.
|
|
|
|
Re: Update table runs too low. Please help [message #433341 is a reply to message #433325] |
Tue, 01 December 2009 12:42   |
lamnguyen14
Messages: 119 Registered: March 2007 Location: Virginia
|
Senior Member |
|
|
I don't have SQLPLUS. I only have TOAD 9.6.1
There are an AUTO TRACE and EXPLAIN PLAN tabs in TOAD. But I don't know how to use that. I clicked on these two tabs but did not see anything in there.
I will ask for SQLPLUS install in my machine. If you don't mind would you please to explain to me about SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS. I am very appreciated that.
I signed up for orafag.com for a long time because I know that my work will convert from Fortran to Oracle. When I have time I visit this website to learn from all of you.
Many thanks to all of you.
|
|
|
Re: Update table runs too low. Please help [message #433342 is a reply to message #433325] |
Tue, 01 December 2009 13:07   |
lamnguyen14
Messages: 119 Registered: March 2007 Location: Virginia
|
Senior Member |
|
|
The IT person just installed SQLPLUS in my machine. I tried
the SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS and get this message.
SQL> SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report.
|
|
|
|
|
|
|
Re: Update table runs too low. Please help [message #433499 is a reply to message #433325] |
Wed, 02 December 2009 10:04   |
lamnguyen14
Messages: 119 Registered: March 2007 Location: Virginia
|
Senior Member |
|
|
Thanks. And here are my tables
create table REQUEST
(rq_id number(9) NOT NULL,
rqdate varchar2(4));
create table REQUEST_UPDATE
(rq_id number(9) NOT NULL,
rq_code varchar2(3),
rq_form varchar2(3));
I also have index(rq_id) on both tables. The join columns are the RQ_ID in both table.
I don't know what is the cardinality.
thanks
|
|
|
|
|
Re: Update table runs too low. Please help [message #433508 is a reply to message #433325] |
Wed, 02 December 2009 10:37   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Try the following in sqlplus:
EXPLAIN PLAN FOR UPDATE request
SET rqdate = :mmdd_var
WHERE dept = '216'
AND rqdate = '0000'
AND (rq_id) IN (
SELECT rq_id
FROM request_update
WHERE NVL(rq_code, 'x') IN ('06', '24')
AND NVL(rq_form, 'x') NOT IN
('01', '02', '03', '04', '05', '06', '07',
'08', '201', '202', '275', '276'));
select * from table(dbms_xplan.display);
Also give the result of this:
SELECT COUNT(*)
FROM request
WHERE dept = '216'
AND rqdate = '0000';
|
|
|
Re: Update table runs too low. Please help [message #433510 is a reply to message #433507] |
Wed, 02 December 2009 10:47   |
mortonmorton
Messages: 6 Registered: October 2009
|
Junior Member |
|
|
It shows what plan / how Oracle will access your data. You'll get details on what it will do and the costs of it. The instruction above shows how to setup the plustrce roll which you need on sqlplus to use the "set autotrace" feature. You could also use the "EXPLAIN PLAN" statement above to generate your explain plan, but you're gonna need to have your own plan_table for that. To give you an idea here's how it looks like:
SQL> set autotrace traceonly explain
SQL> select 'x' from dual;
Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
You could check Oracle documentation on how to read/understand it.
|
|
|
Re: Update table runs too low. Please help [message #433511 is a reply to message #433507] |
Wed, 02 December 2009 10:51   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
post results from following SQL statements:
SELECT count(rq_id)
FROM request_update
WHERE NVL(rq_code, 'x') IN ('06', '24')
AND NVL(rq_form, 'x') NOT IN
('01', '02', '03', '04', '05', '06', '07',
'08', '201', '202', '275', '276'));
SELECT COUNT(*) FROM REQUEST_UPDATE;
|
|
|
|
|
|
Re: Update table runs too low. Please help [message #433642 is a reply to message #433325] |
Thu, 03 December 2009 07:43   |
lamnguyen14
Messages: 119 Registered: March 2007 Location: Virginia
|
Senior Member |
|
|
Good morning.
The Admin just gave me two small tables (they are same name and same structures but less data) to test my code. The Admin just received new data and they need to load them in.
There is one thing that I think I should let all of you know. I have a co-worker who also testing on this. She wrote her update by using cursor. We actually have 25 updates. They are very similar. She ran all her code with 25 updates. It took her 10sec. When I ran all mine with 25 update, it took me 30sec. That is the reason I want to find the way for my update statement run faster.
I just came to talk to her. She ran her code in the same database I ran mine, but her TABLESPACE_NAME and mine are different. Does it make a difference on the run time?
Thank you very much. All of you have spent a lot of time to help me out. I appreciate that.
I ran your queries as you told me:
SQL> SELECT count(rq_id)
2 FROM request_update
3 WHERE NVL(rq_code, 'x') IN ('06', '24')
4 AND NVL(rq_form, 'x') NOT IN
5 ('01', '02', '03', '04', '05', '06', '07',
6 '08', '201', '202', '275', '276');
COUNT(RQ_ID)
------------
20
SQL> SELECT COUNT(*) FROM REQUEST_UPDATE;
COUNT(*)
----------
10000
SQL> select COUNT(DISTINCT(RQ_ID)) from request_update;
COUNT(DISTINCT(RQ_ID))
----------------------
10000
SQL>
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4254023679
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 31 | 117 (6)| 00:00:02 |
| 1 | UPDATE | REQUEST | | | | |
|* 2 | HASH JOIN SEMI | | 1 | 31 | 117 (6)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| REQUEST | 1 | 16 | 107 (5)| 00:00:02 |
|* 4 | TABLE ACCESS FULL| REQUEST_UPDATE | 78 | 1170 | 10 (10)| 00:00:01 |
--------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("RQ_ID"="RQ_ID")
3 - filter("DEPT"='216' AND "RQDATE"='0000')
4 - filter((NVL("RQ_CODE",'x')='24' OR NVL("RQ_CODE",'x')='06') AND
NVL("RQ_FORM",'x')<>'201' AND NVL("RQ_FORM",'x')<>'202' AND
NVL("RQ_FORM",'x')<>'275' AND NVL("RQ_FORM",'x')<>'276' AND
NVL("RQ_FORM",'x')<>'08' AND NVL("RQ_FORM",'x')<>'07' AND
NVL("RQ_FORM",'x')<>'06' AND NVL("RQ_FORM",'x')<>'05' AND
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
NVL("RQ_FORM",'x')<>'04' AND NVL("RQ_FORM",'x')<>'03' AND
NVL("RQ_FORM",'x')<>'02' AND NVL("RQ_FORM",'x')<>'01')
24 rows selected.
|
|
|
Re: Update table runs too low. Please help [message #433650 is a reply to message #433325] |
Thu, 03 December 2009 08:17   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The trouble with using smaller tables for performance tunning is that the number of rows in a table can affect the plan. For small tables oracle will often skip indexes all together and go for the full table scan.
Tablespace itself doesn't really matter, but of course if she's using a different one then she's using different tables to you so colud well be running against a different amount of data.
Can you give the result of the following:
SELECT count(rq_id)
FROM request_update
WHERE NVL(rq_code, 'x') IN ('06', '24');
SELECT count(rq_id)
FROM request_update
NVL(rq_form, 'x') NOT IN
('01', '02', '03', '04', '05', '06', '07',
'08', '201', '202', '275', '276');
SELECT COUNT(*)
FROM request
WHERE dept = '216'
AND rqdate = '0000';
It'd also be usefull to see what code your co-worker is using and what explain plan she gets - make sure you run it against the table you are using.
Finally, if you've got 25 similar updates I'd be looking long and hard for ways to combine them into a single update because that would almost certainly run faster.
|
|
|
Re: Update table runs too low. Please help [message #433653 is a reply to message #433325] |
Thu, 03 December 2009 08:49   |
lamnguyen14
Messages: 119 Registered: March 2007 Location: Virginia
|
Senior Member |
|
|
Thanks much for your advice. I know that the small tables are not good for testing of tunning performance. But the admin usually maintains and loads new data to the tables at the begining of the month.
My co-worker did not show me her code. She just told me the run time and she said she uses cursor to update tables.
And she used the same input tables as mine.
Thanks again.
SQL> SELECT count(rq_id)
2 FROM request_update
3 WHERE NVL(rq_code, 'x') IN ('06', '24');
COUNT(RQ_ID)
------------
517
SQL>
SQL> SELECT count(rq_id)
2 FROM request_update
3 WHERE NVL(rq_form, 'x') NOT IN
4 ('01', '02', '03', '04', '05', '06', '07',
5 '08', '201', '202', '275', '276');
COUNT(RQ_ID)
------------
2533
SQL> SELECT COUNT(*)
2 FROM request
3 WHERE dept = '216'
4 AND rqdate = '0000';
COUNT(*)
----------
74164
SQL>
|
|
|
|
|