Home » RDBMS Server » Performance Tuning » Please help me on tune this query (Oracle 11g)
Please help me on tune this query [message #572297] Mon, 10 December 2012 03:56 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

If I run the first query I am getting the output in 40 secs.

SELECT * FROM  APP.SALES
ORDER BY customer_id;


If I run the below query I am getting the output in 2 secs.

SELECT /*+ INDEX_ASC(SALES IDX_CUSTOMER_ID) */ * FROM  APP.SALES
ORDER BY customer_id;


In the first query cost is low and time is high.

In the second query cost is high and time is low.

which one is best.


The table is having 5509714 records and customer_id is having index and NOT NULL.

In second query why the cost got increased.


When tuning the query we have to focus on time or cost.


Please help me.

Please find the below explain plans for the same.
SQL> explain plan for
  2  SELECT /*+ INDEX_ASC(SALES IDX_CUSTOMER_ID) */ * FROM  APP.SALES
  3  ORDER BY customer_id;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

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


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |  5602K|   817M|  4738K  (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID| SALES             |  5602K|   817M|  4738K  (1)|
|   2 |   INDEX FULL SCAN           | IDX_CUSTOMER_ID   |  5602K|       | 29225   (1)|
--------------------------------------------------------------------------------------

Note
-----

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

   - 'PLAN_TABLE' is old version

12 rows selected.

Elapsed: 00:00:00.59



SQL> explain plan for
  2  SELECT * FROM  APP.SALES
  3  ORDER BY customer_id;

Explained.



SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

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


-------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |  5602K|   817M|       |   224K  (1)|
|   1 |  SORT ORDER BY     |             |  5602K|   817M|  1017M|   224K  (1)|
|   2 |   TABLE ACCESS FULL| SALES       |  5602K|   817M|       | 34962   (1)|
-------------------------------------------------------------------------------

Note
-----

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

   - 'PLAN_TABLE' is old version

12 rows selected.

Elapsed: 00:00:00.61

Re: Please help me on tune this query [message #572298 is a reply to message #572297] Mon, 10 December 2012 04:02 Go to previous messageGo to next message
Roachcoach
Messages: 1215
Registered: May 2010
Location: UK
Senior Member
Quote:
'PLAN_TABLE' is old version


Fix that.


Do you understand how Oracle works in terms of buffer cache, statistics and 'cost' estimates?
Re: Please help me on tune this query [message #572299 is a reply to message #572298] Mon, 10 December 2012 04:16 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
No I am not aware of it since I am new to performance tuning.

Could you please explain this.
Re: Please help me on tune this query [message #572301 is a reply to message #572299] Mon, 10 December 2012 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59269
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Tuning High-Volume SQL Wiki page

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: Please help me on tune this query [message #572302 is a reply to message #572299] Mon, 10 December 2012 04:27 Go to previous messageGo to next message
Roachcoach
Messages: 1215
Registered: May 2010
Location: UK
Senior Member
ajaykumarkona wrote on Mon, 10 December 2012 10:16
No I am not aware of it since I am new to performance tuning.

Could you please explain this.


That's what I thought you may say.

The difficulty is, you cannot effectively tune anything without first understanding how it works.

You may have some success using trial and error/internet found code, however to properly understand this, you need to do a lot of ground work first.

In addition to Michels links, I would suggest you read Database Concepts 11gR2, or whatever version you have.

The answer(s) to your question is it could be that the stats are out, that the data is cached that you're using a tool and only partial results are coming back. It could be any/all of these and a few others to boot.
Re: Please help me on tune this query [message #572305 is a reply to message #572302] Mon, 10 December 2012 06:00 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
When you use the index to do the sorting for you, Oracle just starts peeling rows off the index and giving them to your SQL session. It looks like it only takes 2 seconds, but it has not retrieved all of the results yet, just the first page.
When you don't use the index, Oracle has to find ALL of the matching rows and sort them before it can show you ANY of them, so it takes a lot longer.

The cost you see is the cost to return EVERY row, not the first page. The reason the Indexed version has a high cost is because it will take a long time to return every row, even though the first page was quick.

If you spool the results to a file you will get a better indication of the total time to return every row.

Ross Leishman
Re: Please help me on tune this query [message #572310 is a reply to message #572305] Mon, 10 December 2012 06:32 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks for your reply.
Please help me how can I improve the performance of this query.

Thanks.
Re: Please help me on tune this query [message #572314 is a reply to message #572310] Mon, 10 December 2012 07:45 Go to previous messageGo to next message
cookiemonster
Messages: 10966
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why are you trying to run a query that returns 5.5 million rows at all?
Surely you only need a subset?

You do realise that the only reason you're getting the results in 2 seconds is because you're only fetching a small handful of the total rows.
It'll take a lot longer to fetch all of them.
Re: Please help me on tune this query [message #572512 is a reply to message #572314] Wed, 12 December 2012 11:50 Go to previous message
Kevin Meade
Messages: 1939
Registered: December 1999
Location: Connecticut USA
Senior Member
set timing on

create table temp1
nologging
as
SELECT * FROM  APP.SALES
ORDER BY customer_id;

create table temp2
nologging
as
SELECT /*+ INDEX_ASC(SALES IDX_CUSTOMER_ID) */ * FROM  APP.SALES
ORDER BY customer_id;


Then tell us how long each takes. The index may be faster or slower, depends upon the data. In your case, using the index has allowed you to skip sorting. But this may or may not be better. It can be highly useful if you are trying to get the first few rows quickly, or if you are doing pagniation queries. However, it is a hack in the sense that you used a hint to do it and the hint might change with releases and thus the behavior might change too.


But run the table creates above and then report back.

Kevin
Previous Topic: RBS usage and longops in toad
Next Topic: SQL Tuning...?
Goto Forum:
  


Current Time: Tue Sep 30 02:26:43 CDT 2014

Total time taken to generate the page: 0.10189 seconds