| Consistent gets reduced but Elapsed time increased.. please help. [message #455163] |
Mon, 10 May 2010 09:37  |
nitinkrmishra
Messages: 4
Registered: May 2010
|
Junior Member |
|
|
Hi All,
While tuning a application my consistent gets are reduced by 50% but the query is still taking the same time.
Data is coming from With clause that is having some 40000 rows .Then these 40K rows are joined to a table that is having 2 Billion records having indexes on primary key and date column(bitmap)indexes .
Please help..
|
|
|
|
|
|
|
|
|
|
| Re: Consistent gets reduced but Elapsed time increased.. please help. [message #455684 is a reply to message #455597] |
Wed, 12 May 2010 08:55  |
 |
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
|
Senior Member |
|
|
It may sound obvious in the case where the with clause gives access to an inline view instead of creating a temporary table with the essential data.
When using a temporary table the DB at the beginning of the process performs a full evaluation of the data and stores intermediate result on a temporary table witch could be generated by FULL TABLE SCANS and HASH JOIN that may generate the maximum physical I/O because it loads all the blocks of the tables in the buffer cache, when using an inline view the DB performs no operation at the beginning of the process but it may use NESTED LOOP JOIN and INDEX RANGE SCANS several times, they limit the physical I/O, because the DB loads only those blocks containing matching data, but they tend to increases the total number of I/O operations (logical+physical) by an important factor caused by the multiple access to the same blocks of the tables and their indexes.
I advice you to find some infos about the materialized and inline hints on the with clause.
Bye Alessandro
[Updated on: Wed, 12 May 2010 08:57] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
