Performance Degrading After 11G Upgrade?

May 9, 2012

We have a strange performance issue in our production database.

After migrating our production database from 10g (R2) to 11g (R2) we started seeing major performance issues in the I/O's.

we do have baseline calculation process which runs past midnight every day. Before 11G migration it used to complete in less than 1 Hour, since migration (3 days ago) it started taking more than 5 hours. It just showing major I/O activity (db sequential reads) when the job was running but nothing else. The I/O was always because of the index range scan over a 1.8 billion rows table. By using the Hints we forced Oracle to use the same explain plan it was using on 10G. The query basically index range scans last 60 days worth of data for a specific person out of 1.8 billion rows. Indexes are created with date column in descending order to make the index scans faster while searching for latest data.

when i compared the historical AWR from 10g to 11G I have noticed for same process number of physical reads(sequential) are more in 11G than in 10G. Even physical I/O waits are also more comparatively in 11G because of the more reads i guess. I am thinking may be Oracle scanning index more than what it needs to though we have Histograms on the indexed columns.

My question is , are there any other factors/parameters that we can manipulate to cheat Oracle and make it do fewer physical reads (required) for both both index and table.

Does Histograms only selecting correct index or do they dictating how much percentage of index to be scanned for the current operation also?

Again indexes are same and explain plan is exactly same but more physical I/O in 11G than in 10G. Way to minimize the I/O scans?

View 3 Replies


ADVERTISEMENT

Oracle 10g To 11g Upgrade Performance

Oct 25, 2012

After upgrade 10g to 11g, the below sql is not working. I have issue with connect by, if we use it with subquery it will hang.

select item_code
from bom_list_pos
where ln_id in (select ln_id
from bom_list_nodes
start with ln_id IN (select ln_id from bom_used_work_pack where rownum =1)
connect by prior ln_id = parent_ln_id)

I ran 10g, able to get it less than minute, but 11g hang. below is explain plan.

11g explain plan
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 209 | 148M (1)|494:23:18 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | BOM_LIST_POS | 810K| 8711K| 7484 (1)| 00:01:30 |
|* 3 | FILTER | | | | | |
|* 4 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | |
| 5 | TABLE ACCESS FULL | BOM_LIST_NODES | 46041 | 449K| 241 (1)| 00:00:03 |
|* 6 | FILTER | | | | | |
|* 7 | COUNT STOPKEY | | | | | |
| 8 | INDEX FAST FULL SCAN | UWPD_LN_FK_I | 1 | 5 | 2 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "M_SYS"."BOM_LIST_NODES" "SYS_ALIAS_1" WHERE "LN_ID"=:B1 START
WITH EXISTS (<not feasible>)
3 - filter("LN_ID"=:B1)
4 - access("PARENT_LN_ID"=PRIOR "LN_ID")
filter( EXISTS (<not feasible>)
6 - filter("LN_ID"=:B1)
7 - filter(ROWNUM=1)

-------------------
10g explain plan
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 440 | 9240 | 27 (4)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | BOM_LIST_POS | 88 | 704 | 6 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 440 | 9240 | 27 (4)| 00:00:01 |
| 3 | VIEW | VW_NSO_2 | 5 | 65 | 2 (0)| 00:00:01 |
| 4 | HASH UNIQUE | | 5 | 30 | | |
|* 5 | CONNECT BY WITH FILTERING | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | BOM_LIST_NODES | | | | |
| 7 | NESTED LOOPS | | 1 | 16 | 1 (0)| 00:00:01 |
| 8 | VIEW | VW_NSO_1 | 1 | 13 | 1 (0)| 00:00:01 |
|* 9 | COUNT STOPKEY | | | | | |
| 10 | INDEX FULL SCAN | UWPD_LN_FK_I | 122 | 366 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | LN_PK | 783 | 2349 | 0 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | | | | |
| 13 | CONNECT BY PUMP | | | | | |
| 14 | TABLE ACCESS BY INDEX ROWID| BOM_LIST_NODES | 5 | 30 | 2 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | LN_LN_FK_I | 5 | | 1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | LP_LN_FK_I | 88 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("PARENT_LN_ID"=PRIOR "LN_ID")
9 - filter(ROWNUM=1)
11 - access("LN_ID"="$nso_col_1")
15 - access("PARENT_LN_ID"=PRIOR "LN_ID")
16 - access("LN_ID"="$nso_col_1")

View 3 Replies View Related

Performance Tuning :: Histograms During DB Upgrade

Jun 29, 2013

We are currently migrated our database to Oracle 11g i.e 11.2.0.3 (from 10.2.0.3). Now we are facing application slowness on 11g, we found the issue is with missing histogram or some unnecessary histograms in place. Given below are my details.

When we were in 10g, we were having schema level gather stats job with 'METHOD_OPT' as 'AUTO' running each weekend and gathering only STALE stats. And we had faced some performance issue due to optimizer following wrong path of execution due to some missing histograms and some extras too.So we had done skew ness(for each column in table in database) analysis on DB level using 'WIDTH_BUCKET' function manually on each column,and we deleted no of unnecessary histograms and created some on 10g DB. And updated the default schema level gather stats job with 'METHOD_OPT' as 'FOR ALL COLUMN SIZE REPEAT'. And we were getting stable environment.

Now we just migrated and its done by our techops(Oracle) team, i am confused how the histograms has been changed/modified for columns during migration, causing headache now. So now below are my plans and fears associated with them.

1. If i will make the histograms exactly same in both the environments, i.e. will delete histograms which are present in new DB(11g) but were not in old DB(10g).and creating those which were there in old DB(10g) but now not in new DB(11g). My question is , will it be safe to do this or it may impact negatively with 11g optimizers features in place?

2. Or should i try easier option once by making 'METHOD_OPT' for the schema level stats job as 'AUTO' on 11g and try once, as because i believe 11g is more stable and optimizer is equipped with more intelligence in case of stats gathering using AUTO?

View 3 Replies View Related

Performance Tuning :: Change In Explain Plan Due To Oracle Upgrade

Dec 14, 2010

I need to warn readers that I am not a DBA but am heavily involved in application development. Whatever I know about database tuning is whatever I've managed to pick up via self-learning, and I must admit that the sum total of my knowledge isn't a lot.

Anyway, our "DBAs" recently did an upgrade to our 10g database, going from version 10.2.0.2.0 to 10.2.0.4.0. Immediately after the upgrade, a particular query has started to under-perform. The query itself was not altered in any way during the upgrade.

We have two explain plans for the query, a before and an after plan. The two plans are similar but not identical. The plans are too massive to post here, so I hope the following synopsis of the differences will do.

The 10.2.0.2.0 plan:

shows a HASH GROUP BY
has a TempSpc column in the explain plan
shows a particular table (EMP_HISTORY) as having ~1700 rows

The 10.2.0.4.0 plan:

shows SORT GROUP BY instead of HASH GROUP BY does not show a TempSpc column in the explain plan shows the EMP_HISTORY table as having only 25 rows

Other than these points, no other discernible differences can be noted. I'm wondering what would cause HASH to change to SORT. I'm told that stats are up-to-date.

View 5 Replies View Related

Upgrade :: Cross Platform Upgrade From Single Instance To RAC

Jun 26, 2013

I'm planning to upgrade a small database (~150GB) from 10.2.0.3 on windows 2003 23bit to 11.2.0.3 RAC on Linux 5.8.The database contains oracle spatial too. A suitable method and link to document to be followed. 

View 2 Replies View Related

Upgrade :: ORA-00904 / OBJ$EDITION / Invalid Identifier After Upgrade To 11.2.0.3

Nov 19, 2012

I recently performed an upgrade on a new server from oracle 10gr2 to oracle 11gr2 (11.2.0.3).

I take the rman backup from oracle 10g server and restore it on new server where I installed oracle 11gr2.

But on my previous oracle 10gr2 server I enabled the auditing. After doing successful upgrade now when I try to login with any user except sys I receive the following error:

SQL> conn scott/tiger
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "OBJ$EDITION": invalid identifier
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "OBJ$EDITION": invalid identifier

I got the workaround by setting the parameter audit_trail=FALSE (Previous value was DB_EXTENDED) .But I want my auditing to be enabled as per y requirements.

View 1 Replies View Related

Upgrade :: Upgrade Patch For Enterprise Versus Standard 11.2.0.1

Sep 22, 2012

is the upgrade patch the same for standard edition and enterprise edition or there are two separate patches?

View 5 Replies View Related

Upgrade :: Reference Note IDs For Database Upgrade From 11.2.0.2. To 11.2.0.3.2

Oct 22, 2012

find reference note IDs for DB upgrade from 11.2.0.2 to 11.2.0.3.2, as I am finding only Exadata which I don't want but I want to find for Ebiz database, on OS - Solaris 10 9/10 s10s_u9wos_14a SPARC.

View 1 Replies View Related

Upgrade :: Oracle 11.2.0.3.0 Database Upgrade Changes DATA_PUMP_DIR

Jun 6, 2012

Along with existing RMAN backups we do Exports - of our DB using and OS User and Oracle Wallet.Of the DB's we have upgraded the Data Pump Directory

Select * from dba_directories; (there are other commands to get this info as well).

I captured screens from the DBUA upgrades, but did not see an option to change this information.Is there a way to feed this information to the install moving forward. IE, ./DBUA -silent ?

Also, anyone tracked the percentage of storage increase from 10.2/11.1 to 11.2.

View 4 Replies View Related

Upgrade :: SYS Table Very Slow After 11g Database Upgrade?

Nov 7, 2012

All my sys tables are very slow after my database upgrade from 10.2.0.4 to 11.2.0.3 on AIX 6.1

For example
select * from ALL_TAB_COLUMNS; -- taking 19 seconds in 11.2.0.3 and few millisec in 10.2.0.4

I have deleted and updated fixed and dictionary table statistics , till I facing this issue

View 5 Replies View Related

Upgrade :: Patchset Number To Upgrade Oracle

Jun 14, 2012

Know the Patch set number to upgrade a Oracle 10.2.0.1 to 10.2.0.5 on Solaris 10 Spark 64bits?

View 1 Replies View Related

Upgrade :: Oracle Upgrade 11.1.0.7 To 11.2.0.3 - Compatible

Jul 19, 2013

I am going to upgrade database from 11.1.0.7 to 11.2.0.3

1) If compatible is set to 10.2.0 in 11.2.0.3, will it work ?
2) If compatible set to maximum level, will it affect our application ?
3) Whether any code related problem occurred after upgrading like PL/SQL codes ?

View 6 Replies View Related

Upgrade :: Oracle Database Upgrade

Mar 13, 2013

We are planning to upgrade our database from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi to Oracle Standard Edition 11g . We also have oracle apex installed on Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi database with oracle apex 3.1

No our plan to upgrade the database and oracle apex to 4.2. Since Oracle Enterprise Edition is licensing is very expensive we though of buying standard edition and upgrade to this version.

can we upgrade the oracle database from enterprise edition to latest standard edition?

View 3 Replies View Related

Upgrade :: Database Upgrade From 9.2.0 To 11gr2

Aug 6, 2012

Iam doing some oracle database upgrade from 9.2.0 to 11gr2 . is it possible to upgrade directly from this 9i version to 11gr2 .

Version : 9.2.0 to 11gr2

View 4 Replies View Related

Upgrade :: Compatible Parameter During Upgrade

May 21, 2013

Why COMPATIBLE Parameter is important to be at a certain value before you UPGRADE(10.2.0.4 to 11.2.0.3) to 11g ?

View 2 Replies View Related

Upgrade :: Upgrade 11.2.0.2 To 11.2.0.3 On Linux Environment?

Mar 23, 2013

I try to upgrade 11.2.0.2 to 11.2.0.3 on Linux environment. I have to apply patch P1253900 before the upgrade per oracle doc.

however I got following conflict:

Verifying environment and performing prerequisite checks...
Checking skip_duplicate
Checking skip_subset

Conflicts/Supersets for each patch are:

Patch : 12539000
Conflict with 12827726
Conflict details:
/u01/app/grid/product/11.2.0/grid/lib/libserver11.a:/ksxp.o

Following patches have conflicts: [   12827726   12539000 ] Refer to My Oracle Support Note 1299688.1 for instructions on resolving patch conflicts.

I checked above oracle note, and did not say any resolution.

View 1 Replies View Related

Upgrade :: 10.2.0.1 To 10.2.0.4 Upgrade 32bit

Jun 24, 2012

I am trying to upgrade the database 32bit from 10.2.0.1 to 10.2.0.4 version in lunux 32 bit . I faced the version incompatibilty error during patchset installation and hence ran the patchset installation with -ignoresysprereqs option.Now during the patchset installation i encountered the below error in in the install logfile.

INFO: Start output from spawned process:
INFO: ----------------------------------
INFO:
INFO: /u01/app/oracle/product/10.2.0/bin/genclntsh
INFO: genclntsh: genclntsh: Could not locate /u01/app/oracle/product/10.2.0/network/admin/shrept.lst
[code]....

Need certified os and versions for oracle db 10gr2

View 2 Replies View Related

Upgrade :: Database Upgrade From 11.2.0.1 To 11.2.0.3

Jan 2, 2013

After many tries of upgrade'ing oracle I decided to make backup of my database and then remove 11.2.0.1 and install 11.2.0.3 to recover db. Unfortunately I am not sure how should I perform restoration.I backed up data by calling RMAN> backup database include current controlfile;

After that I moved files from fast_recovery_area and cleared my machine from current oracle release.I also did a copy of directories:

ORACLE_HOMEdbs
ORACLE_HOME
etworkadmin
ORACLE_HOMEowbinadmin
ORACLE_HOMEhostname_dbname
ORACLE_HOMEoc4jj2eeOC4J_DBConsole_hostname_dbname

View 8 Replies View Related

Upgrade :: Migration / Upgrade 10g / R2 To 11g / 2

Jul 6, 2012

I am looking for for exact steps for migration. The important question ist:

-- If we migrate from 10g to 11g, do we need addition (more) CPU's as currently we have?
-- Do wee need more powerful HW.

View 2 Replies View Related

Upgrade :: Oracle 10g To 11g Upgrade

Feb 21, 2013

I am trying to come up with a plan for an upgrade that is needed for a server I maintain. It is a Windows 2003 32bit running Oracle 10.2.0.3 on old Hardware. It also has two obscure 3rd party applications that are running on it that directly access the database. These applications are supported by off site consultants.

My initial plan was to Create a Windows 2008 R2 Virtual Server and install the same version of Oracle 10.2.0.3. Using Rman clone the database to the new server. Have the consultants come in and get the applications working. Once everything in the new environment seems to be working fine, run RMAN again and reclone the database to have all the latest data. Then at a later time upgrade the database to 11g 32bit. Virtually no downtime and we could spend all the time we needed getting the applications working and testing the new environment.

The plan is dead right of the bat though because I realize 10.2.0.3 is not supported by Windows 2008 R2. I really did not want to add an Oracle DB upgrade into the mix at the same time. Just because their are so many changes from the old environment to the new that I want to break this down into manageable chunks. And I can maybe get by with 1 day of down time.

So now I am looking at installing 11g on my Virtual Server, Clone the database, upgrade the database, have the consultants come in and get the applications working. All the while we are down. If we run into any problems, which you always do, it just completely blows the schedule.

View 1 Replies View Related

Upgrade :: Oracle Upgrade 10.2.0.5 Non-ASM 11.2.0.3 ASM?

Jun 29, 2012

I need to upgrade our current system 10.2.0.5 (non Asm) to 11.2.0.3(ASM) on the same host.

So a few options : install ASM on current 10.2.0.5, create volumes etc and shift DB into ASM

upgrade to 11.2.0.3 GI - standalone

upgrade DB to 11.2.0.3

install 11.2.0.3 GI, configure ASM and run against 10.2.0.5

DB upgrade DB to 11.2.0.3

The DB upgrade has to come last I've been told.

View 1 Replies View Related

Performance Tuning :: How Length Of Column Width Effects Index Performance

Sep 30, 2010

How the length of column width effects index performance?

For example if i had IOT table emp_iot with columns:
(id number,
job varchar2(20),
time date,
plan number)

Table key consist of(id, job, time)

Column JOB has fixed list of distinct values ('ANALYST', 'NIGHT_WORKED', etc...).

What performance increase i could expect if in column "job" i would store not names but concrete numbers identifying job names.
For e.g. i would store "1" instead 'ANALYST' and "2" instead 'NIGHT_WORKED'.

View 24 Replies View Related

Performance Tuning :: Fragmentation Can Reduce Performance In Query Times

Jun 16, 2010

I have a question about database fragmentation.I know that fragmentation can reduce performance in query times. The blocks are distributed in many extents and scans process takes a long time. Oracle engine have to locate the address of the next extent..

I want to know if there is any system view in which you can check if your table or index has high fragmentation. If it's needed I will have to re-create, move or rebulid the table or index, but before I want to know if the degree of fragmentation is high.

Any useful script or query to do this, any interesting oracle system view?

View 2 Replies View Related

Performance Tuning :: Performance Standard Edition Without Partitioning?

Jun 16, 2011

How many records could I have in a single table without performance degradation with Standard Edition without partitioning with cutting-edge server (8 or 12 cores, 72 GB RAM, FC 4 Gbit, etc...) and good storage?

300 Millions in only one table with 500K transactions / day is too much?

Simple database with simple schema.

How many records begin to be too many?

View 2 Replies View Related

Performance Tuning :: Procedure Performance On New Database Import?

Nov 15, 2010

Testing our 9i to 11g upgrade, we've imported the entire DB into the new machine.We've found that certain procedures are really suffering performance problems. BUT, we've also found, that if we check out a production copy of the procedure from our source code control, and reinstall it, the performance issue goes away. Just alter the procedure and recompiling does NOT work.

The new machine where the 11g database exists is slightly different than the source, but it's not like we have this problem with every procedure. It's only a couple.

any possible reason that we'd have to re-install a procedure to correct a performance problem?

View 13 Replies View Related

Performance Tuning :: Checking Delete Performance In Package

Apr 12, 2013

I need to check the package performance and need to improve the package performance.

1. how to check the package performance(each and every statement in the package)?
2. In the package using the delete statement to delete all records and observed that delete is taking long time to delete all the records in the table(Table records 7000000). This table is like staging table.Daily need to clean the data before inserting the data into it. what can I use instead of Delete.

View 13 Replies View Related

Performance Tuning :: Query Performance Gain Using Statistics?

Aug 9, 2010

Somewhere I read that we should not use hints in Oracle production environments, but we can use hints in the development environment and on achieving the desired execution plan we can adjust the 'statistics' to follow that plan without hints.

Q1. If it is true what statistics do we adjust for influencing the execution plan and how?

For example, I have the following simple query:

select e.empid, e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;

emp.empid, emp.deptno and dep.deptno columns have indexes and the tables have the standard structure as found in the basic oracle examples.

If I look at the execution plan of the above query then I see that the driving table is empand the driven table is dept.Also the type of join that is taking place is 'Nested Loop'.

Questions: With respect to the above query,
Q 2. If I want to make dept the driving table and emp the driven table then how can I adjust the statistics to achieve that?
Q 3. If I want to use hash join instead of a nested loop join then then how can I adjust the statistics to achieve that?

I can put the ordered and the use_hash hint to effect this but again I have heard that altering statistics is a more robust way to control an execution plan as compared to hints.

View 6 Replies View Related

Performance Tuning :: How To Improve The Performance Of Export Job (expdp)

Dec 6, 2011

I have an issue with export(expdp).

When i exporting an user using expdp utility, the load the on the server is going up-to 5. The size of the database is 180GB. Below is the command that i use for export.

expdp sys/xxxx directory=dbpdump dumpfile=expdp_trk_backup.dmp logfile=expdp_trk_backup.log exclude=statistics schemas=trk

Do i need any look into any memory parameters for this?

View 1 Replies View Related

Performance Tuning :: DECODE In WHERE CLAUSE Performance?

Oct 17, 2011

The following query gets input parameter from the Front End application, which User queries to get Reports.There are many drop down boxes like LOB, FAMILY, BRAND etc., The user may or may not select values from drop down boxes.

If the user select any one or more values ( against each drop down box) it has to fetch all matching values from DB. If the user does'nt select any values it has to fetch all the records, in this case application will send a value 'DEFAULT' (which is not a value in DB ) so that the DB will fetch all the records.

For getting this I wrote a query like below using DECODE, which colleague suggested that will hamper performance.From the below query all the variables V_ are defined in procedure which gets the values selected by user as a comma separated string here V_SELLOB and LOB_DESC is column in DB.

DECODE (V_SELLOB, 'DEFAULT', V_SELLOB, LOB_DESC) IN
OPEN v_refcursor FOR
SELECT /*+ FULL(a) PARALLEL(a, 5) */
*
FROM items a
WHERE a.sku_status = 'A'

[code]...

View 9 Replies View Related

Performance Tuning :: Same Data But Different Performance Results

Sep 3, 2010

what the principal things to look at when we have for the same query different performance results are?I have 2 different bases: the plan and data are the same but performance results are very differents.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved