Home » RDBMS Server » Performance Tuning » Buffer_gets/Buffer_cache (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)
| Buffer_gets/Buffer_cache [message #563394] |
Mon, 13 August 2012 08:51  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hello,
Is there any difference between:
1. buffer_gets/buffer_cache
2. disk_reads/physical_reads
(Consider, I work as developer, so I am not that much good on DBA side)
My database is facing slow performance.
When I looked into it, I found it has high buffer_gets (1388159791) and low_disk_reads (130132).
Queries are working fine, as I verified their execution plans while they are running.
Can you gusy suggest me something I should look into with DBA perspective.
Regards,
Manu
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Buffer_gets/Buffer_cache [message #563433 is a reply to message #563401] |
Mon, 13 August 2012 16:10  |
 |
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
Yes, Buffer_gets are from memory or disk. You can see what the database is spending its
time on at the following:
http://www.orafaq.com/forum/mv/msg/183304/563118/173420/#msg_563118
I would also look at the logical verses physical reads:
ENDOCP1P > @physical
DATE PHYSICAL_READS_LAST_HOUR OBJECT_NAME
-------------------- ------------------------ --------------------
2012-08-13 Monday 6054 D_1F000D5D80000910
2012-08-13 Monday 26179 D_1F000D5D80000005
2012-08-13 Monday 40332 D_1F000D5D80000901
2012-08-13 Monday 46808 DM_SYSOBJECT_S_COMP2
2012-08-13 Monday 64930 DM_RELATION_S
2012-08-13 Monday 78228 NFL_CONTENT_R_COMP1
2012-08-13 Monday 79647 DM_SYSOBJECT_S_COMP1
2012-08-13 Monday 135421 DMR_CONTENT_S
2012-08-13 Monday 165087 NFL_CONTENT_R
2012-08-13 Monday 243480 DM_SYSOBJECT_R_COMP1
2012-08-13 Monday 548052 D_1F000D5D8000010A
2012-08-13 Monday 1475076 DM_SYSOBJECT_R_COMP2
ENDOCP1P > list
1 select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
2 sum(b.PHYSICAL_READS_DELTA) PHYSICAL_READS_LAST_HOUR,
3 a.object_name
4 from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
5 where b.SNAP_ID =(select max(SNAP_ID) from sys.wRM$_SNAPSHOT)
6 and a.object_id=b.OBJ#
7 and b.PHYSICAL_READS_DELTA>0
8 and c.instance_number=(select instance_number from v$instance)
9 and c.snap_id=b.snap_id
10 group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),a.object_name
11* order by 2
ENDOCP1P > @logical
DATE LOGICAL_READS_LAST_HOUR OBJECT_NAME
-------------------- ----------------------- --------------------
2012-08-13 Monday 12096 DM_REGISTERED_R
2012-08-13 Monday 12176 WRH$_SEG_STAT_OBJ_PK
2012-08-13 Monday 17232 LOB$
2012-08-13 Monday 20448 OBJ$
2012-08-13 Monday 23488 DM_SYSOBJECT_S_COMP2
2012-08-13 Monday 35184 I_SYSAUTH1
2012-08-13 Monday 36432 D_1F000D5D8000000F
2012-08-13 Monday 93744 DM_SYSOBJECT_R_COMP2
2012-08-13 Monday 163344 DM_SYSOBJECT_R_COMP1
2012-08-13 Monday 249952 DM_SYSOBJECT_R
2012-08-13 Monday 321568 D_1F000D5D80000901
2012-08-13 Monday 790624 DM_SYSOBJECT_S
2012-08-13 Monday 830560 NFL_CONTENT_S
2012-08-13 Monday 1408384 D_1F000D5D80000109
ENDOCP1P > list
1 select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
2 sum(b.LOGICAL_READS_DELTA) LOGICAL_READS_LAST_HOUR,
3 a.object_name
4 from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
5 where b.SNAP_ID =(select max(SNAP_ID) from sys.wRM$_SNAPSHOT)
6 and a.object_id=b.OBJ#
7 and b.LOGICAL_READS_DELTA>0
8 and c.instance_number=(select instance_number from v$instance)
9 and c.snap_id=b.snap_id
10 group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),a.object_name
11* order by 2
Also look at what is taking up all the memory, many times tables that are missing
indexes will hog all the memory and force unnecessary disk reads.
ENDOCP1P > @v$bh
OBJECT_TYPE INSTANCE MEG_IN_MEMORY OBJECT_IN_MEMORY
----------- -------- ------------- -----------------------------------------
TABLE NDOCP2 1050 NFLPROD.NFL_CONTENT_S
TABLE NDOCP1 2981 NFLPROD.DM_SYSOBJECT_S
TABLE NDOCP3 3219 NFLPROD.DM_SYSOBJECT_S
TABLE NDOCP4 3688 NFLPROD.DM_SYSOBJECT_S
TABLE NDOCP2 3740 NFLPROD.DM_SYSOBJECT_S
ENDOCP1P > list
1 SELECT
2 o.object_type,i.instance_name instance,COUNT(*)*8192/1024/1024 meg_in_memory,
3 o.owner||'.'||o.OBJECT_NAME Object_in_Memory
4 FROM DBA_OBJECTS o, gV$BH bh, gv$instance i
5 WHERE o.DATA_OBJECT_ID = bh.OBJD
6 and bh.status<>'free'
7 and bh.inst_id = i.inst_id
8 and o.object_name like upper('%%')
9 GROUP BY o.owner||'.'||o.OBJECT_NAME,o.object_type,i.instance_name
10 having count(*)>0
11* ORDER BY COUNT(*)
|
|
|
|
| Re: Buffer_gets/Buffer_cache [message #563454 is a reply to message #563394] |
Mon, 13 August 2012 21:45 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi Alan,
I didn't get your first statement.
Alan wroteYes, Buffer_gets are from memory or disk.
Whatever you said is just opposite to what BlackSwan said. According to him, Buffer_gets and buffer_cache is the same thing.
As BlackSwan verified my comments
>so if my buffer_gets is high, my query is using data that is already residing in SGA (In-memory data), am I right?
above is correct.
And now you are saying that buffer_gets are from memory or disk? So it means buffer_gets and buffer_cache are different things, as I am sure about buffer_cache, that its only primary memory access (RAM). So if my application is having high buffer_gets, what should I look into? Logical and Physical reads? And what should I determine after looking into logical vs physical reads?
Please confirm once. Also whatever problem is here, is on the Administration side only, as my queries are properly find tuned.
Can someone other also can verify the above. I am a bit confused now.
Regards,
Manu
|
|
|
|
|
|
| Re: Buffer_gets/Buffer_cache [message #563497 is a reply to message #563456] |
Tue, 14 August 2012 08:12 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi Blackswan,
I found that buffer_gets = buffer_cache hit + physical hits
Also point out the statements in your links, where they implies that buffer_gets in gets from buffer cache only.
I found my staement at
http://www.dbspecialists.com/files/presentations/buffercache.html
Tuning Oracle Buffer's cache SELECT executions, buffer_gets, disk_reads,
first_load_time, sql_text
FROM v$sqlarea
ORDER BY disk_reads
EXECUTIONS indicates the number of times the SQL statement has been executed since it entered the shared SQL area. BUFFER_GETS indicates the collective number of logical reads issued by all executions of the statement. DISK_READS shows the collective number of physical reads issued by all executions of the statement. (A logical read is a read that resulted in a cache hit or a physical disk read. A physical read is a read that resulted in a physical disk read.)
Regards,
Manu
|
|
|
|
|
|
| Re: Buffer_gets/Buffer_cache [message #563505 is a reply to message #563501] |
Tue, 14 August 2012 08:46 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Oh, ok ok.
So how can I find the how many blocks read from cache, and how many from disk.
I read a lot and confused so much now.
Also Michel, can you suggest, If my query is fine tuned (I have verified Index usage, and execution plan), what other things I should inspect, can you please provide me queries to go ahead.
There are 3 processes running simultaneously since last 3 days, and day by day their speed is decreasing. I asked DBA's and they told me that SGA is not enough, they didn't told me anything about Buffer cache, how can I give them my recommendation?
Thanks Much,
Manu
|
|
|
|
|
|
| Re: Buffer_gets/Buffer_cache [message #563535 is a reply to message #563518] |
Tue, 14 August 2012 12:33 |
|
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
A good place to start is with the buffer cache hit ratio. I modify it to see the physical reads per hour and the logical gig per minute. On Databases with high physical reads per hour I work on caching and better indexing of the objects with high physical reads as in the query provided earlier. Databases with high logical reads I work on caching and better indexing of the objects with high logical reads as in the query provided earlier. Here in the buffer cache hit ratio you can see a database with good caching and high logical reads verses a database with very high physical reads.
ENDOCP2P > @hitr
HOST INSTANCE UPDAYS BLKHIT PHYSRDS_PER_HOUR LOGICAL_GIG_PER_MIN
--------------- -------- -------- ------- ---------------- -------------------
proddb05 NDOCP2 32.535 94.03 5168500 10.994
ENDOCP2P > list
1 select
2 host_name host,instance_name instance,sysdate-i.startup_time updays,
3 ROUND(((SUM(DECODE(Name, 'consistent gets', Value, 0))+
4 SUM(DECODE(Name, 'db block gets', Value, 0)) -
5 SUM(DECODE(Name, 'physical reads', Value, 0)) )/
6 (SUM(DECODE(Name, 'consistent gets',Value,0))+
7 SUM(DECODE(Name, 'db block gets', Value, 0)))) *100,2)
8 BlkHit,
9 round(SUM(DECODE(Name, 'physical reads',Value,0))/
10 to_number(sysdate-i.startup_time)/24) Physrds_per_hour
11 ,SUM(DECODE(Name, 'consistent gets',Value,0))/
12 to_number(sysdate-i.startup_time)*
13 8192/1024/1024/1024/24/60 Logical_GIG_PER_MIN
14 ,SUM(DECODE(Name, 'physical reads',Value,0)) Physrds
15 ,SUM(DECODE(Name, 'consistent gets',Value,0)) consistent
16 ,SUM(DECODE(Name, 'consistent gets',Value,0))/
17 to_number(sysdate-i.startup_time)/24 Con_per_hour
18 ,SUM(DECODE(Name, 'db block gets',Value,0)) Dbblock
19 ,SUM(DECODE(Name, 'db block gets',Value,0))/
20 to_number(sysdate-i.startup_time)/24 Dbblock_per_hour
21 from V$SYSSTAT,v$instance i
22* group by host_name,instance_name,(sysdate-i.startup_time)
ECSCDAP2P > @hitr
HOST INSTANCE UPDAYS BLKHIT PHYSRDS_PER_HOUR LOGICAL_GIG_PER_MIN
--------------- -------- -------- ------- ---------------- -------------------
csprdcdadb12 CSCDAP2 56.509 100.00 23220 110.382
|
|
|
|
|
|
|
|
| Re: Buffer_gets/Buffer_cache [message #563550 is a reply to message #563394] |
Tue, 14 August 2012 13:31 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Yes, agree with BlackSwan, saw many examples on AskTom
And I have already checked my Buffer hit ratio, its 99.98%.
Also physical reads are very low (300 times low) as compared to logical reads (got from sql_area)
Any other suggestions? What about server performance, RAM, memory, SGA, processor allocation etc etc....
Thank you,
Manu
|
|
|
|
|
|
| Re: Buffer_gets/Buffer_cache [message #563634 is a reply to message #563558] |
Thu, 16 August 2012 02:51 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Again you are suggesting me to tune SQL, although I have already told that my SQL is properly tuned.
I have ran your query in given post, and attached in the result in csv format.
One more thing to mention, when I am running single query, its fine. But if I am creating 2 or 3 instances of that query (which work on the same object), its getting very very slow.
Thanks,
Manu
-
Attachment: orafaq.csv
(Size: 5.72KB, Downloaded 1987 times)
|
|
|
|
| Re: Buffer_gets/Buffer_cache [message #563638 is a reply to message #563634] |
Thu, 16 August 2012 03:39 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Manu, you keep repeating that your SQL is tuned. If is running too slowly, then it isn't tuned.
I think you may be confusing a symptom of a problem with a cause of a problem. For example, those figures for logical and physical I/O are not the cause of the problem: they are a symptom. The cause of the problem would NOT be all the I/O, the cause would be the NEED for I/O. You need to tune the statement and the segment structures so that it doesn't need to read as many blocks, and that the blocks it does need can be accessed efficiently.
Remember that no end user ever telephoned the helpdesk to complain that the buffer cache hit ratio is too low.
Someone here might be able to help, but since you have not posted the query or the AWR report or AWR SQL report or the DDL of the table(s) and indexes, that seems unlikely.
|
|
|
|
| Re: Buffer_gets/Buffer_cache [message #563642 is a reply to message #563638] |
Thu, 16 August 2012 05:01 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi,
Please give a look to the below execution plan-
select a.SID, a.UserName, a.Status, trunc(b.elapsed_time/60000000) "RUN_MIN", b.object_status, a.sql_id, a.sql_child_number, b.SQL_fullTEXT, c.spid "Unix PID", a.Serial#, to_char(a.logon_time,'DD-MON-YY HH24:MI:SS') start_dt_tm
from v$session a, v$sql b, v$process c
where a.sql_id=b.sql_id and
paddr=addr and
status = 'ACTIVE';
select * from table(dbms_xplan.display_cursor('f3xg0hz9jxfr8',0));
INSERT INTO temp_vendor(vendor_record_seq_no,checksum,rownumber,transaction_type,iu_flag)
SELECT vd1.vendor_record_seq_no, tvr.checksum, tvr.rownumber, tvr.transaction_type, 'U'
FROM vendor_data vd1,
vendor_data vd2,
vendor_data vd3,
vendor_data vd4,
vendor_data vd5,
vendor_data vd6,
(SELECT rownumber,
MAX (DECODE (control_column_seq_no, 391763, original_value, NULL)) AS value1,
MAX (DECODE (control_column_seq_no, 391764, original_value, NULL)) AS value2,
MAX (DECODE (control_column_seq_no, 391761, original_value, NULL)) AS value3,
MAX (DECODE (control_column_seq_no, 391762, original_value, NULL)) AS value4,
MAX (DECODE (control_column_seq_no, 391759, original_value, NULL)) AS value5,
MAX (DECODE (control_column_seq_no, 391760, original_value, NULL)) AS value6
FROM (SELECT /*+ dynamic_sampling(vdt 3) dymanic_sampling_est_cdn(vdt) FULL (vdt) */ rownumber,
VALUE AS original_value,control_column_seq_no
FROM temp_vendor_data vdt
WHERE vdt.KEY = 'Y'
AND error_flag IS NULL) temp
GROUP BY rownumber) tvd,
temp_vendor_record tvr
WHERE vd1.study_seq_no = 22794
AND vd1.control_column_seq_no = 391763
AND tvd.value1 = vd1.original_value
AND vd2.study_seq_no = 22794
AND vd2.control_column_seq_no = 391764
AND tvd.value2 = vd2.original_value
AND vd1.vendor_record_seq_no = vd2.vendor_record_seq_no
AND vd3.study_seq_no = 22794
AND vd3.control_column_seq_no = 391761
AND tvd.value3 = vd3.original_value
AND vd1.vendor_record_seq_no = vd3.vendor_record_seq_no
AND vd4.study_seq_no = 22794
AND vd4.control_column_seq_no = 391762
AND tvd.value4 = vd4.original_value
AND vd1.vendor_record_seq_no = vd4.vendor_record_seq_no
AND vd5.study_seq_no = 22794
AND vd5.control_column_seq_no = 391759
AND tvd.value5 = vd5.original_value
AND vd1.vendor_record_seq_no = vd5.vendor_record_seq_no
AND vd6.study_seq_no = 22794
AND vd6.control_column_seq_no = 391760
AND tvd.value6 = vd6.original_value
AND vd1.vendor_record_seq_no = vd6.vendor_record_seq_no
AND tvr.error_flag IS NULL
AND tvr.rownumber = tvd.rownumber
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 35944 (100)| | | |
| 1 | NESTED LOOPS | | 1 | 12211 | 35944 (1)| 00:07:12 | | |
| 2 | NESTED LOOPS | | 1 | 12181 | 35944 (1)| 00:07:12 | | |
| 3 | NESTED LOOPS | | 1 | 12155 | 35942 (1)| 00:07:12 | | |
| 4 | NESTED LOOPS | | 1 | 12129 | 35940 (1)| 00:07:12 | | |
| 5 | NESTED LOOPS | | 1 | 12103 | 35938 (1)| 00:07:12 | | |
| 6 | NESTED LOOPS | | 3 | 36231 | 35932 (1)| 00:07:12 | | |
|* 7 | HASH JOIN | | 2934 | 33M| 30061 (1)| 00:06:01 | | |
| 8 | VIEW | | 1 | 12025 | 2 (50)| 00:00:01 | | |
| 9 | SORT GROUP BY | | 1 | 2032 | 2 (50)| 00:00:01 | | |
| 10 | TABLE ACCESS BY INDEX ROWID| TEMP_VENDOR_DATA | 1 | 2032 | 1 (0)| 00:00:01 | | |
|* 11 | INDEX RANGE SCAN | TEMP_VENDOR_DATA_IDX3 | 1 | | 1 (0)| 00:00:01 | | |
| 12 | PARTITION HASH SINGLE | | 3675K| 91M| 30030 (1)| 00:06:01 | 69 | 69 |
|* 13 | INDEX RANGE SCAN | VENDOR_DATA_IDX04 | 3675K| 91M| 30030 (1)| 00:06:01 | 69 | 69 |
| 14 | PARTITION HASH SINGLE | | 1 | 26 | 2 (0)| 00:00:01 | 69 | 69 |
|* 15 | INDEX UNIQUE SCAN | VENDOR_DATA_IDX04 | 1 | 26 | 2 (0)| 00:00:01 | 69 | 69 |
| 16 | PARTITION HASH SINGLE | | 1 | 26 | 2 (0)| 00:00:01 | 69 | 69 |
|* 17 | INDEX UNIQUE SCAN | VENDOR_DATA_IDX04 | 1 | 26 | 2 (0)| 00:00:01 | 69 | 69 |
| 18 | PARTITION HASH SINGLE | | 1 | 26 | 2 (0)| 00:00:01 | 69 | 69 |
|* 19 | INDEX UNIQUE SCAN | VENDOR_DATA_IDX04 | 1 | 26 | 2 (0)| 00:00:01 | 69 | 69 |
| 20 | PARTITION HASH SINGLE | | 1 | 26 | 2 (0)| 00:00:01 | 69 | 69 |
|* 21 | INDEX UNIQUE SCAN | VENDOR_DATA_IDX04 | 1 | 26 | 2 (0)| 00:00:01 | 69 | 69 |
| 22 | PARTITION HASH SINGLE | | 1 | 26 | 2 (0)| 00:00:01 | 69 | 69 |
|* 23 | INDEX UNIQUE SCAN | VENDOR_DATA_IDX04 | 1 | 26 | 2 (0)| 00:00:01 | 69 | 69 |
|* 24 | TABLE ACCESS BY INDEX ROWID | TEMP_VENDOR_RECORD | 1 | 30 | 0 (0)| | | |
|* 25 | INDEX UNIQUE SCAN | TEMP_VENDOR_RECORD_IDX1 | 1 | | 0 (0)| | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("TVD"."VALUE1"="VD1"."ORIGINAL_VALUE")
11 - access("VDT"."KEY"='Y' AND "ERROR_FLAG" IS NULL)
13 - access("VD1"."STUDY_SEQ_NO"=22794 AND "VD1"."CONTROL_COLUMN_SEQ_NO"=391763)
15 - access("VD2"."STUDY_SEQ_NO"=22794 AND "VD2"."CONTROL_COLUMN_SEQ_NO"=391764 AND
"VD1"."VENDOR_RECORD_SEQ_NO"="VD2"."VENDOR_RECORD_SEQ_NO" AND "TVD"."VALUE2"="VD2"."ORIGINAL_VALUE")
17 - access("VD3"."STUDY_SEQ_NO"=22794 AND "VD3"."CONTROL_COLUMN_SEQ_NO"=391761 AND
"VD1"."VENDOR_RECORD_SEQ_NO"="VD3"."VENDOR_RECORD_SEQ_NO" AND "TVD"."VALUE3"="VD3"."ORIGINAL_VALUE")
19 - access("VD4"."STUDY_SEQ_NO"=22794 AND "VD4"."CONTROL_COLUMN_SEQ_NO"=391762 AND
"VD1"."VENDOR_RECORD_SEQ_NO"="VD4"."VENDOR_RECORD_SEQ_NO" AND "TVD"."VALUE4"="VD4"."ORIGINAL_VALUE")
21 - access("VD5"."STUDY_SEQ_NO"=22794 AND "VD5"."CONTROL_COLUMN_SEQ_NO"=391759 AND
"VD1"."VENDOR_RECORD_SEQ_NO"="VD5"."VENDOR_RECORD_SEQ_NO" AND "TVD"."VALUE5"="VD5"."ORIGINAL_VALUE")
23 - access("VD6"."STUDY_SEQ_NO"=22794 AND "VD6"."CONTROL_COLUMN_SEQ_NO"=391760 AND
"VD1"."VENDOR_RECORD_SEQ_NO"="VD6"."VENDOR_RECORD_SEQ_NO" AND "TVD"."VALUE6"="VD6"."ORIGINAL_VALUE")
24 - filter("TVR"."ERROR_FLAG" IS NULL)
25 - access("TVR"."ROWNUMBER"="TVD"."ROWNUMBER")
Are you able to see anything that I can rectify in the above exeuction plan. As per my opinion its optimal, as I have tested it using TOAD optimizer (many plans + 5 time execution per plan) and came to that plan with minimum cost and execution time. A large table is used 6 times, 1 time in hash join range scan and 5 time in nested loop join unique scan.
As far as I know, tuning is done on different levels (please see attached)
So now, as per my opinion, its everything fine at SQL tuning, I should do something with Instance and Server tuning.
Thanks,
Manu
|
|
|
|
| Re: Buffer_gets/Buffer_cache [message #563646 is a reply to message #563642] |
Thu, 16 August 2012 05:24 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
If this is the sort of thing that TOAD comes up with, I am not impressed. Look at this hint: /*+ dynamic_sampling(vdt 3) dymanic_sampling_est_cdn(vdt) FULL (vdt) */
Your code is NOT tuned. You had better do a reality check on all the statistics and the actual and expected cardinalities, get rid of the hint, and think about your indexing and partitioning strategy.
Back to the beginning, I'm afraid.
|
|
|
|
| Re: Buffer_gets/Buffer_cache [message #563657 is a reply to message #563646] |
Thu, 16 August 2012 06:34 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Changing this:
SELECT /*+ dynamic_sampling(vdt 3) dymanic_sampling_est_cdn(vdt) FULL (vdt) */ rownumber,
VALUE AS original_value,control_column_seq_no
FROM temp_vendor_data vdt
WHERE vdt.KEY = 'Y'
AND error_flag IS NULL
to:
SELECT /*+ dynamic_sampling(vdt 3) dymanic_sampling_est_cdn(vdt) FULL (vdt) */ rownumber,
VALUE AS original_value,control_column_seq_no
FROM temp_vendor_data vdt
WHERE vdt.KEY = 'Y'
AND error_flag IS NULL
AND control_column_seq_no IN (391763, 391764, 391761, 391762, 391759, 391760)
Might help
|
|
|
|
Goto Forum:
Current Time: Fri Jan 10 18:57:39 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
