Home » RDBMS Server » Performance Tuning » Primary key index not working proper after import (oracle 8.1.7.0.0)
Primary key index not working proper after import [message #379252] Mon, 05 January 2009 12:26 Go to next message
ajaysharma2907
Messages: 14
Registered: June 2008
Location: Delhi
Junior Member

Hi Gurus,

I have a strange problem while importing oracle data.
My production server has crashed stating ora-600 errors.I had the export of the only schema we use.There are two problems i face....

1. While import-ing the data, it takes around 8 hours to import the data for 5.4gb dump for the database of around 25GB.
There are primarily two tables in the database which have around 3700,000 and 6700,000 records respectively.

2.My import terminated with some warnings.
Now when i run certain queries that should use the primary key index they make FULL TABLE SCANS making my database very very slow.
=======================
EXPLAIN PLAN IS ATTACHED
========================
Can i know the reason why this is happening.

I HAVE tried analyze index ..validate structure & compute statistics command but no use.
I even did the INDEX REBUILD but of no use.

Please help me....GURUS..

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROBLEM JIST
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Query making use of primary key index runs dead slow making reports to melt down.
Other queries make use of index and run proper on the same table
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

THANKS FOR ALL THE SUPPORT EVER.

AJAY SHARMA
Re: Primary key index not working proper after import [message #379258 is a reply to message #379252] Mon, 05 January 2009 12:44 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> they make FULL TABLE SCANS
Attached doc does not say so.

>>I HAVE tried analyze index ..validate structure & compute statistics command but no use.
>>I even did the INDEX REBUILD but of no use.

In what order?
Post what you did.
You need to collect stats for both table and index.

>>2.My import terminated with some warnings.
What warnings?
Re: Primary key index not working proper after import [message #379280 is a reply to message #379258] Mon, 05 January 2009 22:04 Go to previous messageGo to next message
ajaysharma2907
Messages: 14
Registered: June 2008
Location: Delhi
Junior Member

MR. MAHESH,

THANKS FOR YOUR REPLY SIR.

WHAT MAKES ME DOUBTFUL IS THE FACT THAT......
I HAVE 3 INDEXES ON THE TABLE AND 1 PRIMARY KEY INDEX......

WHEN MY QUERY MAKES USE OF ANY 3 INDEXES THE QUERY OUTPUTS VERY FAST, BUT WHEN I WRITE QUERY WITH COLUMNS THAT ARE PRESENT IN THE PRIMARY KEY INDEX ONLY THEN MY QUERY SLOWS DOWN.
WHAT COULD BE THE REASON?

THATS WHY I DID NOT ANALYZE TABLE...VALIDATE STRUCTURE & COMPUTE STATISTICS
((PLEASE FORGIVE AS I AM NEW TO TUNING)) Sad
_______________________________________________
I SHALL GET YOU THE EXACT IMPORT-ERROR ONCE IN OFFICE
SORRY FOR THIS
-----------------------------------------------

REGARDS,
AJAY SHARMA



Re: Primary key index not working proper after import [message #379318 is a reply to message #379280] Tue, 06 January 2009 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't post in UPPER case.

Regards
Michel
Re: Primary key index not working proper after import [message #379331 is a reply to message #379252] Tue, 06 January 2009 02:02 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
It may happen when:
1. You defined multicolumn PK without assigning a name to it (Oracle assigns SYS_Cxxx).
2. You defined an index with the same columns as PK but in different order.

In that case when you import your definition the index to support PK is NOT created and Oracle uses the existing index to verify uniqueness (it's Oracle "feature" since 8.0.3).

I recommend to create an index with the same columns as PK and in the same order.

HTH.
Previous Topic: Performance problem (Merged)
Next Topic: PGA MEMORY LEAK
Goto Forum:
  


Current Time: Fri Dec 09 03:49:32 CST 2016

Total time taken to generate the page: 0.10171 seconds