Performance Tuning :: Oracle Buffer Versus AIX Filesystem Cache
May 7, 2011
I am currently in the favorable situation in which I have excess amounts of memory available on the database server - a single node setup. The server only serves the single instance and no other processing. Database size is around 2.3tb and memory is 50gb. For the majority of processing, AIX is allocating a significant amount (anywhere from 30-40%) of the memory to the AIX file system cache (persistent pages).
I've been trying to find documentation about this, but have not had any luck yet. My guess is that it would be better to allow Oracle to cache this data - meaning increase the SGA target and max size to allow for a larger buffer cache. However, the nice thing about the AIX cache is if process memory is needed, the file system cache gives up pages. If the memory was allocated to the SGA, its pretty much locked in.
I have read several articles stating that a larger buffer cache is not always better, as a larger cache takes more management. But having both of the caches active seem to be a waste of memory, effectively storing the data twice - once in AIX persistent pages and a second time in Oracle database buffer cache.
View 4 Replies
ADVERTISEMENT
Nov 30, 2012
Considering the below factors, I am planning to increase the buffer cache value from 256Mb to 512Mb.
1. Buffer cache hit ratio value is around 35% even in the normal period.
2. free buffer requested value is below during peak & normal hours below.
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
free buffer requested 54,694,995 15,226.9 2,523.7
free buffer requested 23,412,674 6,501.7 2,585.9
3. most of the top 5 physical reads & logical reads queries are well tuned and some of queries are doing FTS on small tables (table count min 1500 max 35000). SO indexing option is not required for these queires. But these queries getting executed frequently.
SQL> show sga
Total System Global Area 2148534928 bytes
Fixed Size 731792 bytes
Variable Size 1879048192 bytes
Database Buffers 268435456 bytes
Redo Buffers 319488 bytes
5.top 5 waitevents during db slow performance & high cpu utilization (>80%) issue.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free 1,848,898 153,793 52.00
buffer busy waits 395,280 87,201 29.49
db file scattered read 3,488,648 34,199 11.56
enqueue 4,052 10,897 3.68
CPU time 5,567 1.88
6. Top 5 waitvents during normal activities and CPU utilization is around 40%.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 1,860 45.32
db file scattered read 1,133,669 985 23.99
imm op 776 605 14.73
sbtinfo2 208 139 3.40
sbtbackup 2 123 3.00
View 11 Replies
View Related
Sep 27, 2010
1) Is shutting down the DB flush all the data buffers, from the buffer Cache?
2) In any oracle version, do we have any way to flush only the buffer Cache.
View 1 Replies
View Related
Jan 15, 2011
I have some confusion about Keep Pool in Buffer Cache.
1. What is the reasoning for placing a table in the KEEP buffer pool because if it is frequently accessed, it will be around when needed (ie if it is constantly being accessed it will not age out) .
2. Would the table be still in the Default Pool if the Keep Pool is not sized and the command is being issued alter TABLE SCOTT.EMP storage (buffer_pool keep) ?
3. If the database is restarted will the table be wiped out of the Keep Pool and again be pinned to the Keep Pool ?
View 2 Replies
View Related
Oct 3, 2010
Is there any relationship b/w tuning BUFFER CACHE and BUFFER BUSY WAITS?
1) Buffer Busy Waits are happening as the User process found the same Datablock is being used by another user in the BUFFER CACHE.
2) And also happens, when the server process found the same Datablock are being used in the Datafile.
View 5 Replies
View Related
Jul 30, 2012
Does cache buffer chain latch and buffer busy wait event are related to one any another.
Latch definition from Google says : Latches are simple, low-level serialization mechanisms to protect shared data structures in the system global area (SGA).
what does it mean my protect. Does this mean protects from aging as per LRU algorithm and getting removed from SGA
or
protect from other processes ,say from example from simultaneously DML operations.
or
both
Does buffer busy wait event occurs , because of the cache buffer chain latch ?
View 3 Replies
View Related
Mar 3, 2011
Database : 9.2.0.7
Os : windows 2003 sevrer standdard edition
RAM 4 Gigs
The buffer cache hit ratio in this server is around 83%, where it normaly was around 98% before i did some maintenance activities.
I have done some maintenance activities in January on this database.
Maintenance activties includes below steps
1.In production i have deleted old data in the production tables
2.Reorganized tablespaces,tables
3.Rebuild indexes for those tables.
4. At last collected statistics for those tables.
Now after this activity the buffer cache hit ratio is very low.
View 8 Replies
View Related
May 30, 2013
I have a serious doubt in oracle architecture functionality, when a user issues a update statement the data blocks are carried to db buffer cache and where does the changes to the data blocks are made???? Does a copy of the data block is kept in db buffer cache and the changes are made to the block in buffer cache?? or the a copy of the data block is kept in undo tablespace and changes are made to the blocks in the undo tablespace???
In simple the changes to the data blocks are made at db buffer cache or undo tablespace?
View 7 Replies
View Related
May 16, 2013
Is db_cache_advice = ON still necessary when sga_target is set?
View 2 Replies
View Related
Jan 27, 2011
I was comparing cost of rebuild vs create index...I carried out the following test
SQL> create table t4 as select * from t1;
Table created.
SQL> create table t5 as select * from t1 where 1=2;
Table created.
SQL> create index i5 on t5(id);
Index created. SQL> select bytes,extents,blocks from user_segments where segment_name='I5';
BYTES EXTENTS BLOCKS
---------- ---------- ----------
65536 1 8
SQL> alter index i5 unusable;
Index altered.
SQL> alter table t5 nologging;
Table altered.
SQL> Alter session set skip_unusable_indexes=True;
Session altered.
SQL> insert /*+ append */ into t5 select * from t1;
563904 rows created.
SQL> commit;
Commit complete.
Now I compared the cost (elapsed time, logical I/O) of the operations
create index i4 on t4(id);
Vs
alter index i5 rebuild online;
Following is the related trace of above 2 steps
create index i4 on t4(id)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 1.17 3.38 9497 7869 335 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.17 3.38 9497 7870 335 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5
[code]....
So which option we shall pick in such cases? {Of course I haven't set 'nologging' for the indices but it is same for both indices we are comparing}
View 2 Replies
View Related
Dec 20, 2011
I noticed oracle background process ora_fbda_padwsdpr is suffering from buffer busy wait. When i further finding the object, it was on SYS_FBA_FA tables.
what is this is causing BUFFER BUSY WAIT. Also to add we have disabled flashback database.
View 4 Replies
View Related
Jul 4, 2011
In order to improve the performance of our live server, I am trying to do an exhaustive comparison with our test environment which is quite quick in spite of the fact that we port the data from Live every month.
There are no obviously slow queries appearing in the the top SQLs of AWR, we have optimised such things already. Right now it is about general uplift rather than SQL based tuning.
I picked up random SQLs and I noticed a marked differences in the execution time. Typically they are 3 to 4 times and there are cases much more than that.
1. I observed that, while the explain plan of the queries are same, trace of the queries give a different picture. I have observed that the recursive calls, consistent gets and sorts(memory) are quite high on Live.
2. I have no solid reasons to say this but my instincts tell me that the recursive calls is the major contributing factor. It is sometimes 2000+ for an SQL.
3. On googling more on that, it finally made me compare the data dictionary on the AWR report of test and Live.
The dc_objects caught my eyes. In that 4 hour AWR, there were about 10 million get requests and the pct miss was ~10. For similar load, the test server had 5 million gets with 0.08 PCT miss for 4 hours.
View 27 Replies
View Related
Dec 9, 2011
If you have 3 tables (yr09, yr10,yr11) one with 2009 data, 2010 and 2011 data respectively. And a view (vw_yr091011) with a "union all" on all three.
Question: Will the performance be same for the following two queries ?
Question: Will Oracle read all 3 tables in the view when we search for only one year ?
select count(*) from yr09
where year = 2009;
-- vs
select count(*) from vw_yr091011
where year = 2009;
The following link says yes, the performance remains the same.
Link: [URL]..........
when I tried on a volume of 14000 records. The count came out same but the view took 50 more sec. And the explain plan shows it accessed all three tables.
View 9 Replies
View Related
Oct 2, 2013
I've have a system where the most buffer waits are spent on system tables like
SYS.DBMS_ALERT_INFO
and SYS.DBMS_LOCK_ALLOCATED
Would it convert the system tablespace from dictionary managed to locally managed?
View 7 Replies
View Related
Aug 11, 2010
I have OEl 5.5 installed and patched. I have my SSD mounted..
but when i ran the command 'alter system set db_flash_cache_file ='/media/ssd/' scope=spfile;'
It wouldn't startup giving ORA-01078: failure in processing system parameters...
View 6 Replies
View Related
Jan 2, 2013
I'm trying to demonstrate the working of the OCI client result cache. I've set some parameters,orcl> sho parameter result_cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 1000000
result_cache_max_result integer 5
result_cache_max_size big integer 1984K
result_cache_mode string FORCE
result_cache_remote_expiration integer 0
If I understand the docs correctly, that should be all that is needed.I've complied and run the cdemoqc and cdemoqc2 OCI demos, but I never get anything in the V$CLIENT_RESULT_CACHE_STATS or CLIENT_RESULT_CACHE_STATS$ views. This is probably because the sessions exit after running the queries.
So I've also tried repeating arbitrary queries through cdemo2, which gives a persistent session, but there is still nothing in those views and furthermore the v$result_cache_objects.scan_count for my queries keeps increasing. So I don't think think the client side cache is working.
View 4 Replies
View Related
Jul 4, 2011
i am testing a proc after tuning it but the problem is, it is taking a very very less time which it shouldn't. I know that it is because of the buffer cache and the shared pool. that why i need to clean the cache to retest it.
I cannot bounce the database as other schemas are part of it. so is there any way to clean the cache for that particular schema i.e bouncing any particular schema(i know that the term is not appropriate).
View 4 Replies
View Related
Aug 18, 2010
How does standard vs advanced handle index compression? Is there an advantage to having one vs the other besides the incurred cost?
View 3 Replies
View Related
Aug 15, 2011
I have two design alternatives and need to understand how expensive (speed) is one of them against the other for a medium size table (100K-200K records):
create table xyz
(
f1 number not null,
f2 varchar2(20) not null,
f3 number not null,
f4 varchar2(50),
[code]....
the idea is to optimize the design by using a PK instead of the 3 keys and there is a debate that searching a unique index field(2nd scenario) is of the same speed than searching a PK field (1st scenario).
View 5 Replies
View Related
Feb 22, 2013
how can we check the size of data buffer cache.
View 7 Replies
View Related
Mar 3, 2010
What's the difference between a dirty buffer and a redo buffer?
My understanding is that a dirty buffer is a changed buffer or whenever data changes in the buffer cache, it's marked as dirty. Also, a redo buffer keeps track of changes that were made to the data, so it's also referring to changed data as well...DWBn writes dirty buffers to disk and LGWR writes redo data to redo log filesHow can we differentiate between the two?
View 2 Replies
View Related
May 20, 2013
what will be best buffer cache hit ratio for a database for good performance
note : in general
View 2 Replies
View Related
Apr 18, 2012
I want to know what exact process happens in oracle architecture when an update query is fired.
View 1 Replies
View Related
Jun 28, 2011
I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
View 6 Replies
View Related
Mar 26, 2012
give a script to find how much my db buffe cache and redo log buffer cache is used and how much is free.
View 1 Replies
View Related
Aug 19, 2010
1)If i issue a DELETE statement to delete a row, will this statement drag any data from the datafile to database buffer? How is the change made by a DELETE statement recorded in buffer cache? How is this change then applied to the data in datafiles after commit?
View 7 Replies
View Related
Mar 28, 2011
How can i know which objects used keep buffer cache?
View 5 Replies
View Related
Apr 25, 2013
I want to simulate latch : cache buffer chains wait event due to use of nested loop join for lookup tables
This is what a tried :
-- create parent / child tables
SQL>drop table emp1 purge;
drop table dept1 purge;
create table dept1 (dept_id number primary key,
dept_name char(30));
[Code]....
I traced many queries like the one given below (dept_id between 1 and n where n varied from 10 to 1000) but they always result in hash join
1* select d.dept_name, e.id from sys.dept1 d, sys.emp1 e where d.dept_id = e.dept_id and e.dept_id < 1000
Execution Plan
----------------------------------------------------------
Plan hash value: 619452140
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 998K| 41M| 680 (2)| 00:00:09 |
|* 1 | HASH JOIN | | 998K| 41M| 680 (2)| 00:00:09 |
|* 2 | TABLE ACCESS FULL| DEPT1 | 999 | 34965 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP1 | 999K| 8780K| 672 (2)| 00:00:09 |
----------------------------------------------------------------------------
what can I do to get a nested loop join to simulate latch : cache buffer chains?
View 10 Replies
View Related
Jun 14, 2013
If sga Buffer Cache Size consume full of SGA_target size, if possible that it will cause performance any issue. I am facing CPU 100% consuming while single query execute, Which query generate monthly report data.
I have two question
1)How to fix the CPU 100% consuming
2)How to find total number user hit oracle specific schema.
Oracle 10.2.0.5 Standard
Sga_target : 14G
Sga_max :20G
Pga :3G
Below SGA details
NAME BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size 2.01795197
Redo Buffers 13.9804688
Buffer Cache Size 13632
Shared Pool Size 640
Large Pool Size 16
Java Pool Size 16
Streams Pool Size 16
Granule Size 16
Maximum SGA Size 20480
Startup overhead in Shared Pool 208
Free SGA Memory Available 6144
View 2 Replies
View Related
Jan 26, 2011
Say Database Buffer Cache configured as 2M and my updates may use 4m size,will it throw an error message or update will happen perfectly without any issues?
View 2 Replies
View Related
