| SQL Tuning [message #495822] |
Tue, 22 February 2011 09:35  |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
The below query was running more than 6 hours. after that I stopped the query.
delete from anr_order_errors where market='UB'and ORBIT_NO not in (select ORBIT_NO from occ_con_orders where market='UB');
Can anybody help me to tune this query?
Count:
======
total count of occ_con_orders = 29000
occ_con_orders(with condition market='UB') = 22000
total count of anr_order_errors = 1668908
anr_order_errors (with condition market='UB') = 641706
But I dont know the count of anr_order_errors table, when I use MARKET and ORBIT_NO cloumn in where clause.
Fragmentation:
==============
SQL> select owner,table_name,round(((blocks*8)/1024),2) "size (Mb)" , round((num_rows*avg_row_len/1024/1024),2) "actual_data (M
b)",(round(((blocks*8)/1024),2) - round((num_rows*avg_row_len/1024/1024),2)) "wasted_space (Mb)" from dba_tables
2 where table_name='ANR_ORDER_ERRORS' and (round(((blocks*8)/1024),2) > round((num_rows*avg_row_len/1024/1024),2)) order by
4 asc;
OWNER TABLE_NAME size (Mb) actual_data (Mb) wasted_space (Mb)
------------------------------ ------------------------------ ---------- ---------------- -----------------
VITIB ANR_ORDER_ERRORS 373.23 234.04 139.19
Statistics:
===========
Already I analyzed this table through dbms_stats package.
Explain plan:
=============
This is the plan for above query.
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 105M (4)|352:21:59 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | ANR_ORDER_ERRORS | 638K| 5613K| 10647 (2)| 00:02:08 |
|* 4 | VIEW | index$_join$_002 | 1 | 9 | 174 (4)| 00:00:03 |
|* 5 | HASH JOIN | | | | | |
|* 6 | INDEX RANGE SCAN | COR_IDX1 | 1 | 9 | 180 (4)| 00:00:03 |
|* 7 | INDEX FAST FULL SCAN| COR_PK | 1 | 9 | 87 (3)| 00:00:02 |
---------------------------------------------------------------------------------------------
Since there is a composite index for the market column, I just created a simple index aoe_idx6 for the market column alone. Also I have an index aoe_idx3 on ORBIT_NO column.
and then tried to use HINTS as below.
SQL> explain plan for delete /*+ index(anr_order_errors aoe_idx6, anr_order_errors AOE_IDX3) */ from anr_order_errors where ma
rket='UB'and ORBIT_NO not in (select ORBIT_NO from occ_con_orders where market='UB');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------
Plan hash value: 2918639124
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 638K| 28M| 105M (4)|352:27:58 |
| 1 | DELETE | ANR_ORDER_ERRORS | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| ANR_ORDER_ERRORS | 638K| 28M| 40603 (1)| 00:08:08 |
|* 4 | INDEX RANGE SCAN | AOE_IDX6 | 638K| | 1281 (3)| 00:00:16 |
|* 5 | VIEW | index$_join$_002 | 1 | 9 | 174 (4)| 00:00:03 |
|* 6 | HASH JOIN | | | | | |
|* 7 | INDEX RANGE SCAN | COR_IDX1 | 1 | 9 | 180 (4)| 00:00:03 |
|* 8 | INDEX FAST FULL SCAN | COR_PK | 1 | 9 | 87 (3)| 00:00:02 |
--------------------------------------------------------------------------------------------------
But it accepting only aoe_idx6 index. I dont know why it doesn't take it up aoe_idx3 index.
Now I have 2 questions.
1. How to tune this query effectively?
2. Why the query does not pick up aoe_idx3 index, even I use this in Hint?
|
|
|
|
|
|
|
|
|
|
| Re: SQL Tuning [message #495834 is a reply to message #495824] |
Tue, 22 February 2011 10:56  |
John Watson
Messages: 8928
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Are you sure it is logically possible to use two indexes on anr_order_errors? I don't see how you can.
|
|
|
|
| Re: SQL Tuning [message #495895 is a reply to message #495834] |
Wed, 23 February 2011 02:20 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Do you know I hadn't even noticed that!
Edit: It can use two indexes, at least in 11g. Converts them to bitmaps (even if they are not).
Interesting.
MRK@ora11gmk > create table foo as select * from dba_objects;
Table created.
Elapsed: 00:00:00.92
MRK@ora11gmk > create index idx1 on foo(owner);
Index created.
Elapsed: 00:00:00.56
MRK@ora11gmk > create index idx2 on foo(object_type);
Index created.
Elapsed: 00:00:00.57
MRK@ora11gmk > explain plan for select * from foo where
2 owner = 'SYS' and object_name = 'DUAL' and object_type = 'TABLE';
Explained.
Elapsed: 00:00:00.06
MRK@ora11gmk > SELECT * FROM table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3711323475
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 82 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | FOO | 1 | 207 | 82 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | IDX2 | 2706 | | 9 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | IDX1 | 2706 | | 71 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
5 - access("OBJECT_TYPE"='TABLE')
7 - access("OWNER"='SYS')
Note
-----
- dynamic sampling used for this statement (level=2)
25 rows selected.
Elapsed: 00:00:00.11
Granted the indexes are on deliberately vague columns to try and force this but they are definitely BTrees.
/derail
[Updated on: Wed, 23 February 2011 02:27] Report message to a moderator |
|
|
|
| Re: SQL Tuning [message #495901 is a reply to message #495895] |
Wed, 23 February 2011 03:06 |
John Watson
Messages: 8928
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
I didn't know Oracle could do that. Thanks. Do you know what that access method is called? I can't see a hint to force it.
|
|
|
|
| Re: SQL Tuning [message #495902 is a reply to message #495901] |
Wed, 23 February 2011 03:19 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Not the foggiest I'm afraid - I imagine it'd need to be a rather bizarre/artificial set of circumstance - like those above - to cause this to happen.
I didn't need to hint it, the query was unaltered, there may be a hint but I'm don't know what it would be. Arguably in my example both of those index should have been bitmaps to start with, not btree. It may be there isn't a hint for that as doing this kind of operation on what I would term "appropriate Btrees" would probably be a recipe for disaster for performance.
Edit: google turned up this Johnathon Lewis link:
http://jonathanlewis.wordpress.com/2009/12/23/btree-bitmap/
I've not had time to read in depth.
[Updated on: Wed, 23 February 2011 03:27] Report message to a moderator |
|
|
|
| Re: SQL Tuning [message #495908 is a reply to message #495822] |
Wed, 23 February 2011 04:18 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello Sathik
I think the composite index market + ORBIT_NO on anr_order_errors would have been better. But we don't know how distinct ORBIT_NO is!
BTW have you thought of running the delete in parallel?
Either by using oracle in-built parallel mechanism or running delete on sets of ORBIT_NO in multiple sessions?
Or you can bulk collect the rowids 'from anr_order_errors where ORBIT_NO not in occ_con_orders..' etc and then using those sets of rowids you can run delete in multiple sessions
you may need to take care nobody is updating the table meanwhile.
Or
you can
1) create table t nologging as select * from anr_order_errors where ORBIT_NO not in occ_con_orders etc...
2) create index on t in parallel, nologging mode
3) drop anr_order_errors
4) rename t to anr_order_errors
I have not benchmarked the last option but may be you can try it
Also you may require to check for the existence of any triggers on the anr_order_errors table.
Regards,
OraKaran
|
|
|
|
| Re: SQL Tuning [message #495936 is a reply to message #495901] |
Wed, 23 February 2011 06:07 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
John Watson wrote on Wed, 23 February 2011 20:06I didn't know Oracle could do that. Thanks. Do you know what that access method is called? I can't see a hint to force it.
You can force it with an INDEX_COMBINE hint, which is specific to BITMAP access paths.
But still one selective index is always better than two non-selective indexes, just not as versatile.
As for the OP:
- Make sure anr_order_errors.ORBIT_NO is defined as NOT NULL, or add an IS NOT NULL clause to the outer query
- Make sure occ_con_orders .ORBIT_NO is defined as NOT NULL, or add an IS NOT NULL clause to the inner query.
- Once done, check the plan again. If it is using a HASH ANTI join, great. Otherwise, try restructuring as a MERGE with a DELETE sub-clause.
Ross Leishman
|
|
|
|
| Re: SQL Tuning [message #495955 is a reply to message #495908] |
Wed, 23 February 2011 08:37 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Hi Roachcoach,
i used the hint as you mentioned. But still it is not using the ORBIT_NO column index (aoe_idx3).
Now I understood Index is not going to help us to reduce the cost of this query.
Hi Orakaran,
I have created a composite index on market and orbit_no.
Then I took the explain plan below.There is no improvement.
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 638K| 28M| 105M (4)|352:20:12 |
| 1 | DELETE | ANR_ORDER_ERRORS | | | | |
|* 2 | INDEX RANGE SCAN | AOE_IDX7 | 31936 | 1434K| 1773 (2)| 00:00:22 |
|* 3 | VIEW | index$_join$_002 | 1 | 9 | 174 (4)| 00:00:03 |
|* 4 | HASH JOIN | | | | | |
|* 5 | INDEX RANGE SCAN | COR_IDX1 | 1 | 9 | 180 (4)| 00:00:03 |
|* 6 | INDEX FAST FULL SCAN| COR_PK | 1 | 9 | 87 (3)| 00:00:02 |
---------------------------------------------------------------------------------------------
2 - access("MARKET"='UB')
filter( NOT EXISTS (SELECT /*+ */ 0 FROM (SELECT "ORBIT_NO"
"ORBIT_NO","MARKET" "MARKET",ROWID "ROWID" FROM "OCC_CON_ORDERS"
"indexjoin$_alias$_002","OCC_CON_ORDERS" "indexjoin$_alias$_001" WHERE "MARKET"='UB'
AND ROWID=ROWID AND LNNVL("ORBIT_NO"<>:B1)) "OCC_CON_ORDERS" WHERE "MARKET"='UB'))
3 - filter("MARKET"='UB')
4 - access(ROWID=ROWID)
5 - access("MARKET"='UB')
6 - filter(LNNVL("ORBIT_NO"<>:B1))
I can't able to understand where it is taking time? I think I need to take a trace.
that is the only one option here. I am not sure trace going to help me or not. do I have anyother option here?
[Updated on: Wed, 23 February 2011 10:13] Report message to a moderator |
|
|
|
| Re: SQL Tuning [message #496023 is a reply to message #495955] |
Wed, 23 February 2011 17:00 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
sathik wrote on Thu, 24 February 2011 01:37do I have anyother option here?
You could try what I suggested...
Ross Leishman
|
|
|
|
| Re: SQL Tuning [message #498052 is a reply to message #496023] |
Tue, 08 March 2011 07:43 |
marcodba
Messages: 5
Registered: August 2006
|
Junior Member |
|
|
I think your plan shows that the delete itself is slow, not the way to access the data.
Try to make unusable the index(es) on ANR_ORDER_ERRORS table before doing the delete.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
