Re: PL/SQL Performance Issue or Slow Cursor?

I tried to use hint, it didn't help.

Sorry, I'm new to Explain plan, I need to do a lot of reading before I know exactly what I'm doing.

Or, can you guide me the quickest way to run Explain plan in producing the statistics info.

Currently I'm using PL/SQL Developer to run the test script against the Oracle database, it run across multiple stored procedures, but 95% of the execution time spent on fetching the cursor that I mentioned earlier (I was using break point setting to time the execution path and found the bottleneck). At the end of the execution, PL/SQL Develpoer produces the following statistics info:

Name                            Last        Total
CPU used  by this session       1345        1348
physical reads                  0           0
physical writes                 0           0
session logical reads           310922      310933
sorts (disk)                    0           0
sorts (memory)                  3           3
sorts (rows)                    3           3
table fetch by rowid            933581      933582
table scan blocks gotten        123         123
table scan row gotten           51          51
table scan (long tables)        0           0
table scan (short table)        51          51

Vance.

bialik_at_isdn.net.il (Michael Bialik) wrote in message news:<969f8022.0109251128.5adb071d_at_posting.google.com>...
> Did you try forcing optimizer to start with TEMP_... table by using hint?
>
> CURSOR c_search IS
> SELECT /*+ ORDERED */ PT.ID,
> PT.SET_TYPE,
> PT.LOCATION,
> PT.BACKUP,
> PT.ARCHIVE_ID
> FROM TEMP_RESULT_TABLE TRT, P_TYPES PT
> WHERE TRT.ID = PT.ID
> ORDER BY TRT.ID;
>
> If it will not work - please post:
> 1. EXPLAIN plan.
> 2. Create table(s) statements ( with all indexes ).
> 3. Are you using CBO or RBO?
>
> HTH. Michael.
Received on Wed Sep 26 2001 - 04:22:56 CDT