The query is executed for 4800x longer when starting from a C ++ program than in SQL Plus

Question

We have a serious problem with a query that defies explanation. In SQL Plus or TOAD, it works after 1/2 second, but when launched from a C ++ program through a distributed transaction, it takes 41 minutes. Until this week, this was done 10,000 times from C ++ code in just a second.

Nothing has changed in the database or code or on the W2k servers on which the code is running.

when running from code, it has a very high sequential read db file over 1,000,000

when exactly the same statement is launched from SQL plus db file sequential read - 8

In the same way, an operator does 100,000 times more work when run through code and DT than from sqlplus.

we ran the following query to find out which blocks are being read SELECT p1 "file #", p2 "block #", p3 "class #" FROM v $ session_wait WHERE event = 'db file sequential read'

and these are the tables used in the query. He reads the tables again and again, but the explanation plan indicates that only 8 blocks should be read.

both tables ~ 10 gigabytes in size

here is an example and explanation plan

SELECT COUNT (*) FROM student st, testinstance ti WHERE st.dataset_id = :b2 AND st.student_id = ti.student_id AND ti.testinstance_id > :b1 AND NOT EXISTS ( SELECT 1 FROM programscoringexclusion ex WHERE ex.program_id = wfgeneral.getprogramid (:b3) AND ex.testfamily_id = ti.testfamily_id AND NVL (ex.test_level, NVL (ti.test_level, '*')) = NVL (ti.test_level, '*') AND NVL (ex.battery, NVL (ti.battery, '*')) = NVL (ti.battery, '*') AND NVL (ex.form, NVL (ti.form, '*')) = NVL (ti.form, '*')) Plan SELECT STATEMENT CHOOSECost: 2 9 SORT AGGREGATE Bytes: 43 Cardinality: 1 8 FILTER 5 NESTED LOOPS Cost: 2 Bytes: 43 Cardinality: 1 2 TABLE ACCESS BY INDEX ROWID TABLE BBOX.TESTINSTANCE Cost: 1 Bytes: 32 Cardinality: 1 1 INDEX RANGE SCAN INDEX (UNIQUE) BBOX.XXPK0TESTINSTANCE Cost: 1 Cardinality: 1 4 TABLE ACCESS BY INDEX ROWID TABLE BBOX.STUDENT Cost: 1 Bytes: 11 Cardinality: 1 3 INDEX UNIQUE SCAN INDEX (UNIQUE) BBOX.XXPK0STUDENT Cost: 1 Cardinality: 1 7 TABLE ACCESS BY INDEX ROWID TABLE BBOX.PROGRAMSCORINGEXCLUSION Cost: 1 Bytes: 37 Cardinality: 1 6 INDEX RANGE SCAN INDEX BBOX.XXIE1PROGRAMSCORINGEXCLUSION Cost: 1 Cardinality: 1 

how can we see what the actual plan is for the operator when it is actually running? We can say that this is a reading of tables. Is it possible that the actual plan is different from the one we see, and actually does some kind of Cartesian conjunction or something weird that takes 40 minutes? is there any way to determine what?

Answer 1

To find the actual plan used, you can query v $ sql_plan with sql_id. The easiest way to do this is to put a comment on the request to make it unique, for example

 select /* FROM C++ */ .... 

and

 select /* FROM SQLPLUS */ .... 

Then run the query. For a query from v $ sql, you can find the SQL_ID of the query, for example:

 select sql_id, sql_fulltext from v$sql where upper(sql_fulltext) like '%FROM C++%'; 

Then, using this SQL_ID, you can query v $ sql_plan to get the plan or, better, use the following query:

 select * from table(dbms_xplan.diplay_cursor('SQL ID OBTAINED ABOVE'));