Home » SQL & PL/SQL » SQL & PL/SQL » query dead slow (ORACLE EBS R12 )
query dead slow [message #661222] Sat, 11 March 2017 02:45 Go to next message
annu-agi
Messages: 220
Registered: July 2005
Location: Karachi
Senior Member

hi experts

select * from INV.Mtl_Material_Transactions mmt, inv.mtl_transaction_lot_numbers mtln
where 1=1
and mmt.TRANSACTION_ID = mtln.TRANSACTION_ID
and mmt.INVENTORY_ITEM_ID= mtln.INVENTORY_ITEM_ID 
and mmt.ORGANIZATION_ID = mtln.ORGANIZATION_ID
and mmt.TRANSACTION_DATE = mtln.TRANSACTION_DATE
and mmt.TRANSACTION_SOURCE_TYPE_ID=mtln.TRANSACTION_SOURCE_TYPE_ID
and mmt.transaction_Date between '01-jan-2016' and '31-dec-2016'
;
Above given query result dead slow, even i don't using any group function , it would not present any results timely.
Mtl Material_transaction is around 84849565 number of records and mtl_transaction_lot_numbers having 65833968.

Please see the explain plan screen shot attachment .

Need suggestions and consideration how to improve query result.

/forum/fa/13475/0/

[mod-edit: image inserted into message body by bb]


  • Attachment: Untitled.png
    (Size: 90.19KB, Downloaded 226 times)

[Updated on: Sat, 11 March 2017 22:59] by Moderator

Report message to a moderator

Re: query dead slow [message #661224 is a reply to message #661222] Sat, 11 March 2017 02:54 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
You could apply your
transaction_Date between '01-jan-2016' and '31-dec-2016'
predicate to the MTL_MATERIAL_TRANSACTIONS table and hint the use of the MTL_MATERIAL_TRANSACTIONS_N5 index. And of course you need to type cast the strings to dates.
Re: query dead slow [message #661226 is a reply to message #661224] Sat, 11 March 2017 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

To emphasize what John said: '13-nov-1956' IS between '01-jan-2016' and '31-dec-2016':
SQL> select * from dual where  '13-nov-1956' between '01-jan-2016' and '31-dec-2016';
D
-
X

1 row selected.
And to add to what he said:
SQL> select * from dual where sysdate between '01-jan-2016' and '31-dec-2016' ;
select * from dual where sysdate between '01-jan-2016' and '31-dec-2016'
                                                           *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
So definitively wrong.

Re: query dead slow [message #661233 is a reply to message #661226] Sat, 11 March 2017 12:47 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
Quote:
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
That is because of Brexit Sad
Re: query dead slow [message #661288 is a reply to message #661233] Tue, 14 March 2017 00:38 Go to previous messageGo to next message
annu-agi
Messages: 220
Registered: July 2005
Location: Karachi
Senior Member

Quote:

predicate to the MTL_MATERIAL_TRANSACTIONS table and hint the use of the MTL_MATERIAL_TRANSACTIONS_N5 index. And of course you need to type cast the strings to dates.
please elaborate WHAT DO MEAN BY PREDICATE to the mtl_material_transaction table and type cast??

according to my understanding i have change my query as below , but nothing happens with result, system gone in thinking state and after some time i have to terminate the query.
select 
/* + index(mmt MTL_MATERIAL_TRANSACTIONS_N5) */
* from INV.Mtl_Material_Transactions mmt, inv.mtl_transaction_lot_numbers mtln
where 1=1
and mmt.TRANSACTION_ID = mtln.TRANSACTION_ID
and mmt.INVENTORY_ITEM_ID= mtln.INVENTORY_ITEM_ID 
and mmt.ORGANIZATION_ID = mtln.ORGANIZATION_ID
and mmt.TRANSACTION_DATE = mtln.TRANSACTION_DATE
and mmt.TRANSACTION_SOURCE_TYPE_ID=mtln.TRANSACTION_SOURCE_TYPE_ID
and mmt.transaction_Date between TO_daTE('01-jan-2016') and TO_dATE('31-dec-2016') 

need help in this regards
Re: query dead slow [message #661290 is a reply to message #661288] Tue, 14 March 2017 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Still wrong:
SQL> select  TO_daTE('01-jan-2016') from dual;
select  TO_daTE('01-jan-2016') from dual
                *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Re: query dead slow [message #661294 is a reply to message #661290] Tue, 14 March 2017 01:32 Go to previous messageGo to next message
annu-agi
Messages: 220
Registered: July 2005
Location: Karachi
Senior Member

select  TO_daTE('01-jan-2016') from dual;

whats wrong with it , see the screen shot below
  • Attachment: Untitled.png
    (Size: 116.02KB, Downloaded 51 times)
Re: query dead slow [message #661296 is a reply to message #661294] Tue, 14 March 2017 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Answer: nous ne parlons pas tous la même langue.


Re: query dead slow [message #661299 is a reply to message #661296] Tue, 14 March 2017 01:46 Go to previous messageGo to next message
annu-agi
Messages: 220
Registered: July 2005
Location: Karachi
Senior Member

michel,

Yes, We all speak the same language but i didnt get any ORA- error
Re: query dead slow [message #661301 is a reply to message #661299] Tue, 14 March 2017 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No we don't, as I showed it.
Even then, we not all use the same date format:
SQL> alter session set nls_date_language=american;

Session altered.

SQL> alter session set nls_date_format='yyyy/mm:dd';

Session altered.

SQL> select  TO_daTE('01-jan-2016') from dual;
select  TO_daTE('01-jan-2016') from dual
                *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Read the documentation about TO_DATE function.
Re: query dead slow [message #661302 is a reply to message #661288] Tue, 14 March 2017 01:52 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
You are still not applying the predicate to the correct table.
You need to look up how to use TO_DATE. It needs to have a format specifier, you know. And NLS settings.
Lastly, you need to look up the syntax of the INDEX hint.

Are you serious about this?
Re: query dead slow [message #661307 is a reply to message #661302] Tue, 14 March 2017 03:50 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
As far as I can see the OP is applying the predicate to the table you told him to, and was in the first place. Did you mean the other table?
Re: query dead slow [message #661308 is a reply to message #661307] Tue, 14 March 2017 03:53 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
Oh, did I get that wrong? Yes, I meant the other one. The one with the index.

--update @OP if I made one error, I may have made another. You had better check the ETRM to confirm which index of which table has transaction_date as the leading column. And of course you need to learn how to use a hint.

[Updated on: Tue, 14 March 2017 04:00]

Report message to a moderator

Re: query dead slow [message #661318 is a reply to message #661308] Tue, 14 March 2017 08:34 Go to previous messageGo to next message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
You may NOT have a space in the "/* +". The hint MUST be

/*+ index(mmt MTL_MATERIAL_TRANSACTIONS_N5) */

The /*+ is what tells the optimizer that this is a hint and not just an embedded comment.
Re: query dead slow [message #661424 is a reply to message #661318] Fri, 17 March 2017 02:54 Go to previous messageGo to next message
annu-agi
Messages: 220
Registered: July 2005
Location: Karachi
Senior Member

two things i have done and i got my results ,

first i found number of different indexes on date fields , i remove all and create new one which is predicate with trunc. and now
my where cluse is something like that
trunc(Transaction_Date) >=(date'2017-03-01') and trunc(transaction_DAte) <date'2017-03-02'


thank you all of you
Re: query dead slow [message #661431 is a reply to message #661424] Fri, 17 March 2017 04:42 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
That will give the same results as this:
Transaction_Date >=(date'2017-03-01') and transaction_DAte <date'2017-03-02'

You didn't need trunc.
Re: query dead slow [message #661432 is a reply to message #661431] Fri, 17 March 2017 04:46 Go to previous messageGo to next message
annu-agi
Messages: 220
Registered: July 2005
Location: Karachi
Senior Member

ok ..

but i dint understand what date'2017-01-01' is ??? date''. it not a command or function ??? then what it is ??
Re: query dead slow [message #661434 is a reply to message #661432] Fri, 17 March 2017 05:29 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's a date literal
It's a way of specified a date without using to_date, it only takes that format though.
icon14.gif  Re: query dead slow [message #661435 is a reply to message #661434] Fri, 17 March 2017 06:38 Go to previous messageGo to next message
annu-agi
Messages: 220
Registered: July 2005
Location: Karachi
Senior Member

got it
Re: query dead slow [message #661439 is a reply to message #661435] Fri, 17 March 2017 11:31 Go to previous message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You did not see it when you read the SQL Reference book I pointed you too man times?

Previous Topic: function based index on date column
Next Topic: invalid option using INTERVAL( NUMTODSINTERVAL(1, 'DAY'))
Goto Forum:
  


Current Time: Sun Feb 25 04:23:58 CST 2018

Total time taken to generate the page: 0.02458 seconds