OraFAQ Forum: Performance Tuning » Sql Query comsuming time

Home » RDBMS Server » Performance Tuning » Sql Query comsuming time (oracle 11g, Window 2003 Server)

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

Sql Query comsuming time [message #516164]

Fri, 15 July 2011 05:04

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,

I am using Oracle 11g and window 2003 Server. Below is the Query which is taking so much time for output.

select contentid ,score from tbl_cachedcontent where recordstate=1

Table Structure is

SQL> desc tbl_cachedcontent;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CACHEDCONTENTID                                    NUMBER(38)
 CAMPAIGNID                                         NUMBER(38)
 PLANID                                             NUMBER(38)
 CONTENTID                                          NUMBER(38)
 BUSINESSCATEGORYID                                 NUMBER(38)
 MASTERBUSINESSCATEGORYID                           NUMBER(38)
 FILEID                                             NUMBER(38)
 FILENAME                                           VARCHAR2(256)
 FILEPATH                                           VARCHAR2(2000)
 SCORE                                              NUMBER(38,4)
 RATE                                               NUMBER(15)
 MOODID                                             NUMBER(38)
 RECORDSTATE                                        NUMBER(5)
 AGINGDATETIME                                      TIMESTAMP(6)
 SEQ                                                NUMBER(38)

SQL> select count(*) from tbl_cachedcontent;

  COUNT(*)
----------
    108766

SQL> select recordstate ,count(*) from tbl_cachedcontent group by recordstate;

RECORDSTATE   COUNT(*)
----------- ----------
          1      58765
          0      50001

SQL> select distinct(recordstate) from tbl_cachedcontent;

RECORDSTATE
-----------
          1
          0

Question:: Why this query is taking so much time. This Query is running so freequently using Frontend(vB.net) and comsuming so much time to show result.
When I checked the execution plan of this query it is going for FTS. Even tried after creating a index on recordstate column.
Execution plan as below.

SQL> select contentid ,score from tbl_cachedcontent where recordstate=1
  2  ;

58765 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3054096949

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

-------

| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Tim

e     |

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

-------

|   0 | SELECT STATEMENT  |                   | 54383 |   477K|   349   (1)| 00:

00:05 |

|*  1 |  TABLE ACCESS FULL| TBL_CACHEDCONTENT | 54383 |   477K|   349   (1)| 00:

00:05 |

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

-------


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

   1 - filter("RECORDSTATE"=1)


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

Please help me, in this query performance issue

Regards

Pradeep

Report message to a moderator

Re: Sql Query comsuming time [message #516168 is a reply to message #516164]

Fri, 15 July 2011 05:46

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Your index won't be used because it isn't selective enough. You could try replacing the index with a compound index on all three columns, the optimizer might use that because then it wouldn't have to read the table at all.

[update: by the way, you should consider adding a NOT NULL constraint to RECORDSTATE]

[Updated on: Fri, 15 July 2011 05:49]

Report message to a moderator

Re: Sql Query comsuming time [message #516172 is a reply to message #516168]

Fri, 15 July 2011 06:01

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi John,

I created the index on three columns script below

 create index idx_rec on tbl_cachedcontent(recordstate,contentid,score);

After this I checked the execution plan and query excution. Excution Plan is

SQL> l
  1* select contentid ,score from tbl_cachedcontent where recordstate=1
SQL>    /

58765 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1874759963

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

| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |         | 54383 |   477K|    93   (2)| 00:00:02 |

|*  1 |  INDEX FAST FULL SCAN| IDX_REC | 54383 |   477K|    93   (2)| 00:00:02 |

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


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

   1 - filter("RECORDSTATE"=1)


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

SQL>

Query output is giving same response time as previously.

Regards
Pradeep

Report message to a moderator

Re: Sql Query comsuming time [message #516177 is a reply to message #516172]

Fri, 15 July 2011 06:12

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Quote:

Query output is giving same response time as previously.

Well, only if you believe that 2 seconds is the same as 5 seconds. Of course, just the one test is meaningless, but you say that this query is executed frequently - why not try the index on your production system, and see what happens over a few million executions?

Report message to a moderator