| Please help me on tune this query [message #572297] |
Mon, 10 December 2012 03:56  |
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.
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 #572302 is a reply to message #572299] |
Mon, 10 December 2012 04:27  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
ajaykumarkona wrote on Mon, 10 December 2012 10:16No 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 |
rleishman
Messages: 3728
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 #572314 is a reply to message #572310] |
Mon, 10 December 2012 07:45 |
cookiemonster
Messages: 13920
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 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
