RE: db block gets /consistent gets

by tuningSQL to use more efficient access paths, you can get the same answer to thesame query but do less consistent gets.  This not only consumes less CPU,it also can significantly reduce latching which reduces serialization andmakes your system more scalable.Well, that turned out l

RE: db block gets /consistent gets

pond to ORACLE-L                 To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>         cc:                 Subject:        RE: db block gets /consistent gets Syed, Oracle accesses blocks in one of two modes, current or consistent. A 'db block get' is a current mode ge

Re: db block gets /consistent gets

serialization to the system, and by tuning > SQL to use more efficient access paths, you can get the same answer to the > same query but do less consistent gets. This not only consumes less CPU, > it also can significantly reduce latching which reduces serialization and > makes your system

RE: db block gets /consistent gets

ess paths, you can get the same answer to the same query but do less consistent gets. This not only consumes less CPU, it also can significantly reduce latching which reduces serialization and makes your system more scalable. Well, that turned out longer than I planned. If you're still readin

db block gets /consistent gets

Hi list,   What is db block gets and consistent gets.? How can I reduce consistent gets ? Ask Tom says each consistent gets is latch, how it could be? Thanks in advance   Syed      

RE: consistent gets

yeah rite, i just test u -Original Message- Sent: 08 October 2003 16:29 To: Multiple recipients of list ORACLE-L Hi, Consistent gets means the blocks oracle have to visit , it means the real cost of the SQL.(consisteng gets + db block gets) Reduce SGA size has nothing to do

Re: consistent gets

Hi, Consistent gets means the blocks oracle have to visit , it means the real cost of the SQL.(consisteng gets + db block gets) Reduce SGA size has nothing to do with Consistent gets. TO reduce consistent gets, only Tune the SQL or create proper index etc can help. Zhu Chao

RE: consistent gets

If you want to reduce your consistent gets, you can reduce your sga size   -Original Message-From: Sultan Syed [mailto:[EMAIL PROTECTED]Sent: 08 October 2003 14:14To: Multiple recipients of list ORACLE-LSubject: consistent gets Hi,   What does the meaning for this

Re: consistent gets

Hi consistent gets are when the SQL needs the data in consistent mode i.e not the current data. SELECT statements contribute to consistent gets (read from the RBS). DML normally contribute to db block gets, but say an UPDATE based on a search criterion -- will contribute to consistent gets

consistent gets

Hi,   What does the meaning for this consistent gets. Some time my statement return more consistent gets in statistics. How I can reduce this Consistent gets.   Thanks in advance.      

RE: High consistent gets , 10046

Thank you, Jonathan, No need for apology, all you input is very valuable. Note about update/select for update just great, didn't realize this. About chache chains. Taking real-time snapshots revealed breathtaking FILE# DBABLK COUNT(*) -- -- -- 9

Re: High consistent gets , 10046

Vadim, Apologies, I answered the question you didn't ask - viz why does it take so long, rather than the 'what are the CR gets'. Your second suggestion is the correct one. It seems unreasonable, but when you do the "select for update", Oracle seems to go through a load of read- consistency work

RE: High consistent gets , 10046

Thank you, Anjo. I want to provide more information about this case: - In both cases binding is inside PL/SQL block, bind variable type is correct. this must eliminate reasons 1 and 2 - different bind variables - yes, almost for sure they are different, but this is PK, must make no differenc

RE: High consistent gets , 10046

Thank you, Jonathan, I'll continue looking for my options to cool down the hot spots. Not sure if I can go for partitioning since Oracle charges $$$. Is it correct that oracle counts looking through the chain for the correct copy as many CR? Or the reason for these extra CR is access to undo seg

Re: High consistent gets , 10046

If you can check it in real time, you will probably find that you have a very large number of CR copies of the few blocks that are the focus of the concurrent activity. The excess time is likely to be down to a mixture of CPU as Oracle trawls through the chain looking for the correct copy, and la

Re: High consistent gets , 10046

I can think of three reasons: 1) You are binding with the wrong datatype and you are getting a full table access for the SELECT and then the rowid is remembered for the FOR UPDATE (results in 1 current get). 2) You are implicitly using array fetch in sqlplus, so the number of cr gets will be

High consistent gets , 10046

Dear listers, I'm hunting for top LIO consumers to give a relief to our DB cpu and found something that looks interesting. Many plain good queries show up way to high cr when executed in concurrent environment (50 threads) while perform as predicted when executed from SQL*PLUS. The example bel

Re: Re: more consistent gets, but more quickly?

160 161 buffer is not pinned count 41612 41604 buffer is pinned count 1685183 1685183 consistent gets 43911 43907 no work - consistent read gets 43893

Re: Re: more consistent gets, but more quickly?

160 161 buffer is not pinned count 41612 41604 buffer is pinned count 1685183 1685183 consistent gets 43911 43907 no work - consistent read gets 43893

RE: more consistent gets, but more quickly?

) OF 'IDX_UCM5' (NON-UNIQUE) (Cost=24 Card=7724) Statistics -- 0 recursive calls 0 db block gets 43629 consistent gets 0 physical reads 0 redo size 381 bytes sent via SQL*Ne

Re: Re: more consistent gets, but more quickly?

161 buffer is not pinned count 41612 41604 buffer is pinned count 1685183 1685183 consistent gets 43911 43907 no work - consistent read gets 43893 43889 session

Re: more consistent gets, but more quickly?

Optimizer=CHOOSE (Cost=224 Card=1 Bytes=11) >10 SORT (AGGREGATE) >21 TABLE ACCESS (BY INDEX ROWID) OF 'UCM_USERCOMMENT_MAINTAIN' > (Cost=224 Card=10916 Bytes=120076) 32 INDEX (RANGE SCAN) OF > 'IDX_UCM4' (NON-UNIQUE) (Cost=24 Card=7860) > >

Re: more consistent gets, but more quickly?

TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 27 January 2003 15:56 > As you see, the first sql generated 43629 consistent gets a

more consistent gets, but more quickly?

QUE) (Cost=24 Card=7724) Statistics -- 0 recursive calls 0 db block gets 43629 consistent gets 0 physical reads 0 redo size 381 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net ro

RE: Negative value for Consistent gets etc. in V$Sesstat

t another row, > run query for that row..'(written in Oracle Reports) the run time came > crashing down to 10 Secs. (Hard to believe!!). > > Now for the question. > > After running the report for a few hours I terminate the report and see the > values in V$sesstat. It is

RE: Negative value for Consistent gets etc. in V$Sesstat

ee the > values in V$sesstat. It is showing me negative values for Consistent gets > etc When the report was running i ran the same query on v$sesstat, at > that moment it was showing Consistent gets - 47 million approx. > > 1. Why is it showing negative values? > 2. What does

Re: Negative value for Consistent gets etc. in V$Sesstat

; > Now for the question. > > After running the report for a few hours I terminate the report and see the > values in V$sesstat. It is showing me negative values for Consistent gets > etc When the report was running i ran the same query on v$sesstat, at > that moment it was

Negative value for Consistent gets etc. in V$Sesstat

e question. After running the report for a few hours I terminate the report and see the values in V$sesstat. It is showing me negative values for Consistent gets etc When the report was running i ran the same query on v$sesstat, at that moment it was showing Consistent gets - 47 million approx. 1.

Re: Consistent gets

Anjo, Why do they appear in the first place? Jared Anjo Kolk <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/09/2002 09:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:Re: Con

Re: Consistent gets

No problem as long as the stat "no work - consistent read gets" (v$sysstat) is also close to that number. Anjo. Stephane Faroult wrote: > > Has anybody any idea why a query against tables on which very few if any update are >applied would display a high number of consiste

Consistent gets

Has anybody any idea why a query against tables on which very few if any update are applied would display a high number of consistent gets ? Details : Big query involving 4/5 tables, most of them partitioned. This is a test database, db block buffers about 100M, 5,000,000 of logical reads with

RE: db block get vs consistent gets

Stephane - I believe that Cary Millsap (www.hotsos.com) has studied this issue in quite some detail.   Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 3:14 PM To: Multiple recipients of list ORACLE-L

Re: db block get vs consistent gets

Stephane, The cost of an LIO is determined by many things. It used to be that one row access was one LIO. Now a days you will see that many rows are gotten in a single LIO. That means that the single LIO is more expensive. You will see that depending on the access plan oracle will access one or m

db block get vs consistent gets

Has anybody figures about the CPU cost of a consistent get vs a db block get? I have always expected it to be higher but not extremely higher. I have had a bad surprise today on a process-of-death which I am trying to salvage (although it looks a bit more like the last sacrament rather than a cure

RE: Consistent Gets

CONSISTENT GETS is the number of blocks accessed in buffer cache for normal queries (SELECTs without for update clause). Rick -Original Message- Sent: Tuesday, October 30, 2001 4:46 PM To: Multiple recipients of list ORACLE-L Hi. What exactly does "Consistent Gets" mea

RE: Consistent Gets

Any logical read (which includes SCN verification) Is called as consistent get. Best Regards, K Gopalakrishnan Bangalore, INDIA + (91) 98451 78868 -Original Message- Sent: Tuesday, October 30, 2001 1:46 PM To: Multiple recipients of list ORACLE-L Hi. What exactly does "Consi

Consistent Gets

Hi. What exactly does "Consistent Gets" mean? Thanks Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Wiegard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Publi

Re: Higher Consistent Gets...

e recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 14 September 2001 18:10 |!! Please do not post Off Topic to this List !! | |Hi | |I am having problem with a query. This query fetches rows from a table which |has 15 million rows. | |The problem is, when I execute this query with subquery

RE: Higher Consistent Gets...

ons.co.uk> cc: Sent by: Subject: Higher Consistent Gets... [EMAIL PROTECTED] 09/14/01 09:55 AM Please respon

Re: Higher Consistent Gets...

aj Gopalan ons.co.uk> cc: Sent by: Subject: Higher Con

Higher Consistent Gets...

!! Please do not post Off Topic to this List !! Hi I am having problem with a query. This query fetches rows from a table which has 15 million rows. The problem is, when I execute this query with subquery, the consistent gets are 4700. Where us without the subquery the consistent gets are

RE: Consistent Gets?

Hi Steve, Spot on! Thanx for your help. Up until now, I was unaware that ARRAYSIZE impacts the no. of consistent gets. Well, one learns every day ... Thanx once again for your assistance Paul --- Steve Adams <[EMAIL PROTECTED]> wrote: > Hi Paul, > > Thanks for sending th

RE: Consistent Gets?

hes that. The number of additional 'consistent gets' is not much less than that. This suggests that there is an extra consistent get for each fetch, unless the previous fetch finished at a database block boundary. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @

RE: Consistent Gets?

0 0 consistent changes 0 0 0 consistent gets 118519 154128 35609 CPU used by this session364749461299 CPU used when call started 364749461299 CR blocks created 0 0 0 current blocks converted for CR 0 0

RE: Consistent Gets?

e the table and see if you have any > chained rows. If there are chained > rows and if the STATE_CODE field is not always > in the last row piece, then a > extra consistent gets will be needed to get the > column values from the trailing > row pieces of chained rows that are not >

RE: Consistent Gets?

e and see if you have any > chained rows. If there are chained > rows and if the STATE_CODE field is not always > in the last row piece, then a > extra consistent gets will be needed to get the > column values from the trailing > row pieces of chained rows that are no

RE: Consistent Gets?

Hi Paul, Analyze the table and see if you have any chained rows. If there are chained rows and if the STATE_CODE field is not always in the last row piece, then a extra consistent gets will be needed to get the column values from the trailing row pieces of chained rows that are not excluded by

Re: Consistent Gets?

I believe it's because the query that returned rows had to assemble a result set, hence the extra consistent gets. The second query had no result set, so much less work was done (less consistent gets). >>> [EMAIL PROTECTED] 02/12/01 12:30PM >>> Hi all, Could someone

Re: Consistent Gets?

Thanks for your response Riyaj. I initially had the same thought so I had re-run the queries a no. of times, all giving the same (inconsistent) results. I also confirmed from v$mystat that "consistent gets" as reported by autotrace were being reported proportionately to "no wo

Re: Consistent Gets?

Thanks for your response Riyaj. I initially had the same thought so I had re-run the queries a no. of times, all giving the same (inconsistent) results. I also confirmed from v$mystat that "consistent gets" as reported by autotrace were being reported proportionately to "no wo

Re: Consistent Gets?

commit status, the session doesn't need to do that much work to get the consistent data. To verify this behavior, do the first select again and you could see comparable consistent gets. Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA "This is my opinion and does not bind

Consistent Gets?

Hi all, Could someone attempt to explain the difference in the no. of "consistent gets" reported for these 2 queries? I have a table (TEST1) made up of 11,333 blocks. No indexes on this table. I run two queries, both reported to do full table scans (as expected), one returning al