Re: PL/SQL Performance Issue or Slow Cursor?

From: Vance Wu <vwu_at_anacomp.com>
Date: 7 Oct 2001 01:15:53 -0700
Message-ID: <c3d4638a.0110070015.38ddeab1@posting.google.com>

Dear Michael Bialik,

Here's my result:

1. Use EXPLAIN window of PLSQL Developer to get EXPLAIN PLAN for SQL statement:

        SELECT PT.ID,
               PT.SET_TYPE,
               PT.LOCATION,
               PT.BACKUP,
               PT.ARCHIVE_ID
          FROM P_TYPES PT, TEMP_RESULT_TABLE TRT
          WHERE TRT.ID = PT.ID
          ORDER BY TRT.ID



    - SELECT STATEMENT, COST=837, CARDINALITY=40453388

      |
      - MERGE JOIN, COST=837, CARDINALITY=40453388
        |
        - TABLE ACCESS BY GLOBAL INDEX ROWID P_TYPES, COST=826, CARDINALITY=1981067
        | |
        |  - INDEX FULL SCAN I_P_TYPES_ID, COST=26, CARDINALITY=1981067
        - SORT JOIN, COST=9, CARDINALITY=2042
          |
           - TABLE ACCESS FULL TEMP_RESULT_TABLE, COST=2, CARDINALITY=2042

2. Table and Index definitions on P_TYPES table.

• Tables definitions:

    SQL> DESC P_TYPES

     Name                                      Null?    Type
     ----------------------------------------- -------- ---------------
     BACKUP                                             VARCHAR2(1000)
     LOCATION                                           VARCHAR2(1000)
     F_SIZE                                             NUMBER(10)
     NUMBER_OF_PAGES                                    NUMBER(6)
     EXPIRATION_DATE                           NOT NULL DATE
     ARCHIVE_IND                               NOT NULL CHAR(1)
     ID                                        NOT NULL NUMBER(20)
     SET_TYPE                                  NOT NULL VARCHAR2(2)
     T_TIMESTAMP                                        DATE
     ARCHIVE_ID                                         NUMBER(10)

• Indexes definition on P_TYPES table:

        CREATE INDEX I_P_TYPES_ID ON P_TYPES(ID) TABLESPACE ..
        CREATE INDEX I_P_TYPES_ARCHIVE ON P_TYPES(ARCHIVE_ID) TABLESPACE ..
        CREATE INDEX I_P_TYPES_LOC ON P_TYPES(LOCATION) TABLESPACE ..
        CREATE INDEX I_P_TYPES_BKUP ON P_TYPES(BACKUP) TABLESPACE ..

• Output of Query:

      SELECT * FROM user_tables
      WHERE table_name in ('P_TYPES','TEMP_RESULT_TABLE')

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_RESULT_TABLE              EPWENV

P_TYPES

CLUSTER_NAME                   IOT_NAME
------------------------------ ------------------------------

  PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT ---------- ---------- ---------- ---------- -------------- -----------

        10 40 1 255 5242880 5242880

MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B ----------- ----------- ------------ ---------- --------------- --- -

          1  2147483645            0          1               1 NO  N
                                                                    N

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN

---------- ---------- ------------ ---------- ---------- -----------

AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES

------------------------- ------------------- ---------- ----------
                                                       1          1
                                                       1          1

CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE     T S NES BUFFER_
----- -------- ----------- --------- --- ------------ - - --- -------
    N ENABLED                        NO               N N NO  DEFAULT
    N ENABLED                        YES              N N NO

ROW_MOVE GLO USE DURATION        SKIP_COR MON
-------- --- --- --------------- -------- ---
DISABLED NO  NO                  DISABLED NO
DISABLED NO  NO                  DISABLED NO

Thanks,
Vance.

bialik_at_isdn.net.il (Michael Bialik) wrote in message news:<969f8022.0110031255.4035b581_at_posting.google.com>...

> Hi.
> 
> 1. Use EXPLAIN window of PLSQL Developer to get EXPLAIN PLAN and post
> it.
> 2. What indexes do you have on P_TYPES table?
>    Post:
>    - Tables definitions
>    - Index definitions ( Do you have an index on P_TYPES table with ID
> as leading column?)
>    - Output of query 
>      "SELECT * FROM user_tables 
>        WHERE table_name in ('P_TYPES','TEMP_RESULT_TABLE')"
> 
> DECLARE
>  CURSOR c_search IS
>         SELECT PT.ID,
>                PT.SET_TYPE,
>                PT.LOCATION,
>                PT.BACKUP,
>                PT.ARCHIVE_ID
>           FROM P_TYPES PT, TEMP_RESULT_TABLE TRT
>           WHERE TRT.ID = PT.ID
>           ORDER BY TRT.ID;

Received on Sun Oct 07 2001 - 03:15:53 CDT