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  |
 |
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 #634409 is a reply to message #634408] |
Mon, 09 March 2015 14:30  |
|
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 |
 |
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 #634502 is a reply to message #634500] |
Tue, 10 March 2015 22:43 |
|
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
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 13:03:24 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
