Home » SQL & PL/SQL » SQL & PL/SQL » Update table runs too low. Please help
Update table runs too low. Please help [message #433325] Tue, 01 December 2009 10:10 Go to next message
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 #433326 is a reply to message #433325] Tue, 01 December 2009 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
using sqlplus do the following below
SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
-- run SQL now
Re: Update table runs too low. Please help [message #433327 is a reply to message #433325] Tue, 01 December 2009 10:18 Go to previous messageGo to next message
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 #433328 is a reply to message #433325] Tue, 01 December 2009 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
post DDL or DESCRIBE upd_tb table
Re: Update table runs too low. Please help [message #433329 is a reply to message #433325] Tue, 01 December 2009 10:21 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
This:
NVL(rq_code, 'x') IN ('06', '24')
is the same as this:
rq_code IN ('06', '24')

So lose that first nvl, it's doing nothing except possibly slowing your update down.
Re: Update table runs too low. Please help [message #433330 is a reply to message #433327] Tue, 01 December 2009 10:25 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
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 #433336 is a reply to message #433334] Tue, 01 December 2009 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
both JRowbottom & I requested EXPLAIN PLAN which you ignored.
So we might ignore your requests in the future.

Enjoy!
Re: Update table runs too low. Please help [message #433338 is a reply to message #433325] Tue, 01 December 2009 12:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4643
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 #433340 is a reply to message #433338] Tue, 01 December 2009 12:28 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
using sqlplus do the following below
using SQLPLUS do the following below
SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
-- run SQL now
Re: Update table runs too low. Please help [message #433341 is a reply to message #433325] Tue, 01 December 2009 12:42 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #433343 is a reply to message #433342] Tue, 01 December 2009 13:10 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

below from following URL
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5671636641855

cd $oracle_home/rdbms/admin
log into sqlplus as system
run SQL> @utlxplan
run SQL> create public public synonym plan_table for plan_table
run SQL> grant all on plan_table to public
exit sqlplus and cd $oracle_home/sqlplus/admin
log into sqlplus as SYS
run SQL> @plustrce

[Updated on: Tue, 01 December 2009 13:15]

Report message to a moderator

Re: Update table runs too low. Please help [message #433344 is a reply to message #433325] Tue, 01 December 2009 13:15 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thanks much and I tried and got this:

SQL> GRANT PLUSTRACE TO LN326;
GRANT PLUSTRACE TO LN326
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist


SQL>
Re: Update table runs too low. Please help [message #433345 is a reply to message #433344] Tue, 01 December 2009 13:16 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
see update post above
Re: Update table runs too low. Please help [message #433433 is a reply to message #433325] Wed, 02 December 2009 05:24 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
To get useful answers let us now the table structure, the presences of any index, the explain plan and some word about the cardinality of the filtering and joining columns.

Bye Alessandro
Re: Update table runs too low. Please help [message #433499 is a reply to message #433325] Wed, 02 December 2009 10:04 Go to previous messageGo to next message
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 #433502 is a reply to message #433325] Wed, 02 December 2009 10:17 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
How many rows are in each table?
How many rows in request_update match the sub-query?
And we still need an explain plan
Re: Update table runs too low. Please help [message #433507 is a reply to message #433325] Wed, 02 December 2009 10:34 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
I have 100,000 rows in both tables. And 10,000 in request_update match the sub query.

would you please to tell me what is the explain plan? I would appreciate that. I have no idea what it is.

Thanks much.
Re: Update table runs too low. Please help [message #433508 is a reply to message #433325] Wed, 02 December 2009 10:37 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25047
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 #433512 is a reply to message #433325] Wed, 02 December 2009 10:55 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why count(rq_id)?
Why not just count(*), or did you mean count(distinct(rq_id))?
Re: Update table runs too low. Please help [message #433514 is a reply to message #433512] Wed, 02 December 2009 10:58 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>or did you mean count(distinct(rq_id))?
I erroneously did not consider duplicates.
yes, COUNT(DISTINCT(RQ_ID)) is the meaningful metric.
Re: Update table runs too low. Please help [message #433523 is a reply to message #433325] Wed, 02 December 2009 12:34 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Hi

my SQLPLUS server is down. If it is not back up before I go home. I will post everything tomorrow.

I would like to say many thanks to all of you.

LN
Re: Update table runs too low. Please help [message #433642 is a reply to message #433325] Thu, 03 December 2009 07:43 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
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> 


Re: Update table runs too low. Please help [message #433654 is a reply to message #433325] Thu, 03 December 2009 08:53 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Try this:
remove the nvl from rq_code in your update - you don't need it.
Then add an index to request_update on rq_code.
Re: Update table runs too low. Please help [message #433817 is a reply to message #433325] Fri, 04 December 2009 06:55 Go to previous message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Good morning.

Thank you very much. I combined all the update statement to one. Remove NVL and add index for rq_code. My update statement run much faster.

I have learned a lot for all of you. Many many thanks.

LN
Previous Topic: using AND conditon in select statement
Next Topic: pipe delimmited file without sql inside
Goto Forum:
  


Current Time: Fri Dec 09 09:34:48 CST 2016

Total time taken to generate the page: 0.12123 seconds