Link List:
Check refresh mode, refresh method and last refresh time from USER_MVIEWS:
SELECT mview_name, refresh_mode, refresh_method, last_refresh_type, last_refresh_date FROM user_mviews;
Change of refresh behaviour in 10g
Upgrading from Oracle 9i to Oracle 10g will change the MV refresh behaviour. Oracle 10g will use the DELETE command to remove rows and a normal INSERT to repopulate it. In Oracle 9i and earlier releases, Oracle did a TRUNCATE and INSERT /*+APPEND*/, which is more efficient, but had the side effect that users will see no rows while the refresh is taking place.
If you prefer the older truncate/append behaviour, change the refresh method to set atomic_refresh = false. Here is an example:
BEGIN
-- use this with 10g/11g to return to truncate/append behavior
dbms_mview.refresh('MY_TEST_MV', method=>'C', atomic_refresh=>false);
END;
/
ATOMIC_REFRESH
Atomic_refresh parameter is to allow either all of the tasks of a transaction are performed or none of them (Atomicity). If set to TRUE, then all refreshes are done in one transaction. If set to FALSE, then the refresh of each specified materialized view is done in a separate transaction.If set to FALSE, Oracle can optimize refresh by using parallel DML and truncate DDL on a materialized views.
If you set that to FALSE, it’ll do a truncate + insert /*+ append */ on a FULL refresh.
When atomic_refresh=>true, Oracle performs deleting from MView table. When atomic_refresh=>false, Oracle truncates the MView table.
Atomic refresh does a
a) truncate (data disappears right away, poof)
b) insert /*+ APPEND */ – direct path load, which maintains indexes
c) commits (data reappears)
The indexes won’t/don’t go unusable, but the materialized view “disappears” for the duration of the refresh.
Source / Reference Links:
Query on v$sysstat
Redo generated since instance started:
SELECT name, value
FROM v$sysstat
WHERE name like ‘%redo%’
order by name
/
redo blocks read for recovery 0
redo blocks written 6075463
redo buffer allocation retries 21
redo entries 11244851
redo log space requests 25
redo log space wait time 278
redo log switch interrupts 0
redo ordering marks 161393
redo size 2987873528
redo subscn max counts 0
redo synch time 5684
redo synch writes 96011
redo wastage 18833908
redo write time 43250
redo writer latching time 6
redo writes 72039
Daily Count and Size of Redo Log Space (Single Instance):
SELECT A.*, Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM ( SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max# FROM v$log_history GROUP BY To_Char(First_Time,'YYYY-MM-DD') ORDER BY 1 DESC ) A, ( SELECT Avg(BYTES) AVG#, Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes FROM v$log ) B;
Total Redo Log Size:
select sum(value)/1048576 redo_MB from sys.gv_$sysstat where name = 'redo size'; REDO_MB ----------------------- 1938.911716461181640625
Find the number of archivelogs generated each hour of the day:
col MidN format 999 col 1AM format 999 col 2AM format 999 col 3AM format 999 col 4AM format 999 col 5AM format 999 col 6AM format 999 col 7AM format 999 col 8AM format 999 col 9AM format 999 col 10AM format 999 col 11AM format 999 col Noon format 999 col 1PM format 999 col 2PM format 999 col 3PM format 999 col 4PM format 999 col 5PM format 999 col 6PM format 999 col 7PM format 999 col 8PM format 999 col 9PM format 999 col 10PM format 999 col 11PM format 999 select to_char(first_time,'mm/dd/yy') logdate, sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN", sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM", sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM", sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM", sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM", sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM", sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM", sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM", sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM", sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM", sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM", sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM", sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon", sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM", sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM", sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM", sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM", sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM", sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM", sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM", sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM", sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM", sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM", sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM" from v$log_history group by to_char(first_time,'mm/dd/yy') order by 1 /
Segments causing redo log generaion:
SELECT to_char(begin_interval_time,'YY-MM-DD HH24') snap_time,
dhso.object_name,
sum(db_block_changes_delta) BLOCK_CHANGED
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND begin_interval_time BETWEEN to_date('12-02-12 08:00','YY-MM-DD HH24:MI')
AND to_date('12-02-13 08:00','YY-MM-DD HH24:MI')
GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'),
dhso.object_name
HAVING sum(db_block_changes_delta) > 0
ORDER BY sum(db_block_changes_delta) desc ;
Also Check:
Reference / Source Links :
Here are some tutorials and articles that helps you to get a quick start onto MySQL:
Buffer pool or buffer cache is a memory structure within Oracle SGA that is used for caching data blocks in the memory. Accessing data from the memory is significantly faster than accessing data from disk. The goal of block buffer tuning is to efficiently cache frequently used data blocks in the buffer cache (SGA) and provide faster access to data.
Tuning block buffer is a key task in any Oracle tuning initiative and is a part of an ongoing tuning and monitoring of production databases. The task of database performance optimization by allocating appropriate memory structure within SGA is a very interesting and challenging task for a DBA. Generally, it takes a few iterations before you can reach to a satisfactory memory allocation. This article presents a tuning approach that greatly simplifies the tuning effort. The methodology discussed here will help DBAs in creating a proactive database tuning and monitoring strategy for their production databases. If you are a DBA responsible for tuning a database without enough physical memory on the server, you will find this article very informative and useful. With the help of an established methodology and tuning strategy, you can easily identify the problem areas and tune the SGA memory structure.
Using this methodical approach, a DBA can easily identify the problem areas, and tune the database block buffers. The DBA can create the following buffer pool to efficiently cache data blocks in SGA:
KEEP
Cache tables that are very critical for system performance. Typically, lookup tables are very good candidates for the KEEP pool. The DBA should create the KEEP pool large enough to maintain 99% buffer hit ratio on this pool.
RECYCLE
Cache tables that are not critical for system performance. Typically, a table containing historical information that is either rarely queried or used by batch process is a good candidate for the RECYCLE pool. The DBA should create the RECYCLE pool large enough to finish the current transaction.
DEFAULT
Cache tables that do not belong to either KEEP or RECYCLE pool.
The DBA can setup OEM jobs, Oracle statspack, or custom monitoring scripts to monitor your production database block buffer efficiency, and to identify and tune the problem area.
Draft Article
Source / Reference Links:
Don’t Let Referential Integrity Degrade Your Database Performance
Install statspack
cd $ORACLE_HOME/rdbms/admin sqlplus "/ as sysdba" @spdrop.sql -- Drop and install statspack sqlplus "/ as sysdba" @spcreate.sql -- Enter tablespace names when prompted
Take performance snapshots of the database
sqlplus perfstat/perfstat exec statspack.snap; -- Take a performance snapshots -- or exec perfstat.statspack.snap(i_snap_level=>10); -- or instruct statspack to do gather more details in the snapshot -- (look up which oracle version supports which level)
The spauto.sql script can be customized and executed to schedule the collection of STATPACK snapshots.
Statspack reporting
-- Get a list of snapshots select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT; @spreport.sql -- Enter two snapshot id's for difference report
Source:
Oracle error: “data got committed in another/same session, cannot update row.”
When this error continued to pop up, the only solution that worked for me was this from OTN Discussion Forums:
Just a heads up, if you really need to get that column updated: 1. Copy and edit the value to a safe place such as notepad or another sql developer worksheet, don't try to save in the table. 2. In a sql developer worksheet, make an update query to set the BLOB/CLOB field to NULL 3. Run the update query and commit 4. refresh your table view, the BLOB/CLOB should be NULL now 5. Copy the value back in and commit This worked very well for me but it must be done in the same sql developer instance that is throwing the error. I tried to have a coworker null the field and it did not affect my session at all even after refreshing the table. Also my coworker's sql developer showed the field to be NULL even before I asked them to try to edit it.
Memory Notification: Library Cache Object loaded into SGA Heap size ** K exceeds notification threshold (8192K)
These are warning messages only which should not cause any problems. These warning messages appear due to new event messaging mechanism and memory manager in 10g Release 2.
In 10g, there is a new undocumented parameter that sets the KGL heap size warning threshold. This parameter was not present in 10gR1. Warnings are written if heap size exceeds this threshold.
Set _kgl_large_heap_warning_threshold to a reasonable high value or zero to prevent these warning messages. Value needs to be set in bytes.
To find out the hidden ‘_kgl_large_heap_warning_threshold’ parameter value in the database:
SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '%kgl_large_heap%'; Parameter Session Value Instance Value --------------------------------- ------- ------- _kgl_large_heap_warning_threshold 2097152 2097152
If you want to set this to 8192 (8192 * 1024) and are using an spfile:
(logged in as “/ as sysdba”)
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ; SQL> shutdown immediate SQL> startup If using an "old-style" init parameter,edit the init parameter file and add _kgl_large_heap_warning_threshold=8388608
The default threshold in 10.2.0.1 is 2M. So these messages could show up frequently in some application environments. In 10.2.0.2, the threshold was increased to 50MB after regression tests, so this should be a reasonable and recommended value.
Check your product version:
select BANNER from sys.v_$version;
Source / Reference:
Data required by oracle user process is loaded into buffer cache, if it is not already present in cache. Proper memory tuning is required to avoid repeated disk access for the same data. This means that there should be enough space in buffer cache to hold required data for long time. If same data is required in very short intervals then such data should be permanently pinned into memory. Oracle allows us to use multiple buffers. Using multiple buffers we can control that how long objects should be kept in memory.
Note that in Oracle 10g, the terms are changed to the keep cache and the recycle cache. However, many people still refer to these two as pools. Some of the Oracle documentation still uses the term pool when referring to these caches.
Keep Buffer Pool
Data which is frequently accessed should be kept in Keep buffer pool. Keep buffer pool retains data in the memory. So that next request for same data can be entertained from memory. This avoids disk read and increases performance. Usually small objects should be kept in Keep buffer. DB_KEEP_CACHE_SIZE initialization parameter is used to create Keep buffer Pool. If DB_KEEP_CACHE_SIZE is not used then no Keep buffer is created. Use following syntax to create a Keep buffer pool of 40 MB.
DB_KEEP_CACHE_SIZE=40M
To pin an object in Keep buffer pool use DBMS_SHARED_POOL.KEEP method.
Recycle Buffer Pool
Blocks loaded in Recycle Buffer pool are immediate removed when they are not being used. It is useful for those objects which are accessed rarely. As there is no more need of these blocks so memory occupied by such blocks is made available for others data. For example if ASM is enabled then available memory can be assigned to other SGA components . Use following syntax to create a Recycle Buffer Pool
DB_RECYCLE_CACHE_SIZE=20M
Default Pool
If an object is not assigned a specific buffer pool then its blocks are loaded in default pool DB_CACHE_SIZE initialization parameter is used to create Default Pool. For more information on Default Pool visit following link,
BUFFER_POOL value in storage clause of schema objects lets you to assign an object to a specific Buffer pool. Value of BUFFER_POOL can be KEEP,RECYCLE or DEFAULT.
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
set pages 999
set lines 92
spool keep_syn.lst
drop table t1;
create table t1 as
select
o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks
from
dba_objects o,
v$bh bh
where
o.data_object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM')
and
bh.status != 'free'
group by
o.owner,
o.object_name,
o.subobject_name,
o.object_type
order by
count(distinct file# || block#) desc
;
select
'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);'
from
t1,
dba_segments s
where
s.segment_name = t1.object_name
and
s.owner = t1.owner
and
s.segment_type = t1.object_type
and
nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
and
buffer_pool <> 'KEEP'
and
object_type in ('TABLE','INDEX')
group by
s.segment_type,
t1.owner,
s.segment_name
having
(sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80
;
spool off;
Source / Reference Links:
Foreign keys produce potentially damaging locking problems if the foreign key columns on the child table are not indexed. Below query lists all of the foreign keys that do not have the appropriate indexes in place on the child table. It shows the foreign key constraints that cause locking problems.
SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name
||'['||acc.position||'])'||' ***** Missing Index'
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
AND (acc.owner, acc.table_name, acc.column_name, acc.position)
IN
(SELECT acc.owner, acc.table_name, acc.column_name, acc.position
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
MINUS
SELECT table_owner, table_name, column_name, column_position
FROM all_ind_columns)
ORDER BY acc.owner, acc.constraint_name,
acc.column_name, acc.position;
By creating an index on the foreign key of the child table, “table-level” locks can be avoided.
Keep in mind that you will often be creating an index on the foreign keys in order to optimize join and queries. However, if you fail to create such a foreign key index and if the parent table is subject to updates, you may see heavy lock contention. If ever in doubt, it’s often safer to create indexes on ALL foreign keys, despite the possible overhead of maintaining unneeded indexes.
Source / Reference links: