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
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
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
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
May 19, 2011
jrew.exe has stopped working when iam trying to install oracle8i on WINDOWS 2008 SERVER.
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
Aug 9, 2013
How to force an index if the table not using the index?
View 10 Replies
View Related
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
Jul 11, 2012
Can we create non-cluster index on a clustered index?
View 5 Replies
View Related
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
Jan 10, 2011
What is the difference between index rebuild and index rebuild online.
View 3 Replies
View Related
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
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
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
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
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
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
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
Aug 8, 2011
How can i rename a index? is there a way except for drop and create.
View 2 Replies
View Related
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