How-to add new device to cacti by script

Adding many devices to cacti is a tedious work. For NoSQL DB, they always have lots of hosts in a cluster. So I wrote a script to add them easily.

<?php

#This script auto_add_device.php is to add new device automatically
#
#Table auto_add_list is source of new hosts
#create table auto_add_list (
#id mediumint(8) unsigned NOT NULL auto_increment,
#hostname varchar(100) not null,
#tree varchar(100) not null,
#type varchar(100) not null,
#status varchar(100) not null default 'new',
#create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
#last_update_date datetime,
#PRIMARY KEY (id),
#CONSTRAINT uc_hostname_type UNIQUE (hostname,type)
#);
#
#type: Linux, MongoDB, Cassandra, MySQLInnodb, MySQLMemory, pushVM
#tree: MongoDB-szoommdb
#status: new, added, failed
#
#insert into auto_add_list(hostname,type,tree) values ('alexzeng.wordpress.com','MongoDB','MongoDB-alexzeng');

$CACTI_HOME = '/export/home/cacti';
$mysql_host = '';      #localhost by default
$mysql_user = 'cacti';
$mysql_pass = 'password';
$mysql_port = 3306;
$mysql_ssl  = FALSE;   # Whether to use SSL to connect to MySQL.

#Call main functions
connect_db();
add_device();

# ============================================================================
# This is the main function. 
# ============================================================================
function add_device() {
   #print("The start\n");
   $result = run_query("select id,hostname,tree,type from auto_add_list where status='new'");
   foreach ( $result as $row ) {  
     if ( $row && is_array($row) 
      && array_key_exists('hostname', $row) 
      && array_key_exists('type', $row) 
      )
     {
        $hostname  = $row['hostname'];  
        $type = strtoupper($row['type']);
        $id = $row['id'] ;            
        $tree = $row['tree'];    
        $status = 'new';
        if ( $type == 'LINUX' ) {
          print("Add Linux host $hostname \n");          
          #...
        } elseif ( $type == 'MONGODB' ) {
          print("Add MongoDB host $hostname \n");
          add_device_general($type, $hostname, $tree);
          $status = 'added';
        } elseif ( $type == 'CASSANDRA' ) {
          print("Add Cassandra host $hostname \n");         
          #...
        } elseif ( $type == 'MYSQLINNODB' ) {
          print("Add MySQLInnodb host $hostname \n");         
          #...
        } elseif ( $type == 'MYSQLMEMORY' ) {
          print("Add MySQLMemory host $hostname \n");         
          #...
        } elseif ( $type == 'PUSHVM' ) {
          print("Add pushVM host $hostname \n");         
          #...
        } else {
          print("unknown type: $type \n");
          $status = 'unknown type';
        }
        if ($id == 1) {
          print("id $id\n");
        }
        $result = run_query("update auto_add_list set status='$status', last_update_date=CURRENT_TIMESTAMP() where id=$id ");        
     }
   }
  #print("The end\n");
}

# ============================================================================
# Add general devices
# ============================================================================
function add_device_general($device_type, $hostname, $tree ) {
  global $CACTI_HOME;
  
  $result = explode('.', $hostname);
  $short_name = @$result[0];
  #print("$hostname $short_name\n");
  
  #Add device
  $host_template_id = get_host_template_id($device_type);
  print("host_template_id $host_template_id\n");
  `php -q $CACTI_HOME/cli/add_device.php --description=$short_name --ip=$hostname --avail=none --version='' --template=$host_template_id`;
  
  #Add graphs
  $host_id = get_host_id($hostname);
  $result = run_query("SELECT " .
                             "host_template_graph.graph_template_id AS id " .
                     "FROM host_template_graph " .
                     "LEFT JOIN graph_templates " .
                             "ON (host_template_graph.graph_template_id = graph_templates.id) " .
                     "WHERE host_template_id = $host_template_id");
  foreach( $result as $row ) {
    if ( $row && is_array($row) ) {
      $graph_template_id = $row['id'];
       print("graph_template_id $graph_template_id\n");
      `php -q $CACTI_HOME/cli/add_graphs.php --graph-type=cg --host-id=$host_id --graph-template-id=$graph_template_id`;
    }
  }
  
  #Add it to tree
  print("Add it to tree\n");
  list($parent_node, $subheader_name) = explode('-', $tree);
  print("parent_node $parent_node\n");
  $parent_id = get_tree_id($parent_node);
  if ( $subheader_name ) {
     print("subheader_name $subheader_name\n");
     $sub_header_id = get_subheader_id($subheader_name);
     if($sub_header_id == -1) {
        print("$subheader_name doesn't exist, add it\n");
       `php -q $CACTI_HOME/cli/add_tree.php --type=node --node-type=header --tree-id=$parent_id --name="$subheader_name"`;
       $sub_header_id = get_subheader_id($subheader_name);
     }
     print("sub_header_id $sub_header_id\n");
    `php -q $CACTI_HOME/cli/add_tree.php --type=node --node-type=host --tree-id=$parent_id --host-id=$host_id --parent-node=$sub_header_id`;
  } else {
    `php -q $CACTI_HOME/cli/add_tree.php --type=node --node-type=host --tree-id=$parent_id --host-id=$host_id`;
  }
}

function get_subheader_id($subheader_name) {
  $query = "select id from graph_tree_items WHERE graph_tree_id=4 and upper(title)=upper('$subheader_name')";
  $id = run_query_get_id($query);
  return $id;
}

function get_tree_id($treename) {
  $query = "select id from graph_tree where upper(name) like upper('%$treename%') ";
  $id = run_query_get_id($query);
  return $id;
}

function get_host_template_id($type) {
  $query = "select id from host_template where upper(name) like upper('%$type%')";
  $id = run_query_get_id($query);
  return $id;
}

function get_host_id($hostname) {
  $query = "select id from host where hostname='$hostname' ";
  $id = run_query_get_id($query);
  return $id;
}

#Run given query, and get the first row in it's result
function run_query_get_id ($query) {
  $result = run_query($query);
  $id = -1;
  #print_r($result);
  foreach ( $result as $row ) {
    if ( $row && is_array($row) ) {
      $id = $row[0];
      #print("id $id\n");
      break;
    }
  }
  return $id;
}

# ============================================================================
# Wrap mysql_query in error-handling, and instead of returning the result,
# return an array of arrays in the result.
# ============================================================================
function run_query($sql) {
   global $conn;
   #print("sql $sql\n");
   $result = @mysql_query($sql, $conn);
   $array = array();
   while ( $row = @mysql_fetch_array($result) ) {
      $array[] = $row;
      #print_r($row);
   }
   return $array;
}

function connect_db() {
   # Process connection options and connect to MySQL.
   global $mysql_host, $mysql_user, $mysql_pass, $mysql_port, $mysql_ssl, $conn;
   # Connect to MySQL.
   $user = $mysql_user;
   $pass = $mysql_pass;
   $port = $mysql_port;
   if ( !extension_loaded('mysql') ) {
      print("The MySQL extension is not loaded");
      die("The MySQL extension is not loaded");
   }
   if ( $mysql_ssl ) {
      $conn = mysql_connect($mysql_host, $user, $pass, true, MYSQL_CLIENT_SSL);
   }
   else {
      $conn = mysql_connect($mysql_host, $user, $pass);
   }
   if ( !$conn ) {
      die("MySQL: " . mysql_error());
   }
   $db_selected = mysql_select_db('cacti', $conn);
   if (!$db_selected) {
     die ('Can\'t use cacti : ' . mysql_error());
   }
}
?>

With this script, what you need to do is just add one row to the table as follows:

insert into auto_add_list(hostname,type,tree) values ('alexzeng.wordpress.com','MongoDB','MongoDB-alexzeng'); 

As usual, it’s not a perfect script but it’s a good start

SQL Plan Management handy commands

I have a blog about SPM previously. Now I want to introduce a set of handy commands to operate SPM.

The script is ksh script. Here is the summary commands:

 ora spm
  - spmlike <pattern>                 List SQL Plan Baselines by matching sql_text to pattern
  - spmlist <sql_handle/hash_value> [plan_name]             List SQL Plan Baselines for giving SQL
  - spmshow <sql_handle/hash_value> [plan_name]             Show SQL Plan Baselines for a given SQL
  - spmload <hash_value> [plan_hash_value]             Load itself's sql plan from cursor cache for a given hash_value
  - spmbind <sql_handle/hash_value> <good_hash_value> [plan_hash_value]    Bind <good_hash_value>'s plan to <sql_handle/hash_value>
  - spmloadgood <sql_handle/hash_value> <good_hash_value> [plan_hash_value]    Load <good_hash_value>'s plan to <sql_handle/hash_value>
  - spmalter <sql_handle/hash_value> <attribute_name> <attribute_value> [plan_name]     Change SQL Plan Baseline attribute
  - spmdisable <sql_handle/hash_value> [plan_name]             Disable the Plan
  - spmenable <sql_handle/hash_value> [plan_name]             Enable the Plan
  - spmfix <sql_handle/hash_value> [plan_name]             Fix the Plan
  - spmunfix <sql_handle/hash_value> [plan_name]             Unfix the Plan
  - spmdrop <sql_handle/hash_value> [plan_name]             Drop the plan from SPM
  - spmevolve <sql_handle/hash_value> [plan_name]             Evolve the plan from SPM
  - spmmigol <attribute_name> <attribute_value> [fixed]           Migrate the plan to SPM using DBMS_SPM.MIGRATE_STORED_OUTLINE
  - spmmigolname <outline_name> [fixed]           Migrate outline plan to SPM by name
  - spmmigolcat <category_name> [fixed]           Migrate outline plan to SPM by category

Some examples:
Ex1. Find a SQL with SQL Plan Baselines using “ora spmlike”

$ ora spmlike "id=20"

SQL_HANDLE           PLAN_NAME                      ENA ACC FIX ONE_HASH_VALUE SQL_TEXT_80
-------------------- ------------------------------ --- --- --- -------------- --------------------------------------------------
SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbbdbd90e8e YES NO  NO                 select * from t1 where id=20
SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbb844cb98a YES YES NO                 select * from t1 where id=20

Ex2. Drop a Sql Plan Baseline of this SQL using “ora spmdrop”

$ ora spmdrop SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbbdbd90e8e
List SQL Plan Baselines of sql handle SQL_7e22e17af815c16b

SQL_HANDLE           PLAN_NAME                      ENA ACC FIX ONE_HASH_VALUE SQL_TEXT_80
-------------------- ------------------------------ --- --- --- -------------- --------------------------------------------------
SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbbdbd90e8e YES NO  NO                 select * from t1 where id=20
SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbb844cb98a YES YES NO                 select * from t1 where id=20

Drop Sql Plan Baselines of sql_handle SQL_7e22e17af815c16b
...Dropped 1 SQL Plans

PL/SQL procedure successfully completed.

List SQL Plan Baselines of sql handle SQL_7e22e17af815c16b

SQL_HANDLE           PLAN_NAME                      ENA ACC FIX ONE_HASH_VALUE SQL_TEXT_80
-------------------- ------------------------------ --- --- --- -------------- --------------------------------------------------
SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbb844cb98a YES YES NO                 select * from t1 where id=20

Ex3. Bind a good plan to this SQL using “ora spmloadgood”

--1.  Suppose FULL TABLE SCAN is better for this SQL. Using hint or other methods to get the FULL TABLE SCAN plan in cursor cache.
SQL> set autotrace on
SQL> select /*+ full(t1) */ * from t1 where id=20;

        ID
----------
NAME
--------------------------------------------------------------------------------------------------------------------------------
        20
COL$

Execution Plan
----------------------------------------------------------
Plan hash value: 838529891

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    27 |    49 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    27 |    49 |
----------------------------------------------------------

...

--2. Get the hinted SQL's hash value
SQL> set autotrace off
SQL> select hash_value,sql_text from v$sql where sql_text like 'select /*+ full(t1) */ * from t1 where id=20';

HASH_VALUE SQL_TEXT
---------- ------------------------------------------------------------
2336257891 select /*+ full(t1) */ * from t1 where id=20

--3. Bind the good plan to the original SQL.
$ ora spmloadgood SQL_7e22e17af815c16b 2336257891
Load SQL 2336257891 plan in cursor cache to sql_handle SQL_7e22e17af815c16b
...Loaded 1 SQL Plans

PL/SQL procedure successfully completed.

List SQL Plan Baselines of sql handle SQL_7e22e17af815c16b

SQL_HANDLE           PLAN_NAME                      ENA ACC FIX ONE_HASH_VALUE SQL_TEXT_80
-------------------- ------------------------------ --- --- --- -------------- --------------------------------------------------
SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbb844cb98a YES YES NO      2008498169 select * from t1 where id=20
SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbbdbd90e8e YES YES YES     2008498169 select * from t1 where id=20

As you can see, the good plan is loaded as FIXED plan SQL_PLAN_7w8r1gbw1bhbbdbd90e8e

Ex4. Migrate outlines to SPM using “ora spmmigolname”

--1. Suppose we have an outline
SQL> select ol_name,sql_text from outln.ol$ where ol_name='OL_288078590';

OL_NAME                        SQL_TEXT
------------------------------ ------------------------------------------------------------
OL_288078590                   select *
                               from t1
                               where id=50
--2. There is no SQL Plan Baseline for this SQL now
$ ora spmlist 288078590
Try to get sql_handle by hash_value 288078590:
...Not found sql_handle for hash_value 288078590, exit...

--3. Let's load migrate the outline to SPM
$ ora spmmigolname OL_288078590

-------------------------------------------------------------------------------
               Migrate Stored Outline to SQL Plan
Baseline Report
-------------------------------------------------------------------------------

Summary:
--------
Number of stored
outlines to be migrated: 1

Stored outlines migrated successfully: 1

Changing converted outlines to PLAN_MIGRATED category

PL/SQL procedure successfully completed.

SQL_HANDLE           PLAN_NAME                      ENA ACC FIX ONE_HASH_VALUE SQL_TEXT_80
-------------------- ------------------------------ --- --- --- -------------- --------------------------------------------------
SQL_dedf84a0faafbebe OL_288078590                   YES YES YES      288078590 select *
                                                                               from t1
                                                                               where id=50

Ex5. Show the SQL Plan Baselines using “ora spmshow”

ora spmshow 288078590
Try to get sql_handle by hash_value 288078590:
...Got sql_handle SQL_dedf84a0faafbebe
List SQL Plan Baselines of sql handle SQL_dedf84a0faafbebe

SQL_HANDLE           PLAN_NAME                      ENA ACC FIX ONE_HASH_VALUE SQL_TEXT_80
-------------------- ------------------------------ --- --- --- -------------- --------------------------------------------------
SQL_dedf84a0faafbebe OL_288078590                   YES YES YES      288078590 select *
                                                                               from t1
                                                                               where id=50

Show SQL Plan Baselines of sql handle 288078590

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_dedf84a0faafbebe
SQL text: select * from t1 where id=50
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: OL_288078590         Plan id: 2219620746
Enabled: YES     Fixed: YES     Accepted: YES     Origin: STORED-OUTLINE
--------------------------------------------------------------------------------

Plan hash value: 3636266709

----------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    27 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    27 |     2 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1 |     1 |       |     1 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=50)

Note
-----
   - cpu costing is off (consider enabling it)

29 rows selected.

Here is the script:

#!/bin/ksh
#set -x
#
# 12/19/2011  AlexZeng -- 'ora spm*' for SQL Plan Management

if [ "$1" = "spm" ]; then
    echo " ora spm"
    echo "       - spmlike                  List SQL Plan Baselines by matching sql_text to pattern";
    echo "       - spmlist  [plan_name]             List SQL Plan Baselines for giving SQL";
    echo "       - spmshow  [plan_name]             Show SQL Plan Baselines for a given SQL";
    echo "       - spmload  [plan_hash_value]             Load itself's sql plan from cursor cache for a given hash_value";
    echo "       - spmbind   [plan_hash_value]    Bind 's plan to ";
    echo "       - spmloadgood   [plan_hash_value]    Load 's plan to ";
    echo "       - spmalter    [plan_name]     Change SQL Plan Baseline attribute";
    echo "       - spmdisable  [plan_name]             Disable the Plan";
    echo "       - spmenable  [plan_name]             Enable the Plan";
    echo "       - spmfix  [plan_name]             Fix the Plan";
    echo "       - spmunfix  [plan_name]             Unfix the Plan";
    echo "       - spmdrop  [plan_name]             Drop the plan from SPM";
    echo "       - spmevolve  [plan_name]             Evolve the plan from SPM";
    echo "       - spmmigol   [fixed]           Migrate the plan to SPM using DBMS_SPM.MIGRATE_STORED_OUTLINE";
    echo "       - spmmigolname  [fixed]           Migrate outline plan to SPM by name";
    echo "       - spmmigolcat  [fixed]           Migrate outline plan to SPM by category";
exit 0
fi

#Sql Plan Managment functions
function spm_hv2sqlhandle {
sqlplus -s "/ as sysdba" <set line 1000 pagesize 0 feedback off tab off echo off heading off numwidth 30
set serveroutput on
declare
v_sql_handle varchar2(100);
begin
  select sql_handle into v_sql_handle
    from dba_sql_plan_baselines b, v\$sql s
   where s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE
     and s.hash_value=$1
     and rownum=1;
  dbms_output.put_line(v_sql_handle);
exception when others then
  dbms_output.put_line('NOT_FOUND');
end;
/
EOF
}

if [ "$1" = "spmlike" ]; then
if [ "$2" = "" ]; then
echo "ora spmlike                  List SQL Plan Baselines by matching sql_text to pattern";
exit;
fi
sqlplus -s "/ as sysdba" <col SQL_HANDLE for a20
col SQL_TEXT_80 for a50
select
   sql_handle, plan_name,
   enabled, accepted, fixed,
   (select hash_value from v\$sql s where s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE and rownum=1) as one_hash_value,
   dbms_lob.substr(sql_text,80) as sql_text_80
from dba_sql_plan_baselines b
where upper(dbms_lob.substr(sql_text,4000)) like upper('%$2%')
order by sql_text_80 desc
;
EOF
exit;
fi

if [ "$1" = "spmlist" ]; then
if [ "$2" = "" ]; then
echo "ora spmlist  [plan_name]             List SQL Plan Baselines for giving SQL";
exit;
fi

iv=$2
if [ "$iv" -eq "$iv" 2> /dev/null ]; then
  echo "Try to get sql_handle by hash_value $iv:"
  p_sql_handle=`spm_hv2sqlhandle $iv`
  if [ "$p_sql_handle" = "NOT_FOUND" ]; then
		 echo "...Not found sql_handle for hash_value $iv, exit..."
     exit;
  else
		 echo "...Got sql_handle $p_sql_handle"
  fi
else
  p_sql_handle=$iv
fi

if [ "$3" = "" ]; then
  filter="1=1"
  echo "List SQL Plan Baselines of sql handle $iv"
else
  filter="PLAN_NAME='$3'"
  echo "List SQL Plan Baselines of sql handle $iv with PLAN_NAME $3"
fi

sqlplus -s "/ as sysdba" <col SQL_HANDLE for a20
col SQL_TEXT_80 for a50
select
   sql_handle, plan_name,
   enabled, accepted, fixed,
   (select hash_value from v\$sql s where s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE and rownum=1) as one_hash_value,
   dbms_lob.substr(sql_text,80) as sql_text_80
from dba_sql_plan_baselines b
where sql_handle='$p_sql_handle' and $filter
order by one_hash_value desc,enabled, accepted, fixed
;
EOF
exit;
fi

if [ "$1" = "spmshow" ]; then
if [ "$2" = "" ]; then
echo "ora spmshow  [plan_name]             Show SQL Plan Baselines for a given SQL";
exit;
fi

iv=$2
if [ "$iv" -eq "$iv" 2> /dev/null ]; then
  echo "Try to get sql_handle by hash_value $iv:"
  p_sql_handle=`spm_hv2sqlhandle $iv`
  if [ "$p_sql_handle" = "NOT_FOUND" ]; then
		 echo "...Not found sql_handle for hash_value $iv, exit..."
     exit;
  else
		 echo "...Got sql_handle $p_sql_handle"
  fi
else
  p_sql_handle=$iv
fi

ora spmlist $p_sql_handle

if [ "$3" = "" ]; then
pname="NULL";
echo "Show SQL Plan Baselines of sql handle $iv"
else
pname="'$3'";
echo "Show SQL Plan Baselines of sql handle $iv with PLAN_NAME $3"
fi

sqlplus -s "/ as sysdba" <SELECT p.*
   FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('$p_sql_handle', $pname, 'TYPICAL')) p;
EOF
exit;
fi

if [ "$1" = "spmload" ]; then
if [ "$2" = "" ]; then
echo "ora spmload  [plan_hash_value]             Load itself's sql plan from cursor cache for a given hash_value";
exit;
fi
if [ "$3" = "" ]; then
phv="NULL";
else
phv="$3";
fi

echo "Load SQL $2 plan in cursor cache to SQL Plan Baselines"
sqlplus -s "/ as sysdba" <v_sql_id, plan_hash_value=>$phv, fixed=>'NO',enabled=>'YES');
  dbms_output.put_line('...Loaded '||cnt||' SQL Plans');
end;
/
EOF
ora spmlist $2
exit;
fi

if [ "$1" = "spmbind" ]; then
if [ "$2" = "" -o "$3" = "" ]; then
echo "ora spmbind   [plan_hash_value]    Bind 's plan to ";
exit;
fi

if [ "$4" = "" ]; then
  filter="1=1";
else
  filter="plan_hash_value=$4";
fi

iv=$2
if [ "$iv" -eq "$iv" 2> /dev/null ]; then
  echo "Try to get sql_handle by hash_value $iv:"
  p_sql_handle=`spm_hv2sqlhandle $iv`
  if [ "$p_sql_handle" = "NOT_FOUND" ]; then
		 echo "...Not found sql_handle for hash_value $iv, exit..."
     exit;
  else
		 echo "...Got sql_handle $p_sql_handle"
  fi
else
  p_sql_handle=$iv
fi

echo "Load SQL $3 plan in cursor cache to sql_handle $p_sql_handle"
sqlplus -s "/ as sysdba" <'$p_sql_handle',
                                                 sql_id=>r.sql_id, plan_hash_value=>r.plan_hash_value,
																								 fixed=>'YES',enabled=>'YES');
    cnt_total := cnt_total + cnt;
  end loop;
  dbms_output.put_line('...Loaded '||cnt_total||' SQL Plans');
end;
/
EOF
ora spmlist $p_sql_handle
exit;
fi

if [ "$1" = "spmloadgood" ]; then
if [ "$2" = "" -o "$3" = "" ]; then
echo "ora spmloadgood   [plan_hash_value]    Load 's plan to ";
exit;
fi

iv=$2
if [ "$iv" -eq "$iv" 2> /dev/null ]; then
  echo "Try to get sql_handle by hash_value $iv:"
  p_sql_handle=`spm_hv2sqlhandle $iv`
  if [ "$p_sql_handle" = "NOT_FOUND" ]; then
     echo "...SQL $2 didn't yet, load it...\n";
     ora spmload $iv
     p_sql_handle=`spm_hv2sqlhandle $iv`
     if [ "$p_sql_handle" = "NOT_FOUND" ]; then
       echo "...Looks like load SQL $iv failed or something is wrong, exit..."
       exit;
     fi
  else
     echo "...Got sql_handle $p_sql_handle"
  fi
else
  p_sql_handle=$iv
fi

ora spmbind $p_sql_handle $3 $4
exit;
fi

if [ "$1" = "spmalter" ]; then
if [ "$2" = "" -o "$3" = "" -o "$4" = "" ]; then
echo "ora spmalter    [plan_name]     Change SQL Plan Baseline attribute";
exit;
fi

if [ "$5" = "" ]; then
pname="NULL";
else
pname="'$5'";
fi

iv=$2
if [ "$iv" -eq "$iv" 2> /dev/null ]; then
  echo "Try to get sql_handle by hash_value $iv:"
  p_sql_handle=`spm_hv2sqlhandle $iv`
  if [ "$p_sql_handle" = "NOT_FOUND" ]; then
     echo "...Not found sql_handle for hash_value $iv, exit..."
     exit;
  else
     echo "...Got sql_handle $p_sql_handle"
  fi
else
  p_sql_handle=$iv
fi

ora spmlist $p_sql_handle

echo "Change SQL Plan Baseline attribute '$3' to '$4'"
sqlplus -s "/ as sysdba" < '$p_sql_handle', PLAN_NAME => $pname,
																					 ATTRIBUTE_NAME => '$3', ATTRIBUTE_VALUE => '$4');
  dbms_output.put_line('...Altered '||cnt||' SQL Plans');
end;
/
EOF
ora spmlist $p_sql_handle
exit;
fi

if [ "$1" = "spmdisable" ]; then
if [ "$2" = "" ]; then
echo "ora spmdisable  [plan_name]             Disable the Plan";
exit;
fi
ora spmalter $2 enabled NO $3
exit;
fi

if [ "$1" = "spmenable" ]; then
if [ "$2" = "" ]; then
echo "ora spmenable  [plan_name]             Enable the Plan";
exit;
fi
ora spmalter $2 enabled YES $3
exit;
fi

if [ "$1" = "spmfix" ]; then
if [ "$2" = "" ]; then
echo "ora spmfix  [plan_name]             Fix the Plan";
exit;
fi
ora spmalter $2 fixed YES $3
exit;
fi

if [ "$1" = "spmunfix" ]; then
if [ "$2" = "" ]; then
echo "ora spmunfix  [plan_name]             Unfix the Plan";
exit;
fi
ora spmalter $2 fixed NO $3
exit;
fi

if [ "$1" = "spmdrop" ]; then
if [ "$2" = "" ]; then
echo "ora spmdrop  [plan_name]             Drop the plan from SPM";
exit;
fi
if [ "$3" = "" ]; then
pname="NULL";
else
pname="'$3'";
fi

iv=$2
if [ "$iv" -eq "$iv" 2> /dev/null ]; then
  echo "Try to get sql_handle by hash_value $iv:"
  p_sql_handle=`spm_hv2sqlhandle $iv`
  if [ "$p_sql_handle" = "NOT_FOUND" ]; then
     echo "...Not found sql_handle for hash_value $iv, exit..."
     exit;
  else
     echo "...Got sql_handle $p_sql_handle"
  fi
else
  p_sql_handle=$iv
fi

ora spmlist $p_sql_handle
echo "Drop Sql Plan Baselines of sql_handle $p_sql_handle"
sqlplus -s "/ as sysdba" <'$p_sql_handle', plan_name=>$pname);
  dbms_output.put_line('...Dropped '||cnt||' SQL Plans');
end;
/
EOF
ora spmlist $p_sql_handle
exit;
fi

if [ "$1" = "spmevolve" ]; then
if [ "$2" = "" ]; then
echo "ora spmevolve  [plan_name]             Evolve the plan from SPM";
exit;
fi
if [ "$3" = "" ]; then
pname="NULL";
else
pname="'$3'";
fi

iv=$2
if [ "$iv" -eq "$iv" 2> /dev/null ]; then
  echo "Try to get sql_handle by hash_value $iv:"
  p_sql_handle=`spm_hv2sqlhandle $iv`
  if [ "$p_sql_handle" = "NOT_FOUND" ]; then
     echo "..Not found sql_handle for hash_value $iv, exit..."
     exit;
  else
     echo "...Got sql_handle $p_sql_handle"
  fi
else
  p_sql_handle=$iv
fi

ora spmlist $p_sql_handle
echo "Evolve Sql Plan Baselines of sql_handle $p_sql_handle"
sqlplus -s "/ as sysdba" <'$p_sql_handle', plan_name=>$pname);
  dbms_output.put_line( dbms_lob.substr( rpt, 32767, 1 ) );
end;
/
EOF
ora spmlist $p_sql_handle
exit;
fi

if [ "$1" = "spmmigol" ]; then
if [ "$2" = "" -o "$3" = "" ]; then
echo "ora spmmigol   [fixed]           Migrate the plan to SPM using DBMS_SPM.MIGRATE_STORED_OUTLINE";
exit;
fi
if [ "$4" = "" ]; then
pfixed="YES";
else
pfixed="$4";
fi
sqlplus -s "/ as sysdba" <'$2', attribute_value=>'$3', fixed=>'$pfixed');
  dbms_output.put_line( dbms_lob.substr( rpt, 32767, 1 ) );
	dbms_output.put_line('Changing converted outlines to PLAN_MIGRATED category');
  if lower('$2') = 'outline_name' then
	    execute immediate 'alter outline $3 change category to PLAN_MIGRATED';
      :pname := '$3';
	elsif lower('$2') = 'category' then
      for r in (select ol_name, 'alter outline '||ol_name||' change category to PLAN_MIGRATED' st from outln.ol\$ where category='$3')
      loop
        dbms_output.put_line(r.st);
        execute immediate r.st;
      end loop;
	elsif lower('$2') = 'all' then
      for r in (select ol_name, 'alter outline '||ol_name||' change category to PLAN_MIGRATED' st from outln.ol\$)
      loop
        dbms_output.put_line(r.st);
        execute immediate r.st;
      end loop;
  elsif lower('$2') = 'sql_text' then
      for r in (select ol_name,sql_text from outln.ol\$)
      loop
        if substr(r.sql_text,1,4000) = '$3' then
          dbms_output.put_line('alter outline '||r.ol_name||' change category to PLAN_MIGRATED');
          execute immediate 'alter outline '||r.ol_name||' change category to PLAN_MIGRATED';
          :pname := r.ol_name;
        end if;
      end loop;
	end if;
end;
/
col SQL_HANDLE for a20
col SQL_TEXT_80 for a50
select
   sql_handle, plan_name,
   enabled, accepted, fixed,
   (select hash_value from v\$sql s where s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE and rownum=1) as one_hash_value,
   dbms_lob.substr(sql_text,80) as sql_text_80
from dba_sql_plan_baselines b
where PLAN_NAME = upper(:pname)
order by one_hash_value desc,enabled, accepted, fixed
;
EOF
exit;
fi

if [ "$1" = "spmmigolname" ]; then
if [ "$2" = "" ]; then
echo "ora spmmigolname  [fixed]           Migrate outline plan to SPM by name";
exit;
fi
if [ "$3" = "" ]; then
pfixed="YES";
else
pfixed="$3";
fi
ora spmmigol outline_name $2 $pfixed
exit;
fi

if [ "$1" = "spmmigolcat" ]; then
if [ "$2" = "" ]; then
echo "ora spmmigolcat  [fixed]           Migrate outline plan to SPM by category";
exit;
fi
if [ "$3" = "" ]; then
pfixed="YES";
else
pfixed="$3";
fi
ora spmmigol category $2 $pfixed
exit;
fi

It’s a pity that I don’t know how to wrap the following part to a function in ksh. So the below part is repeated several times.

iv=$2
if [ "$iv" -eq "$iv" 2> /dev/null ]; then
  echo "Try to get sql_handle by hash_value $iv:"
  p_sql_handle=`spm_hv2sqlhandle $iv`
  if [ "$p_sql_handle" = "NOT_FOUND" ]; then
     echo "...Not found sql_handle for hash_value $iv, exit..."
     exit;
  else
     echo "...Got sql_handle $p_sql_handle"
  fi
else
  p_sql_handle=$iv
fi

It’s very easy to use at UNIX/LINUX hosts. Also, it can be converted to PL/SQL procedures easily.

How-to write Python for Oracle

First of all, you need to install the Python driver for Oracle module, cx_Oracle. The easiest way is to use pip or easy_install to install it.

$ pip install cx_Oracle
or
$ easy_install cx_Oracle

Then, you can start to write scripts. Here is an example:

#!/usr/bin/python
'''
Create a user, drop a user
'''

    def __do_create_oracleuser(self):
        isSuccess = False
        
        #password equal to username
        oracle_pass = self.newuser  
        privileges = self.privileges

        check_user_sql = "select count(*) from dba_users \
                          where username = upper('%s') " \
                          % (self.newuser, )                
        get_temptbs_sql = '''select decode(PROPERTY_VALUE,'SYSTEM', tablespace_name, PROPERTY_VALUE)
                            from 
                            (select tablespace_name 
                            from (select tablespace_name,sum(bytes) 
                                  from dba_temp_files 
                                  group by tablespace_name 
                                  order by 2 desc)
                             where rownum=1
                            ) a,
                            (select PROPERTY_VALUE 
                             FROM DATABASE_PROPERTIES 
                             where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'
                            ) b'''
        create_user_sql = "create user %s identified by %s        \
                           default tablespace %s quota %dG on %s" \
                           % (self.newuser, oracle_pass, self.tablespace,
                              self.space, self.tablespace )
        #permission = "connect, resource"
        #grant_sql = "grant %s to %s" % (permission, self.newuser)
         
        try:
            source = cx_Oracle.makedsn(self.vip, self.port, self.sid)
            db =  cx_Oracle.connect(self.sysuser,self.syspass, source)
            cursor = db.cursor()
            #1 check user exist
            cursor.execute(check_user_sql)
            count = cursor.fetchone()     
            if count[0] > 0:
                raise DbUserCreateError('DbUserCreateError: In Oracle DB User [' + 
                                    self.newuser + '] Already Exists.')        
            cursor.execute(get_temptbs_sql)
            temptbs = cursor.fetchone()
            create_user_sql += " temporary tablespace %s" %temptbs;
            cursor.execute(create_user_sql)
            if privileges:
                for privilege in privileges.strip().split(';'):
                    if privilege:
                        privilege_sql = "grant %s to %s" % (privilege, self.newuser)
                        cursor.execute(privilege_sql)
            
        except cx_Oracle.DatabaseError as exc:
            error, = exc.args
            self.logger.error(exc)
            raise DbUserCreateError(error) 
        else:
            isSuccess = True
            self.logger.info('Create Oracle Db User [' + self.newuser + '] Success!')
        finally:
            try:
                cursor.close()           
                db.close()
            except:
                self.logger.error('cx_Oracle.connect or db.cursor')
            
        return { 
                'username': self.newuser,
                'password' : oracle_pass,
                'db' : self.sid,
                'success' : isSuccess ,
                } 


    def __do_drop_oracleuser(self, db_item):
        sysuser = db_item['admin_user']
        syspass = db_item['admin_passwd']
        vip = db_item['vip']
        port = db_item['db_port']
        sid = db_item['sid']
        olduser = db_item['olduser']

        check_user_sql = "select count(*) from dba_users \
                          where username = upper('%s') " \
                          % (olduser)  
        drop_sql = "drop user %s cascade" % olduser
        #TO-DO: lock user when decomm
        #lock_sql = "alter user %s account lock" % olduser
        try:
            
            source = cx_Oracle.makedsn(vip, port, sid)
            db =  cx_Oracle.connect(sysuser, syspass, source)
            cursor = db.cursor()
            #drop oracle user
            try:
                #1 check user exist
                cursor.execute(check_user_sql)
                count = cursor.fetchone()     
                if count[0] > 0:
                    cursor.execute(drop_sql)
               
            except cx_Oracle.DatabaseError as exc:
                #self.logger.error(exc)
                error, = exc.args
                raise DbUserDropError("OracleDbUserDropError:" + error)
            else:
                self.logger.info('Drop Oracle User[' + olduser + '] Success!')     
            
        except cx_Oracle.DatabaseError as exc:
            self.logger.error(exc)
            error, = exc.args
            raise DbUserDropError("OracleDbUserDropError:" + error)
        finally:
            try:
                cursor.close()           
                db.close()
            except:
                self.logger.error('cx_Oracle.connect or db.cursor')

Of course, it’s a quite simple example. But it’s a good and great start