Home » RDBMS Server » Performance Tuning » Performance issue (Oracle 9i)
Performance issue [message #340534] Wed, 13 August 2008 03:51 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

SELECT DISTINCT order_id FROM ITEM
WHERE EXISTS
(SELECT DISTINCT order_id FROM TRANS_TBL
WHERE updt_dt BETWEEN TO_DATE('01/01/2008','MM/dd/yyyy') AND
TO_DATE('08/12/2008','MM/dd/yyyy')
)
AND current_loc_cd='OHIO' AND destination_loc_cd='OBMA'
ORDER BY order_id
/



For the above query, while executing Elapsed time is: 00:00:12:08


But if RULE hint is used in the query, Elapsed time is: 00:00:03:04


But I dont want to use rule hint as its officially not supported.
Need your suggesion.

Regards,
Oli
Re: Performance issue [message #340537 is a reply to message #340534] Wed, 13 August 2008 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So don't use it.

More than 200 posts and you still don't know how to post! Mad


Regards
Michel
Re: Performance issue [message #340538 is a reply to message #340537] Wed, 13 August 2008 04:18 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Michel for the reply...

SELECT DISTINCT order_id 
	FROM ITEM
WHERE EXISTS
(
SELECT  DISTINCT order_id 
	FROM TRANS_TBL
	WHERE updt_dt BETWEEN TO_DATE('01/01/2008','MM/dd/yyyy') 
	AND TO_DATE('08/12/2008','MM/dd/yyyy')
)
AND current_loc_cd='OHIO' 
AND destination_loc_cd='OBMA'
ORDER BY order_id
/



Quote:
So, don't use it

You meant "let it be...let performance problem occur!!"?
Regards,
Oli

[Updated on: Wed, 13 August 2008 04:27]

Report message to a moderator

Re: Performance issue [message #340564 is a reply to message #340538] Wed, 13 August 2008 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I was not talking about formatting but about all the information you have to give when you ask for a performances issue.

Regards
Michel
Re: Performance issue [message #340577 is a reply to message #340538] Wed, 13 August 2008 06:01 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

hi,
Have you done things like got the most up to date stats on the tables and indexes.

have you also gone through the process on gather system stats.

regards

Alan
Re: Performance issue [message #340596 is a reply to message #340577] Wed, 13 August 2008 06:30 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
INDEX USING:

ITEM:
_______

INDEX XIFITEM_1 ON (UPD_DT, DESTINATION_LOC_CD) OF ITEM
UNIIQUE INDEX XPK_ITEM  ON (ORDER_ID, ORDER_TYPE_CODE, STATUS_CODE, UPD_DT) OF ITEM

TRANS_TBL:
__________


UNIQUE INDEX TRANS_TBL_PKEY ON TRANS_TBL
(ORDER_ID, TRANS_SEQ_NBR)
INDEX  XI1_TRANS_TBL ON  UPD_DT



Without Hint:
_________________

83 rows selected.
Elapsed: 00:00:12.06

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=151193 Card=1 Bytes=
          42)

   1    0   SORT (UNIQUE) (Cost=151192 Card=1 Bytes=42)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TRANS_TBL'
           (Cost=4 Card=2 Bytes=36)

   3    2       NESTED LOOPS (Cost=151190 Card=1 Bytes=42)
   4    3         TABLE ACCESS (FULL) OF 'ITEM' (Cos
          t=151186 Card=1 Bytes=24)

   5    3         INDEX (RANGE SCAN) OF 'TRANS_TBL_PKEY' (
          UNIQUE) (Cost=2 Card=2)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      15855  consistent gets
      15673  physical reads
          0  redo size
        868  bytes sent via SQL*Net to client
        303  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         63  rows processed
		 
		 
		 
		 
		 
		 
----


Using Rule hint:

83 rows selected.

Elapsed: 00:00:03.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=151193 Card=1 Bytes=
          42)

   1    0   SORT (UNIQUE) (Cost=151192 Card=1 Bytes=42)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TRANS_TBL'
           (Cost=4 Card=2 Bytes=36)

   3    2       NESTED LOOPS (Cost=151190 Card=1 Bytes=42)
   4    3         TABLE ACCESS (FULL) OF 'ITEM' (Cos
          t=151186 Card=1 Bytes=24)

   5    3         INDEX (RANGE SCAN) OF 'TRANS_TBL_PKEY' (
          UNIQUE) (Cost=2 Card=2)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      15855  consistent gets
      15671  physical reads
          0  redo size
        869  bytes sent via SQL*Net to client
        303  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         63  rows processed
		 
		 



Statistics were gathered.

Regards,
Oli

[Updated on: Wed, 13 August 2008 06:30]

Report message to a moderator

Re: Performance issue [message #340598 is a reply to message #340596] Wed, 13 August 2008 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So no difference, neither in plan nor in statistics, you have to investigate the differences in what you didn't post.

Regards
Michel
Re: Performance issue [message #340604 is a reply to message #340534] Wed, 13 August 2008 06:56 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Sorry, pasted the same plan without hint also! Here is the info.

SELECT DISTINCT order_id 
	FROM ITEM
WHERE EXISTS
(
SELECT  DISTINCT order_id 
	FROM TRANS_TBL
	WHERE updt_dt BETWEEN TO_DATE('02/01/2008','MM/dd/yyyy') 
	AND TO_DATE('08/12/2008','MM/dd/yyyy')
)
AND current_loc_cd='OHIO' 
AND destination_loc_cd='OBMA'
ORDER BY order_id
/



67 rows selected.

Elapsed: 00:00:06.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=151193 Card=1 Bytes=
          42)

   1    0   SORT (UNIQUE) (Cost=151192 Card=1 Bytes=42)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TRANS_TBL'
           (Cost=4 Card=1 Bytes=18)

   3    2       NESTED LOOPS (Cost=151190 Card=1 Bytes=42)
   4    3         TABLE ACCESS (FULL) OF 'ITEM' (Cos
          t=151186 Card=1 Bytes=24)

   5    3         INDEX (RANGE SCAN) OF 'TRANS_TBL_PKEY' (UNIQUE) 
   (Cost=2 Card=2)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      15855  consistent gets
      15668  physical reads
          0  redo size
        869  bytes sent via SQL*Net to client
        303  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         67  rows processed


Using rule hint:
____________________

Elapsed: 00:00:03.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   SORT (UNIQUE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'ITEM'

   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'TRANS_TBL'

   5    4           INDEX (RANGE SCAN) OF 'XI1_TRANS_TBL'
           (NON-UNIQUE)

   6    3         INDEX (RANGE SCAN) OF 'XPK_ITEM' (UNIQUE)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        276  consistent gets
          3  physical reads
          0  redo size
        869  bytes sent via SQL*Net to client
        303  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         67  rows processed
		 


Re: Performance issue [message #340713 is a reply to message #340604] Wed, 13 August 2008 22:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I think your SQL is missing a line. There is no correllation predicate between the sub-query and the outer-query.

Also, the DISTINCT on the inner-query is not required.

SELECT DISTINCT order_id 
	FROM ITEM
WHERE EXISTS
(
SELECT  order_id 
	FROM TRANS_TBL
	WHERE updt_dt BETWEEN TO_DATE('02/01/2008','MM/dd/yyyy') 
	AND TO_DATE('08/12/2008','MM/dd/yyyy')
        AND trans_tbl.order_id = item.order_id   --- *** THIS LINE ***
)
AND current_loc_cd='OHIO' 
AND destination_loc_cd='OBMA'
ORDER BY order_id
/

Ross Leishman
Re: Performance issue [message #340771 is a reply to message #340713] Thu, 14 August 2008 04:06 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for pointing out the mistake.

But In most of SQL's that using RULE hint, I can see better performance then using CBO

The elasped time is one-third or one-fourth of what we get done using CBO.


Regards,
Oli

[Updated on: Thu, 14 August 2008 04:07]

Report message to a moderator

Re: Performance issue [message #340785 is a reply to message #340771] Thu, 14 August 2008 04:57 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

hi,
What version database and what operating system are you using.

maybe we need to look at this from a different angle. If the sql is okay then maybe other things are impacting the CBO.

regards

Alan
Re: Performance issue [message #340787 is a reply to message #340785] Thu, 14 August 2008 05:08 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
alanm wrote on Thu, 14 August 2008 04:57
hi,
What version database and what operating system are you using.

maybe we need to look at this from a different angle. If the sql is okay then maybe other things are impacting the CBO.

regards

Alan




Thanks for the reply..
Here is the info:
OS: Unix
DATABASE: Oracle 9.2.0.5

What are the things that need to be taken care of?


Thanks,
Oli

[Updated on: Thu, 14 August 2008 05:55]

Report message to a moderator

Re: Performance issue [message #340796 is a reply to message #340787] Thu, 14 August 2008 06:09 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

Hi,
can you run this script

select	pname, pval1 
from 	sys.aux_stats$ 
where 	sname = 'SYSSTATS_MAIN';



and post the result

regards

Alan
Re: Performance issue [message #340804 is a reply to message #340796] Thu, 14 August 2008 06:48 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Showing the below error:


ERROR at line 2:
ORA-00942: table or view does not exist



Regards,
Oli
Re: Performance issue [message #340808 is a reply to message #340804] Thu, 14 August 2008 07:12 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

who are you logged in as? sysdba?

regards

Alan
Re: Performance issue [message #340811 is a reply to message #340808] Thu, 14 August 2008 07:25 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
not as Sysdba. I dont have the priviledge. Any other way?
What should be the approach that should be taken to remove RULE hints? I can see that most of the SQL's using Rule hint giving better performance then using any other hints? Its 1/4th of the elasped time for a query then using any other hints/without hints.

In the below link have the same issue but not getting any appropriate solution why using RULE hint makes response time 1/4th (very less compared to using other hints).Whats is the alternative for it?


http://www.freelists.org/archives/oracle-l/04-2008/msg00251.html

Regards,
Oli

[Updated on: Thu, 14 August 2008 07:38]

Report message to a moderator

Re: Performance issue [message #340816 is a reply to message #340811] Thu, 14 August 2008 08:42 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

hi,
Who then look after the maintenance of your database?
Are you able to get access as sysdba?

regards

Alan
Re: Performance issue [message #340817 is a reply to message #340816] Thu, 14 August 2008 08:44 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

Alan,

I have no acces as sysdba.


Regards,
Oli
Re: Performance issue [message #340821 is a reply to message #340817] Thu, 14 August 2008 08:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When you say 'Statistics were gathered', could you elaborate on
1) When they were gathered
2) How they were gathered (details please)
Re: Performance issue [message #340836 is a reply to message #340821] Thu, 14 August 2008 09:41 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
It was gathered 3 weeks back by the DBA.
Re: Performance issue [message #340838 is a reply to message #340836] Thu, 14 August 2008 09:50 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

hello Oli,
Is it possible to get the DBA to run that script I sent earlier.

regarda

Alan
Re: Performance issue [message #340841 is a reply to message #340838] Thu, 14 August 2008 09:59 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Alan/JRowbottom for giving your time.
@Alan:
What I am going to do is I am going to provide the SQL to DBA
and ask him to run the query and give the result.

I will get back to you after getting the result.



Regards,
Oli
Previous Topic: Update million rows
Next Topic: tracing, connection pools, java programs
Goto Forum:
  


Current Time: Fri Dec 09 07:41:48 CST 2016

Total time taken to generate the page: 0.26567 seconds