runsql.sh shell tool to run a SQL file

Purpose: Run SQL or PL/SQL in a file.

Background:

For the SQL that takes a long time to finish, we usually let them run as a backend job in database server. E.g. rebuild index, Online Table Redefinition.
This shell tool will make it simple to call a SQL_file.
.
When doing low level things I prefer to run as sys via sqlplus on the local server (not via client sqlplus and never thru tool).  General good practice.  Eliminate workstation or network flaps etc from causing issues.
.

Notes: use full name when referencing the database objects. e.g. scott.emp;

cat  $SQLPATH/runsql.sh
.
{code start}

#Goal: run SQL in a file, so we can run it with nohup in background,


if [ $# != 1 ]
then
 echo Usage: $0 "SQL file_name"
 echo Usage 2: nohup $0 "SQL_file_name > 0.log &"
 exit
else
 echo $1
 sql_file=$1
fi

#my_path=$(pwd)
#. $HOME/.bash_profile
#echo $my_path

sqlplus -S / as sysdba  <<+++
set wrap off
--set feedback off
set pagesize 0
set verify off
set termout off

set echo off
set verify off
set arraysize 2000
set linesize 5000
set trimspool on
set long 5000
set serveroutput on
set timing on

column filename new_val filename

select '$sql_file'||'_'|| instance_name || to_char(sysdate, '_yyyymmdd_hh24miss')||'.log' filename
from v\$instance;

spool &filename

select sysdate from dual;

@$sql_file

exit;

+++

MAIL_TO=your_name@abc.com
CC=your_group_name@abc.com

tail "$sql_file"*.log | mail -s "SQL $sql_file done at $(hostname) $ORACLE_SID" -c $CC $MAIL_TO

{code end}
.

Ora-4068 Handler

What is an ORA-4068 Error?

An ORA-4068 error occurs when:

• you have a pl/sql package
• someone uses the package
• the package maintains a state
• you recreate the package (deploy a new version), killing their state

How is an ORA-4068 Error Handled?

The first time a pl/sql package is invoked it will throw an ORA-4068 error.  One simply needs to capture this error and retry the call to the pl/sql package.  This will 'clean' a flag on the DB connection and the ORA-4068 error should no longer be thrown.  When using a DB connection pool it is possible to migrate connections between retries and receive a connection that has not had the ORA-4068 'flag' cleared, thus requiring a max of NUM_CONNECTIONS_IN_DB_POOL + 1 retries.

Why should I care about handling ORA-4068 Errors?

By handling ORA-4068 errors you allow the database team to deploy new pl/sql package versions as much as they like without requiring a restart of your application (yay)!

Code Example:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleDriver;
import java.sql.SQLException;
public class ExecutePackageProcedureTwice {
public  static void main(String[] args) throws Exception {
Connection conn = null;
CallableStatement cstmt = null;
long sleepInSecs = 20;
try {
    conn = getConnection();
    cstmt = conn.prepareCall("{call lib_history_pkg.log_book(?,?)}");
    cstmt.setString(1,"1");
    cstmt.setString(2,"1");
    executePkg(conn, cstmt);
    System.out.println("Sleeping for " + sleepInSecs + " seconds...");
    Thread.sleep(sleepInSecs*1000);
    System.out.println("Out of sleep...");
    executePkg(conn, cstmt);
} catch (SQLException e) {
            if(reExecutionRequired(e)){
                System.out.println("ORA-04068 detected - re-executing the package...");
                executePkg(conn, cstmt);
            } else
                throw e;    
} finally {
    try {
        if(cstmt != null)
            cstmt.close();
        if(conn != null)
            conn.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}
}
private static boolean reExecutionRequired(SQLException e) {
        return "72000".equals(e.getSQLState()) && e.getErrorCode() == 4068;
    }
private static Connection getConnection() throws Exception {
DriverManager.registerDriver(new OracleDriver());
return DriverManager.getConnection("jdbc:oracle:thin:@test_db01.abc.com:1521:test1", "app_user", "abc");
}
private static void executePkg(Connection conn,  CallableStatement cstmt) throws Exception {
System.out.println("Executing the package...");
cstmt.executeUpdate();
conn.commit();
}
}

Reference:

http://yong321.freeshell.org/oranotes/4068.txt

.

Polyglot persistence update 2012-Sept

This Polyglot Persistence broadcasting will be irregular.

[MongoDB]

* Pros *
 - Concurrency Improvements, from daemon global lock to DB Level Locking.
    -- it'll eventruall support Collection level locking in future release.
 - TTL Collections
 - Aggregation Framework,
 - Tag Aware Sharding
http://docs.mongodb.org/manual/release-notes/2.2/

* Cons *

Their arguments center around a couple of core themes:

- Product Maturity: low QA quality, sub-systems repeatedly breaking, driver inconsistencies, scattered and incomplete documentation, complex node management.

- Design Decisions: single write lock, memory-mapped files means that the server has little control over its performance, replication has no proxying causing ridiculous connection numbers, sharding is possible but very complex to implement b/c of several special nodes, sharding is unreliable, lots of pitfalls and gotchas.

The key thing to understand about MongoDB is that it's not a magic bullet. It has significant tradeoffs like everything else. At the end of the day, MongoDB kind of lives in its own little niche. It makes a lot of unique trade-offs that must be understood to use it effectively.

MongoDB is really a set of trade-offs. Many of the (NoSQL and SQL) databases are very specific in what they do. MongoDB is less specific but is serviceable for handling many different cases.

However, once you get to a certain scale, MongoDB will underperform the specialized solution. In fact, I'm seeing this at my day job where we are actively moving several sub-systems off MongoDB and onto better-suited products.

http://www.quora.com/MongoDB/Which-companies-have-moved-away-from-MongoDB-and-why

* Story *

Instagram was in the process of moving to MongoDB, but they eventually gave up and stuck with PostgreSQL.
- 2012-May-16


[PostgreSQL] 9.2

1.1 Index-only scans
1.2 Replication improvements
1.3 JSON datatype
1.4 Range Types

Many clients are excited about [JSON datatype], this could be another reason to move away from MongoDB.

http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2


[HBase]

* Cons *
Rows in HBase tables are sorted by row key. The sort is byte-ordered. All table accesses are via the table row key -- its primary key.

- byte-ordered, many sites have complained the inflexibility of this abstract sorting.

When desiging the key, we need to fully understand how the byte array is stored, put together the frequently accessed data set alone.

Example: lexicographical int sort results:
 1,10,100,11,12,13,14,15,16,17,18,19,2,20,21, ..., 9,91,92,93,9495,96,97,98,99.

To keep shaping the natural order, then the row keys must be left padding 0:
 001,002,...012,013,014,015,...099,100,101,...999.




Reference,

数据按照Row key的字典序(byte order)排序存储。设计key时，要充分排序存储这个特性，将经常一起读取的行存储放到一起。(位置相关性) 注意： 字典序对int排序的结果是1,10,100,11,12,13,14,15,16,17,18,19,2,20,21,…,9,91,92,93,94,95,96,97,98,99。要保持整形的自然序，行键必须用0作左填充。

http://www.cnblogs.com/wuren/


Thanks,
Charlie 木匠 | Database Architect Developer

SQL Plan Baseline fix Oracle Stream AQ high CPU used

Goal
----
Fix Oracle Stream AQ high CPU SQL. Tune SQL plan without change the source code(SQL text),

Story
-----
We are running 2 way master-master stream replication. One working database used 80~90% CPU,
The TOP SQL is related to Stream AQ process on table STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_C.
Normally, there are less than 100 rows in this busy table.
It is an IOT table, the data grows and then deletes, that creates lots of bee hive empty space in the index data block.
The CBO choosed FAST FULL SCAN for primary key leading portion predicate query.
Index range scan would be much better than FAST FULL SCAN.

There could be other solutions available too, e.g. 
- Adjust the table and index statistics.
- Regularly compact and rebuild the IOT.

Note
----
We might not need to prepare binding variable for targeted SQL plan. I will test it later.

Ref
---
A:\SQL\CBO\tune_sql_by_baseline01.sql

| Modification History:
|   Date       Who          What
| 18-Nov-2009: Charlie(Yi): Show binding variable value,
| 11-Nov-2009: Charlie(Yi): Create the file,


Solution
--------
* tune SQL execution plan by SQL plan baseline, load_plans_from_cursor_cache to 
  load the hint/changed resulting plan(s) into the SQL plan baseline of the original SQL statement,
  and then remove old baseline,
  
After fixed the SQL, we moved the baseline to another replication database through dbms_spm.pack_stgtab_baseline,

-- Original SQL
SELECT enq_tid
FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_C ctiot
WHERE enq_tid IN
  (SELECT msg_enq_tid
  FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_I qidx1
  WHERE qidx1.subscriber# = :1
  AND qidx1.name          = :2
  AND qidx1.queue#        = :3
  )
AND ctiot.cscn >= :4
AND ctiot.cscn  < :5
order by cscn,enq_tid;

------------------------------------------------------------------------------------------------------
| Id  | Operation | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |     |     |     |   4 (100)|     |
|   1 |  SORT ORDER BY |     |   1 |  91 |   4  (50)| 00:00:01 |
|*  2 |   FILTER |     |     |     |  |     |
|   3 |    NESTED LOOPS |     |   1 |  91 |   3  (34)| 00:00:01 |
|   4 |     SORT UNIQUE |     |   1 |  60 |   2   (0)| 00:00:01 |
|*  5 |      INDEX FAST FULL SCAN| SYS_IOT_TOP_56264     |   1 |  60 |   2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN | AQ$_CAPTURE_QUEUE_TABLE_Y |   1 |  31 |   0   (0)|     |
------------------------------------------------------------------------------------------------------

-- Hinted SQL
SELECT enq_tid
FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_C ctiot
WHERE enq_tid IN
  (SELECT --+ index(qidx1)
   msg_enq_tid
  FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_I qidx1
  WHERE qidx1.subscriber# = 2
  AND qidx1.name          = '0'
  AND qidx1.queue#        = 0
  )
AND ctiot.cscn >= 1
AND ctiot.cscn  < 91284204479
order by cscn,enq_tid;

----------------------------------------
| Operation   | Name       |
----------------------------------------
| SELECT STATEMENT |                   |                   
|  INDEX RANGE SCAN| SYS_IOT_TOP_56264 |
----------------------------------------

Step                                            
----

REM display current "bad" sql plan,

SQL>
set serveroutput off
set linesize 300

var l_bad_sql_id varchar2(30);
var l_sql_text varchar2(200);
exec :l_bad_sql_id := '52m1t23gdwdbb';
exec :l_sql_text := '"STRMADMIN"."AQ$_CAPTURE_QUEUE_TABLE_C"';

REM 2nd option to get SQL_ID,
SELECT b.executions EXEC,  INST_ID, b.sql_id, CHILD_NUMBER,
ROUND(b.ROWS_PROCESSED/b.executions,1) rows_per,
 ROUND(b.BUFFER_GETS/b.executions,1) gets_per,
 ROUND(b.ELAPSED_TIME/b.executions/1000000,3) ela_per, 
 b.module, parse_calls, first_load_time, disk_reads, 
 b.sql_text
FROM gv$sql b
WHERE UPPER(b.sql_text) LIKE UPPER('%'||:l_sql_text||'%')
  AND b.executions > 0
  and b.sql_text not like 'SELECT b.executions EXEC%'
ORDER BY b.executions DESC;


REM show execution plan,
select * from table(dbms_xplan.display_awr(sql_id=>:l_bad_sql_id, format=>'basic rows cost partition note last'));

SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, format=>'basic rows cost partition note last')) t 
WHERE sql_id = :l_bad_sql_id;


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

REM - show binding variable value,
select name, position,datatype_string, last_captured, value_string, b.child_number, b.child_address, b.address
from v$sql_bind_capture b
where sql_id = :l_bad_sql_id
and was_captured <> 'NO';

select name, datatype_string, value_string
from v$sql_bind_capture b
where sql_id = :l_bad_sql_id
and was_captured <> 'NO';


REM create sql plan baseline for the SQL,

declare
 l_pls number;
begin
 l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            => :l_bad_sql_id,
   plan_hash_value   => NULL,
   fixed             => 'NO',
   enabled           => 'NO'
 );
 u$err.trc('Number of plans loaded:'||l_pls);

end;
/

REM get sql text, sql_handle
select sql_text, sql_handle, plan_name, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines
where sql_text like '%'||:l_sql_text||'%';

var l_sql_handle varchar2(30);
exec :l_sql_handle := 'SQL_2ff2efd831f30775';

REM change sql hint to tune SQL execution plan,
set serveroutput off
set linesize 300
var l_sql_id varchar2(30);
var l_sql_plan_hash_value number;
var l_sql_child_number number;

var B1 number;
exec :B1 := 2;
var B2 varchar2(30);
exec :B2 := '0';
var B3 number;
exec :B3 := 0;
var B4 number;
exec :B4 := 91284219049;
var B5 number;
exec :B5 := 91284219064;

SELECT enq_tid
FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_C ctiot
WHERE enq_tid IN
  (SELECT --+ index(qidx1)
   msg_enq_tid
  FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_I qidx1
  WHERE qidx1.subscriber# = :B1
  AND qidx1.name          = :B2
  AND qidx1.queue#        = :B3
  )
AND ctiot.cscn >= :B4
AND ctiot.cscn  < :B5
order by cscn,enq_tid;


REM capture new SQL_ID and plan_hash_value,
begin

with m as (select sid from v$mystat where rownum <= 1)
select prev_sql_id, prev_child_number
 into :l_sql_id, :l_sql_child_number
from m, v$session s
where s.sid = m.sid;
select plan_hash_value into :l_sql_plan_hash_value from v$sql
where sql_id = :l_sql_id
and rownum <= 1;

end;
/

print :l_sql_id;
print :l_sql_plan_hash_value;
print :l_sql_child_number;

REM verify the new SQL plan,
select * from table(dbms_xplan.display_cursor(sql_id=>:l_sql_id, cursor_child_no=>:l_sql_child_number,
format=>'basic rows cost partition note last'));

REM load new sql plan into plan baseline, with same sql_handle,
declare
 l_pls number;
begin

 l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            => :l_sql_id,
   plan_hash_value   => :l_sql_plan_hash_value,
   sql_handle        => :l_sql_handle,
   fixed             => 'NO',
   enabled           => 'NO'
 );
 u$err.trc('Number of plans:'||l_pls);
end;
/

REM display sql plan baseline,
SELECT *
FROM table(dbms_xplan.display_sql_plan_baseline(
 sql_handle => :l_sql_handle,
 format => 'typical'
))
;


PLAN_TABLE_OUTPUT

Plan name: SQL_PLAN_2zwrgv0sz61vp0ea25bc7  Plan id: 245521351
Enabled: NO Fixed: NO Accepted: YES  Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
|*  5 |      INDEX RANGE SCAN| SYS_IOT_TOP_56264 |     1 |    60 |     2   (0)| 00:00:01 |


REM enable new plan in plan baseline,
REM changes an attribute of a single plan or all plans,

set serveroutput on

declare
 l_pls number;
begin

 l_pls := dbms_spm.ALTER_SQL_PLAN_BASELINE( 
   sql_handle        => :l_sql_handle,
   plan_name         => 'SQL_PLAN_2zwrgv0sz61vp0ea25bc7',
   attribute_name => 'enabled', 
   attribute_value => 'YES'
 );
 u$err.trc('Number of plans:'||l_pls);
end;
/

REM fixed new plan in plan baseline,
REM if it is realy a good plan,
declare
 l_pls number;
begin

 l_pls := dbms_spm.ALTER_SQL_PLAN_BASELINE( 
   sql_handle        => :l_sql_handle,
   plan_name         => 'SQL_PLAN_2zwrgv0sz61vp0ea25bc7',
   attribute_name => 'fixed', 
   attribute_value => 'YES'
 );
 u$err.trc('Number of plans:'||l_pls);
end;
/

REM verify attribute ENABLED and FIXED,
select sql_text, sql_handle, plan_name, enabled, accepted, fixed, autopurge 
from dba_sql_plan_baselines
where sql_handle = :l_sql_handle;


REM verify new plan is picked up by CBO optimizer,
explain plan for 
SELECT enq_tid
FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_C ctiot
WHERE enq_tid IN
  (SELECT 
   msg_enq_tid
  FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_I qidx1
  WHERE qidx1.subscriber# = :B1
  AND qidx1.name          = :B2
  AND qidx1.queue#        = :B3
  )
AND ctiot.cscn >= :B4
AND ctiot.cscn  < :B5
order by cscn,enq_tid;

select * from table(dbms_xplan.display());

alter system flush shared_pool;


REM remove all baselines with same sql_text,
DECLARE
  ret PLS_INTEGER;
BEGIN
  ret := dbms_spm.drop_sql_plan_baseline('SQL_2ff2efd831f30775', 'SQL_PLAN_2zwrgv0sz61vp980bca0f');
end;
/

DECLARE
  ret PLS_INTEGER;
BEGIN
  FOR c IN (SELECT sql_handle, plan_name
            FROM dba_sql_plan_baselines 
            WHERE creator = USER --'SH'
            --AND created > systimestamp - to_dsinterval('0 00:15:00')
            --AND enabled='NO'
            and Upper(sql_text) like '%QIDX1.SUBSCRIBER# = %'
            )
  LOOP
    ret := dbms_spm.drop_sql_plan_baseline(c.sql_handle, c.plan_name);
    u$err.trc(c.sql_handle ||': '|| c.plan_name);
  END LOOP;
END;
/


-- Moving SQL Plan Baselines,

exec dbms_spm.create_stgtab_baseline( table_name => 'MYSTGTAB', table_owner => 'OUTLN', tablespace_name => 'USERS');

DECLARE
  ret PLS_INTEGER;
BEGIN
  ret := dbms_spm.pack_stgtab_baseline( table_name => 'MYSTGTAB',
    table_owner => 'OUTLN',
    sql_handle => 'SQL_2ff2efd831f30775'
  );
end;
/

exp system tables=outln.MYSTGTAB file=baseline_stg
imp system FROMUSER=outln TOUSER=outln tables=MYSTGTAB file=baseline_stg


DECLARE
  ret PLS_INTEGER;
BEGIN
  ret := dbms_spm.unpack_stgtab_baseline( table_name => 'MYSTGTAB',
    table_owner => 'OUTLN',
    sql_handle => 'SQL_2ff2efd831f30775'
  );
end;
/

Thanks,

Charlie | Database Architect Developer

Oracle SQL*Developer features wanted

I'm a PL/SQL developer, I've been with Toad for 10 years. I got my MacBook Pro in 2012 April. Since there is no PL/SQL Developer release for Mac, and Toad is too expensive, I have to get my feet wet on SQL*Developer.
After 3 months use of SQL*Developer, here're something I hope I can get.

I hope below issues will be solved on future SQL*Developer release.

*******

Connections navigator: manipulate to sub-procedure/function, sub-sub<...>-procedures.
SQL Worksheet code edit mode:
 - Edit/Find... is often hanging.
 - Shortcut/hot keys to change upper/lower case.
 - block selection and edit/fill.
Save Package spec and body... :
 - sometimes saved mess/wrong code, cannot be compiled.
 - can I change the suffix to .sql from .pls ?
...
Some more is coming soon.


Notes.

Version 3.1.07.
Build MAIN-07.42


Thanks,

Charlie | Database Architect Developer

Cassandra: how to create a COLUMN FAMILY with Leveled Compaction?

My stupid simple question is: how to create a COLUMN FAMILY with Leveled Compaction in Cassandra 1.1+?

There is no example in documentation:
http://www.datastax.com/docs/1.1/configuration/storage_configuration#compaction-strategy

I try it on Cassandra 1.1.0 and 1.1.2, both failed. The COLUMN FAMILY is still 'SizeTieredCompactionStrategy'.  :(

Here is my test and output:

@host01:/usr/share/cassandra>cqlsh host01 --cql3
Connected to BookCluster at host01:9160.
[cqlsh 2.2.0 | Cassandra 1.1.0 | CQL spec 3.0.0 | Thrift protocol 19.30.0]
Use HELP for help.
cqlsh>
cqlsh> use demo;

cqlsh:demo> CREATE COLUMNFAMILY book
        ... (isbn varchar,
        ...  book_id bigint,
        ...  price int,
        ...  obj varchar,
        ...  PRIMARY KEY (isbn, book_id)
        ... )
        ... WITH compaction_strategy_class='LeveledCompactionStrategy';
cqlsh:demo>
cqlsh:demo> describe COLUMNFAMILY book;

CREATE COLUMNFAMILY book (
  isbn text PRIMARY KEY
) WITH
  comment='' AND
  comparator='CompositeType(org.apache.cassandra.db.marshal.LongType,org.apache.cassandra.db.marshal.UTF8Type)'
AND
  read_repair_chance=0.100000 AND
  gc_grace_seconds=864000 AND
  default_validation=text AND
  min_compaction_threshold=4 AND
  max_compaction_threshold=32 AND
  replicate_on_write=True AND
  compaction_strategy_class='SizeTieredCompactionStrategy' AND
  compression_parameters:sstable_compression='org.apache.cassandra.io.compress.SnappyCompressor';

cqlsh:demo>

update:

it's been fixed since Cassandra 1.1.5


Thanks,
Charlie (@mujiang) 一个 木匠
=======
Data Architect Developer
http://mujiang.blogspot.com

Web Service by Python

To develop web service with Python is really simple.

Here are 2 examples.

Demo 1, hello world.

#!/usr/bin/env python27

#!/usr/bin/python27

from wsgiref.simple_server import make_server

# A relatively simple WSGI application. It's going to print out Hello,

def simple_app(environ, start_response):

    status = '200 OK'

    headers = [('Content-type', 'text/plain')]

    start_response(status, headers)

    ret = 'Hello Friend.'

    return ret

httpd = make_server('', 7001, simple_app)

print "Serving on port 7001..."

httpd.serve_forever()

Demo 1.1, Print out the environment parameters.

from wsgiref.util import setup_testing_defaults

from wsgiref.simple_server import make_server

# A relatively simple WSGI application. It's going to print out the

# environment dictionary after being updated by setup_testing_defaults

def simple_app(environ, start_response):

    setup_testing_defaults(environ)

    status = '200 OK'

    headers = [('Content-type', 'text/plain')]

    start_response(status, headers)

    ret = ["%s: %s\n" % (key, value)

           for key, value in environ.iteritems()]

    return ret

httpd = make_server('', 7100, simple_app)

print "Serving on port 7100..."

httpd.serve_forever()