Home » RDBMS Server » Performance Tuning » Same Data, Two machines but different timings. Why? (10.2.0.1.0)
Same Data, Two machines but different timings. Why? [message #634404] Mon, 09 March 2015 12:40 Go to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
Hi
Here is the situation:

1) Machine 1: Core 2 Duo, 4 GB RAM, No of records are around 2 million >>> Time taken to fetch result of a specific query = 2 seconds


2) Machine 2: Core i-7, 4 GB RAM, SAME RECORDS AS ABOVE transferred by export/import command >> Time taken to fetch result of THE SAME QUERY = 7 seconds


Can you please guide me why time taken by second machine is 250% more as compared to that of machine 1 (even machine 2 has powerful processor)?

how can i improve machine 2 performance? The oracle version being used on both machines is 10.2.0.1.0


Please guide.

Thanks
Re: Same Data, Two machines but different timings. Why? [message #634405 is a reply to message #634404] Mon, 09 March 2015 12:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post SQL & EXPLAIN PLAN from both systems?

does Machine 2 have current statistics?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Re: Same Data, Two machines but different timings. Why? [message #634406 is a reply to message #634405] Mon, 09 March 2015 13:21 Go to previous messageGo to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
Respected BlackSwan,

I thought as I am using the same SQL query in both the cases so it becomes "in difference".

Soon I will try to post the SQL query (which is basically being used in forms to populate fields regarding historical sales)


does Machine 2 have current statistics? I am filed to understand your this question. How cani check "current statistics"?


Thanks
Re: Same Data, Two machines but different timings. Why? [message #634408 is a reply to message #634406] Mon, 09 March 2015 13:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ask your DBA to make this analysis, it seems, given your previous questions, you are not equipped to deal with this issue.

Re: Same Data, Two machines but different timings. Why? [message #634409 is a reply to message #634408] Mon, 09 March 2015 14:30 Go to previous messageGo to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
Here is the query being used
select  to_char(invdate, 'MON-YYYY') as mmyy, nvl(sum(qty),0) as mqty, nvl(min(uprice),0) as  pmin, nvl(max(uprice),0) as pmax
                                           from inv1s a, inv0s b
                                           where a.invno=b.invno
                                           and return2='N'
                                           and pcode=13145
                                           group by to_char(invdate, 'MON-YYYY'), to_number(to_char(invdate, 'YYYY')),  to_number(to_char(invdate, 'MM'))
                                           order by to_number(to_char(invdate, 'YYYY')) desc,  to_number(to_char(invdate, 'MM')) desc



The Explain Plan from machine 1 is as follow:
STATEMENT_ID  PLAN_ID  TIMESTAMP  REMARKS  OPERATION  OPTIONS  OBJECT_NODE  OBJECT_OWNER  OBJECT_NAME  OBJECT_ALIAS  OBJECT_INSTANCE  OBJECT_TYPE  OPTIMIZER  SEARCH_COLUMNS  ID  PARENT_ID  DEPTH  POSITION  COST  CARDINALITY  BYTES  OTHER_TAG  PARTITION_START  PARTITION_STOP  PARTITION_ID  OTHER  OTHER_XML  DISTRIBUTION  CPU_COST  IO_COST  TEMP_SPACE  ACCESS_PREDICATES  FILTER_PREDICATES  PROJECTION  TIME  QBLOCK_NAME  
   1  10-MAR-15     SELECT STATEMENT                       ALL_ROWS     0     0  4915  4915  401  13233                       790853200  4779              59     
   1  10-MAR-15     SORT  GROUP BY                          1  0  1  1  4915  401  13233                 <other_xml><info typ e="db_version">10.2. 0.1</info><info type ="parse_schema"><![C     790853200  4779           (#keys=3) TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("INVDATE"),'YYYY'))[22], TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("INVDATE"),'MM'))[22], TO_CHAR(INTERNAL_FUN CTION("INVDATE"),'MON-YYYY')[8], SUM("QTY")[22], MAX("UPRICE")[22], MIN("UPRICE")[22]  59  SEL$1  
   1  10-MAR-15     HASH JOIN                             2  1  2  1  4909  38190  1260270                       758843891  4779  1147000  "A"."INVNO"="B"."INVNO"     (#keys=1) "UPRICE"[NUMBER,22], "QTY"[NUMBER,22], "INVDATE"[DATE,7]  59     
   1  10-MAR-15     TABLE ACCESS  FULL     OWNER  INV1S  A@SEL$1  1  TABLE  ANALYZED     3  2  3  1  2818  38129  686322                       446571452  2741        "PCODE"=13145  "A"."INVNO"[NUMBER,22], "QTY"[NUMBER,22], "UPRICE"[NUMBER,22]  34  SEL$1  
   1  10-MAR-15     TABLE ACCESS  FULL     OWNER  INV0S  B@SEL$1  2  TABLE  ANALYZED     4  2  3  2  1424  465900  6988500                       203587531  1389        "RETURN2"='N'  "B"."INVNO"[NUMBER,22], "INVDATE"[DATE,7]  18  SEL$1  






The explain plan from machine 2 is as follow:
STATEMENT_ID	PLAN_ID	TIMESTAMP	REMARKS	OPERATION	OPTIONS	OBJECT_NODE	OBJECT_OWNER	OBJECT_NAME	OBJECT_ALIAS	OBJECT_INSTANCE	OBJECT_TYPE	OPTIMIZER	SEARCH_COLUMNS	ID	PARENT_ID	DEPTH	POSITION	COST	CARDINALITY	BYTES	OTHER_TAG	PARTITION_START	PARTITION_STOP	PARTITION_ID	OTHER	OTHER_XML	DISTRIBUTION	CPU_COST	IO_COST	TEMP_SPACE	ACCESS_PREDICATES	FILTER_PREDICATES	PROJECTION	TIME	QBLOCK_NAME
 	21	10-MAR-15	 	SELECT STATEMENT	 	 	 	 	 	 	 	ALL_ROWS	 	0	 	0	4915	4915	401	13233	 	 	 	 	 	 	 	790853200	4779	 	 	 	 	59	 
 	21	10-MAR-15	 	SORT	GROUP BY	 	 	 	 	 	 	 	 	1	0	1	1	4915	401	13233	 	 	 	 	 	<other_xml><info typ e="db_version">10.2. 0.1</info><info type ="parse_schema"><![C	 	790853200	4779	 	 	 	(#keys=3) TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("INVDATE"),'YYYY'))[22], TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("INVDATE"),'MM'))[22], TO_CHAR(INTERNAL_FUN CTION("INVDATE"),'MON-YYYY')[8], SUM("QTY")[22], MAX("UPRICE")[22], MIN("UPRICE")[22]	59	SEL$1
 	21	10-MAR-15	 	HASH JOIN	 	 	 	 	 	 	 	 	 	2	1	2	1	4909	38190	1260270	 	 	 	 	 	 	 	758843891	4779	1147000	"A"."INVNO"="B"."INVNO"	 	(#keys=1) "UPRICE"[NUMBER,22], "QTY"[NUMBER,22], "INVDATE"[DATE,7]	59	 
 	21	10-MAR-15	 	TABLE ACCESS	FULL	 	OWNER	INV1S	A@SEL$1	1	TABLE	ANALYZED	 	3	2	3	1	2818	38129	686322	 	 	 	 	 	 	 	446571452	2741	 	 	"PCODE"=13145	"A"."INVNO"[NUMBER,22], "QTY"[NUMBER,22], "UPRICE"[NUMBER,22]	34	SEL$1
 	21	10-MAR-15	 	TABLE ACCESS	FULL	 	OWNER	INV0S	B@SEL$1	2	TABLE	ANALYZED	 	4	2	3	2	1424	465900	6988500	 	 	 	 	 	 	 	203587531	1389	 	 	"RETURN2"='N'	"B"."INVNO"[NUMBER,22], "INVDATE"[DATE,7]	18	SEL$1




N.B. There is some data addition in Machine 1 as compared to Machine 2 and i have not updated machine 2 yet but Machine 1 is still fast with output


Here are table structures which have invno as primary key and as foreign key under the concept of master detail

inv0s
Name	Null?	Type
MYUSER	NOT NULL	VARCHAR2(10)
INVNO	NOT NULL	NUMBER(6)
ADDAID	NOT NULL	NUMBER(3)
CCODE	NOT NULL	NUMBER(3)
INVDATE	NOT NULL	DATE
CORD	NOT NULL	VARCHAR2(1)
REMARKS	 	VARCHAR2(80)
RETURN2	NOT NULL	VARCHAR2(1)
INVCASHD	 	NUMBER(9,2)
INVCASHR	 	NUMBER(9,2)
PREF	 	VARCHAR2(1)
COLLECTORID	 	NUMBER(2)
FLAG2	 	VARCHAR2(1)
FLAG	 	VARCHAR2(1)
PDATE	 	DATE
GCCODE	 	NUMBER(4)
BNO	NOT NULL	VARCHAR2(25)
BAMT	NOT NULL	NUMBER(5)
INVTYPE	 	VARCHAR2(1)
CASH	 	NUMBER(5)
CASH2	 	NUMBER(5)
CNAME	 	VARCHAR2(40)
MYINVNO	 	NUMBER(6)
MYDATE	 	DATE
BORC	NOT NULL	VARCHAR2(1)
BANKCCODE	NOT NULL	NUMBER
NICK	 	VARCHAR2(20)
INVCASHD1	 	NUMBER(8,2)
INVCASHD2	 	NUMBER(8,2)

inv1s


Name	Null?	Type
INVNO	NOT NULL	NUMBER(6)
PCODE	NOT NULL	NUMBER(5)
QTY	NOT NULL	NUMBER(7,2)
QTYTYPE	 	VARCHAR2(1)
UPRICE	NOT NULL	NUMBER(11,4)
SNO	 	NUMBER(3)
LOTNO	 	VARCHAR2(15)
BARCODE	 	VARCHAR2(15)
PNAME	 	VARCHAR2(40)
DISCPCENT	 	NUMBER(5,2)
DISCAMT	 	NUMBER(6,2)
ODISCAMT	 	NUMBER(6,2)

Re: Same Data, Two machines but different timings. Why? [message #634412 is a reply to message #634409] Mon, 09 March 2015 16:32 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
There could be any number of reasons why this timing is different.

1. oracle query plans might differ due to some reason (this was noted by other posters).
2. could be there is a hardware difference (different disk drives for example) (or one has SSD and the other has 7200rpm disks?).
3. might be a hardware problem too. Maybe your I-7 machine has a lot of faults causing re-reads or other such thing, check your system logs.

Since this is an Oracle website, I guess it makes sense to start with the query plan.

[Updated on: Mon, 09 March 2015 16:32]

Report message to a moderator

Re: Same Data, Two machines but different timings. Why? [message #634417 is a reply to message #634412] Tue, 10 March 2015 00:43 Go to previous messageGo to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member

1) Can you suggest me how to improve the above query to have a better plan?
2) Suggest me the hardware factors so that performance can be improved. In fact, i have used many machines to test. The result are somewhat as follow

DELL T-110-ii >> 7 seconds
i-3 >> 12 seconds
i-5 >> 10 seconds
i-7 >> 7 seconds
(an other server machine, spec unknow at the moment) >> 12 seconds

All above had at least 4 GB RAM. Same data. Same query/form.

Needing guidance to improve factors.

Thanks

Re: Same Data, Two machines but different timings. Why? [message #634444 is a reply to message #634417] Tue, 10 March 2015 03:27 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
You need to trace it, then analyse the hardware and the DB parameters and the plans. But in my experience trying to make different kit with different specs perform identically is an exercise in futility at best.
Re: Same Data, Two machines but different timings. Why? [message #634498 is a reply to message #634444] Tue, 10 March 2015 19:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Re: Same Data, Two machines but different timings. Why? [message #634500 is a reply to message #634498] Tue, 10 March 2015 22:39 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I have always loved that word "kit".
Re: Same Data, Two machines but different timings. Why? [message #634502 is a reply to message #634500] Tue, 10 March 2015 22:43 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
SELECT To_char(invdate, 'MON-YYYY') AS mmyy, 
       Nvl(SUM(qty), 0)             AS mqty, 
       Nvl(Min(uprice), 0)          AS pmin, 
       Nvl(Max(uprice), 0)          AS pmax 
FROM   inv1s a, 
       inv0s b 
WHERE  a.invno = b.invno 
       AND return2 = 'N' 
       AND pcode = 13145 
GROUP  BY To_char(invdate, 'MON-YYYY'), 
          To_number(To_char(invdate, 'YYYY')), 
          To_number(To_char(invdate, 'MM')) 
ORDER  BY To_number(To_char(invdate, 'YYYY')) DESC, 
          To_number(To_char(invdate, 'MM')) DESC  


First things first: You need to prefix your columns with the alias of the table they come from. is return2 from A or B? Same for every other column in the query. Where they live makes a difference on potential indexing, if indexing would even help. Please provide a fully qualified set of column names in the code. Then we can discuss more.

Kevin
Previous Topic: Performance Monitor looked ugly
Next Topic: Information to provide when asking for tuning help.
Goto Forum:
  


Current Time: Fri Mar 29 09:15:58 CDT 2024