OraFAQ Forum: Performance Tuning » Query optimisation using meaningless condition

Home » RDBMS Server » Performance Tuning » Query optimisation using meaningless condition

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query optimisation using meaningless condition [message #344213]

Fri, 29 August 2008 01:58

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

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344216 is a reply to message #344213]

Fri, 29 August 2008 02:05

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Because you are lucky.

Provide the usual information for Performances questions.

Regards
Michel

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344222 is a reply to message #344216]

Fri, 29 August 2008 02:20

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...

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344268 is a reply to message #344222]

Fri, 29 August 2008 04:45

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.

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344289 is a reply to message #344222]

Fri, 29 August 2008 05:46

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck

Regards
Michel

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344307 is a reply to message #344268]

Fri, 29 August 2008 06:20

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.

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344316 is a reply to message #344213]

Fri, 29 August 2008 06:38

alammas
Messages: 46
Registered: July 2008

Member

Search may Table scan,Clustered,Non-clustered ,full text or hardware address.
Is that new way?

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344324 is a reply to message #344307]

Fri, 29 August 2008 07:04

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...

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344326 is a reply to message #344324]

Fri, 29 August 2008 07:12

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

You can get the timings with

SET TIMING ON

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344333 is a reply to message #344326]

Fri, 29 August 2008 07:28

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;

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344776 is a reply to message #344333]

Mon, 01 September 2008 04:19

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
---------------

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344807 is a reply to message #344776]

Mon, 01 September 2008 05:31

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);

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344841 is a reply to message #344776]

Mon, 01 September 2008 06:37

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

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344861 is a reply to message #344841]

Mon, 01 September 2008 08:00

rleishman
Messages: 3728
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

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344868 is a reply to message #344807]

Mon, 01 September 2008 08:12

bmikle
Messages: 6
Registered: August 2008

Junior Member

Here is the output...

SIGN(ID) COUNT(*)
---------- ----------
1 60325

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344869 is a reply to message #344861]

Mon, 01 September 2008 08:16

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!

Report message to a moderator

Re: Query optimisation using meaningless condition [message #344911 is a reply to message #344869]

Mon, 01 September 2008 16:24

rleishman
Messages: 3728
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

Report message to a moderator

Previous Topic:	unstable sort is inapprehensible (merged)
Next Topic:	Table Partitioning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 22:45:52 CST 2024