INSERT into temporary table GTT very slow from PL/SQL

Question

I have a query that performs fantastic when executed from SQL.

It is a join between a table and a query. Both the tables are having close to 4 mn records. There are bitmap indexes on the doc table that i am trying to provide hint on. The Explain plan does show they are helping the join well when i see from toad.

I have provided 2 other hints to see if they help as can be seen. One is for the Direct path APPEND and other to utilize the existing BTree index on pda.

When this query is run for substituted variables from SQL the results are instantaneous, but same query inside the procedure is taking 8 seconds or more.

Aside of the Procedure's plan which DBA hasnt yielded yet to, what are if any glaring misses that i may have you think? Thanks in advance.

 
     INSERT                                                           /*+ APPEND */
          INTO  tmp_search_gross_docs (document_id,
                                       last_name,
                                       first_name,
                                       person_doc_association_id,
                                       association_date)
       SELECT                 /*+INDEX(pda IDX_DOC_PDOC_DOCID ) USE_NL(pda doc) */
             pda.document_id,
              last_name,
              first_name,
              person_doc_association_id,
              association_date
         FROM   pda,
              (SELECT /*+INDEX_COMBINE(attr IDX_BMP_SEARCH_FN,IDX_BMP_SEARCH_LN)*/
                      document_id, last_name, first_name
                 FROM doc attr
                WHERE first_name LIKE l_first_name OR last_name LIKE l_last_name) doc
        WHERE pda.document_id = doc.document_id;
                         ) doc
                 WHERE pda.document_id = doc.document_id;

EXPLAIN Plan (from Toad for bind variables)

INSERT STATEMENT ALL_ROWSCost: 1,086,010 Bytes: 15,309,420 Cardinality: 364,510
11 LOAD AS SELECT TMP_SEARCH_GROSS_DOCS
10 TABLE ACCESS BY INDEX ROWID TABLE PDA Cost: 3 Bytes: 20 Cardinality: 1
9 NESTED LOOPS Cost: 1,086,010 Bytes: 15,309,420 Cardinality: 364,510
7 TABLE ACCESS BY INDEX ROWID TABLE ATTR Cost: 23,893 Bytes: 8,019,220 Cardinality: 364,510
6 BITMAP CONVERSION TO ROWIDS
5 BITMAP OR
2 BITMAP MERGE
1 BITMAP INDEX RANGE SCAN INDEX (BITMAP) IDX_BMP_SEARCH_FN 4 BITMAP MERGE
3 BITMAP INDEX RANGE SCAN INDEX (BITMAP) IDX_BMP_SEARCH_LN 8 INDEX RANGE SCAN INDEX IDX_PDA_EXP_DOC Cost: 2 Cardinality: 1

The cardinality 364,510 seems off as the table contains 3738562 rows and for a substituted values of the columns in WHERE the count is only 8892.

But again, this plan at least tells me that the right indexes are being used and runs very fast from the toad editor.

The actual plan from the PL/SQL is still not available.

Not sure if this adds some valuable info or not. But thought will edit nevertheless. Thanks

Not sure if this will help, but eventually it is building up a recordset that is then read by the application. I should point out that i did try the BULK COLLECT with a collection on the same SQL and that was the same performance too. Somehow feel the SELECT is not taking optimal path that it does when executed from editor.. — user2275460

haki haki · Accepted Answer · 2013-04-13T07:04:01

First, I don't think inserting into GTT with append has any logic. i might be wrong but from what i know append bypass the buffer cache and writes directly to the file, it writes above the high water mark and does not allow querying until a commit is made. a GTT is not on regular data files - it's on the temp files and it's being truncated on commit (default settings).

I think that if you don't need to manipulate the data after the query consider returning a ref cursor to the application. it's basically the same thing - a lot of DAL layers are implemented this way.

if you still want the GTT , i would check my temp file allocation, both size and actual disks - your DBA might have put them on different devices.

INSERT into temporary table GTT very slow from PL/SQL

3 Answers