Solved

Oracle 10g Performance problem with unique indexes

Posted on 2006-07-19
13
1,363 Views
Last Modified: 2008-01-09
After experiencing a data load problem, I began testing to identify where the problem existed.

The database configuration is Oracle 10g, two-node RAC, Linux 4 64-bit.

                    create table TEST (field1 varchar2(10), field2 varchar2(10))
                    insert into TEST values ('test', 'test')
                                                                                 .........The insert happens immediately.

                   insert into TEST values ('test1', 'test')
                   insert into TEST values ('test2', 'test')
                                                                                 .........Again, the insert happens immediately.

                   create index TEST1 on test (field1)
                   insert into TEST values ('test1', 'test')
                                                                                 .........the insert happens immediately with this non-unique index.


                   create UNIQUE index TEST2 on test (field1)
                   insert into TEST values ('test1', 'test')
                                                                                 .........This time the insert attempt takes 13 seconds
                                                                                          to identify that it is a duplicate row.

In a table of only 2 fields and less than a half dozens rows, 13 seconds is a LONG time for the UNIQUE CONSTRAINT error to be displayed.  My original insert was attempting to insert several hundred rows into a table that has several thousand rows.  This script ran for hours without inserting a single row.

Any suggestions on what parameters could be effecting constraints? index lookups?  etc.   How can this database be taking this long to look at indexes and identify duplicate rows?

Statistics are current on both tables and indexes.

Attempted shutting down one node to eliminate possibility of communication problems between the nodes.  No impact.

attempted/validated these parameters:
_optimizer_cost_based_transformation=off
parallel_min_servers = 2
parallel_max_servers= 135
pga_aggregate_target = 800mb
workarea_size_policy = AUTO

Any suggestions???



0
Comment
Question by:LauraGanino
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17142824
Do your indexes and tables have separate tablespaces?
0
 
LVL 14

Assisted Solution

by:sathyagiri
sathyagiri earned 50 total points
ID: 17142845
Also check your trace files and log files to check if there are any errors being reported
0
 
LVL 18

Assisted Solution

by:rbrooker
rbrooker earned 50 total points
ID: 17142951
Hi,

for the 3 inserts, generate an explain plan for each of them making sure you analyze the tables / indexes prior to running each test.
it could be that the explain plan for the insert using the unique index is waaaaay different from the explain plans of the other two.

good luck.

:)
0
 

Author Comment

by:LauraGanino
ID: 17142958
The indexes and tables are in separate tablespaces.  But, even so, with only 3 or 4 rows in the table, I can't imagine that the problem is the relative location of the tables/indexes.  (yes, the large table and its indexes are also in separate tablespaces).  Also, since the non-unique index doesn't cause any delay, I'm leaning toward the problem being in the "unique" constraint.  The system tablespace is also separate.  The entire database is on a SAN and the initial load (before indexes) was fast, so I don't think there's an I/O problem.  The index creation was a little slow, but the PGA_AGGREAGATE_TARGET size was initially 100MB, so it was increased to improve the performance of the index creation and queries with table joins.

There are no errors, just long running constraint checks.

The DBA suggested that the problem might be the communication between the two nodes.  If the node I'm connected to needs to confirm that the buffer on the other node does not have any pending transactions, that could account for the delay with "unique" indexes.  But, I would think that shutting down the other node would eliminate that possibility.

Any other suggestions???
0
 
LVL 47

Expert Comment

by:schwertner
ID: 17144282
Increase the TEMPORARY tablespace
and the sort_area size.

INIQUE uses sort operation to create the index.
The biger the sort-area-size is the less will be the
physical reads/writes to disk.
0
 

Author Comment

by:LauraGanino
ID: 17144952
In Oracle 10g, the SORT_AREA size is controlled by the PGA_AGGREGATE_TARGET.  We increased this parameter from 100MB to 800MB and enabled the WORKAREA_SIZE_POLICY.   Will changes to the SORT_AREA_SIZE parameter be considered or ignored with WORKAREA_SIZE_POLICY set to AUTO?

I'm an "old-school" person myself, and have always preferred manually setting the parameters for tuning, but have been advised to let 10g handle it through the PGA_AGGREGATE_TARGET.  Does anyone have any experience with letting Oracle handle the memory allocation?  Is 10g really as good as Oracle says it is? and if so, what's the trick?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Accepted Solution

by:
schwertner earned 400 total points
ID: 17145262
Also will be good to increase LARGE_POOL. See this:

In the manual memory management model, the DBA needs to calibrate the individual components of the SGA. To let Oracle automatically manage the memory allocations to the individual components of the SGA, he needs to set the new initialization parameter SGA_TARGET. By default, the SGA_TARGET parameter is set to zero. Once he provides a nonzero value for the SGA_TARGET parameter, Automatic Shared Memory Management is enabled.  
Automatic Shared Memory Management provides the following benefits:
■ Less chance of running out of shared pool memory
■ Optimal use of available memory
■ Significant performance improvement because memory allocation keeps step
with fluctuations in the database workload
In order to use Automatic Shared Memory Management, the DBA should first set the initialization parameter STATISTICS_LEVEL to its default value of TYPICAL or ALL. Oracle doesn’t populate the V$SHARED_POOL_ADVICE and the V$DB_CACHE_ADVICE views if the STATISTICS_LEVEL parameter is set to BASIC.   The new Oracle Database 10g background process MMAN performs all the memory resizing necessary for the Automatic Shared Memory Management feature. The MMAN process constantly monitors the workload of the database and adjusts the size of the individual memory components accordingly.
Under Automatic Shared Memory Management, the database determines the ideal allocations of memory for the four automatic components. It does this with the help of internal algorithms that continually analyze the nature of the database workload. After you first incorporate Automatic Shared Memory Management, Oracle doesn’t know the ideal levels for these components. It arrives at these after a period of gradual calibration based on the nature of your workload. Under Automatic Shared Memory Management, the database manages the following four major components of the SGA, also known as the auto-tuned SGA parameters:
■ Buffer cache (DB_CACHE_SIZE)
■ Shared pool (SHARED_POOL_SIZE)
■ Large pool (LARGE_POOL_SIZE)
■ Java pool (JAVA_POOL_SIZE)



Yes PGA_AGRAGATE_TARGET should turn on automatic management (up to the popinted RAM amount!).
0
 
LVL 47

Expert Comment

by:schwertner
ID: 17145277
Also do not forget the statistics - CBO will run bad without fresh statistics!
0
 

Author Comment

by:LauraGanino
ID: 17152882
Thank you all for the suggestions and support.  Turns out that apparently Oracle is aware of this problem and now has a patch available.  It appears to be a problem only on Oracle 10.2.0.1 on Linux 4 when attempting to insert rows with duplicate keys into tables with unique indexes or attempting to update rows that would result in duplicate keys on tables with unique indexes.  How could THAT one be overlooked by QA???
0
 
LVL 47

Expert Comment

by:schwertner
ID: 17153006
Upgrade the Oracle version to 10.2.0.2
Oracle is very complex product and nobody is perfect.
0
 

Author Comment

by:LauraGanino
ID: 17153209
Yes, it is, and, it's still my favorite database platform. Also, as a previous QA analyst, I'm all too aware that "nobody is perfect" -- I've had my share of oversights.  :-)

My client is planning the upgrade but encountering some platform/hardware/database compatibility support "certification" issues.  

Are you aware of any REAL problems with Linux 4.3 on Dell Intel or running Oracle 10.2.0.2 on RH4.2?
Apparently Dell isn't officially supporting the RH4.3/Intel combo yet and Oracle isn't officially supporting the 10.2.0.2/RH4.2 combo yet.
   
...Always a challenge...
0
 
LVL 47

Expert Comment

by:schwertner
ID: 17153420
This is the certification matrix from Oracle.
I searched Google and found problems only when using 64 bit RH4.

Certify - Additional Info Oracle Database - Enterprise Edition Version 10gR2 On Linux (x86)
 
Operating System: Linux (x86) Version Red Hat Enterprise AS/ES 4
Oracle Database - Enterprise Edition Version 10gR2
N/A Version N/A
Status: Certified
Product Version Note:
None available for this product.
Certification Note:
·      The OTN online Release Notes have been updated with additional RH 4.0 and SLES9 certification information as of September 9th, 2005.
Existing patch sets:
     10.2.0.2
0
 
LVL 47

Expert Comment

by:schwertner
ID: 17153451
I found the correct entry for your case in the certification matrix:

Certify - Additional Info Oracle Database - Enterprise Edition Version 10gR2 64-bit On Linux x86-64 (AMD64/EM64T)
 
Operating System: Linux x86-64 (AMD64/EM64T) Version Red Hat Enterprise AS/ES 4
Oracle Database - Enterprise Edition Version 10gR2 64-bit
N/A Version N/A
Status: Certified
Product Version Note:
None available for this product.
Certification Note:
·      binutils on RHEL4 Update 1 is not available.
·      Customers need to apply an hotfix available at: http://oss.oracle.com/projects/compat-oracle/files/RedHat/ choose binutils-2.15.92.0.2-13.0.0.0.2.x86_64.rpm
Existing patch sets:
     10.2.0.2
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now