From Oracle to a Successful Vineyard

Last Month , I was there in Nasik ( One Small Town near Mumabi ). We roam around the city and visited many places in town. Our Driver suggested to visit Sual Vineyard. I am not very found of wine ,

But had very good time with friends in Napa Valley, CA, US, So thought let me experience Indian Vineyard.In Vineyard , we learned that owner of Sula Vineyard Mr. Rajeev is graduated in Economics and Industrial Engineering from Stanford and was working with Oracle in the Silicon Valley. Latter he left

Job and started Vineyard in India , today Sula is one of the well know Wine band in India. Story look interesting , so thought, to share with all(Links are given below ).

From Oracle to a Successful Vineyard: Rajeev Samant of Sula Wines

http://www.delhiwineclub.com/Interview/interview_with_rajeev2.asp
http://www.pagalguy.com/plugins/p2_news/printarticle.php?p2_articleid=912

NEW option in ADRCI purge acommand - UTSCDMP

NEW option in ADRCI purge acommand - UTSCDMP
There is new option in ADRCI for purge command - UTSCDMP

adrci> help purge Usage: PURGE [[-i ] [-age [-type ALERT INCIDENT TRACE CDUMP HM UTSCDMP]]]: Purpose: Purge the diagnostic data in the current ADR home. If no option is specified, the default purging policy will be used. Options: [-i id1 id1 id2]: Users can input a single incident ID, or a range of incidents to purge. [-age ]: Users can specify the purging policy either to all the diagnostic data or the specified type. The data older than ago will be purged [-type ALERT INCIDENT TRACE CDUMP HM UTSCDMP]: Users can specify what type of data to be purged. Examples: purge purge -i 123 456 purge -age 60 -type incident

There where some issue that directories cdmp_* in repositories (=$diagnostic_dest/Diag/rdbms/database_name/instance_name/bdump ) are not purging automatically. When you run following command, it remove cdmp_* directories, which is older the 3600 Minutes

adrci> purge -age 3600 -type UTSCDMP

The views expressed are my own and not necessarily those of my current or past employer. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine


Other ADR/ADRCI related posts

1. Automatic Diagnostic Repository (ADR)
2. Oracle 11g new Features , IPS ( Incident Packaging Service)
3. How to check alert.log in Oracle 11g

AIOUG - TechNight in Bangalore

The All India Oracle User Group with a 4 lakh -strong developer base and a community of seven-lakh users in India is organising a second meeting in Bangalore on October 22 to share experiences and provide insights into shape of things to come. It is the second largest technology network of professionals for Oracle after the US. Soon these TechNights will be held in other parts of India, such as Pune, Mumbai and Delhi.

Like several successful Oracle user groups around the world, AIOUG is also a not for profit organization formed by Oracle users for Oracle users. Let’s join hands across the country to make AIOUG a global lead user group.

AIOUG first TechNight held at Hyderabad, Please click here to see TechNight pictures

TechNight All India Oracle User Group (AIOUG)

AIOUG started his first ever Technical session in India at Hyderabad… On … Friday, July 18th 2008, between 5:00 PM to 8:00 PM. Session started with quick introduction about AIOUG, there mission goal by Murali Vallath. Latter Phani Arega took technical session on "Efficient SQL Programming - Some Tricks and Tips" and Vivek Sharma on " Real Time Performance Tuning".

Both the technical session was quite good and they shared lots of real life examples in there presentation. Here is some photos from AIOUG TechNight

Murali Vallath

Phani Arega

Vivek Sharma

Satyendra Kumar & PS Janakiram

DBMS_STATS Enhancements in Oracle 11g Database

DBMS_STATS Enhancements in Oracle 11g Database
Virag Sharma virag123@gmail.com

We know in 10g and 11g there is automatic job that collect stats of database based on certain preferences settings , lets have a look what are these preferences and what are there default values and how DBMS_STATS is different in 11g from 10g

Changing Preferences for Statistics

Preference name	Default Values
CASCADE	AUTO
DEGREE	AUTO
ESTIMATE_PERCENT	AUTO
METHOD_OPT	FOR ALL COLUMN SIZE AUTO
NO_INVALIDATE	AUTO
GRANULARITY	AUTO
PUBLISH ( New in 11g)	TRUE
INCREMENTAL new in 11g	FALSE
STALE_PERCENT (New in 11g)	10

# # In Oracle 10g # $ sqlplus "/ as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 6 19:04:57 2008 SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual; DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_SAMPLE_SIZE SQL> SQL> SELECT dbms_stats.get_param('method_opt') FROM dual; DBMS_STATS.GET_PARAM('METHOD_OPT') -------------------------------------------------------------------------------- FOR ALL COLUMNS SIZE AUTO SQL> SELECT dbms_stats.get_param('GRANULARITY') FROM dual; DBMS_STATS.GET_PARAM('GRANULARITY') -------------------------------------------------------------------------------- AUTO SQL> SELECT dbms_stats.get_param('NO_INVALIDATE') FROM dual; DBMS_STATS.GET_PARAM('NO_INVALIDATE') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_INVALIDATE SQL> SELECT dbms_stats.get_param('DEGREE') FROM dual; DBMS_STATS.GET_PARAM('DEGREE') -------------------------------------------------------------------------------- NULL SQL> SELECT dbms_stats.get_param('CASCADE') FROM dual; DBMS_STATS.GET_PARAM('CASCADE') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_CASCADE <> SQL> exec dbms_stats.set_param('ESTIMATE_PERCENT','100'); PL/SQL procedure successfully completed. SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual; DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT') --------------------------------------------------------------------- 100 SQL> exec dbms_stats.RESET_PARAM_DEFAULTS(); PL/SQL procedure successfully completed. SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual; DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT') --------------------------------------------------------------------- DBMS_STATS.AUTO_SAMPLE_SIZE ->

GET_PARAM , RESET_GLOBAL_PREFS_DEFAULTS and SET_PARAM are obsolete in Oracle 11g.

In place of above procedures need to use following procedures GET_PREFS , RESET_GLOBAL_PREF_DEFAULTS and SET_GLOBAL_PREFS

SQL> Select dbms_stats.GET_PREFS('CASCADE') from dual; DBMS_STATS.GET_PREFS('CASCADE') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_CASCADE SQL> Select dbms_stats.GET_PREFS('DEGREE') from dual; DBMS_STATS.GET_PREFS('DEGREE') -------------------------------------------------------------------------------- NULL SQL> Select dbms_stats.GET_PREFS('ESTIMATE_PERCENT') from dual; DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_SAMPLE_SIZE SQL> Select dbms_stats.GET_PREFS('METHOD_OPT') from dual; DBMS_STATS.GET_PREFS('METHOD_OPT') -------------------------------------------------------------------------------- FOR ALL COLUMNS SIZE AUTO SQL> Select dbms_stats.GET_PREFS('NO_INVALIDATE') from dual; DBMS_STATS.GET_PREFS('NO_INVALIDATE') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_INVALIDATE SQL> Select dbms_stats.GET_PREFS('GRANULARITY') from dual; DBMS_STATS.GET_PREFS('GRANULARITY') -------------------------------------------------------------------------------- AUTO SQL> Select dbms_stats.GET_PREFS('PUBLISH') from dual; DBMS_STATS.GET_PREFS('PUBLISH') -------------------------------------------------------------------------------- TRUE SQL> Select dbms_stats.GET_PREFS('INCREMENTAL') from dual; DBMS_STATS.GET_PREFS('INCREMENTAL') -------------------------------------------------------------------------------- FALSE SQL> Select dbms_stats.GET_PREFS('STALE_PERCENT') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT') -------------------------------------------------------------------------------- 10

In Oracle 11g you can set preference not only on database level but on global level , schema level , table level as well. It really give more control to DBA for example on one table you want histograms should collected always or want to set METHOD_OPT => ‘FOR ALL COLUMNS SIZE 254’ on some tables

Procedure for setting preference on global , database , schema and table level are given below

1. SET_GLOBAL_PREFS

2. SET_DATABASE_PREFS

3. SET_SCHEMA_PREFS

4. SET_TABLE_PREFS

• Preference set on global apply for new objects or object which no preference available

• Database level preference will be applied on all objects in the database excluding the tables owned by Oracle. These tables can included by passing TRUE for the add_sys parameter of procedure.

• If you set preference value to NULL , it will set to Oracle default value

>

# #Test case # SQL> create table test1(abc number); Table created. SQL> create table test2(abc number); Table created. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST1') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST1') -------------------------------------------------------------------------------- 10 SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 10 SQL> execute dbms_stats.set_table_prefs('SYS', 'TEST2', 'STALE_PERCENT', '35'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 35 SQL> execute dbms_stats.set_global_prefs('STALE_PERCENT', '20'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST1') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST1') -------------------------------------------------------------------------------- 20 SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 35 SQL> exec dbms_stats.SET_DATABASE_PREFS('STALE_PERCENT', '15'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST1') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST1') -------------------------------------------------------------------------------- 20 SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 35 SQL> exec dbms_stats.set_database_prefs('STALE_PERCENT','30'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 35 SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'BUG', 'BUG_USER') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','BUG','BUG_USER') -------------------------------------------------------------------------------- 30 SQL> exec dbms_stats.SET_DATABASE_PREFS('STALE_PERCENT', '15'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 35 SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'BUG', 'BUG_USER') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','BUG','BUG_USER') -------------------------------------------------------------------------------- 15

Published and Pending Statistics

Statistics published by default when system/user/job collect stats. In Oracle 11g we have option to put newly collect stats in pending state and published latter once satisfied by performance testing based on new statistics.

In 10g we have face performance issue when collected stats on OLTP (24 X 7 ) database, probably because stats of one index/ table published before other. So collect stats in pending stat , test it and once satisfied with testing published it.

SQL> col name form a40 SQL> col VALUE form a6 SQL> col ISSES_MODIFIABL VALUE form a6 SQL> col ISSES_MODIFIABL form a6 SQL> col ISSYS_MODIFIABLE form a12 SQL> select name,value,isses_modifiable,issys_modifiable from v$parameter where name='optimizer_use_pending_statistics' ; NAME VALUE ISSES_MODIFIABL ISSYS_MODIFI ---------------------------------------- ------ --------------- ------------ optimizer_use_pending_statistics FALSE TRUE IMMEDIATE

# # Test Case # # By default Publish is set TRUE # SQL> select dbms_stats.get_prefs('PUBLISH') publish from dual PUBLISH --------- TRUE SQL> select dbms_stats.get_prefs('PUBLISH', 'VIRSHARM', 'TEST1') publish from dual; PUBLISH ------------ TRUE # # Setting Publish false for TEST1 table # SQL> exec dbms_stats.set_table_prefs('VIRSHARM', 'TEST1', 'PUBLISH', 'false'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('PUBLISH', 'VIRSHARM', 'TEST1') publish from dual; PUBLISH ------------- FALSE SQL> execute dbms_stats.gather_table_stats('VIRSHARM', 'TEST1'); PL/SQL procedure successfully completed. SQL> select table_name, last_analyzed from user_col_pending_stats where table_name ='TEST1' TABLE_NAME LAST_ANALYZED ---------- --------------- TEST1 30-APR-08 SQL> select table_name, last_analyzed from user_ind_pending_stats where table_name ='TEST1'; no rows selected SQL> select table_name, last_analyzed from user_col_pending_stats where table_name ='TEST1'; TABLE_NAME LAST_ANALYZED ---------- --------------- TEST1 30-APR-08 # # Following views will have no record for table TEST1 # because stats not published # SQL> select table_name, last_analyzed from user_tables where table_name = 'TEST1'; TABLE_NAME LAST_ANALYZED ------------------------------ ------------------- TEST1 SQL> select index_name, last_analyzed from user_indexes where table_name ='TEST1'; no rows selected SQL> select column_name, last_analyzed from user_tab_columns where table_name='TEST1' ; COLUMN_NAME LAST_ANALYZED ------------ --------------- ABC

>

# # Testing # SQL> ALTER SESSION SET optimizer_use_pending_statistics =TRUE; Session altered. # # OR export / import STATS # SQL> exec dbms_stats.create_stat_table('VIRSHARM','STATS'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.export_pending_stats(tabname=>'TEST1',stattab=>'STATS'); PL/SQL procedure successfully completed. # # Use expdp /impdp ot exp/imp to import stats in TEST/QA database # SQL> exec dbms_stats.import_table_stats(ownname=>'VIRSHARM',tabname=>'TEST1', stattab=>'STATS'); select column_name, last_analyzed from user_tab_columns wheretable_name='TEST1';

# # Published stats # Publish the Stats to Data Dictionary for Optimizer Usage # SQL> exec dbms_stats.publish_pending_stats(tabname=>'TEST1'); PL/SQL procedure successfully completed.

If Stats published and performance become worse , then you can restore old version of stats. Oracle will manage the historical statistics repository, purging the statistics on a regular basis, by default every 31 days

By default Oracle keep stats for 31 day after that it purge. Retention can be increased useing following

SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION ( 60 ); PL/SQL procedure successfully completed.