-
Performance Problem on test enviroment
Hi DBA's
I am having a problem running a sql against my test environment, this sql takes about 10-15 minutes to execute. I did a reorg/runstats against the ts, didn't work. I ran the same sql against my production environment it took 12 sec. can any one tell me why on my test environment this sql is taking over 10 min. to execute.
Any help will be Helpfull,
This is the sql:
SELECT A.EMPLID, A.LAST_NAME, A.FIRST_NAME, A.MIDDLE_NAME, A.DEPTID, A.EMPL_STATUS, B.PHONE_TYPE, B.PHONE
FROM PS_EMPLOYEES A, PS_PERSONAL_PHONE B, PS_PERS_SRCH_QRY1 B1
WHERE B.EMPLID = B1.EMPLID
AND B1.ROWSECCLASS = 'DPCOBALL'
AND ( A.EMPLID = B.EMPLID
AND A.DEPTID = '149000'
AND A.EMPL_STATUS = 'A' )
Thanks
Omar:cool:
-
Re: Performance Problem on test enviroment
What is the explain plan on prod?
What is the explain plan on test?
Are they different?
-
Performance Problem on test enviroment
the explain plan in prod--->don't have any plan & Packages attached
the explain plan in test--->don't have any plan & Packages attached
no difference in clusting, in either enviroments,
could it be the way statistics used for access path to to optermizer?
Omar:cool:
-
if you dont have plan no one here can help you
-
Re: Performance Problem on test enviroment
Originally posted by Sinbad69
could it be the way statistics used for access path to to optermizer?
Almost undoubtedly so, although there could be data volume or instance configuration issues here as well ... but you need as a minimum to get the explain plans from the two different servers.
-
Re: Re: Performance Problem on test enviroment
Originally posted by slimdave
Almost undoubtedly so, although there could be data volume or instance configuration issues here as well ... but you need as a minimum to get the explain plans from the two different servers.
He could also try the dart board of database solutions.
-
How bout the basics boys.
Is the hardware the same?
The amount of data?
What is the current state of the Hyperdiscombobulator?
I remember when this place was cool.
-
I would write the SQL as such.
Code:
SELECT emp.emplid, emp.last_name, emp.first_name,
emp.middle_name, emp.deptid, emp.empl_status,
phone.phone_type, phone.phone
FROM ps_employees emp
INNER JOIN
ps_personal_phone phone
ON emp.emplid = phone.emplid
INNER JOIN
( SELECT emplid
FROM ps_pers_srch_qry1
WHERE rowsecclass = 'DPCOBALL' ) psrch
ON phone.emplid = psrch.emplid
WHERE emp.deptid = '149000'
AND emp.empl_status = 'A';
Could it be that you are missing an index in test? Also you may want to make sure that the test server is not swapping memory to disk.
-
Originally posted by Mr.Hanky
How bout the basics boys.
There is nothing more basic than an explain plan.
Originally posted by Mr.Hanky
Is the hardware the same?
Why would the hardware need to be the same?
Originally posted by Mr.Hanky
The amount of data?
Why do you have to have exactly the same amount of data in test as you do in prod?
Originally posted by Mr.Hanky
What is the current state of the Hyperdiscombobulator?
Is that your specialty???
-
Hun, just check if all the indexes are present or same with
the prod. Then analyze compute statistics the tables and
indexes in ur test db.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|