Re: a performance question

From: Jay <jwu_at_nctr.fda.gov>
Date: Fri, 21 Mar 2003 10:25:25 -0600
Message-ID: <R1Hea.62$N5.5044@mencken.net.nih.gov>

I do have the trace and tkprof as below. Can any expert provide insight? Thanks.

TKPROF: Release 9.2.0.1.0 - Production on Thu Mar 20 11:44:59 2003

Trace file: ./prod_s001_925908.trc
Sort options: default

count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call


****************************************************************************

UPDATE request

SET print_flag = 'Y'
WHERE lib_proc_nbr > 125636 AND lib_proc_nbr < 125737

call count cpu elapsed disk query current rows ------- ------ ----- ---------- ------ ---------- ---------- ------

Parse        0  0.00       0.00      0          0          0       0
Execute      1  0.01     920.27      0          6         99      96
Fetch        0  0.00       0.00      0          0          0       0
------- ------ ----- ---------- ------ ---------- ----------  ------
total        1  0.01     920.27      0          6         99      96

Misses in library cache during parse: 0
Misses in library cache during execute: 1 Optimizer goal: CHOOSE
Parsing user id: 492 (****)

Rows Execution Plan

-------  ---------------------------------------------------
      0  UPDATE STATEMENT   GOAL: CHOOSE
      0   UPDATE OF 'REQUEST'
      0    INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'RDDEQUEST_LIBPROCNBR'
               (UNIQUE)






**************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ ----- ---------- ------- ---------- ---------- ------

Parse        0   0.00       0.00       0          0          0      0
Execute      1   0.01     920.27       0          6         99     96
Fetch        0   0.00       0.00       0          0          0      0
------- ------  ----- ---------- ------- ---------- ----------  -----
total        1   0.01     920.27       0          6         99     96

Misses in library cache during parse: 0
Misses in library cache during execute: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ ----- ---------- ------- ---------- ---------- -------

Parse        0   0.00       0.00       0          0          0        0
Execute      0   0.00       0.00       0          0          0        0
Fetch        0   0.00       0.00       0          0          0        0
------- ------  ----- ---------- ------- ---------- ----------  -------
total        0   0.00       0.00       0          0          0        0

Misses in library cache during parse: 0

    1 user SQL statements in session.
    0 internal SQL statements in session.     1 SQL statements in session.
    1 statement EXPLAINed in this session.

Trace file: ./prod_s001_925908.trc
Trace file compatibility: 9.00.01
Sort options: default

       1  session in tracefile.
       1  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       1  SQL statements in trace file.
       1  unique SQL statements in trace file.
       1  SQL statements EXPLAINed using schema:
           PCRS.prof$plan_table
             Default table was used.
             Table was created.
             Table was dropped.
      25  lines in trace file.

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:5tGea.192058$S_4.102741_at_rwcrnsc53...

> Hard to say.  An explain plan and a tkprof would have helped a lot.
> Jim
>
> --
> Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
> with family.  Remove the negative part, keep the minus sign.  You can

figure

> it out.
> "Jay" <jwu_at_nctr.fda.gov> wrote in message
> news:_AFea.60$N5.4917_at_mencken.net.nih.gov...
> > I have a table that has about 100,000 records. The primary key has type

> > number. When I did a 'update column=value where primary_key > number and
> > primary < number (about 100 records), it took about 15 minutes. Our
> database
> > server is a capable machine and this slow update is totally

upacceptible.

> > After we dropped the table and recreated it, the same update took less
> than
> > 1 second. I am wondering what change within the datase has made the huge
> > performance difference. Thanks.
> >
> > Jay
> >
> >
>
>

Received on Fri Mar 21 2003 - 10:25:25 CST