Title: RE: Tkprof output

Lee,

This query seems suspect

UPDATE VM_LIVE.SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE=1
WHERE
 ACXIOM_CUSTOMER_KEY = :b1  AND VERSION_NO = :b2

because of this

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute  39562      4.55       7.22      10897     118687          1           1
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    39562      4.55       7.22      10897     118687          1           1


It's reading a ton of blocks to operate on ONE record.  What's the table structure here?  What's the index structure?  Cardinality? 

The buffer gets in the other queries are suspect too.  What's your blocksize?  It's reading a ton of blocks to arrive at the result.



    -----Original Message-----
    From:   Robertson Lee - lerobe [SMTP:[EMAIL PROTECTED]]
    Sent:   Thursday, August 30, 2001 11:56 AM
    To:     Multiple recipients of list ORACLE-L
    Subject:        Tkprof output

    Apologies for the length of the mail.

    This query is running for a mad amount of time, anyone any ideas.

    Code and tkprof out put shown below.

    Huge TIA

    Lee (who must learn more about such things !!!)

    DECLARE CURSOR TEMP_CDS IS
    SELECT ACXIOM_CUSTOMER_KEY,
    ������ VERSION_NO,
    ������ ADDRESS_OCCUPANCY_KEY
    FROM�� CUSTOMER_DETAIL_SOURCE
    WHERE� VISIBLE=1;


    COUNTER NUMBER(8);

    BEGIN
    �� COUNTER:=0;
    �� FOR I IN TEMP_CDS
    �� LOOP


    ���� UPDATE &SCHEMA..SINGLE_CUSTOMER SC
    ����������� SET VISIBLE = 1
    ����������� WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY
    ����������� AND�� VERSION_NO�������� =I.VERSION_NO;


    ���� UPDATE &SCHEMA..SINGLE_CUSTOMER_HISTORY SCH
    ����������� SET VISIBLE = 1
    ����������� WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY
    ����������� AND�� VERSION_NO�������� =I.VERSION_NO;


    ���� UPDATE &SCHEMA..ADDRESS_OCCUPANCY AO
    ����������� SET VISIBLE = 1
    ����������� WHERE ADDRESS_OCCUPANCY_KEY = I.ADDRESS_OCCUPANCY_KEY;


    ����� COUNTER := COUNTER + 1;
    ����� IF (COUNTER = 50000)
    ����� THEN
    ��������� COUNTER:=0;
    ��������� COMMIT;
    ����� END IF;
    �� END LOOP;
    �� COMMIT;


    Sort options: prsela� exeela� fchela�
    ********************************************************************************
    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 VM_LIVE.SINGLE_CUSTOMER SC SET VISIBLE=1
    WHERE
    �ACXIOM_CUSTOMER_KEY = :b1� AND VERSION_NO = :b2



    call���� count������ cpu��� elapsed������ disk����� query��� current������� rows
    ------- ------� -------- ---------- ---------- ---------- ----------� ----------
    Parse������� 0����� 0.00������ 0.00��������� 0��������� 0��������� 0���������� 0
    Execute� 39562���� 15.51���� 398.98����� 56555���� 181085����� 40672������ 39562
    Fetch������� 0����� 0.00������ 0.00��������� 0��������� 0��������� 0���������� 0
    ------- ------� -------- ---------- ---------- ---------- ----------� ----------
    total��� 39562���� 15.51���� 398.98����� 56555���� 181085����� 40672������ 39562


    Misses in library cache during parse: 0
    Misses in library cache during execute: 1
    Optimizer goal: CHOOSE
    Parsing user id: 39� (VM_LIVE)�� (recursive depth: 1)


    Rows���� Execution Plan
    -------� ---------------------------------------------------
    ����� 0� UPDATE STATEMENT�� GOAL: CHOOSE
    ����� 0�� UPDATE OF 'SINGLE_CUSTOMER'
    ����� 0��� TABLE ACCESS (BY INDEX ROWID) OF 'SINGLE_CUSTOMER'
    ����� 0���� INDEX (UNIQUE SCAN) OF 'SINGLE_CUSTOMER_PK' (UNIQUE)


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

    UPDATE VM_LIVE.ADDRESS_OCCUPANCY AO SET VISIBLE=1
    WHERE
    �ADDRESS_OCCUPANCY_KEY = :b1



    call���� count������ cpu��� elapsed������ disk����� query��� current������� rows
    ------- ------� -------- ---------- ---------- ---------- ----------� ----------
    Parse������� 0����� 0.00������ 0.00��������� 0��������� 0��������� 0���������� 0
    Execute� 39562���� 12.57���� 186.88����� 57285���� 124038����� 40726������ 39562
    Fetch������� 0����� 0.00������ 0.00��������� 0��������� 0��������� 0���������� 0
    ------- ------� -------- ---------- ---------- ---------- ----------� ----------
    total��� 39562���� 12.57���� 186.88����� 57285���� 124038����� 40726������ 39562


    Misses in library cache during parse: 0
    Optimizer goal: CHOOSE
    Parsing user id: 39� (VM_LIVE)�� (recursive depth: 1)


    Rows���� Execution Plan
    -------� ---------------------------------------------------
    ����� 0� UPDATE STATEMENT�� GOAL: CHOOSE
    ����� 0�� UPDATE OF 'ADDRESS_OCCUPANCY'
    ����� 0��� INDEX (UNIQUE SCAN) OF 'I_ADDRESS_OCCUPANCY_I4' (UNIQUE)


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

    UPDATE VM_LIVE.SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE=1
    WHERE
    �ACXIOM_CUSTOMER_KEY = :b1� AND VERSION_NO = :b2



    call���� count������ cpu��� elapsed������ disk����� query��� current������� rows
    ------- ------� -------- ---------- ---------- ---------- ----------� ----------
    Parse������� 0����� 0.00������ 0.00��������� 0��������� 0��������� 0���������� 0
    Execute� 39562����� 4.55������ 7.22����� 10897���� 118687��������� 1���������� 1
    Fetch������� 0����� 0.00������ 0.00��������� 0��������� 0��������� 0���������� 0
    ------- ------� -------- ---------- ---------- ---------- ----------� ----------
    total��� 39562����� 4.55������ 7.22����� 10897���� 118687��������� 1���������� 1


    Misses in library cache during parse: 0
    Optimizer goal: CHOOSE
    Parsing user id: 39� (VM_LIVE)�� (recursive depth: 1)


    Rows���� Execution Plan
    -------� ---------------------------------------------------
    ����� 0� UPDATE STATEMENT�� GOAL: CHOOSE
    ����� 0�� UPDATE OF 'SINGLE_CUSTOMER_HISTORY'
    ����� 0��� INDEX (UNIQUE SCAN) OF 'SINGLE_CUSTOMER_HISTORY_PK' (UNIQUE)


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

    SELECT ACXIOM_CUSTOMER_KEY,VERSION_NO,ADDRESS_OCCUPANCY_KEY��
    FROM
    �CUSTOMER_DETAIL_SOURCE� WHERE VISIBLE = 1



    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��� 39562����� 1.51������ 2.04������� 392����� 39618��������� 0������ 39562
    ------- ------� -------- ---------- ---------- ---------- ----------� ----------
    total��� 39562����� 1.51������ 2.04������� 392����� 39618��������� 0������ 39562


    Misses in library cache during parse: 0
    Parsing user id: 39� (VM_LIVE)�� (recursive depth: 1)




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

    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����� 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


    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 118686���� 32.63���� 593.08���� 124737���� 423810����� 81399������ 79125
    Fetch��� 39562����� 1.51������ 2.04������� 392����� 39618��������� 0������ 39562
    ------- ------� -------- ---------- ---------- ---------- ----------� ----------
    total�� 158248���� 34.14���� 595.12���� 125129���� 463428����� 81399����� 118687


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


    ��� 4� user� SQL statements in session.
    ��� 0� internal SQL statements in session.
    ��� 4� SQL statements in session.
    ��� 3� statements EXPLAINed in this session.
    ********************************************************************************
    Trace file: ora_349778.trc
    Trace file compatibility: 7.03.02
    Sort options: prsela� exeela� fchela�
    ������ 1� session in tracefile.
    ������ 4� user� SQL statements in trace file.
    ������ 0� internal SQL statements in trace file.
    ������ 4� SQL statements in trace file.
    ������ 4� unique SQL statements in trace file.
    ������ 3� SQL statements EXPLAINed using schema:
    ���������� VM_LIVE.prof$plan_table
    ������������ Default table was used.
    ������������ Table was created.
    ������������ Table was dropped.
    � 158286� lines in trace file.



    The information contained in this communication is
    confidential, is intended only for the use of the recipient
    named above, and may be legally privileged. If the reader
    of this message is not the intended recipient, you are
    hereby notified that any dissemination, distribution or
    copying of this communication is strictly prohibited.
    If you have received this communication in error, please
    re-send this communication to the sender and delete the
    original message or any copy of it from your computer
    system.

Reply via email to