| Please guide to improve the performance of query [message #594241] |
Mon, 26 August 2013 09:20  |
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 #594264 is a reply to message #594254] |
Tue, 27 August 2013 01:20  |
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 #594267 is a reply to message #594266] |
Tue, 27 August 2013 01:45 |
John Watson
Messages: 8963
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 #594279 is a reply to message #594268] |
Tue, 27 August 2013 03:56 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
ajaykumarkona wrote on Tue, 27 August 2013 07:59Hi,
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
