Home » RDBMS Server » Performance Tuning » SQL Tuning (Oracle 10.2.0.4)
SQL Tuning [message #495822] Tue, 22 February 2011 09:35 Go to next message
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 #495823 is a reply to message #495822] Tue, 22 February 2011 09:39 Go to previous messageGo to next message
BlackSwan
Messages: 23150
Registered: January 2009
Senior Member
WHY MY INDEX IS NOT BEING USED
http://communities.bmc.com/communities/docs/DOC-10031

http://searchoracle.techtarget.com/tip/Why-isn-t-my-index-getting-used

http://www.orafaq.com/tuningguide/not%20using%20index.html
Re: SQL Tuning [message #495824 is a reply to message #495823] Tue, 22 February 2011 09:53 Go to previous messageGo to next message
Roachcoach
Messages: 1233
Registered: May 2010
Location: UK
Senior Member
Your hint syntax is, I think, incorrect.

explain plan for delete /*+ index(anr_order_errors aoe_idx6) index(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');


Is how I would do it.


Chances are, however, that it doesn't want to use the index because it wouldn't be optimal. See the post above me for why Smile
Re: SQL Tuning [message #495826 is a reply to message #495824] Tue, 22 February 2011 09:57 Go to previous messageGo to next message
BlackSwan
Messages: 23150
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
Re: SQL Tuning [message #495834 is a reply to message #495824] Tue, 22 February 2011 10:56 Go to previous messageGo to next message
John Watson
Messages: 4859
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 Go to previous messageGo to next message
Roachcoach
Messages: 1233
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 Go to previous messageGo to next message
John Watson
Messages: 4859
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 Go to previous messageGo to next message
Roachcoach
Messages: 1233
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3701
Registered: October 2005
Location: Melbourne, Australia
Senior Member
John Watson wrote on Wed, 23 February 2011 20:06
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.


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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3701
Registered: October 2005
Location: Melbourne, Australia
Senior Member
sathik wrote on Thu, 24 February 2011 01:37
do 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 Go to previous message
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.
Previous Topic: inserting data using FOR ALL
Next Topic: Stored Procedure is taking too long time to Execute. (2 Merged)
Goto Forum:
  


Current Time: Fri Dec 19 22:10:11 CST 2014

Total time taken to generate the page: 0.12517 seconds