Home » RDBMS Server » Performance Tuning » query slow after 10g upgrade (10.2.0.3 on hp-ux)
query slow after 10g upgrade [message #348847] |
Thu, 18 September 2008 01:58 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
seema.taunk
Messages: 96 Registered: October 2006
|
Member |
|
|
Hi,
After upgradation from oracle 9i to oracle 10g, query become slow. Preiously it was taking 30 mins , now it is taking 2.5 hrs to complete. We have rebuilt indexes & done table reorganization also , after which it took 2 hrs to complete.
Optimizer mode is choose & statatistics were latest.
Previous optimizer mode was RULE.
This might be reason, but duration is much more.
Any suggestion?
Attached awr report.
|
|
|
|
|
Re: query slow after 10g upgrade [message #349053 is a reply to message #348931] |
Thu, 18 September 2008 13:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
macdba
Messages: 27 Registered: May 2005 Location: US
|
Junior Member |
|
|
Check the explain plan and compare that with the old one. Due to lot of changes in Optimizer behavior in 10g, queries use different execution plan and thus causes slowness.
regards
--Mak
|
|
|
Re: query slow after 10g upgrade [message #349330 is a reply to message #348847] |
Sat, 20 September 2008 05:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
harrysmall3
Messages: 109 Registered: April 2008 Location: Massachusetts
|
Senior Member |
|
|
Hi Seema,
Definitely the next step is as advised; concurring with Kriptas
Quote: | please write down the query
|
and Mak
Please provide that information so all can continue to contribute suggestions that build to isolate a solution.
As Mak noted
Quote: |
Due to lot of changes in Optimizer behavior in 10g, queries use different execution plan
|
My perspective,given the scope of the enviroment change, is that the issue merits a "divide and conquer" approach:
I would want to isolate where the 'extra' query time is being spent- what part / component is taking the bulk of the time. Next, determine how that time is being spent (eg, I/O, latch Waits,cpu,etc). And then, figure out possible ways to improve the most time consuming operation.
I do have one adhoc question - is this query the only one
in your system that has been adversely affected?
Please provide table and index descriptions for query datasources as well.
Eager to assist,
Harry
|
|
|
Re: query slow after 10g upgrade [message #352695 is a reply to message #349330] |
Thu, 09 October 2008 01:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
seema.taunk
Messages: 96 Registered: October 2006
|
Member |
|
|
Dear all,
Please find the query attached
DECLARE SEL10_GDET
CURSOR FOR
SELECT GDET_REFER,
GDET_SITE,
GDET_YEAR,
GDET_ENTITY,
GDET_UNIT,
GDET_CURR,
GDET_ACCT,
GDET_VEND,
GDET_INVC,
GDET_DESC,
GDET_AMT,
GDET_PER,
GDET_DOCN,
GDET_REV,
GDET_3LA,
GDET_SUB_ACC,
*III* GDET_DATE,
*OOO* GDET_DATE,
*SSS* CONVERT (CHAR( ,GDET_DATE,112),
GDET_CODE,
GDET_CIND,
GDET_DIND,
GDET_RECON,
GDET_EXPN
FROM F1_GDET
WHERE (GDET_SITE = :K-GDET-SITE)
AND (GDET_YEAR = :K-GDET-YEAR)
AND (GDET_ACCT LIKE :K-GDET-ACCT)
AND (GDET_VEND LIKE :K-GDET-VEND)
END-EXEC.
Stats are gathered automatically using script daily
This is the only query which is adversely afftected.
Explain plan before & after 10g upgrade is same .
Plan Statistics DB/Inst: FONESBH/fonesbh Snaps: 1578-1589
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 6,774,700 6,774,700.3 76.2
CPU Time (ms) 1,512,711 1,512,711.3 53.2
Executions 1 N/A N/A
Buffer Gets ########## 19,137,392.0 49.9
Disk Reads 2,033,596 2,033,596.0 57.9
Parse Calls 1 1.0 0.0
Rows ########## 15,001,239.0 N/A
User I/O Wait Time (ms) 6,513,127 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version Count 3 N/A N/A
Sharable Mem(KB) 91 N/A N/A
-------------------------------------------------------------
Execution Plan
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 223K(100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| F1_GDET | 38303 | 4226K| 223K (1)| 00:44:39 |
| 2 | INDEX RANGE SCAN | AK_2_GDET | 3830K| | 50157 (1)| 00:10:02 |
-----------------------------------------------------------------------------------------
Thanks
|
|
|
Re: query slow after 10g upgrade [message #352700 is a reply to message #352695] |
Thu, 09 October 2008 01:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
seema.taunk
Messages: 96 Registered: October 2006
|
Member |
|
|
Size of table is 10 GB
& indexes are as below
INDEX_NAME COLUMN_NAME COLUMN_POSITION
--------------- --------------- ---------------
AK_0_GDET GDET_SITE 1
AK_0_GDET GDET_YEAR 2
AK_0_GDET GDET_ENTITY 3
AK_0_GDET GDET_UNIT 4
AK_0_GDET GDET_CURR 5
AK_0_GDET GDET_ACCT 6
AK_0_GDET GDET_REFER 7
AK_1_GDET GDET_SITE 1
AK_1_GDET GDET_VEND 2
AK_1_GDET GDET_REFER 3
AK_2_GDET GDET_SITE 1
AK_2_GDET GDET_VEND 2
AK_2_GDET GDET_INVC 3
AK_2_GDET GDET_REFER 4
F1_GDETPK GDET_REFER 1
|
|
|
|
Re: query slow after 10g upgrade [message #352746 is a reply to message #348847] |
Thu, 09 October 2008 05:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
Hi.
1. What are the data types of GDET_ACCT andd GDET_VEND columns?
-- If these are NUMBERs then optimizer can not use the index because of LIKE.
2. Can you create an index:
CREATE INDEX ... ON F1_GDET (
GDET_SITE, GDET_YEAR, GDET_ACCT, GDET_VEND )
NOLOGGING COMPUTE STATISTICS;
HTH.
Michael
|
|
|
Re: query slow after 10g upgrade [message #352861 is a reply to message #352746] |
Thu, 09 October 2008 23:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
seema.taunk
Messages: 96 Registered: October 2006
|
Member |
|
|
Thanks for your reply.
Datatypes of columns are:
GDET_ACCT -> NOT NULL VARCHAR2(10)
GDET_VEND -> NOT NULL VARCHAR2(10)
Optimizer is using AK_2_GDET index. If it uses AK_0_GDET index, time might reduce, but for that we have to use hint, & application will change. We don't want to do this.
Any suggestions? Thanks
|
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:55:58 CDT 2024
|