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 RepliesHow can i know which objects used keep buffer cache?
View 5 Replies View RelatedSay 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 RelatedDoes 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 ?
how can we check the size of data buffer cache.
View 7 Replies View Relatedwhat will be best buffer cache hit ratio for a database for good performance
note : in general
I want to know what exact process happens in oracle architecture when an update query is fired.
View 1 Replies View Relatedgive 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 RelatedDatabase : 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.
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
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.
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 ?
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?
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
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.
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.
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?
I learnt that logWriter writes in the redo log files when redo log buffer is 1/3 full, it means that 66 % of redo log buffer are always empty and never used,
if no, isn't a waste of memory (66 % always empty !)
How to move a partition of a table to db_keep_cache ?
View 8 Replies View RelatedI am in charge of several instances located on a Linux server CentOS, virtualized on a ESX 3.5 environment.
From time to time (every 4 to 5 days), I have some errors in the alert.log. Last occurence was last night :
Corrupt block relative dba: 0x01004e12 (file 4, block 19986)
Fractured block found during buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x01004e12
last change scn: 0x0000.131aaa5b seq: 0x2 flg: 0x04
[Code] ........
We are doing user manual backup (with BEGIN/END BACKUP) every night at 8PM, ending at 9PM approx. Then, fractured blocks never occur during backups. At 1AM, the maintenance window is opening, thus explaining the GATHER_STATS_JOB job.
When I check corruption on early morning, I am always unable to reproduce the problem. DBV is OK without issues. We never had a problem with the data itself, whatever it is a table or an index in the reported failed block.
I would like to know what could cause these logical corruption, and how to stop them ?
We are getting Negative values of Library cache hit ratio in AWR Report of 11g(11.2.0.3) with Solaris[tm] OE (64-bit). Why it shows negative value.
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.87 Redo NoWait %: 99.99
Buffer Hit %: 92.17 In-memory Sort %: 100.00
Library Hit %: -3,321.23 Soft Parse %: 81.95
Execute to Parse %: 92.88 Latch Hit %: 95.11
Parse CPU to Parse Elapsd %: 87.25 % Non-Parse CPU: 81.39
How can i delete a database? i using the dbca,but the Delate a Database can not use,why?
View 3 Replies View RelatedHow can I delete all the objects from my database?
View 18 Replies View RelatedHow to delete flashback log and to release the space?
I guess that it maybe like archive log ,it can release the space using RMAN.But when i try a test ,it fail.
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE FLASHBACK OFF;
I have got oracle 11g release 1, I need recomendation, how can I delete safely content from the folder C:oraclediag dbmsinstancenameinstancenameincident?
can i delete files manually which are not useful?
I am trying to delete a database using dbca, but the database configuration option and delete option are not enabled. It's grayed.
View 1 Replies View RelatedWhy large delete statements, with no where clauses, could be using up temp tablespace segments? I thought temp tablespace was just for sort operations,joins, etc.
We have a weekly job that inserts rows into a staging table, deletes, with commit, the a few minutes later and repeats the process. Each delete is using some of the temp tablespace. Eventually we run out of space (ora-01652). That's another thing. I would have thought the space would be released for the next process, but it's now.
SNAP_TIME USERNAME SESSION_ADDR SESSION_NUM MB SQLHASH SQLADDR SQL_TEXT
2011-06-15; 19:10 SCHEMA_USER 0700000209286B00 49680 980 3165065004070000020A8F9F20 DELETE FROM SCHEMA.FIN_RECORDS_TO_EVAL
2011-06-15; 19:15 SCHEMA_USER 070000020F2A7670 10601 275 3165065004070000020A8F9F20 DELETE FROM SCHEMA.FIN_RECORDS_TO_EVAL
2011-06-15; 19:20 SCHEMA_USER 070000020925FE38 19368 4748 3165065004070000020A8F9F20 DELETE FROM SCHEMA.FIN_RECORDS_TO_EVAL
2011-06-15; 19:25 SCHEMA_USER 070000020925FE38 19368 15783 3165065004070000020A8F9F20 DELETE FROM SCHEMA.FIN_RECORDS_TO_EVAL
We will probably switch to truncate statements, but this situation really puzzles me. I've tried view the docs but don't see any of delete statements using temp tablespace.
we are getting below error:
ora-00257 archiver error. connect internal only until freed
when we tried to remove the unwanted arc files thro ASMCMD,we are getting below error:
ASMCMD> rm -ef 2011_04_05/
Unknown option: e
usage: rm [-rf] <name1 name2 . . .>
ASMCMD> rm -rf 2011_04_05/
ORA-15032: not all alterations performed
ORA-15028: ASM file '+XCOM_BACKUP_DG/TXCOM/ARCHIVELOG/2011_04_05/thread_2_seq_27215.1143.747641143' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ORA-15032: not all alterations performed
ORA-15028: ASM file '+XCOM_BACKUP_DG/TXCOM/ARCHIVELOG/2011_04_05/thread_3_seq_21762.826.747641143' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)
further we checked FRA size:
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 3.19 0 38
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
and checked any arc processing holding lock on arc files:
> ps -ef | grep -i ora_arc*
oracle 6989 1 0 15:07 ? 00:00:00 ora_arc0_TXCOM1
oracle 6991 1 0 15:07 ? 00:00:00 ora_arc1_TXCOM1
oracle 12246 12164 0 15:17 pts/4 00:00:00 grep -i ora_arc*
oracle 13452 1 0 Mar23 ? 00:01:07 ora_arc0_TWEBAPPS1
oracle 13454 1 0 Mar23 ? 00:00:30 ora_arc1_TWEBAPPS1
oracle 15402 1 0 Mar23 ? 00:00:50 ora_arc0_SXCOM1
[Code] ........
but we were not able to remove those .arc files from that folder. finally we have down all the instances and deleted those files manually.
Any impact is there if is do the following:
ALTER TABLE MY_TABLE TRUNCATE PARTITION P1 UPDATE GLOBAL INDEXES;
will there be any lock on the table during this operation? DML operations will work without any issue or not?
What is the difference between cache fusion and Cache Coherency. Both are same or different functionality.
View 1 Replies View Related