DB Stopped Choosing Index

Aug 26, 2010

Have an issue where after several months, our Oracle database has stopped choosing an index.

Here's some background information:

For the sake of simplification, the table has two key fields, SITE and ID, and the table is partitioned by SITE. Since the query is doing an ORDER BY ID DESC, we had to create a function-based DESC index as well as a regular index to convince the database to choose the index:

INDEX_1: SITE Asc, ID Asc
INDEX_2: SITE Desc, ID Desc

So when the index is chosen the showplan displays INDEX RANGE SCAN DESCENDING on INDEX_1

Now, after several months where the index was being chosen for all 4 SITEs, the index only works for 3 of the 4 SITEs.
And for 1 of the SITEs it shows 'TABLE ACCESS FULL'.

Strangely, we created another instance of the database (a byte for byte image) for troubleshooting purposes, with the same database engine version (but different OS version) and the index is being chosen for that new instance. Of course there's an explaination for this, but it's beyond my comprehension as to why you have an exact image with a different behavior.

Here's what Oracle version:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE9.2.0.7.0Production
TNS for Solaris: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

By the way, HINTS are not an option since the application that executes this query doesn't allow us to modify the SQL.

View 9 Replies


ADVERTISEMENT

Performance Tuning :: Dynamic SQL Choosing Wrong Index?

Aug 16, 2013

This is a vendor app and some times the dynamic sql will work great. Sometimes - not so much. There is something that is telling the optimizer to use an incorrect index.

1) Is there a memory setting I can use that optimizer determine the correct index to use?

2) How do I figure out what influenced the optimizer's decision to choose a particular index?

View 3 Replies View Related

Upgrade :: Choosing Character Set

Jan 2, 2013

I am planning to migrate to 11.2.0.2.0 . My present DB version is 9.2.0.6.0 64bit on solaris 5.10 and my present character set is US7ASCII.
I want to keep the provision for non-english data too, though currently my DB is storing only English language. oracle recommended to use unicode in 11g AL32UTF8.Does it support smart characters. what are the pros/cons. other character set...

View 2 Replies View Related

Installation :: Stopped At 59 % - Oracle 12c 64 Bit

Jul 30, 2013

I have Windows 7 Enterprise, 64 bit operating system. I downloaded Oracle 12c for 64 bits. I downloaded both zipped files in one folder that I created and un-zipped in the same folder as well.Once I start setup.exe,

I got three times File not found message as follows:

File not found C:app....admindr0ulib.sql.sbsFile not found C:app...installsbs rcsess.batFile not found C:app...schemamk_dir.sql.sbs

 On all of the above situations, I clicked "Continue" since I have no idea where to find these files.At 59% installation, it is in the process of setup and displaying the message "updating registry key "HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/KEY_OraDB12Home1" At this point it pretty much hangs up for ever. 

View 11 Replies View Related

Windows :: JREW.EXE Has Stopped Working?

May 19, 2011

jrew.exe has stopped working when iam trying to install oracle8i on WINDOWS 2008 SERVER.

View 1 Replies View Related

Server Administration :: Dba_jobs Stopped Running

Feb 19, 2011

I have a dba_job that is scheduled to run every hour and for the past couple of weeke it is behaving strangely. It fails running every alternate hour. I have checked the alert log whenever it fails but nothing is reported in the alert log. The job is not broken.

View 4 Replies View Related

Reports & Discoverer :: 10g Suddenly Stopped Working?

Mar 21, 2012

Im not able to open the oracle reports 10g under Oracle Developer Suite Using win 7 enterprise version It gives me no error but unable to see the reports opening.It is running in the task manager but it is not opening.

View 2 Replies View Related

Windows / .NET :: ODBC Administrator Has Stopped Working

Dec 8, 2012

I installed the Oracle instant client version 10.2.0.3 and the corresponding ODBC driver. When I try to add a DSN in ODBC Manager using the Oracle driver, it crashes and I get this message:

"ODBC Administrator has stopped working"
"Windows is checking for a solution to the problem..."

Trying to isolate this problem, I ran odbcad32.exe under DependencyWalker and it worked correctly without crashing. According to the Dependency Walker FAQ, this can be due to a "buffer overrun, stray/bad/freed pointer, etc.". They have a switch to set SET NODEBUG_HEAP=1 to check for this. When I did this, it crashes insider DependencyWalker also.

View 0 Replies View Related

Forms :: Oracle 6i Runform Has Stopped Working Windows 7

Nov 25, 2012

I have an oracle application (Froms 6i) that is being accessed from a PC client running Windows 7 Professional. Whenever I try to access the the application I get a "Oracle Forms Runform has stopped working" error. I tried installing the runtime over and over again withn no use. This is not the first problem I encounter with "Windows 7- Oracle 6i" combination.

View 1 Replies View Related

Forms :: Reports Background Engine Stopped Working

Jun 28, 2011

When i run my reports on Windows 7 i receive the following error Reports Background engine Stopped working

When i view the detail of the error it shows

Problem Event Name APPCRASH
Application name RWRBE60.EXE
Application Version 3.0.0.0
Application Timestamp 38a27e49
Fault Module Name rwlib60.dll
Fault Module Version 0.0.0.0

I think it is sufficient information for you experts to resolve this issue reports are designed in developer 6i please view the attached image for report error detail.

View 8 Replies View Related

Client Tools :: ISQLPlus Stopped Working Unexpectedly

Mar 20, 2011

I'm having some trouble with iSQL*Plus.I instaled oracle database and at first it worked fine, but then I installed client on the same maschine and all products that come with client, and somewhere in the meantime iSQL*Plus stopped working.

Then I uninstalled client and products that go with him (Policy Manager, Oracle Wallet...) because I thought that they have "broken" iSQL*Plus, but it still doesn't work. Finally, I re-installed iSQL*Plus, but that didn't work either. Interesting thing is that when I type in command line

" c:/miljana/bin/isqlplusctl start "

it says that iSQLPlus is started, but when I go to browser there is an error:

" Firefox can't establish a connection to the server at miljana.localhost.com:5560. "

Also from Administrative tools/Services if a try to start iSQL*Plus there is an error:

" Could not start the OracleOraDb10g_home1iSQL*Plus service on Local Computer.
Error 1067: The process terminated unexpectedly. "

I know that there is a lot of topics for this, but most of solutions where for Linux, and didn't work for me.I'm using Oracle 10g Enterprise Edition on WindowsXP

View 1 Replies View Related

Text :: Index For Domain Index With Composite Domain Index (CDI) Very Slow

Jun 27, 2012

I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:

Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....

I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.

View 5 Replies View Related

Performance Tuning :: Local Index Versus Global Index On Partitioned Table

Jun 28, 2011

I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.

View 6 Replies View Related

Server Administration :: Convert Global Index To Local Index

Jun 23, 2011

I have a global index and I want to convert it to local index.Is there a way to recreate local index with out dropping the global index.

I can create a local index first and then drop the global index. But is there a way to create it with out dropping the global index, just convert it.

View 5 Replies View Related

SQL & PL/SQL :: ORA-01502 - Index Or Partition Of Such Index Is In Unusable State?

Nov 29, 2010

I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using
sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.

View 3 Replies View Related

Performance Tuning :: Index With NVL / Query Is No Longer Using Index

Nov 19, 2010

I have a query which had a join:

a.c1=b.c1 and a.c2=@var

where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like

a.c1(+)=b.c1 and nvl(a.c2,@var)=@var

This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')

But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?

View 2 Replies View Related

Performance Tuning :: Force Index If Table Not Using Index?

Aug 9, 2013

How to force an index if the table not using the index?

View 10 Replies View Related

When Index Lock Contention Occurs / How To Know Which Index Is Causing

Aug 27, 2012

We have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.

This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.

Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.

View 5 Replies View Related

SQL & PL/SQL :: Create Non-cluster Index On A Clustered Index?

Jul 11, 2012

Can we create non-cluster index on a clustered index?

View 5 Replies View Related

SQL & PL/SQL :: Difference Between Index And Clustered Index

Aug 3, 2010

Any on give explanation for difference between Index and Clustered Index?

It will be great if i get explanation how memory allocation and Execution takes place.?

View 4 Replies View Related

Performance Tuning :: Difference Between Index Rebuild And Index Rebuild Online?

Jan 10, 2011

What is the difference between index rebuild and index rebuild online.

View 3 Replies View Related

PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full

Oct 5, 2013

Let's consider such table that all rows fit into single block:

SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin

[code].....

why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2

View 8 Replies View Related

Last Used Time Of Index

Apr 7, 2011

I'm using Oracle 11g and I have a bunch of indexes and I want to check if they are being used. I just ran my workloads and now I want to see when each one was last used so I can see if it was during my timeframe or not.

After I ran my test, I found the below, but since I did not enable this, plus I have many indexes.

--Monitor an index to see if it's used
alter index SAMPLE_INDEX monitoring usage;
select * from v$object_usage where index_name = 'SAMPLE_INDEX';
alter index SAMPLE_INDEX nomonitoring usage;

View 4 Replies View Related

Index With Degree More Than 1

Jul 29, 2010

There is an index with degree 16 on a rac env. The base table has no degree. The table and index are not partitioned. Does the degree of index only affect index DDL (alter, rebuild etc)? Any effects on query (PQ)?

View 4 Replies View Related

Index In Cache

Apr 21, 2013

i have a application which uses 32 tables for retrieval in this 4 tables are important and have a size more than 100 mb can i move the index of these 4 tables cache memory to improve the applications retrieval performance if i done so ,then that will affect any other applications performance

View 1 Replies View Related

SQL & PL/SQL :: Index And Group By

Sep 5, 2012

I have a query that extracts the row with a max(record_date) within a group.

select ssn, fund, type_indicator, annuitant, cur_year, record_date, guarantee
from LC_MORTALITY
where (ssn, fund, type_indicator, annuitant, cur_year, record_date) in
(select ssn, fund, type_indicator, annuitant, cur_year, max(record_date)
from LC_MORTALITY
group by ssn, fund, type_indicator, annuitant, cur_year);

the table has index that matches the group by clause exactly.

create index IDX_LC_MORTALITY_sftayd on LC_MORTALITY
(SSN,
FUND,
TYPE_INDICATOR,
ANNUITANT,
cur_year,
record_date

However, the plan ignores the index

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1963K| 241M| | 1375K (4)| 05:21:04 |
|* 1 | HASH JOIN RIGHT SEMI| | 1963K| 241M| 9701M| 1375K (4)| 05:21:04 |
| 2 | VIEW | VW_NSO_1 | 145M| 8038M| | 241K (12)| 00:56:22 |
| 3 | HASH GROUP BY | | 145M| 8038M| | 241K (12)| 00:56:22 |
| 4 | TABLE ACCESS FULL| LC_MORTALITY | 145M| 8038M| | 219K (3)| 00:51:13 |
| 5 | TABLE ACCESS FULL | LC_MORTALITY | 145M| 9840M| | 222K (5)| 00:51:49 |
---------------------------------------------------------------------------------------------
[code]...

It uses the index but still shows "table access full" in one place. Why is there still a full access?Can I do anything to optimize further?

View 9 Replies View Related

SQL & PL/SQL :: Use Of Table Index?

Feb 10, 2012

Am pasting the sample code here, which i got from some site..

DECLARE
TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population population_type;
continent_population population_type;
howmany NUMBER;

[code]...

Here we are fetching indexes (like Antartica/Australia) from these two statements continent_population.FIRST or continent_population.LAST. If there are three or more indexes in the same table, how to fetch all of them?

I have tried using this, but doesnt work because loop variables are by default integers:

for i in continent_population.FIRST .. continent_population.LAST loop
dbms_output.put_line('i:'||i);
end loop;

View 2 Replies View Related

SQL & PL/SQL :: Explicit Name For Index Of IOT

Feb 18, 2011

Need some Clarification on the below query:

Can we specify explicit name for the index of the IOT.

View 2 Replies View Related

SQL & PL/SQL :: How To Rename A Index

Aug 8, 2011

How can i rename a index? is there a way except for drop and create.

View 2 Replies View Related

When To Use A Reverse Key Index

Jul 3, 2012

I've read some documentation about the reverse key indexes, but I haven't understood when really it can be useful use them. when it's smart to use them according to your experiences?

View 4 Replies View Related







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