OraFAQ Forum: Performance Tuning » Primary key index not working proper after import

Home » RDBMS Server » Performance Tuning » Primary key index not working proper after import (oracle 8.1.7.0.0)

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

Primary key index not working proper after import [message #379252]

Mon, 05 January 2009 12:26

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

Attachment: PLAN_TABLE.TXT
(Size: 82.04KB, Downloaded 1732 times)

Report message to a moderator

Re: Primary key index not working proper after import [message #379258 is a reply to message #379252]

Mon, 05 January 2009 12:44

Mahesh Rajendran
Messages: 10708
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?

Report message to a moderator

Re: Primary key index not working proper after import [message #379280 is a reply to message #379258]

Mon, 05 January 2009 22:04

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

Report message to a moderator

Re: Primary key index not working proper after import [message #379318 is a reply to message #379280]

Tue, 06 January 2009 01:00

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Don't post in UPPER case.

Regards
Michel

Report message to a moderator

Re: Primary key index not working proper after import [message #379331 is a reply to message #379252]

Tue, 06 January 2009 02:02

michael_bialik
Messages: 621
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.

Report message to a moderator

Previous Topic:	Performance problem (Merged)
Next Topic:	PGA MEMORY LEAK

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 01:31:58 CST 2024