Home » RDBMS Server » Performance Tuning » Sql Query comsuming time (oracle 11g, Window 2003 Server)
Sql Query comsuming time [message #516164] Fri, 15 July 2011 05:04 Go to next message
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
Re: Sql Query comsuming time [message #516168 is a reply to message #516164] Fri, 15 July 2011 05:46 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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
Re: Sql Query comsuming time [message #516177 is a reply to message #516172] Fri, 15 July 2011 06:12 Go to previous messageGo to next message
John Watson
Messages: 8922
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?
Re: Sql Query comsuming time [message #516181 is a reply to message #516177] Fri, 15 July 2011 06:30 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
I think, it is something wrong with the concept. What is the sense of sending so often 58765 rows to the frontend?
Re: Sql Query comsuming time [message #516834 is a reply to message #516181] Thu, 21 July 2011 02:42 Go to previous message
MicroJoey
Messages: 6
Registered: November 2010
Junior Member
You get more rows,so i think the best method is that create a compound index on all three columns.

Previous Topic: Performance Tuning SQL Queries
Next Topic: delete statement taking forever
Goto Forum:
  


Current Time: Thu Mar 28 07:15:45 CDT 2024