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 Go to next message
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 #563396 is a reply to message #563394] Mon, 13 August 2012 08:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Buffer_gets/Buffer_cache [message #563397 is a reply to message #563396] Mon, 13 August 2012 09:01 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

I was expecting your comment very first. Smile

FYI, I am not asking to tune my SQL. It's already tuned, I assure you. I am confused with what these different parameters means?

Thanks,
Manu
Re: Buffer_gets/Buffer_cache [message #563398 is a reply to message #563397] Mon, 13 August 2012 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>1. buffer_gets/buffer_cache
desired block already resides within SGA
>2. disk_reads/physical_reads
desired block does not resides within SGA

Re: Buffer_gets/Buffer_cache [message #563399 is a reply to message #563398] Mon, 13 August 2012 09:24 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

ok, means

buffer_gets=buffer_cache

and

disk_reads=physical_reads, so if my buffer_gets is high, my query is using data that is already residing in SGA (In-memory data), am I right?

Thanks,
Manu
Re: Buffer_gets/Buffer_cache [message #563401 is a reply to message #563399] Mon, 13 August 2012 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>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.
Re: Buffer_gets/Buffer_cache [message #563433 is a reply to message #563401] Mon, 13 August 2012 16:10 Go to previous messageGo to next message
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 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Hi Alan,

I didn't get your first statement.

Alan wrote
Yes, 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 #563456 is a reply to message #563454] Mon, 13 August 2012 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6749454952894

http://docs.oracle.com/cd/E11882_01/server.112/e16638/sql_overview.htm#i26072

[Updated on: Mon, 13 August 2012 22:12]

Report message to a moderator

Re: Buffer_gets/Buffer_cache [message #563497 is a reply to message #563456] Tue, 14 August 2012 08:12 Go to previous messageGo to next message
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 #563501 is a reply to message #563497] Tue, 14 August 2012 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Logical reads are read in the cache, if the block is not in the cache then it is read from disk to cache and then read from cache to PGA.

Regards
Michel
Re: Buffer_gets/Buffer_cache [message #563505 is a reply to message #563501] Tue, 14 August 2012 08:46 Go to previous messageGo to next message
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 #563518 is a reply to message #563505] Tue, 14 August 2012 10:44 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Also, now I have AWR report in HTML format. In which sections, I should look?

Thanks,
Manu
Re: Buffer_gets/Buffer_cache [message #563535 is a reply to message #563518] Tue, 14 August 2012 12:33 Go to previous messageGo to next message
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 #563541 is a reply to message #563535] Tue, 14 August 2012 12:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Buffer Cache Hit Ratio is a mythical indicator of performance!
Re: Buffer_gets/Buffer_cache [message #563548 is a reply to message #563541] Tue, 14 August 2012 13:29 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
That is why I modified it to have physical reads per hour
and logical gig per minute. Now the instances with the
execessive physical reads per hour stand out.
HOST            INSTANCE   UPDAYS  BLKHIT PHYSRDS_PER_HOUR LOGICAL_GIG_PER_MIN
--------------- -------- -------- ------- ---------------- -------------------
proddb04        NDOCP1      7.292   95.12         10408096          27.1131334
proddb05        NDOCP2      7.756   98.48            20564          .168191876
proddb06        NDOCP3      7.744   86.82            48992          .045197757
proddb07        NDOCP4      7.733   26.45          6921669          1.19318099
proddb04        NWEBP1      7.292   86.79          9260625          8.59430087
proddb05        NWEBP2      7.743   89.79          5927616          7.35832536
proddb06        NWEBP3      7.733   82.56         11954652          8.68198851
proddb07        NWEBP4      7.719   90.91          5263179          7.34488272
csprdesbdb01-fe CSESBP1   370.171   96.51           502565          1.80237539
csprdesbdb02-fe CSESBP2   370.170   98.01           391118          2.47142194
csprdcdadb11    CSCDAP1    21.739   99.99            29124          66.4427194
csprdcdadb12    CSCDAP2    21.736  100.00            13694          81.5946945
csprdcdadb13    CSCDAP3    21.735  100.00            11027          67.3472659
csprdcdadb14    CSCDAP4    21.734  100.00            13206           77.555757
stagedb03       NWEBS1       .363   88.32           178783          .191235947
stagedb04       NWEBS2       .363   88.23           177139          .137767605
stagedb03       NDOCS1       .363  -13.30           265177          .028280657
stagedb04       NDOCS2       .363   26.17           491468          .080415011
csstgesbdb01    CSESBS1   145.379   88.17          1424909          1.50346683
csstgesbdb02    CSESBS2   145.445   80.70           716105          .457753094
csstgcdadb11    CSCDAS1    22.022   99.86            10534          .985885322
csstgcdadb12    CSCDAS2    22.020   99.99              672          1.03987562
csqatesbdb01    CSESBQ    143.952   93.43           606307          1.15360619
csqatcdadb01    CSCDAQ     24.089   98.83            12941          .133536069
testdb02        NDOCT        .376   99.99            12700          10.8892221
testdb02        NWEBT        .376   87.66           318944          .278428954
testdb02        NALFT        .375   99.56             6840          .169672093
testdb02        NTOOLS       .375   99.60             4774           .14935791
csdevesbdb22    CSESBD    111.606   36.47           332551          .061596003
csdevcdadb01.cs CSCDAD     32.881   99.11             1224          .014170571
sbdevdocdb01    NDOCPD    153.045   38.55           507485          .099657446
devmon01        CSWEBD    278.736   96.27            43926          .146893942
csdevtlsdb01    CSWEBD    231.877   94.96            19733          .042691703
sbdevdb01       NALFD      67.189   99.97              502          .199616499
sbdevdb01       NWEBPD     46.908   97.38             8278          .033745764
Re: Buffer_gets/Buffer_cache [message #563550 is a reply to message #563394] Tue, 14 August 2012 13:31 Go to previous messageGo to next message
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 #563558 is a reply to message #563550] Tue, 14 August 2012 16:15 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
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
Did you check out what was taking up memory with the v$bh query?
Tables that take up a lot of memory are candidates for better indexing.
Re: Buffer_gets/Buffer_cache [message #563634 is a reply to message #563558] Thu, 16 August 2012 02:51 Go to previous messageGo to next message
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 1624 times)
Re: Buffer_gets/Buffer_cache [message #563638 is a reply to message #563634] Thu, 16 August 2012 03:39 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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) */
What is the point of hinting dynamic sampling (especially with a spelling mistake) and then trying to force a full table scan? Which you don't actually get.
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 Go to previous message
cookiemonster
Messages: 13917
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
Previous Topic: Need help with Queries
Next Topic: how to find all indexes scripts in particular schema
Goto Forum:
  


Current Time: Thu Mar 28 17:38:31 CDT 2024