OraFAQ Forum: Performance Tuning » query slow after 10g upgrade

Home » RDBMS Server » Performance Tuning » query slow after 10g upgrade (10.2.0.3 on hp-ux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query slow after 10g upgrade [message #348847]

Thu, 18 September 2008 01:58

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.

Attachment: awrrpt_1_1103_1104.txt
(Size: 88.19KB, Downloaded 1831 times)

Report message to a moderator

Re: query slow after 10g upgrade [message #348873 is a reply to message #348847]

Thu, 18 September 2008 03:33

Kriptas
Messages: 118
Registered: June 2008

Senior Member

please write down the query

Report message to a moderator

Re: query slow after 10g upgrade [message #348931 is a reply to message #348847]

Thu, 18 September 2008 05:52

rooneyd
Messages: 9
Registered: September 2008

Junior Member

Hi Seema,

How were the statistics gathered, Is the job to automatically gather the statistics enabled. What is your compatible parameter set too?

Report message to a moderator

Re: query slow after 10g upgrade [message #349053 is a reply to message #348931]

Thu, 18 September 2008 13:13

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

Report message to a moderator

Re: query slow after 10g upgrade [message #349330 is a reply to message #348847]

Sat, 20 September 2008 05:26

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

Check the explain plan

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

Report message to a moderator

Re: query slow after 10g upgrade [message #352695 is a reply to message #349330]

Thu, 09 October 2008 01:07

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( Cool

,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

Report message to a moderator

Re: query slow after 10g upgrade [message #352700 is a reply to message #352695]

Thu, 09 October 2008 01:16

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

Report message to a moderator

Re: query slow after 10g upgrade [message #352705 is a reply to message #352700]

Thu, 09 October 2008 01:25

seema.taunk
Messages: 96
Registered: October 2006

Member

Stats are gathered using this script

exec dbms_stats.gather_table_stats (OWNNAME=>'''||a.TABLE_OWNER ||''', TABNAME=>'''||a.TABLE_NAME||''' ,ESTIMAT
E_PERCENT=>20, METHOD_OPT=>''for all columns size 1'', DEGREE=>4,CASCADE=>TRUE);

[Updated on: Thu, 09 October 2008 02:04]

Report message to a moderator

Re: query slow after 10g upgrade [message #352746 is a reply to message #348847]

Thu, 09 October 2008 05:27

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

Report message to a moderator

Re: query slow after 10g upgrade [message #352861 is a reply to message #352746]

Thu, 09 October 2008 23:10

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

Report message to a moderator

Re: query slow after 10g upgrade [message #353172 is a reply to message #348847]

Sun, 12 October 2008 03:38

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Don't measure the timing factor in order to examine whether query is faster/slower. Data might not be cached and hence lots of physical reads and more timing. While you executing query compare logical reads.

Report message to a moderator

Previous Topic:	After Defining Index
Next Topic:	about sql access advisor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 09:39:21 CST 2024