Performance problem 8.1.6

From: Andy Rigby <andy_at_softap.co.uk>
Date: 3 Apr 2002 04:43:40 -0800
Message-ID: <56c32d01.0204030443.5728967@posting.google.com>

I'm having problems with one particular (and quite complex) select statement, which is taking too long to execute. The problem appears to be related to the ORDER BY Q_SEG0 DESC, because if I change this to use ascending order, the speed is lightning fast again.

I have created an index on Q_SEG0 (descending), PTR (ascending), also tried both index columns ascending, but no improvement. PTR is also a primary key. All tables and indexes have been analysed.

The issue is further complicated by the fact that I am selecting from a view (TBL6_QNX_VIEW).

Here is the trace output, I'd be interested to see if anyone can suggest some tweaks to help the performance:

SELECT *
FROM
(SELECT /*+FIRST_ROWS */ * FROM TBL6_3_QNX_VIEW WHERE (Q_SEG0 < 'OKOPRPPS') OR (Q_SEG0 = 'OKOPRPPS' AND (PTR>190228)) ORDER BY Q_SEG0 desc,PTR) WHERE ROWNUM<= 17 ORDER BY Q_SEG0 desc,PTR

call     count       cpu    elapsed       disk      query    current  
     rows

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

Parse        1      0.00       0.00          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch        3      0.52       1.87       1143     100026         12  
       17

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

total        5      0.52       1.87       1143     100026         12  
       17

Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 23

Rows Row Source Operation

-------  ---------------------------------------------------
     17  COUNT STOPKEY 
     17   VIEW 
     17    SORT ORDER BY STOPKEY 
  33088     CONCATENATION 
      2      NESTED LOOPS 
      3       TABLE ACCESS FULL TBL6_3_QNX 
      2       TABLE ACCESS BY INDEX ROWID TBL6 
      4        INDEX UNIQUE SCAN (object id 4868)
      0      NESTED LOOPS 
      1       TABLE ACCESS FULL TBL6_3_QNX 
      0       TABLE ACCESS BY INDEX ROWID TBL6 
      0        INDEX UNIQUE SCAN (object id 4868)
  33086      NESTED LOOPS 
  33087       TABLE ACCESS FULL TBL6_3_QNX 
  33086       TABLE ACCESS BY INDEX ROWID TBL6 
  66172        INDEX UNIQUE SCAN (object id 4868)

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

View composition:

The view consists of 2 tables:

TBL_6_QNX which is 2 columns: Q_SEG0 (char 20) and PTR (PK, integer)

AN_OTHER which is various columns then LRN (PK, integer)

View is defined as:

SELECT * FROM TBL_6_QNX, AN_OTHER where PTR=LRN WITH READ ONLY

Andy Received on Wed Apr 03 2002 - 06:43:40 CST