Home » RDBMS Server » Performance Tuning » SQL query performance issue
SQL query performance issue [message #347319] Thu, 11 September 2008 05:17 Go to next message
kirtani
Messages: 8
Registered: August 2008
Junior Member
SELECT history.init_number,history.current_administrator,init.CLASS,SUBSTR(resolution_time(history.init_number),1,1)
FROM xxe_f749_init init,xxe_f749_init_history history
WHERE history.Init_Number=init.Init_Number
AND (history.create_date_sec,history.init_number) IN (
SELECT MAX(create_date_sec),init_number
FROM xxe_f749_init_history
GROUP BY init_number)
AND history.init_status NOT IN ('Resolved','Closed','Dormant')
AND UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE','ALPHA MAINT ONSITE')
AND init.CLASS IN ('1/A','2/B','3/C','4/D')
AND SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
AND UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
AND init.command !='AMST'
AND SUBSTR(resolution_time(history.init_number),1,1)=
'0'



Hi All,

Above pasted is a query i am facing performance issue with.
Query takes in all 40 min to execute.If i remove the last AND clause(SUBSTR(resolution_time(history.init_number),1,1)='0') it executes in a minutes time.
Any help on this would of great help.

Regards

[Updated on: Thu, 11 September 2008 05:20]

Report message to a moderator

Re: SQL query performance issue [message #347321 is a reply to message #347319] Thu, 11 September 2008 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information (like execution plans).

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: SQL query performance issue [message #347322 is a reply to message #347319] Thu, 11 September 2008 05:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The function RESULUTION_TIME is where all the time is being consumed.
Can you post the code of that function?
Re: SQL query performance issue [message #347334 is a reply to message #347322] Thu, 11 September 2008 05:36 Go to previous messageGo to next message
kirtani
Messages: 8
Registered: August 2008
Junior Member
[COLOR=darkred]SELECT history.init_number,history.current_administrator,init.CLASS,SUBSTR(resolution_time(history.init_number),1,1)
FROM xxe_f749_init init,xxe_f749_init_history history
WHERE history.Init_Number=init.Init_Number
AND (history.create_date_sec,history.init_number) IN (
SELECT MAX(create_date_sec),init_number
FROM xxe_f749_init_history
GROUP BY init_number)
AND history.init_status NOT IN ('Resolved','Closed','Dormant')
AND UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE','ALPHA MAINT ONSITE')
AND init.CLASS IN ('1/A','2/B','3/C','4/D')
AND SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
AND UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
AND init.command !='AMST'
AND SUBSTR(resolution_time(history.init_number),1,1)='0'



In the above code i am also selecting the output of resolution_time function.Output from the function doesnt takes time.Issue comes when i put a check(AND clause) on the output of the function


Re: SQL query performance issue [message #347340 is a reply to message #347334] Thu, 11 September 2008 05:48 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
Can you try some thing like this
I am not sure this would help.just believing the function would be executed once instead of twice

select * from (SELECT history.init_number,history.current_administrator,init.CLASS,SUBSTR(resolution_time(history.init_number),1,1) as res_time
FROM xxe_f749_init init,xxe_f749_init_history history 
WHERE history.Init_Number=init.Init_Number
AND (history.create_date_sec,history.init_number) IN (
SELECT MAX(create_date_sec),init_number 
FROM xxe_f749_init_history 
GROUP BY init_number)
AND history.init_status NOT IN ('Resolved','Closed','Dormant')
AND UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE','ALPHA MAINT ONSITE') 
AND init.CLASS IN ('1/A','2/B','3/C','4/D')
AND SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
AND UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
AND init.command !='AMST')
where res_time ='0'

[Updated on: Thu, 11 September 2008 05:49]

Report message to a moderator

Re: SQL query performance issue [message #347341 is a reply to message #347340] Thu, 11 September 2008 05:52 Go to previous messageGo to next message
kirtani
Messages: 8
Registered: August 2008
Junior Member
It errors out this way...
As per my knowledge we cant put check on alias name
Re: SQL query performance issue [message #347364 is a reply to message #347341] Thu, 11 September 2008 06:47 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
You can use alias name if it is part of the inline view.
select * from
(select employee_id , salary+1000 as revised_sal 
from employees)
where revised_sal  < 3500

EMPLOYEE_ID	REVISED_SAL
127	        3400
128	        3200
132	        3100
135	        3400
136	        3200


Post the error you encounter and the code you are trying

[Updated on: Thu, 11 September 2008 06:48]

Report message to a moderator

Re: SQL query performance issue [message #347369 is a reply to message #347364] Thu, 11 September 2008 07:03 Go to previous messageGo to next message
kirtani
Messages: 8
Registered: August 2008
Junior Member
Query worked fine now but it is taking same amount of time
Re: SQL query performance issue [message #347416 is a reply to message #347334] Thu, 11 September 2008 10:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The function in the where clause may well be executed for each row of the table. The row in the Select clause will only be executed for the rows that meet all of the other criteria.

You said yourself that removing the function from the Where clause speeds up the query by a actor of 40 - what more would it take to make you suspect that this funtion is a large part of the problem?
Re: SQL query performance issue [message #347464 is a reply to message #347416] Thu, 11 September 2008 13:55 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Commit JR's comment to memory -
Quote:
what more would it take to make you suspect that this funtion is a large part of the problem?


Isolating what criteria in a query is the root of a problem is the whole battle and you're at this point.

Provide the info Michel and JR requested:


Quote:
The function RESULUTION_TIME is where all the time is being consumed.
Can you post the code of that function?


Ater reading the forum post format guidelines -
Quote:
provide the requested and usual information (like execution plans).


My suggestion is to then measure what overhead is from the
SUBSTR & User Function vs the change in execution plan as a whole.

ie - You could remove SUBSTR & Resolution_History and test just the overhead of the extra criteria, and with the explain plans, weight the addition of a functional based index (if feasible) for that field against modification of the query for access approach.

Regards,
Harry
Re: SQL query performance issue [message #347597 is a reply to message #347464] Fri, 12 September 2008 05:05 Go to previous messageGo to next message
kirtani
Messages: 8
Registered: August 2008
Junior Member
Thanks....issue got resolved
Re: SQL query performance issue [message #348387 is a reply to message #347319] Tue, 16 September 2008 10:41 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
in that case local temporary tables can help, but ORACLE DO NOT HAVE THEM!!!
-------------------------
BURN ORACLE
Re: SQL query performance issue [message #348388 is a reply to message #348387] Tue, 16 September 2008 10:46 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Kriptas wrote on Tue, 16 September 2008 08:41
in that case local temporary tables can help, but ORACLE DO NOT HAVE THEM!!!
-------------------------
BURN ORACLE


Oracle does have Global Temporary Table, but knowing how to (ab)use them would require you to actually RTFM.
Re: SQL query performance issue [message #348392 is a reply to message #348388] Tue, 16 September 2008 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ana, as Frank says: "don't feed the troll". Wink

Regards
Michel
Re: SQL query performance issue [message #348399 is a reply to message #347319] Tue, 16 September 2008 11:14 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
oracle global temporary tables are ... ( can say that word in public)
if I need to use temporary table in lots of stored procedures, or scripts i need to create zilion global temporary tables for my needs, or i need to create 'UNIVERSAL' global temporary table with large column count, and diferent column types.
but if i need in one sript use 10 temporary tables (using temporary tables in lots of times helps to speed up query meny times) so i need minimum 10 global temporary tables....
so MS SQL LOCAL and GLOBAL temporary tables are BETTER!!! and you have any arguments about that.
Re: SQL query performance issue [message #348400 is a reply to message #347319] Tue, 16 September 2008 11:20 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Any architecture which "requires" Temporary Table is flawed & should be trashed.

Re: SQL query performance issue [message #348403 is a reply to message #347319] Tue, 16 September 2008 11:38 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
you need read less 'oracle' books.
oracle sub selects are nightmare, you need to use lots of hints like no_unnest ant etc, and it WONT WORK FAST like it can work if I use temporary tables.
oracle concept is wrong in many cases, but wrong concept makes BIG MONEY for conlulting, suppurt, powerfull servers, and writing books about nothing that helps.

if you do not know how to work with local temporary tables, and how it is easy, and how it speed up query performance, so beter you do not say a word.

in my case, i have writen seect from 90 tables (not distinct table names) or 30 tables/subselects

it is so HARD to tune that query...
so if I use 10-20 local temporary tables (with tables prefiltering, online temporary table indexation on needed colums) , I speeded up query about 100 times (included all indexation and temporary table creation time), and server has a minimum load at script execution time.

[Updated on: Tue, 16 September 2008 11:46]

Report message to a moderator

Re: SQL query performance issue [message #348410 is a reply to message #348403] Tue, 16 September 2008 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you need hints then you don't know how to write a query.
If you need local temporary table then you don't know how to write in Oracle.
Full stop.

Regards
Michel

[Updated on: Tue, 16 September 2008 11:51]

Report message to a moderator

Re: SQL query performance issue [message #348413 is a reply to message #347319] Tue, 16 September 2008 11:59 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
I havent seen any your posts, where I can see that you know how to write query in oracle.
if you working with ~20GB database, that explains all....
And do not tell me that oracle official bull shit, that temporary tables is better do not use.
there is 1000 examples that they wrong.
My time costs money, and if I need 10 times to write query, which is 10 times slower (then others write te same query it runs 100 times slower) than in MS SQL SERVER so what I can say?


I want to not use hints, but oracle optimiser is so dumb, so i need to show to him how query must be executed.
statistics do not helps and other things do not helps...
it uses indexes whent it do not need to do that, and when it is need to use indexes it do not use them...
so oracle is BIG BUG.

[Updated on: Tue, 16 September 2008 12:04]

Report message to a moderator

Re: SQL query performance issue [message #348417 is a reply to message #348413] Tue, 16 September 2008 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 16 September 2008 18:50
If you need hints then you don't know how to write a query.
If you need local temporary table then you don't know how to write in Oracle.
Full stop.

Regards
Michel


Re: SQL query performance issue [message #348436 is a reply to message #348417] Tue, 16 September 2008 14:23 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:
If you need hints then you don't know how to write a query


Hi all,

Is that always true? I have found some performance issues while tuning queries with joins.I initially tried creating index.But creating index for my query was not a solution as i cvould see some of queries giving worst perfortmance..and that was resolved using hints.

sometime optimizer can't choose the best plan and hints are used if you know more about your data than oracle then hints an be used.Thats what my understanding is. Is that always true that where there is performnace issue for the skewed data there is no need to use hint.

Your suggesions will help...


Regards,
OLI




Regards,
Oli



[Updated on: Tue, 16 September 2008 14:29]

Report message to a moderator

Re: SQL query performance issue [message #348445 is a reply to message #347319] Tue, 16 September 2008 15:52 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Quote:
but oracle optimiser is so dumb, so i need to show to him how query must be executed.
statistics do not helps and other things do not helps...
it uses indexes whent it do not need to do that, and when it is need to use indexes it do not use them...
so oracle is BIG BUG.


Perhaps it is just that your goals and Oracle's are out of synch. I have been in this situation recently.

Before drawing this conclusion, it is worth investigating
why Oracle makes different decisions than the yours (when you
plan provides optimal results).

My DEV environ is tuned for OLTP so i re-adjust the weights,
and expected buffering in my session to "resync" my goals with
Oracle.

(I feel obliged to give credit to rleishman for giving me the 'goal' perspective for these situations).

My high volume queries always start with

ALTER SESSION SET OPTIMIZER_INDEX_CACHING  = 0;
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 100;


After that, when I want to hash join so does Oracle -
and when we disagree I usually come up short.

Regards
Harry
Re: SQL query performance issue [message #348453 is a reply to message #347319] Tue, 16 September 2008 16:38 Go to previous messageGo to next message
satm2008
Messages: 10
Registered: September 2008
Location: Toronto, Canada
Junior Member
Let me put it this way. First you placed the tables in wrong (ie, against right-to-left) manner. In ORACLE, the driver table appears right and the child/driven table appears left. And second, the search-criteria normally begins at bottom-to-top so you should pick the matching/required parents records first and then let it check child against already-picked-parent records. In that manner, the search would be quicker and with better performance. Lets try it out with the following and find out the outcome.
And I recommend (though am not sure if it is proven), to put checked-against-checking as in
       WHERE Child = Parent 
         AND Parent = ‘What_I_am_looking for’ ;

This would be easier for index based column search thus better performance.

Try the following and post the outcome.

SELECT 	history.init_number, 
        history.current_administrator,
        init.CLASS,
        SUBSTR(resolution_time(history.init_number),1,1)
  FROM 	xxe_f749_init_history history,	   -- DETAIL, PLACE IT LEFT
	xxe_f749_init init         	   -- DRIVER, PLACE IT RIGHT MOST
 WHERE 
 	(history.create_date_sec, history.init_number) 
        IN (
            SELECT 	MAX(create_date_sec),
                        init_number 
              FROM 	xxe_f749_init_history 
             GROUP BY init_number )    
   AND 	history.init_status NOT IN ('Resolved','Closed','Dormant')
   AND 	UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE','ALPHA MAINT ONSITE') 
   AND 	SUBSTR(resolution_time(history.init_number),1,1)='0'
   AND 	history.Init_Number=init.Init_Number    -- now find matching child for that parent 
   AND 	SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
   AND 	UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
   AND 	init.command !='AMST'    
   AND 	init.CLASS IN ('1/A','2/B','3/C','4/D') –- place all parent-checks first
;


Good luck Smile

[Updated on: Tue, 16 September 2008 16:42]

Report message to a moderator

Re: SQL query performance issue [message #348456 is a reply to message #347319] Tue, 16 September 2008 16:44 Go to previous messageGo to next message
satm2008
Messages: 10
Registered: September 2008
Location: Toronto, Canada
Junior Member
And one more to note, any usage of NULL or functions in the WHERE clause would nullify/prevent the use of index in the search.

Check it out. Good luck Smile
Re: SQL query performance issue [message #348482 is a reply to message #347319] Tue, 16 September 2008 23:06 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
harrysmall3, atm I have iteresting problem
I have staging area (transportable tablespace from OLTP system with tables and indexes and etc).
selecting from staging area tables and joining them i need to use hash join, because nested loops and index range scans have huge performance hit.
so system parameter OPTIMIZER_INDEX_COST_ADJ is set to 100.
but then optimizer wont use my bitmap indexes in DW tables, it start use it if OPTIMIZER_INDEX_COST_ADJ = 30, so I cant use the alter session statement because in query is used OLTP staging area tables and DW tables together.
I need to use hints to show how to use one and other table for optimiser.
this problem is show up not the long time ago, so there is posibility that DBA do something with server.
DBA is black box, I cant tune queries for performance without hints, because execution plans dramaticaly changing every week.
Re: SQL query performance issue [message #348495 is a reply to message #347319] Tue, 16 September 2008 23:53 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
table placing is critical only in RBO, in CBO optimizer replacing tables like he want why making execution plan.

please try this queries
  SELECT history.init_number, 
         history.current_administrator,
         init.CLASS,
         SUBSTR(resolution_time(history.init_number),1,1)
  FROM 	xxe_f749_init init
  INNER JOIN xxe_f749_init_history ON history.Init_Number=init.Init_Number 
				      AND history.init_status NOT IN ('Resolved','Closed','Dormant')
				      AND UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE','ALPHA MAINT ONSITE')
				      AND SUBSTR(resolution_time(history.init_number),1,1)='0'
  LEFT JOIN (SELECT MAX(create_date_sec) max_sec,
                    init_number in_no 
             FROM  xxe_f749_init_history 
             GROUP BY init_number) ON history.create_date_sec=max_sec 
				      AND in_no=history.init_number
  WHERE SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
	AND UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
	AND init.command !='AMST'    
	AND init.CLASS IN ('1/A','2/B','3/C','4/D')
	AND in_no IS NOT NULL


  SELECT history.init_number, 
         history.current_administrator,
         init.CLASS,
         history.r_time
  FROM 	xxe_f749_init init
  INNER JOIN ( SELECT init_number,
		      current_administrator,
	 	      SUBSTR(resolution_time(history.init_number),1,1) r_time
	       FROM xxe_f749_init_history
	       WHERE history.init_status NOT IN ('Resolved','Closed','Dormant')
		     AND UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE',
							     'ALPHA MAINT ONSITE')) history  ON history.Init_Number=init.Init_Number
	       											AND r_time='0'
  LEFT JOIN (SELECT MAX(create_date_sec) max_sec,
                    init_number in_no 
             FROM xxe_f749_init_history 
             GROUP BY init_number) ON history.create_date_sec=max_sec 
				      AND in_no=history.init_number
  WHERE SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
	AND UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
	AND init.command !='AMST'    
	AND init.CLASS IN ('1/A','2/B','3/C','4/D')
	AND in_no IS NOT NULL

[Updated on: Wed, 17 September 2008 00:10]

Report message to a moderator

Re: SQL query performance issue [message #348785 is a reply to message #347319] Wed, 17 September 2008 23:34 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Kriptas, concerning your question regarding

Quote:
but then optimizer wont use my bitmap indexes in DW tables


When you forced the query to use the bitmap index was there a performance gain?

Can you post the query and the explain plans for both execution variations? I've been hit with that disappointment myself -
thinking I've found the perfect bitmap index scenario and having Oracle not take that route.

Just take it the step further and test the actual performance with the bitmap index before forming conclusion of its merit, even when it appears to be the logical choice.

If your environment is changing a lot, definitely of merit to
use optimizer hints to lock your query into the best plan once you have found it. Then run the situation by your DBA's to get a better understanding of the system state and what issues they may be tackling with tuning transitions.

Always good for working relationships to share this information;
document the changes in the Access path's that Oracle has been switching around.

It could be very useful information for the DBA's in whatever the bigger scenario is that they are working on.
Presenting them with your analysis in conjunction with an inquiry on system status will probably get you a more detailed explanation in return.

Best Regards,
Harry
last post went over 80 chars [message #348791 is a reply to message #347319] Wed, 17 September 2008 23:40 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Apologies for exceeding 80 chars in a line in the previous post,
slipped my eye in preview mode.

-H
Re: SQL query performance issue [message #348814 is a reply to message #347319] Thu, 18 September 2008 00:25 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
the query is simple


select *
from sdwh_blnc a
where a.sal_dt=to_date('2008-09-01','yyyy-MM-dd')



table sdwh_blnc has 2 000 M records, it is huge table, about 500gb of size.

it is range partitioned by sal_dt.
one partition is designated for one month records(for 30 sal_dt).
one partition has about 50 M records, one sal_dt has about 1,5M records.

there is local bitmap index on sal_dt.

for few months there was all ok, optimiser used bitmap index and returned records very fast.
and now it use full partition scan, and performance downgraded dramaticaly.

if in one query I join accounts table with this query as subselect it return me result in 1,5 hours, before that (and if I use hints now) it run time was 1 minute.

i tried to set system parameter optimizer_index_cost_adj=50 (before it was 100) but it helped only for a week, no I need set that parameter to 30 to have the same results...
Re: SQL query performance issue [message #348818 is a reply to message #347319] Thu, 18 September 2008 00:34 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>for few months there was all ok, optimiser used bitmap index and returned records very fast.
In the past Oracle has had problems with bit mapped indexes.
Bitmap index perform better on occasion after being periodically rebuilt.



Re: SQL query performance issue [message #348833 is a reply to message #348818] Thu, 18 September 2008 01:09 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
anacedent, I do not understand your post...
oracle version 10g. in that table only newes partition indexes are rebuild after new day records are inserted.

so why selecting from old partitions where indexes and statistics are the same as 2 months ago is different now.
Re: SQL query performance issue [message #349084 is a reply to message #347319] Thu, 18 September 2008 17:12 Go to previous message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Kriptas I think Anacedant is indicating that there may be more
degeneration with bitmap indexes over btree indexes and merit a rebuild more frequently.

I was just reading some bitmap articles and commentary and there appears to be a lot of "back and forth" debating on
the merits of bitmap indexes across a wide range of index related topics.

Here is a link to an article from Jonathon Lewis on the topic
of index degeneration in general

http://www.dbazine.com/oracle/or-articles/jlewis14

As well, one on bitmap usage where he questions some of the
adages on what makes a good candidate for a bitmap

http://jonathanlewis.wordpress.com/2006/11/29/bitmap-indexes/

Everything with a grain of salt until tested for oneself.
I would try out rebuilding the bitmap and regathering statistics just to see if it is a factor or not.

Regards
Harry



Previous Topic: How the Query works
Next Topic: Find out what query is running for specific session?
Goto Forum:
  


Current Time: Tue Dec 06 06:23:51 CST 2016

Total time taken to generate the page: 0.12484 seconds