Materialized view – NOLOGGING
Compares refreshing materialized view with NOLOGGING
1) Refresh Materialized view with NOLOGGING
-- Capture redo size before refreshing materialized view
SQL> SELECT vs.name, vm.value
FROM v$mystat vm, v$statname vs
WHERE vm.statistic# = vs.statistic#
AND vs.name like ‘%redo size%’;
NAME VALUE
—————————————————————- ———-
redo size 148331524
-- Check if the materialized view has nologging turned on
SQL> SELECT logging FROM user_tables WHERE table_name = ‘SALES_MV’;
LOG
—
NO
-- Passing atomic_refresh will refresh the table with nologging if the materialized view is set to nologging
SQL> exec dbms_mview.refresh(‘sales_mv’, atomic_refresh=>false);
PL/SQL procedure successfully completed.
-- Capture redo size after refreshing materialized view
SQL> SELECT vs.name, vm.value
FROM v$mystat vm, v$statname vs
WHERE vm.statistic# = vs.statistic#
AND vs.name like ‘%redo size%’;
NAME VALUE
—————————————————————- ———-
redo size 157719208
2) Refresh materalized view which has NOLOGGING turned on without using atomic_refresh option which defaults to true
-- Capture redo size after refreshing materialized view
SQL> SELECT vs.name, vm.value
FROM v$mystat vm, v$statname vs
WHERE vm.statistic# = vs.statistic#
AND vs.name like ‘%redo size%’;
NAME VALUE
—————————————————————- ———-
redo size 157719208
SQL> exec dbms_mview.refresh(‘sales_mv’);
PL/SQL procedure successfully completed.
SQL> SQL> SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like ‘%redo size%’;
NAME VALUE
—————————————————————- ———-
redo size 278564284
Compare Size using atomic refresh set to false (NOLOGGING is used) – 9,387,684
Compare Size using atomic refresh set to true – 12,0845,076
As you see the amount of redo generated is less when table is NOLOGGING and atomic_refresh is used
How to find parameters that will take into effect for new sessions?
Using the following query one can find the list of parameters that will take info effect for new sessions if the value of the parameter is changed.
SQL> SELECT name FROM v$parameter WHERE issys_modifiable = ‘DEFERRED’;
The parameter has be changed using the deferred option:
SQL> alter system set sort_area_size=65538 deferred;
System altered.
How to find the process id listening on a port?
On Windows – Example trying to find which process is listening on port 1433
Show if any process is listening on port 1433 for example
c:\temp> netstat -ano | find /I “1433″ | find /I “LISTEN”
Proto Local Address Foreign Address State PID
TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING 6244
Using tlist or task manager one can find the PID (last column) from the above command to find the process
c:\temp> tlist | find /I “6244″
6244 sqlservr.exe
On Linux
Using netsat find if any process is listening to port 22
$ netstat -an | grep 22 | grep LISTEN
tcp 0 0 :::22 :::* LISTEN
Using fuser one can find the process id listening on the port, the process id is 3788, one has to be root to see the result
[root@localhost ~]# fuser -v 22/tcp
here: 22
USER PID ACCESS COMMAND
22/tcp root 3788 f…. sshd
Using lsof using the port#, below shows SSH is listening to port and process id is 3788. This command needs to be run as root.
[root@localhost ~]# /usr/sbin/lsof -i :22
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME
sshd 3788 root 3u IPv6 8374 TCP *:ssh (LISTEN)
On AIX
https://www-304.ibm.com/support/docview.wss?uid=swg21264632
How to setup/install StatsPack?
Statspack can be installed by executing the following script, it will prompt for password for user PERFSTAT, the tablespace for PERFSTAT and temporary tablespace for PERFSTAT.
-- script to install statspack, the script writes output to spckpg.lis and after the script you are logged in as user PERFSTAT
@?/rdbms/admin/spcreate.sql
…
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
SQL> show user;
USER is “PERFSTAT”
-- script to install statspack, login as sysdba
@?/rdbms/admin/spdrop.sql
