Home » RDBMS Server » Performance Tuning » SQL - Query Performance (Oracle Database 11g Release 11.1.0.6.0, Windows Server 2003)
SQL - Query Performance [message #484243] |
Mon, 29 November 2010 03:04 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
MSAM123
Messages: 29 Registered: June 2007
|
Junior Member |
|
|
Hi All,
The below query takes more time to retrieve the data.
SELECT COL1, COL2, COL3 FROM ADDRESSINFO
WHERE
(COL4 like '%1%' OR COL5 like '%1%')
AND (COL6 like '%STEADING%' OR COL7 like '%STEADING%' OR COL8 like '%STEADING%')
AND (COL9 LIKE '%DURHAM%' OR COL10 LIKE '%DURHAM%');
In the test environment :
--------------------------
Total number of records in ADDRESSINFO table = 2500000
Time taken to retrieve the data = 23 Sec
In Production environment:
---------------------------
Total number of records in ADDRESSINFO table = 30000000
Time taken to retrieve the data = 15 mins
The columns used in the where clause of the query are indexed.
Can you please suggest me how I can improve the performance of this query.
Thanks and Regards,
MSAM
|
|
|
Re: SQL - Query Performance [message #484248 is a reply to message #484243] |
Mon, 29 November 2010 03:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Dipali Vithalani
Messages: 278 Registered: March 2007 Location: India
|
Senior Member |
|
|
Dear,
You wrote that the columns in where clause are indexed.. but the query you posted wont be able to use those indexes because the values with which the column should be compared are starting with "%".
Do search and read the about the scenarios which suppresses the use of index. It can be useful to you..
Regards,
Dipali.
|
|
|
Re: SQL - Query Performance [message #484290 is a reply to message #484248] |
Mon, 29 November 2010 06:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
MSAM123
Messages: 29 Registered: June 2007
|
Junior Member |
|
|
Hi Dipali,
Thank you for the response.
As suggested by you I just went through the related topics which suppress the use of indexes. I got some information related to this and this is what I tried.
I dropped the existing indexes and recreated as
CREATE INDEX IDX_COL4 ON ADDRESSINFO
(CASE WHEN "COL4" LIKE '%1%' THEN 1 END );
CREATE INDEX IDX_COL6 ON ADDRESSINFO
(CASE WHEN "COL6" LIKE '%STEADING%' THEN 1 END );
Likewise I created the indexes on all the columns which are being used in the where clause.
The select query used to retrieve the data is
SELECT COL1, COL2, COL3 FROM ADDRESSINFO
WHERE
case when COL6 like '%STEADING%' then 1 end is not null
Now this query retrieves the data in milli seconds, which previously used to take 10-15 secs. Till here everything is fine, now when I add more conditions in the where clause the query takes same amount of time as earlier.
SELECT COL1, COL2, COL3 FROM ADDRESSINFO
WHERE
(
case when COL6 like '%STEADING%' then 1 end is not null
OR
case when COL7 like '%STEADING%' then 1 end is not null
)
The above query takes 22 seconds to retrieve the data.
What is wrong with this approach?
Thanks,
Msam
[Updated on: Mon, 29 November 2010 06:57] Report message to a moderator
|
|
|
|
|
|
|
Re: SQL - Query Performance [message #484370 is a reply to message #484359] |
Tue, 30 November 2010 01:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Try:
SELECT COL1, COL2, COL3 FROM ADDRESSINFO
WHERE
(
(case when COL6 like '%STEADING%' then 1 end) = 1
OR
(case when COL7 like '%STEADING%' then 1 end) = 1
)
|
|
|
Re: SQL - Query Performance [message #484375 is a reply to message #484248] |
Tue, 30 November 2010 01:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Yasir Hashmi
Messages: 304 Registered: April 2006
|
Senior Member |
|
|
Deepali,
Quote:You wrote that the columns in where clause are indexed.. but the query you posted wont be able to use those indexes because the values with which the column should be compared are starting with "%".
This is not true.
The optimizer would use eithere a full table scan or full index scan.
It is not that the index would not be used.The index range scan would not be possible.
[Updated on: Tue, 30 November 2010 02:02] by Moderator Report message to a moderator
|
|
|
Re: SQL - Query Performance [message #484379 is a reply to message #484243] |
Tue, 30 November 2010 02:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
Try UNION instead of OR:
SELECT COL1, COL2, COL3 FROM ADDRESSINFO
WHERE case when COL6 like '%STEADING%' then 1 end is not null
UNION
SELECT COL1, COL2, COL3 FROM ADDRESSINFO
WHERE case when COL7 like '%STEADING%' then 1 end is not null
HTH
|
|
|
Re: SQL - Query Performance [message #484381 is a reply to message #484379] |
Tue, 30 November 2010 02:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
I think you will find that those 2.5M rows in Dev have been cached, whereas the 30M rows in Prod do not fit into the cache and must be read from disk.
If you clear your cache on Dev and run it again, you will find it running 10 to 15 times faster than Prod (based on the relative table size). As previously mentioned, it won't range scan the index, so don't expect your Prod performance to improve.
Remember that you will need to clear the BUFFER cache, the OS cache (if it exists) and the DISK cache. Easy way to do all of these is to turn off the computer. If you have external network disk, you will need to turn it off as well otherwise you won't clear the disk cache.
All of this will hopefully demonstrate that the Dev performance you are experiencing is unrealistic. If you want it to go faster, try using the Parallel Query Server.
Ross Leishman
|
|
|
Re: SQL - Query Performance [message #484383 is a reply to message #484381] |
Tue, 30 November 2010 03:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
MSAM123
Messages: 29 Registered: June 2007
|
Junior Member |
|
|
Thank you all for your valuable feedback.
I tried the below on the test environment which has 2.5M rows.
SQL> SELECT COL1, COL2, COL3 FROM ADDRESSINFO
2 WHERE
3 (
4 (CASE WHEN COL6 LIKE '%STEADING%' THEN 1 END IS NOT NULL)
5 OR
6 (CASE WHEN COL7 LIKE '%STEADING%' THEN 1 END IS NOT NULL)
7* );
COL1 COL2 COL3
------------------------------------------------------
DH8 6GA BK8 61A LM612
DI8 6LA BB8 62A LM613
DP8 6PA BL8 63A LM614
3 rows selected.
Elapsed: 00:00:24.48
With the use of UNION operator the query takes less than a second.
SQL> SELECT COL1, COL2, COL3 FROM ADDRESSINFO
2 WHERE
3 CASE WHEN COL6 LIKE '%STEADING%' THEN 1 END IS NOT NULL
4 UNION
5 SELECT COL1, COL2, COL3 FROM ADDRESSINFO
6 WHERE
7 CASE WHEN COL7 LIKE '%STEADING%' THEN 1 END IS NOT NULL ;
COL1 COL2 COL3
------------------------------------------------------
DH8 6GA BK8 61A LM612
DI8 6LA BB8 62A LM613
DP8 6PA BL8 63A LM614
3 rows selected.
Elapsed: 00:00:00.85
Can you please tell me what is causing the time difference in the above queries?
Thanks,
Msam
|
|
|
|
|
Re: SQL - Query Performance [message #484395 is a reply to message #484392] |
Tue, 30 November 2010 05:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
MSAM123
Messages: 29 Registered: June 2007
|
Junior Member |
|
|
Explain Plan for the first query (Uses OR operator)
Rows Plan
------ ------------------
253040
SELECT STATEMENT
253040
TABLE ACCESS FULL ADDRESSINFO
Explain Plan for the second query (Uses UNION operator)
Rows Plan
------ ------------------
259528
SELECT STATEMENT
259528
SORT UNIQUE
UNION-ALL
129764
TABLE ACCESS BY INDEX ROWID ADDRESSINFO
108
INDEX FULL SCAN IDX_COL6
129764
TABLE ACCESS BY INDEX ROWID ADDRESSINFO
182
INDEX FULL SCAN IDX_COL7
[Updated on: Tue, 30 November 2010 05:07] Report message to a moderator
|
|
|
Re: SQL - Query Performance [message #484397 is a reply to message #484395] |
Tue, 30 November 2010 05:21 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
When posting explain plans can you please use the following method to obtain them:
SQL> explain plan for select * from dual;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
SQL>
They're a lot more readable that way.
Also can you list out the indexes currently on the table so we can see which are and aren't beng used.
|
|
|
Re: SQL - Query Performance [message #484402 is a reply to message #484397] |
Tue, 30 November 2010 06:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
MSAM123
Messages: 29 Registered: June 2007
|
Junior Member |
|
|
Explain Plan for the first query (Uses OR operator)
PLAN_TABLE_OUTPUT
Plan hash value: 2047210256
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 253K| 3706K| 49798 (1)| 00:09:58 |
|* 1 | TABLE ACCESS FULL| ADDRESSINFO | 253K| 3706K| 49798 (1)| 00:09:58 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(CASE WHEN "COL6" LIKE '%STEADING%' THEN 1 END IS NOT NULL OR
CASE WHEN "COL7" LIKE '%STEADING%' THEN 1 END IS NOT NULL)
Explain Plan for the second query (Uses UNION operator)
PLAN_TABLE_OUTPUT
Plan hash value: 2438991125
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 259K| 3041K| | 1357 (52)| 00:00:17 |
| 1 | SORT UNIQUE | | 259K| 3041K| 9M| 1357 (52)| 00:00:17 |
| 2 | UNION-ALL | | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| ADDRESSINFO | 129K| 1520K| | 69 (0)| 00:00:01 |
|* 4 | INDEX FULL SCAN | IDX_COL6 | 108 | | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| ADDRESSINFO | 129K| 1520K| | 96 (0)| 00:00:02 |
|* 6 | INDEX FULL SCAN | IDX_COL7 | 182 | | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(CASE WHEN "COL6" LIKE '%STEADING%' THEN 1 END IS NOT NULL)
6 - filter(CASE WHEN "COL7" LIKE '%STEADING%' THEN 1 END IS NOT NULL)
Currently there are two more indexes on this table on COL4 and COL5.
CREATE INDEX IDX_COL4 ON ADDRESSINFO
(CASE WHEN "COL4" LIKE '%1%' THEN 1 END )
CREATE INDEX IDX_COL5 ON ADDRESSINFO
(CASE WHEN "COL5" LIKE '%1%' THEN 1 END )
Thanks,
Msam
|
|
|
|
|
Re: SQL - Query Performance [message #484409 is a reply to message #484406] |
Tue, 30 November 2010 06:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
Hi.
1. OR prevents optimizer from using an indexes.
2. UNION - enables it ( because it's actually 2 different statements each using 1 index).
However, it will work ONLY while your application is looking for 'STEADING' string.
You'll need to create a new index for each searched string.
HTH
[Updated on: Tue, 30 November 2010 06:59] Report message to a moderator
|
|
|
|
Re: SQL - Query Performance [message #484413 is a reply to message #484409] |
Tue, 30 November 2010 07:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
michael_bialik wrote on Tue, 30 November 2010 12:591. OR prevents optimizer from using an indexes.
Actually Blackswan already proved that's not always true back up the thread, however it may will be true in this specific case.
@MSAM123 - I suggest you look into oracle text - it's specifically designed to handle these sorts of searches. If you search this site you should find some examples, probably by Barbara Boehmer.
|
|
|
Re: SQL - Query Performance [message #484422 is a reply to message #484413] |
Tue, 30 November 2010 08:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The following demonstration uses Oracle Text with a multi_column_datastore and field sections.
SCOTT@orcl_11gR2> CREATE TABLE addressinfo
2 (col1 NUMBER,
3 col2 NUMBER,
4 col3 NUMBER,
5 col4 VARCHAR2 (10),
6 col5 VARCHAR2 (18),
7 col6 VARCHAR2 (12),
8 col7 VARCHAR2 (12),
9 col8 VARCHAR2 (12),
10 col9 VARCHAR2 (10),
11 col10 VARCHAR2 (10))
12 /
Table created.
SCOTT@orcl_11gR2> INSERT INTO addressinfo VALUES
2 (1, 2, 3,
3 '1', '1',
4 'A STEADING B', 'C STEADING D', 'E STEADING F',
5 'G DURHAM H', 'I DURHAM J')
6 /
1 row created.
SCOTT@orcl_11gR2> INSERT INTO addressinfo VALUES
2 (4, 5, 6,
3 '1', '2',
4 'A STEADING B', 'SOMEBODY', 'NOBODY',
5 'G DURHAM H', 'WHOMEVER')
6 /
1 row created.
SCOTT@orcl_11gR2> INSERT INTO addressinfo VALUES
2 (7, 8, 9,
3 '2', '1',
4 'SOMEBODY', 'B STEADING A', 'NOBODY',
5 'WHOMEVER', 'H DURHAM B')
6 /
1 row created.
SCOTT@orcl_11gR2> INSERT INTO addressinfo VALUES
2 (10, 11, 12,
3 '2', '2',
4 'SOMEBODY', 'ANYBODY', 'NOBODY',
5 'WHOMEVER', 'WHATEVER')
6 /
1 row created.
SCOTT@orcl_11gR2> INSERT INTO addressinfo
2 SELECT object_id, null, null,
3 SUBSTR (object_name, 1, 10), object_type, status,
4 SUBSTR (owner, 1, 10), null, null, null
5 FROM all_objects
6 /
72702 rows created.
SCOTT@orcl_11gR2> BEGIN
2 CTX_DDL.CREATE_PREFERENCE
3 ('address_datastore',
4 'MULTI_COLUMN_DATASTORE');
5 CTX_DDL.SET_ATTRIBUTE
6 ('address_datastore',
7 'COLUMNS',
8 'col4, col5, col6, col7, col8, col9, col10');
9 CTX_DDL.CREATE_SECTION_GROUP
10 ('address_sg',
11 'basic_section_group');
12 CTX_DDL.ADD_FIELD_SECTION ('address_sg', 'col4', 'col4', TRUE);
13 CTX_DDL.ADD_FIELD_SECTION ('address_sg', 'col5', 'col5', TRUE);
14 CTX_DDL.ADD_FIELD_SECTION ('address_sg', 'col6', 'col6', TRUE);
15 CTX_DDL.ADD_FIELD_SECTION ('address_sg', 'col7', 'col7', TRUE);
16 CTX_DDL.ADD_FIELD_SECTION ('address_sg', 'col8', 'col8', TRUE);
17 CTX_DDL.ADD_FIELD_SECTION ('address_sg', 'col9', 'col9', TRUE);
18 CTX_DDL.ADD_FIELD_SECTION ('address_sg', 'col10', 'col10', TRUE);
19 END;
20 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> ALTER TABLE addressinfo ADD (search_cols VARCHAR2 (1))
2 /
Table altered.
SCOTT@orcl_11gR2> CREATE INDEX address_idx
2 ON addressinfo (search_cols)
3 INDEXTYPE IS CTXSYS.CONTEXT
4 PARAMETERS
5 ('datastore address_datastore
6 section group address_sg')
7 /
Index created.
SCOTT@orcl_11gR2> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11gR2> SELECT col1, col2, col3
2 FROM addressinfo
3 WHERE CONTAINS
4 (search_cols,
5 '(1 WITHIN col4 OR 1 WITHIN col5) AND
6 (steading WITHIN col6 OR steading WITHIN col7 OR steading WITHIN col8) AND
7 (durham WITHIN col9 OR durham WITHIN col10)') > 0
8 /
COL1 COL2 COL3
---------- ---------- ----------
1 2 3
4 5 6
7 8 9
3 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2524500015
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 1696 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ADDRESSINFO | 32 | 1696 | 4 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | ADDRESS_IDX | | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CTXSYS"."CONTAINS"("SEARCH_COLS",'(1 WITHIN col4 OR 1 WITHIN col5)
AND (steading WITHIN col6 OR steading WITHIN col7 OR steading WITHIN
col8) AND (durham WITHIN col9 OR durham WITHIN col10)')>0)
Note
-----
- dynamic sampling used for this statement (level=2)
SCOTT@orcl_11gR2>
|
|
|
Re: SQL - Query Performance [message #484494 is a reply to message #484422] |
Wed, 01 December 2010 01:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
MSAM123
Messages: 29 Registered: June 2007
|
Junior Member |
|
|
Hi,
I followed all the steps as listed above. I tried this in SCOTT schema and the result is as expected.
I tried to do the same in my test schema on ADDRESSINFO table but the query retrieves 0 records.
Test_DB> SELECT col1,col2,col3,col4,col5,col6,col7,col8,col9,col10
2 FROM addressinfo
3 WHERE CONTAINS
4 (search_cols,
5 '(1 WITHIN col4 OR 1 WITHIN col5) AND
6 (steading WITHIN col6 OR steading WITHIN col7 OR steading WITHIN col8) AND
7 (durham WITHIN col9 OR durham WITHIN col10)') > 0
8 /
no rows selected
Elapsed: 00:00:00.01
When I execute the below query I get 4 records.
Test_DB>SELECT col1,col2,col3,col4,col5,col6,col7,col8,col9,col10
2 FROM addressinfo
3 WHERE
4 (col4 like '%1%' OR col5 like '%1%')
5 AND (col6 like '%STEADING%' OR col7 like '%STEADING%' OR col8 like '%STEADING%')
6 AND (col9 LIKE '%DURHAM%' OR col10 LIKE '%DURHAM%');
COL1 COL2 COL3 COL4 COL5 COL6 COL7 COl8 COl9 COl10
---- ---- ---- ---- ---- ---- ---- ---- ---- ----
Pst olptsgb20 SP1 0IK 1 UNNAMED THE STEADINGS DURHAM
Pst ossv7676545720 KY2 8BG 1 UNNAMED THE STEADINGS DURHAM
Pst gpgb75674567465 JH4 6TR 1 UNNAMED THE STEADINGS DURHAM
Pst lmg4545293168920 LO9 3PI 1 UNNAMED THE STEADINGS DURHAM
4 rows selected.
Elapsed: 00:00:23.23
Could you please let me know what is wrong with the first query.
Thanks,
Msam
|
|
|
|
|
Goto Forum:
Current Time: Thu Sep 12 10:25:18 CDT 2024
|