OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » SELECT query (Oracle,11.1.0.6,RHEL 5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SELECT query [message #494125]

Fri, 11 February 2011 07:46

arunb1982
Messages: 34
Registered: December 2010
Location: Bangalore

Member

Hi,

Our application servers will be running a SELECT which returns zero rows all the time( Know this is bit stupid Sad

)

This SELECT is put into a package and this package will be called by application servers very frequently which is causing unnecessary CPU.

Original query and plan

 SQL> SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION
FROM AIMUSER.SEGMENT_JOBS
WHERE  SEGMENT_JOB_ID NOT IN
(SELECT SEGMENT_JOB_ID
FROM AIMUSER.SEGMENT_JOBS)  2    3    4    5  ;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 684873774

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |   239K|  5858K|       |  9950   (1)| 00:02:00 |
|*  1 |  HASH JOIN RIGHT ANTI |                 |   239K|  5858K|  4224K|  9950   (1)| 00:02:00 |
|   2 |   INDEX FAST FULL SCAN| SEGMENT_JOBS_PK |   239K|  1405K|       |   280   (1)| 00:00:04 |
|   3 |   TABLE ACCESS FULL   | SEGMENT_JOBS    |   239K|  4452K|       |  9108   (1)| 00:01:50 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("SEGMENT_JOB_ID"="SEGMENT_JOB_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      38724  consistent gets
          0  physical reads
          0  redo size
        554  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Option 1:

SQL> SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION
FROM AIMUSER.SEGMENT_JOBS
WHERE 1=0  2    3  ;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 458328595

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    19 |     0   (0)|          |
|*  1 |  FILTER            |              |       |       |            |          |
|   2 |   TABLE ACCESS FULL| SEGMENT_JOBS |   239K|  4452K|  9108   (1)| 00:01:50 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        554  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Option 2:

SQL> r
  1  SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION
  2  FROM AIMUSER.SEGMENT_JOBS
  3* WHERE ROWNUM<1

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3715155214

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    19 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |              |       |       |            |          |
|   2 |   TABLE ACCESS FULL| SEGMENT_JOBS |     1 |    19 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        554  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Which option will be better or do we have other options?
They need to pass the column's with zero rows to a ref cursor.

Thanks,
Arun

Report message to a moderator

Re: SELECT query [message #494128 is a reply to message #494125]

Fri, 11 February 2011 07:49

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The best one is

NULL;

Regards
Michel

Report message to a moderator

Re: SELECT query [message #494131 is a reply to message #494125]

Fri, 11 February 2011 07:53

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

1 recursive call in Q1, none in Q2. Number of bytes in full table scan is also no comparison.

We must know though, WHY is this silliness being allowed? Whoever "thinks" this is necessary really has some screws loose. Sounds like another clueless manager who needs to be reassigned. I cannot think of a reason for the silly maneuver, but please tell us the reason for it. Who knows, I could be completely wrong here and am willing to accept that.

Report message to a moderator

Re: SELECT query [message #494159 is a reply to message #494125]

Fri, 11 February 2011 10:56

arunb1982
Messages: 34
Registered: December 2010
Location: Bangalore

Member

Hi Michel,

Thanks for the response.

Is this what you think. I'm not so clear.

SQL> SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION FROM AIMUSER.SEGMENT_JOBS WHERE NULL=NULL;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 458328595

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    19 |     0   (0)|          |
|*  1 |  FILTER            |              |       |       |            |          |
|   2 |   TABLE ACCESS FULL| SEGMENT_JOBS |   239K|  4452K|  9108   (1)| 00:01:50 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        554  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Hi Joy,

Thanks.Even I'm not sure about the requirement for this.As far as I know, they need to pass the structure to the ref cursor.And the rows from the table will be deleted on regular intervals as when jobs are completed.Even I'm in search of the coder who developed this

Thanks,
Arun

Report message to a moderator