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
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 #348873 is a reply to message #348847] Thu, 18 September 2008 03:33 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
please write down the query
Re: query slow after 10g upgrade [message #348931 is a reply to message #348847] Thu, 18 September 2008 05:52 Go to previous messageGo to next message
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?
Re: query slow after 10g upgrade [message #349053 is a reply to message #348931] Thu, 18 September 2008 13:13 Go to previous messageGo to next message
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 messageGo to next message
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


Re: query slow after 10g upgrade [message #352695 is a reply to message #349330] Thu, 09 October 2008 01:07 Go to previous messageGo to next message
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
Re: query slow after 10g upgrade [message #352700 is a reply to message #352695] Thu, 09 October 2008 01:16 Go to previous messageGo to next message
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 #352705 is a reply to message #352700] Thu, 09 October 2008 01:25 Go to previous messageGo to next message
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 messageGo to next message
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
Re: query slow after 10g upgrade [message #353172 is a reply to message #348847] Sun, 12 October 2008 03:38 Go to previous message
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.
Previous Topic: After Defining Index
Next Topic: about sql access advisor
Goto Forum:
  


Current Time: Tue Dec 06 04:29:46 CST 2016

Total time taken to generate the page: 0.15108 seconds