Jul-15

26-July
Perl date conversions – a good tool with in shell scripting..
http://www.tutorialspoint.com/perl/perl_date_time.htm

24-July
Linux NTP (network Time Protocal) configuration – in /etc/sysconfig/ntp

OPTIONS=”-x –u ntp:ntp –p  /var/run/ntpd.pid”

-x option will prevent time being adjust past.

If NPT is not configured CRS will automatically configure CTSS (Cluster Time Synchronization Service) using the daemon octcssd.bin
18-July
Latches in 12c.
https://fritshoogland.wordpress.com/2015/07/17/oracle-12-and-latches/
https://fritshoogland.wordpress.com/2015/07/17/oracle-12-and-latches-part-2/

On execution plans – notes
http://www.toadworld.com/platforms/oracle/b/weblog/archive/2015/06/05/sql-tuning-lessons-learned-from-a-lifetime-in-explain-plans
http://www.toadworld.com/platforms/oracle/b/weblog/archive/2015/07/17/sql-tuning-lessons-learned-from-a-lifetime-in-explain-plans-part-5

Oracle even 1005 may be for latching. X$TRACE – research more.
You can call a function using oradebug, example to get a latch (see more details in the above latches blog from Fritsh part 1)

 
SQL> oradebug setmypid
Statement processed.
SQL> oradebug call kslgetl 0x60023a80 0 0 2442
Function returned 1

Kslgetl (ksl-get-l-latch) is the function call for to get a latch. Kslwt is function call to get wait interface.

13-July
When you update a table with Zone Maps – that specific zone will be get invalidated and rest of the zone maps will be usable by CBO. So, the Zone map can be completely or partially STALE.

10-July
ZONE Maps: is like EXADATA Storage Index structure, but physically existing in the data dictionary, while storage Indexes are populated to the memory at the run time automatically. ZONE maps need to be created and capture the MAX and MIN values (aggregated values) for a 8MB of a table. During rune time, Oracle inspects this information to decide whether a physical read is required or not for a specific zones.

SQL> create materialized zonemap employee_sex_zm on employee(sex);

Unlike indexes, Zone maps will not be updated automatically during a DML operation. If is behave more like Materialized views – Its on DEMAND.

Oracle stores the Zone map catalog in two data dictionary views – DBA_ZONEMAPS and DBA_ZONEMAP_MEASURES. Zone maps are available with Exadata or SuperCluster.
https://richardfoote.wordpress.com/2014/09/03/12-1-0-2-introduction-to-zone-maps-part-i-map-of-the-problematique/

08-July
Complete set of Oracle background processes.
http://docs.oracle.com/database/121/REFRN/GUID-86184690-5531-405F-AA05-BB935F57B76D.htm

IMCO Process: Oracle IMCO process is responsible for population or re-population of column stores for in-memory enabled objects set as LOW/MEDIUM/HIGH/CRITICAL. IMCO process populate the column store starting from the table where the priority is set to CRITICAL, then continue in the order HIGH, MEDIUL and LOW. The tables where priority is set to NONE will not be populated by IMCO process. No more tables will be populated once the allocated memory (INMEMORY_SIZE) is full.

Procedure detach the DEFAULT_MAINTENANCE_PLAN from the Scheduler Windows. First identify the maintenance windows attached to the

SQL> select  RESOURCE_PLAN,window_name,active from DBA_SCHEDULER_WINDOWS;

RESOURCE_PLAN                  WINDOW_NAME          ACTIV
------------------------------ -------------------- -----
DEFAULT_MAINTENANCE_PLAN       MONDAY_WINDOW        FALSE
DEFAULT_MAINTENANCE_PLAN       TUESDAY_WINDOW       FALSE
DEFAULT_MAINTENANCE_PLAN       WEDNESDAY_WINDOW     FALSE
DEFAULT_MAINTENANCE_PLAN       THURSDAY_WINDOW      FALSE
DEFAULT_MAINTENANCE_PLAN       FRIDAY_WINDOW        FALSE
DEFAULT_MAINTENANCE_PLAN       SATURDAY_WINDOW      FALSE
DEFAULT_MAINTENANCE_PLAN       SUNDAY_WINDOW        FALSE
                               WEEKNIGHT_WINDOW     FALSE
                               WEEKEND_WINDOW       FALSE

9 rows selected.

Build script to disassociate the DEFAULT_MAINTENANCE_PLAN from the Scheduler Windows using DBMS_SCHEDULER.SET_ATTRIBUTE

SQL> SELECT 'Exec dbms_scheduler.set_attribute('''||WINDOW_NAME||''''||','||'''RESOURCE_PLAN'''||','''');'
from DBA_SCHEDULER_WINDOWS
where RESOURCE_PLAN='DEFAULT_MAINTENANCE_PLAN'  ;  2    3

'EXECDBMS_SCHEDULER.SET_ATTRIBUTE('''||WINDOW_NAME||''''||','||'''RESOURCE_PLAN'''||','''');'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Exec dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');
Exec dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');
Exec dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','');
Exec dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');
Exec dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');
Exec dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
Exec dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');
7 rows selected. 

07-July
Oracle 12c In-memory related parameters

inmemory_clause_default
inmemory_force
inmemory_max_populate_servers
inmemory_query
inmemory_size – Size of the im-memory column store. Default is 0 and minimum should be 100M. This parameter can be set in PDB level and control the usage, if not set explicitly, will inherit from the CDB. However, CDB value is the total amount of memory available for all PDBs. SGA_TARGET or MEMORY_TARGET considered when we are changing the INMEMORY_SIZE as it is part of the SGA. The inmemory heap divides into two components – IMCA_RW and IMCA_RO – one compartment got the column store (80%) while the other got journal and other related memory structures (20%).

inmemory_trickle_repopulate_servers_percent
optimizer_inmemory_aware

immemory usage SQL

SELECT mem inmem_size,
       tot disk_size,
       bytes_not_pop,
       (tot/mem) compression_ratio,
       100 *((tot-bytes_not_pop)/tot) populate_percent
FROM
  (SELECT SUM(INMEMORY_SIZE)/1024/1024/1024 mem,
    SUM(bytes)              /1024/1024/1024 tot ,
    SUM(bytes_not_populated)/1024/1024/1024 bytes_not_pop
   FROM v$im_segments
   )

Active scheduler windows

SQL> col WINDOW_NAME for a20
SQL> col NEXT_START_DATE for a40
SQL> col LAST_START_DATE for a40
SQL> select  window_name,active,NEXT_START_DATE,LAST_START_DATE ,DURATION from DBA_SCHEDULER_WINDOWS;

To close a scheduler window

SQL> EXECUTE DBMS_SCHEDULER.CLOSE_WINDOW ('SATURDAY_WINDOW');
 

Upgrading to 12c using RMAN DUPLICATE.

http://allthingsoracle.com/upgrading-a-database-using-recovery-manager-rman-duplicate-command-in-oracle-12c/
06-July
Set table preference for METHOD_OPT to enable statistics – enable histograms for all index columns

SQL> exec DBMS_STATS.SET_TABLE_PREF (‘Owner’,‘table Name’,’METHOD_OPT’,’ for all indexed columns size auto');

To check the AUTOSTATS jobs are running or not –

select CLIENT_NAME,JOB_NAME,JOB_SCHEDULER_STATUS
from DBA_AUTOTASK_CLIENT_JOB
where client_name = 'auto optimizer stats collection';

AUTOSTATS enabled or not –

SQL> select CLIENT_NAME,status ,WINDOW_GROUP from DBA_AUTOTASK_CLIENT;

Enable histograms for only a column

SQL> exec DBMS_STATS.SET_TABLE_PREF (‘Owner’,‘table Name’,’METHOD_OPT’,’FOR COLUMNS SIZE AUTO <column Name>’);

03-July
You may dump a index block tree using OBJECT_ID from DBA_OBJECTS.

SQL>  alter session set events 'immediate trace name treedump level <object ID> ';

branch: 0x1800523 25167139 (0: nrow: 21, level: 1)
leaf: 0x1800524 25167140 (-1: row:485.485 avs:828)
leaf: 0x1800525 25167141 (0: row:479.479 avs:820)
leaf: 0x1800526 25167142 (1: row:479.479 avs:820)
leaf: 0x1800527 25167143 (2: row:479.479 avs:820)
leaf: 0x1800528 25167144 (3: row:479.479 avs:820)
leaf: 0x1800529 25167145 (4: row:479.479 avs:819)
leaf: 0x180052a 25167146 (5: row:479.479 avs:820)

Where in row:479.479 => <total number of index entries>: <Number of Non-deleted index entries>
In older versions row:nrow:rrow
Where nrow => Number of index entries
rrow => Number of non-deleted index entries
avs => is the free space available in the block bytes

crfclust.bdb file removal -???

http://askdba.org/weblog/forums/topic/cleaning-up-huge-crfclust-bdb-in-11-2-0-2-grid-home/

02-July
Oracle Big Data SQL: is a unified query mechanism to address various types of data source – a relation database, HDFS or any NoSQL data source. Oracle Big Data SQL deploys a mechanism known as Query Franchising to access the data from various data sources alternate to the Language federation. These are the light weight dedicated agents deployed in each data source to mine the data. These agents are capable to do smart scan functions and return the desired result sets.
For example during the query parsing time, the oracle databases interrogated the HDFS Name Node to locate the Hadoop Data Locations (or InputSplits) . From this information the data access method is delveloped and sends to the Big data agents working in the storage cells or data nodes. Read more details at:
http://www.oracle.com/technetwork/database/bigdata-appliance/learnmore/bigdatasqloverview21jan2015-2408000.pdf?ssSourceSiteId=ocomen

Oracle NO SQL Configurations..
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/BigDataSQL/BigDataSQL_HOL.html

Oracle 12c partitioning new features
http://www.oracle.com/technetwork/database/options/partitioning/partitioning-wp-12c-1896137.pdf

Oracle 12c now offers Partial Indexes – you may build index only certain numbers of partitions not on all of them – both local and global indexes.
https://richardfoote.wordpress.com/2013/07/08/12c-partial-indexes-for-partitioned-tables-part-i-ignoreland/

1-July

To get CRS version

oracle@:/home/oracle&gt; $ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.3.0] 
oracle@:/home/oracle&gt; $ crsctl query crs softwareversion
Oracle Clusterware version on node [Node1] is [11.2.0.3.0]

 

Advertisements
%d bloggers like this: