Home » RDBMS Server » Performance Tuning » Please guide to improve the performance of query (Oracle 11g)
Please guide to improve the performance of query [message #594241] Mon, 26 August 2013 09:20 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,


The below query is taking 45 seconds to return 170 records.

select *
from RANGE where WSNO = 3
order by PREFERENCE desc;

The total number of records in the table is 1060.

Even it's a very small table why it's taking 45 seconds.
Can anybody please help me on how to get the output in 2 to 3 seconds.
I want all the columns from the table.

I am posting the execution plan and DDL for table and index.

PLAN_TABLE_OUTPUT

Plan hash value: 3593186720
 
-------------------------------------------------------------------------------------------------------
| SNO  | Operation                    | EMPNAME                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |    31 | 23281 |    21   (5)| 00:00:01 |
|   1 |  SORT ORDER BY               |                        |    31 | 23281 |    21   (5)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID | RANGE                 |    31 | 23281 |    20   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | INDX_WSNO              |    31 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (SNOentified by operation SNO):
---------------------------------------------------
 
   3 - access("WSNO"=3)

CREATE TABLE RANGE
(
  SNO                              NUMBER,
  BUSNO                            NUMBER,
  EMPNAME                          NVARCHAR2(64),
  PREFERENCE                       NUMBER,
  TSNO                             NUMBER,
  MEMBER                          CHAR(1 CHAR) ,
  EQU                             CHAR(1 CHAR) ,
  REMAIL                          CHAR(1 CHAR) ,
  SSR                             CHAR(1 CHAR) ,
  SUB                             CHAR(1 CHAR) ,
  SPN                             CHAR(1 CHAR) ,
  SEMPNAME                        NVARCHAR2(128),
  FVL                             NUMBER(32),
  TVL                             NUMBER(32),
  ISD                             CHAR(1 CHAR),
  CHANGED                         NVARCHAR2(64),
  CDATE                           TIMESTAMP(6) ,
  UDBY                            NVARCHAR2(64),
  UDATE                           TIMESTAMP(6),
  LSTU                            CLOB,
  WSNO                            NUMBER,
  ASTN                            CHAR(1 CHAR),
  ASTNPL                          CHAR(1 CHAR),
  AVAF                            CHAR(1 CHAR),
  REST                            SYS.XMLTYPE
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   11
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
XMLTYPE REST STORE AS CLOB 
      ( TABLESPACE  USERS 
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  10
        NOCACHE
        INDEX       (
          TABLESPACE USERS
          STORAGE    (
                      INITIAL          64K
                      NEXT             1
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                     ))
        STORAGE    (
                    INITIAL          64K
                    NEXT             1M
                    MINEXTENTS       1
                    MAXEXTENTS       UNLIMITED
                    PCTINCREASE      0
                    BUFFER_POOL      DEFAULT
                   )
      )
  LOB (LSTU) STORE AS 
      ( TABLESPACE  USERS 
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        RETENTION
        NOCACHE
        INDEX       (
          TABLESPACE USERS
          STORAGE    (
                      INITIAL          64K
                      NEXT             1
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                     ))
        STORAGE    (
                    INITIAL          64K
                    NEXT             1M
                    MINEXTENTS       1
                    MAXEXTENTS       UNLIMITED
                    PCTINCREASE      0
                    BUFFER_POOL      DEFAULT
                   )
      )
NOCACHE
NOPARALLEL
MONITORING;

CREATE INDEX INDX_WSNO ON RANGE(WSNO);

CREATE UNIQUE INDEX RULE_EMPNAME ON RANGE(BUSNO, EMPNAME);


Please help me how to improve the performance of this query with all columns of the table.

Thanks.


Re: Please guide to improve the performance of query [message #594243 is a reply to message #594241] Mon, 26 August 2013 09:34 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
>The below query is taking 45 seconds to return 170 records.

explain the complete discrepancy between the values above & the fact the EXPLAIN PLAN shows 31 rows returned in 00:01 second.
Re: Please guide to improve the performance of query [message #594254 is a reply to message #594241] Mon, 26 August 2013 15:36 Go to previous messageGo to next message
John Watson
Messages: 4689
Registered: January 2010
Location: Global Village
Senior Member
What performance do you get if you do not project either the REST or LSTU columns?
Re: Please guide to improve the performance of query [message #594264 is a reply to message #594254] Tue, 27 August 2013 01:20 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi ,

I have verified again I am getting 170 records for the below query.

select count(*)
from RANGE where WSNO = 3
order by PREFERENCE desc;--170


I dont know why execution plan is showing like this.

Without REST or LSTU columns I am getting output in 1 second.
But I need all the columns from the table.

Please help me.

Thanks.


Re: Please guide to improve the performance of query [message #594265 is a reply to message #594264] Tue, 27 August 2013 01:28 Go to previous messageGo to next message
John Watson
Messages: 4689
Registered: January 2010
Location: Global Village
Senior Member
You have determined that it is either the column REST or the column LSTU that slows tings down. Would it be useful to know which?
Re: Please guide to improve the performance of query [message #594266 is a reply to message #594265] Tue, 27 August 2013 01:33 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

The problem only with REST column.
If I comment only this column I am getting output in 1 to 2 seconds.

Please help me how to improve the performance of this query.

Thanks.
Re: Please guide to improve the performance of query [message #594267 is a reply to message #594266] Tue, 27 August 2013 01:45 Go to previous messageGo to next message
John Watson
Messages: 4689
Registered: January 2010
Location: Global Village
Senior Member
You need to work on your problem solving skills.
I have helped you to identify the problem area, now you need to come up with solutions. The obvious approach is to investigate the structure and content of your XML data. Better start reading the XML DB Developer's Guide.
Re: Please guide to improve the performance of query [message #594268 is a reply to message #594267] Tue, 27 August 2013 01:59 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

Can you please help me what is the resolution for this.
I never worked on XMLTYPE.

Please help me.

Thanks.
Re: Please guide to improve the performance of query [message #594279 is a reply to message #594268] Tue, 27 August 2013 03:56 Go to previous message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
ajaykumarkona wrote on Tue, 27 August 2013 07:59
Hi,

Can you please help me what is the resolution for this.
I never worked on XMLTYPE.

Please help me.

Thanks.

Then why are you being asked to tune a query that involves it? Wait, are you hoping that by posting on here, other people will do your work for you, then you can let your bosses think (incorrectly) that you are competent in a technology that you are clearly not competent in? Is that not somewhat disingenuous? You should really take John Watson's advice and go and read up on the subject that you are trying to work on, or let your bosses know that you are not qualified to work on it, maybe they will either give you the time to learn it, or even better, send you on a course for you to learn the subject.
Previous Topic: Moving Tables to Encrypted Tablespace.
Next Topic: NOWAIT lock requests could hang (like Parallel Queries may hang "enq: TS - contention")
Goto Forum:
  


Current Time: Fri Oct 24 08:50:53 CDT 2014

Total time taken to generate the page: 0.10512 seconds