Sql Result Cache

Istedigimiz bazi sorgularin sonuclari diskten fiziksel I/O yapmaktansa memoryden getirebilme yeniligidir. Boylece fiziksel I/O nun neden oldugu Cpu artislarinada bir onlem almis oluruz.
Result cache query’in sonuclarini butun sessionlarin ortak kullanimina acar.aslinda client side veserver side olarak birbirinden ayirmak lazimdir.Client tarafinda SGA kullanilmaz.Result_cache_mode diye yeni bir parametremiz gelmistir.
Bu parametre Manuel ve force olmak uzere iki secenekten olusur.
Manuel modda /*+result_cache */ hintini kullanmamiz lazimdir.Select /*+ result_cache */ job_id,max(salary) from employees group by job_id gibiforce modda ise otomatik olarak sql result cache kullanilir.Force modda kullanmak istemezsek /*+no_result_cache*/ hinti tercih edilebilir.CLIENT_RESULT_CACHE_SIZE parametresi ile clientlarin ne kadar result cache sahip olacaklarina karar verebiliriz. 0 olmasi disabled olmasi anlamına gelir.CLIENT_RESULT_CACHE_LAG parametresi ilede ayrica clientlarin ne kadar sıklıkta veritabanı ile sekronize olmasini saglariz.Tabiki boyle bir ozelliginde CLIENT_RESULT_CACHE_STATS$ monitor edilmesi mumkundur.
SQL> set serveroutput onSQL> execute dbms_result_cache.memory_reportR e s u l t C a c h e M e m o r y R e p o r t[Parameters]Block Size = 1024 bytesMaximum Cache Size = 950272 bytes (928 blocks)Maximum Result Size = 47104 bytes (46 blocks)[Memory]Total Memory = 46340 bytes [0.048% of the Shared Pool]… Fixed Memory = 10696 bytes [0.011% of the Shared Pool]… State Object Pool = 2852 bytes [0.003% of the Shared Pool]… Cache Memory = 32792 bytes (32 blocks) [0.034% of the Shared Pool]……. Unused Memory = 30 blocks……. Used Memory = 2 blocks……….. Dependencies = 1 blocks……….. Results = 1 blocks…………… SQL = 1 blocksPL/SQL procedure successfully completed.To remove all existing results and clear the cache memory, use the command:
SQL>execute dbms_result_cache.flushselect name, value from v$result_cache_statistics;The output of this query will be similar to the following:NAME VALUE——————– ———-Block Size (Bytes) 1024Block Count Maximum 3136Block Count Current 32Result Size Maximum (Blocks) 156Create Count Success 2Create Count Failure 0Find Count 0Invalidation Count 0Delete Count Invalid 0Delete Count Valid 0Block Size (Bytes)her memory blogunun sizeBlock Count Maximummax izin vereilen memory block sayisiBlock Count Currentsuanda nekadar memeory blogu ayrıldıgıResult Size Maximum (Blocks)tekbir result cache max. nekadar ayrıldıgıCreate Count Successkac kere basarılı bir sekilde result cache yaratıldıCreate Count Failurekac kere fail ettiFind CountKac kere basarılı bir sekilde result cache bulunduInvalidation Countinvalidation sayısıDelete Count Invalidsilinen invalid cacheDelete Count Validsilinen valid cachebir sistemin SQL query result cache iyi kullandıgının gostergesi Create Count Failure ve Delete Count Valid dusuk olmasidir ve find countun yuksek olmasıdır

About oracledocuments

Zekeriya Beşiroğlu, It joined the Bilginc IT Academy in July 2000. In the meantime, the Oracle Education, Oracle Data Base Management System and Oracle Internet Technologies, Oracle development technologies such as the training of Oracle products is responsible for training as consultants. During this task, since 1 April 2008 with 293 Oracle training in total, in 2150 Oracle customers successfully submitted. Oracle 10g New York in November 2002 have received training and education in Turkey was the first time the consultants. 20 April 2008 on education in Turkey 11g is the first who was a consultant. Oracle Real Application Cluster Expert advisor is certified is the first . Http://zekeriyabesiroglu.blogspot.com and Http://www.oracleforum.info owner and manager of the site. Its own has more than one hundred articles. In addition, creation and improvement of training materials are also related to work. Oracle products are used, database management and reporting Academy eruditely It also is working on. From the date of 1 January 2009 will continue to work as the Technical Director
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s