July-13

30-July

ASH report


set linesize 200
spool a.txt
select output from table(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(l_dbid=><dbid>,l_inst_num=> 1,l_btime=>sysdate-1, l_etime=>sysdate)) ;
spool off

select output from
table(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(l_dbid=><dbid>,l_inst_num=> 2,l_btime=>sysdate-1, l_etime=>sysdate,l_sql_id =>'<sqlID>')) ;

23-July

Index / table pre-fetch can be seen in X$KCBKPFS fixed table.

Normally “db file parallel read”  (Non-contiguous multi-block read – http://www.oaktable.net/content/oracle-standard-io-waits ) can lead to index block pre-fetch.  You can see the pre-fetch statitics in v$sysstat or v$sesstat

SQL>  select name,value from v$sysstat where name like ‘%prefetch%’;

_index_prefetch_factor => Index pre-fetch factor, default is 100.  A lower value favor index pre-fetch.

_db_file_noncontig_mblock_read_count => Maximum mnumber of blocks pre-fetched in one batch.  Setting to 1 will disable pre-fetch.

19 July

x$kglcursor and x$kglcursor_child are the x$ tables for v$SQL.  Column KGLOBT03 is the SQL_ID.   V$SQL entries are coming from x$kglcursor_child while there will be  N+ 1 entries in v$kglcursor where N is the number children.  When a cursor is INVALIDATED, the entries in x$kglcursor_child will be removed and x$kglcursor will be presevered.

17 July

Some ZFS basic commands

https://blogs.oracle.com/panni/entry/zfs_basics

11 July

In-memory parallel execution:  Till 11gR2, any parallel process I/O will bypass buffer cache and directly palceed to PGA of the session.  This direct I/O will not make use of the blocks exisiting in the buffer cache.   Memory capcity of a database will get unused for any such I/O and most of the modern systems got large memory.  By enbaling In-memory Parallel execution, Oracle will place the table blocks to the buffer cache using an algorithm like, frequency of the table access, frequency of the table update, table size etc.  Once Oracle decided to use buffere cache for parallel read, the table will be fragmented and distributed among the available nodes buffer cache – known as affiinitiation.

You can disable this option in auto DOP environment using the underscore parameter _PARALLEL_CLUSTER_CACHE_POLICY=ADAPTIVE.  Parameter value CACHED will enable the this feature.

https://blogs.oracle.com/datawarehousing/entry/in-memory_parallel_execution_i

http://www.rittmanmead.com/2010/01/in-memory-parallel-execution-in-oracle-database-11gr2/

In a parallel query, if the table size is bigger than 2% of the buffer cache size, then Oracle employ  Direct I/O and read the blocks directly to PGA.  In case the table size is less than 2% of the buffer cache, then oralce will scan throough the buffer cache and do a regular I/O to read the blocks.  2% threshold can be adjusted using the parameter _PARALLEL_MIN_TABLE_THRESHOLD.

10 July

PARALLEL_ADAPTIVE_MULTI_USER :  TRUE will control the requested parallelism during the startup time based on the resource availability.  In 11gR2 introduced automatic parallelism  using PARALLEL_DEGREE_POLICY.  By setting this parameter 3 new features are enabled,

1) Auto DOP (Degree Of Parallelism)

2) Parallel Statement Queueing

3) In memory parallel execution

PARALLEL_DEGREE_POLICY = AUTO | MANUAL |LIMITED

MANUAL => None of the new features enabled

AUTO       => All the 3 new features enabled

LIMITED => Only auto DOP will be enabled

PARALLEL_SERVERS_TARGET= 100 => Number maximum parallel slaves to 100.

Auto DOP:  Oracle optimizer evaluates each SQL statement and if the estimated execution time is more than 10 seconds, is a candidate for parallele execution.  The threshold 10 second is manged thorugh a parameter PARALLEL_MIN_TIME_THRESHOLD.

Parallel Statement Queueing: If a statement requires exceeding the free slaves ( PARALLEL_SERVERS_TARGET – current usage), then oracle will queue the current statement till the number parallel servers become available.  We can see the queueing  from V$SQL_MONITOR under STATUS column, will be ‘QUEUED’.  The wait posted will be “resmgr: pq queued” (11.2.0.2).  In the case 11.2.0.1 – there are two different events “PX Queuing: statement queue” ( means this is the next statement ot run) and “Enq:  JX – SQL statement queue” ( There more statements ahead of this statement).

You can enable / disable statement queuing  using hints.  Can avoid queuing by using the hint NO_STATEMENT_QUEUING  or enable queuin for a single statement for a single SQL when auto DOP is disabled, using  STATEMENT_QUEUING.

http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-parallel-execution-fundamentals-133639.pdf

05 July

FCF can achieved using FAN events notifications.  In case of clients are not supporting FAN, should use TCP/IP connection timeout.

http://docs.oracle.com/cd/B28359_01/server.111/b28282/configbp009.htm#ABC3637411SRI12

Preventing login storms:  A node failure can drive the exisiting connecitons failover to the surviving node and spike the CPU usage and impact the availability of services.  Configure connection rate limiter to limit the connections due to a node failure.

http://www.oracle.com/technetwork/database/enterprise-edition/oraclenetservices-connectionratelim-133050.pdf

Conneciton rate listener can be configured in listener level as

CONNECTION_RATE_LISTENER=10
LISTENER=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)(RATE_LIMIT=yes))
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1522)(RATE_LIMIT=yes))
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1523))  => No limit
)

Also you can configure the connection limit for a port directly.

LISTENER= (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)(RATE_LIMIT=5))
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1522)(RATE_LIMIT=10))
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1523))
)

http://docs.oracle.com/cd/E11882_01/network.112/e10835/listener.htm#autoId9

04 July

CDB$ROOT   => Root container

PDB$SEED  => Seed container

To switch a container

SQL> ALTER SESSION SET CONTAINER=container_name

To  list the PDB in the CDB – use DBA_PDBS view

PDB status

SQL> SELECT NAME,OPEN_MODE FROM V$PDBS WHERE CON_ID > 2;

To execute a single statement in multiple PDBs, use DBMS_SQL package as

DBMS_SQL.PARSE(c => c1 ,
statement => taskList,
language_flag => DBMS_SQL.NATIVE,
edition= > NULL,
apply_crossedition_trigger => NULL,
fire_apply_trigger => NULL,
schema => ‘HR’,
container => <pdbname>);

http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_admin.htm#autoId10

start / stop PDBS

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;

SQL> ALTER PLUGGABLE DATABASE ALL EXCEPT hrdb,salesdb CLOSE IMMEDIATE;

SQL> STARTUP PLUGGABLE DATABASE HRDB OPEN;

User creation

SQL> CREATE USER thomas …….  CONTAINER=CURRENT | ALL |<container name>

A common user name (from CDB CONTAINER=ALL) must start with “C##” and a local user name can not start with c##, example

SQL> CREATE USER c##thomas … CONTAINER=ALL;

Creating PDB from seedDB

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
DEFAULT TABLESPACE sales
DATAFILE ‘/disk1/oracle/dbs/salespdb/sales01.dbf’ SIZE 250M AUTOEXTEND ON
PATH_PREFIX = ‘/disk1/oracle/dbs/salespdb/’
FILE_NAME_CONVERT = (‘/disk1/oracle/dbs/pdbseed/’, ‘/disk1/oracle/dbs/salespdb/’);

==> MAXSIZE maximum size of total tablespaces for salespdb

==> MAX_SHARED_TEMP_SIZE  –  maximum size of TEMP space can be used by the users from salespdb

==> FILE_NAME_CONVERT – location of the seedb to copy files

Cloning pdb1 to pdb2 – local

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
FILE_NAME_CONVERT = (‘/disk1/oracle/pdb1/’, ‘/disk2/oracle/pdb2/’)
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M);

http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_plug.htm#autoId2

12c VM-linux-oracle install tips

https://blogs.oracle.com/wim/entry/easily_install_oracle_rdbms_12cr1

03 July

Automatic baseline plan Capture:  Oracle captures SQL baseline plans automatically for any repeatable SQL.  Set the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE.

http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_spm.htm#TGSQL615

In 12c, you can make column invisible and access the data normally.

SQL> ALTER TABLE EMP MODIFY(age INVISIBLE);

SQL> ALTER TABLE EMP MODIFY(age VISIBLE);

WHn you make a column INVISIBLE, column order will change once you put back to VISIBLE.

http://docs.oracle.com/cd/E16655_01/server.121/e17636/tables.htm#ADMIN13867

Concurrent STATS gathering:  You can enable concurrent statistics gathering for a partitioned table or hash table (when you gather SCHEMA level stats).

SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS(‘CONCURRENT’,’TRUE’);

Oracle will decide the number ofr parallel process based on the setting JOB_QUEUE_PROCESSES (minum should be 4) or enable parallel statement tuning by enabling auto parallelism.

https://blogs.oracle.com/optimizer/entry/gathering_optimizer_statistics_is_one

Shutting down a PDB: – Use SHUTDOWN or SHUTDOWM IMMEDIATE to close a PDB.  make sure you have connected to the PDB, you want to shutdown.  There is no SHUTDOWN TRANSACTIONAL or SHUTDOWN ABORT or equivalent  to shutdown a PDB.

Modifiable Parameters and PDB: Certain parameters are modifiable in PDB level, get the parameter from v$SYSTEM_PARAMETER where ISPDB_MODIFIABLE is TRUE.

PDB and Tablespaces:  Each PDB can have a own default tablespace and tablespace type.  There can be a single TEMP tablespace for entire  CBD or optionally can have a TEMP for a single CDB.

PDB and Service:  to create service for a PDB

SRVCTL ADD SERVICE -db mycdb -service hr -pdb hrpdb

Also use the PL/SQL package DBMS_SERVICE.CREATE_SERVICE to create a new service in a single instance and make sure to execute the command in the respective PDB.

Get current container anme and ID.

SQL> select SYS_CONTEXT(‘USERENV’,’CON_NAME’) from dual;

SQL> select SYS_CONTEXT(‘USERENV’,’CON_ID’) from dual;

PDB /CDB users: There can be common and PDB local users and administrators.

Grant CDB_DBA => CDB administrators

Grant PDB_DBA => PDB administrators

02 July

In oracle 12c, DROP PARTITION and TRUNCATE PARTITION optimized more, index maintenance is only for metadata keeping the global indexes USABLE.   SYS. PMO_DEFERRED_GIDX_MAINT_JOB, by default  scheduled at 2:00 AM will clean up the stale entires.  Use the clause UPDATE INDEXES along with DROP or TRUNCATE to enable asynchornus index global index maintenance.

You can clean also up the indexes by using

  • DBMS_PART.CLEANUP_GIDX
  • ALTER INDEX REBUILD PARTITION
  • ALTER INDEX PARTITION COALESCE CLEANUP

http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_admin002.htm#autoId2

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: