Home » RDBMS Server » Performance Tuning » Query optimisation using meaningless condition
Query optimisation using meaningless condition [message #344213] Fri, 29 August 2008 01:58 Go to next message
bmikle
Messages: 6
Registered: August 2008
Junior Member
Hello!

Yesterday I found rather strange Oracle 9i behavior. I executed the following query:

SELECT *
FROM MyTable
WHERE name like '%26%'

The execution time of this query is about 1 sec.

"MyTable" has about 500 000 rows and 15 columns. The primary key is "Id", that was generated using sequense, so it is always bigger than 0.

Then, I inserted one line into the query:

SELECT *
FROM MyTable
WHERE name like '%26%'
AND Id > -1

Once again, all of the rows has Id bigger than -1. But the execution time of the new query is about 100msecs, that is 10 times smaller.

Furthermore, I tried to write more complex query, and the difference became extremely bid: with this condition (Id > -1) the time was 1 sec, and without it -- 4 minutes!

The question is WHY? I can't understand it.

Thanks in advance,
Michael.

P.S. For those who can read them, here are execution plans from TOAD.

For first query:

SELECT STATEMENT Optimizer Mode=CHOOSE
----TABLE ACCESS FULL MyTable


For second query:

SELECT STATEMENT Optimizer Mode=CHOOSE
----TABLE ACCESS BY INDEX ROWID MyTable
--------INDEX RANGE SCAN PK_MyTable
Re: Query optimisation using meaningless condition [message #344216 is a reply to message #344213] Fri, 29 August 2008 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because you are lucky.

Provide the usual information for Performances questions.

Regards
Michel
Re: Query optimisation using meaningless condition [message #344222 is a reply to message #344216] Fri, 29 August 2008 02:20 Go to previous messageGo to next message
bmikle
Messages: 6
Registered: August 2008
Junior Member
Mmm, what kind of information should I provide?

I'm rather new with Oracle and oracle forums, so probably I don't now some rules...
Re: Query optimisation using meaningless condition [message #344268 is a reply to message #344222] Fri, 29 August 2008 04:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Explain Plans for the two queries are a minimum requirement.

Details of the indexes on the table are good too.
Re: Query optimisation using meaningless condition [message #344289 is a reply to message #344222] Fri, 29 August 2008 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck

Regards
Michel
Re: Query optimisation using meaningless condition [message #344307 is a reply to message #344268] Fri, 29 August 2008 06:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Sorry - I didn't see that you'd already supplied Plans.

Are those all the Plan details? It looks like you've got no stats on the tables?

I suspect that if you sre noticing a performance improvement it is probably down to cached data the second time round.

Try this:

1) Run the Original Query, and note execution time.
2) Run the query with the Id > -1, and note execution time
3) Re-run the Original Query, and note execution time.

If you could do this in SQL*Plus and cut/paste the whole thing into a post here, that'd be great.
Re: Query optimisation using meaningless condition [message #344316 is a reply to message #344213] Fri, 29 August 2008 06:38 Go to previous messageGo to next message
alammas
Messages: 46
Registered: July 2008
Member
Search may Table scan,Clustered,Non-clustered ,full text or hardware address.
Is that new way?
Re: Query optimisation using meaningless condition [message #344324 is a reply to message #344307] Fri, 29 August 2008 07:04 Go to previous messageGo to next message
bmikle
Messages: 6
Registered: August 2008
Junior Member
Hello, JRowbottom!

I wrote the following script and executed it in SQL*Plus:

SELECT 'Starting...', TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF') FROM DUAL;

SELECT count(*) FROM MyTable;

SELECT 'first query executed', TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF') FROM DUAL;

SELECT count(*) FROM MyTable WHERE Id > -1;

SELECT 'second query executed', TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF') FROM DUAL;

SELECT count(*) FROM MyTable;

SELECT 'first query executed', TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF') FROM DUAL;

SELECT count(*) FROM MyTable WHERE Id > -1;

SELECT 'second query executed', TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF') FROM DUAL;


The output is:

SQL> @test.sql

'STARTING.. TO_CHAR(SYSTIMESTA
----------- ------------------
Starting... 16:03:34.793000


  COUNT(*)
----------
     60301


'FIRSTQUERYEXECUTED' TO_CHAR(SYSTIMESTA
-------------------- ------------------
first query executed 16:03:35.950000


  COUNT(*)
----------
     60301


'SECONDQUERYEXECUTED' TO_CHAR(SYSTIMESTA
--------------------- ------------------
second query executed 16:03:36.153000


  COUNT(*)
----------
     60301


'FIRSTQUERYEXECUTED' TO_CHAR(SYSTIMESTA
-------------------- ------------------
first query executed 16:03:37.387000


  COUNT(*)
----------
     60301


'SECONDQUERYEXECUTED' TO_CHAR(SYSTIMESTA
--------------------- ------------------
second query executed 16:03:37.403000


You can see, that the first query execution time is bigger all the time.

I can't get more about this plans, I don't know why...
Re: Query optimisation using meaningless condition [message #344326 is a reply to message #344324] Fri, 29 August 2008 07:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can get the timings with
SET TIMING ON
Re: Query optimisation using meaningless condition [message #344333 is a reply to message #344326] Fri, 29 August 2008 07:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
an you run this, so we can get some easy to read, repeatable timings:
SET SERVEROUTPUT ON SIZE 10000

DECLARE
  v_time  pls_integer;
  v_iter  pls_integer := 100;
  v_val   pls_integer;
BEGIN
  v_time := dbms_utility.get_time;
  
  FOR i in 1..iter LOOP
    SELECT count(*) into v_val FROM mytable;
  END LOOP;

  dbms_output.put_line('Timing 1: '||to_char(dbms_utility.get_time - v_time));

  v_time := dbms_utility.get_time;
  
  FOR i in 1..iter LOOP
    SELECT count(*) into v_val FROM mytable WHERE id > -1;
  END LOOP;

  dbms_output.put_line('Timing 2: '||to_char(dbms_utility.get_time - v_time));
END;


If you run this, it will produce explain plans in an easy to read format:
explain plan for SELECT count(*) FROM mytable;

select * from table(dbms_xplan.display());

explain plan for SELECT count(*) FROM mytable where id > 1;

select * from table(dbms_xplan.display());select * from user_ind


If you could run this, then it will give us details of the indexes on teh table:
select i.index_name,i.uniqueness, c.column_name,c.column_position
from   user_indexes i,user_ind_columns c
where  i.index_name = c.index_name
and    i.table_name = 'MYTABLE'
order by 1,4;
Re: Query optimisation using meaningless condition [message #344776 is a reply to message #344333] Mon, 01 September 2008 04:19 Go to previous messageGo to next message
bmikle
Messages: 6
Registered: August 2008
Junior Member
Thank you, JRowbottom!

Here are the results of the scripts (table "Nomenklatura" is the real name of "MyTable"):

Timing 1: 12555
Timing 2: 174


Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS FULL | NOMENKLATURA | | | |
----------------------------------------------------------------------

Note: rule based optimization

10 rows selected.


Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS FULL | NOMENKLATURA | | | |
|* 2 | INDEX RANGE SCAN | PK_NOMENKLATURA | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS FULL | NOMENKLATURA | | | |
|* 2 | INDEX RANGE SCAN | PK_NOMENKLATURA | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS FULL | NOMENKLATURA | | | |
|* 2 | INDEX RANGE SCAN | PK_NOMENKLATURA | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS FULL | NOMENKLATURA | | | |
|* 2 | INDEX RANGE SCAN | PK_NOMENKLATURA | | | |
-------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

2 - access("NOMENKLATURA"."ID">1)
2 - access("NOMENKLATURA"."ID">1)
2 - access("NOMENKLATURA"."ID">1)
2 - access("NOMENKLATURA"."ID">1)

Note: rule based optimization

29 rows selected.





INDEX_NAME UNIQUENES
------------------------------ ---------
COLUMN_NAME
------------------------------------------
COLUMN_POSITION
---------------
PK_NOMENKLATURA UNIQUE
ID
1

U_NOMENKLATURA#OMEGA UNIQUE
OMEGA
1

INDEX_NAME UNIQUENES
------------------------------ ---------
COLUMN_NAME
------------------------------------------
COLUMN_POSITION
---------------



INDEX_NAME UNIQUENES
------------------------------ ---------
COLUMN_NAME
------------------------------------------
COLUMN_POSITION
---------------
PK_NOMENKLATURA UNIQUE
ID
1

U_NOMENKLATURA#OMEGA UNIQUE
OMEGA
1

INDEX_NAME UNIQUENES
------------------------------ ---------
COLUMN_NAME
------------------------------------------
COLUMN_POSITION
---------------

Re: Query optimisation using meaningless condition [message #344807 is a reply to message #344776] Mon, 01 September 2008 05:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm stumped - I can think of no good reason why doing the index range scan followed by a table_access would be quicker than a full table scan. something odd is going on here.

Can you just run
SELECT sign(id),count(*) FROM <yourtable> GROU BY sign(id);
Re: Query optimisation using meaningless condition [message #344841 is a reply to message #344776] Mon, 01 September 2008 06:37 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

Note: rule based optimization


Any reason for choosing to use RBO ?

Regards

Raj
Re: Query optimisation using meaningless condition [message #344861 is a reply to message #344841] Mon, 01 September 2008 08:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Its a bit hard to tell because your latest tests did not clear PLAN_TABLE between tests.

The following plan table output:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS FULL | NOMENKLATURA | | | |
|* 2 | INDEX RANGE SCAN | PK_NOMENKLATURA | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS FULL | NOMENKLATURA | | | |
|* 2 | INDEX RANGE SCAN | PK_NOMENKLATURA | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS FULL | NOMENKLATURA | | | |
|* 2 | INDEX RANGE SCAN | PK_NOMENKLATURA | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS FULL | NOMENKLATURA | | | |
|* 2 | INDEX RANGE SCAN | PK_NOMENKLATURA | | | |
-------------------------------------------------------------------------

is actually the confusion of two separate plans:
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
|* 2 | INDEX RANGE SCAN | PK_NOMENKLATURA | | | |

and

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS FULL | NOMENKLATURA | | | |


So what this tells me is that faster one (index scan) is JUST scanning the index - not the table. The slow one is full scanning the table.

This is to be expected. Even though a range scan reads only one block at a time (and FTS reads many), a single index block contains far more rows than a table block and is also more likely to be cached and will not suffer from chaining.

Now if the index range scan ALSO read the table (add some SELECT columns not in the index) then it would be different story - the index scan would then be much slower.

I see in your earlier examples that you were selecting some other columns. Without a SQL Trace its impossible to tell what's going on with those.If you are still interested, trace the session, run the output through TK*Prof and post it here. Don't forget to format it with CODE tags though.

Ross Leishman
Re: Query optimisation using meaningless condition [message #344868 is a reply to message #344807] Mon, 01 September 2008 08:12 Go to previous messageGo to next message
bmikle
Messages: 6
Registered: August 2008
Junior Member
Here is the output...


SIGN(ID) COUNT(*)
---------- ----------
1 60325
Re: Query optimisation using meaningless condition [message #344869 is a reply to message #344861] Mon, 01 September 2008 08:16 Go to previous messageGo to next message
bmikle
Messages: 6
Registered: August 2008
Junior Member
Hello, rleishman!

Actually, the huge difference in speed can be seen on query:

SELECT count(*) FROM Nomenklatura

if I add the line "WHERE Id > -1".

I will be glad to provide you the trace of the session, but I don't know exactly what is it. Can you give me a short guide what to do?

Thank you for your help, anyway!
Re: Query optimisation using meaningless condition [message #344911 is a reply to message #344869] Mon, 01 September 2008 16:24 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That query does not contain any columns other than the indexed column. The indexed query runs faster because it does not have to refer to the table.

If you included some non-indexed columns, Oracle would have to read the table row from the index, making it much slower.

Instructions for tracing and tkprof can be found in the Performance Tuning Manual

Ross Leishman
Previous Topic: unstable sort is inapprehensible (merged)
Next Topic: Table Partitioning
Goto Forum:
  


Current Time: Wed Dec 07 14:14:02 CST 2016

Total time taken to generate the page: 0.16998 seconds