tidbit:DB2:Global temporary table indexes

Global temporary table is a great way to store session level data in DB2. However, it is important to understand, that these  are not 'in-memory' tables and performance of global temporary tables is much lower than arrays (DB2 9.5) or collections (DB2 9.7).

Another very important factor is to make sure to use indexes on global temporary tables. For example:

DECLARE GLOBAL TEMPORARY TABLE session.lookup_data ( row_id integer, value varchar(4000) ) NOT LOGGED on commit PRESERVE rows;

CREATE unique index session.lookup_data_idx ON session.lookup_data (row_id );

Index on global temporary table will provide huge performance improvement.

Another option is "NOT LOGGED" on table, which improves performance, but not drastically.

From a practical perspective, global temporary table are useful when we need to store millions of rows on a session level.

If the need is to store hundreds of rows, arrays are much faster.

As a practical case, recently I switched from session level tables to arrays and data processing performance jumped from 25 records/second to 550 records/second with the use of arrays.

tidbit:Oracle:Exadata Wait Events (cell related)

Exadata Wait Events:

NAME                                                             WAIT_CLASS
---------------------------------------------------------------- --------------------------------------------------

cell list of blocks physical read                                User I/O
cell manager cancel work request                                 Other
cell manager closing cell                                        System I/O
cell manager discovering disks                                   System I/O
cell manager opening cell                                        System I/O
cell multiblock physical read                                    User I/O
cell single block physical read                                  User I/O
cell smart file creation                                         User I/O
cell smart flash unkeep                                          Other
cell smart incremental backup                                    System I/O
cell smart index scan                                            User I/O
cell smart restore from backup                                   System I/O
cell smart table scan                                            User I/O
cell statistics gather                                           User I/O
cell worker idle                                                 Idle
cell worker online completion                                    Other
cell worker retry                                                Other

tidbit:Oracle:Exadata System Statistics (cell related)

Exadata cell statistics:

STATISTIC# NAME                                                                  CLASS
---------- ---------------------------------------------------------------- ----------
        51 cell physical IO interconnect bytes                                      64
       224 cell physical IO bytes saved during optimized file creation              64
       225 cell physical IO bytes saved during optimized RMAN file restore          64
       226 cell physical IO bytes eligible for predicate offload                    64
       227 cell physical IO bytes saved by storage index                             8
       228 cell smart IO session cache lookups                                      64
       229 cell smart IO session cache hits                                         64
       230 cell smart IO session cache soft misses                                  64
       231 cell smart IO session cache hard misses                                  64
       232 cell smart IO session cache hwm                                          64
       233 cell num smart IO sessions in rdbms block IO due to user                 64
       234 cell num smart IO sessions in rdbms block IO due to big payload          64
       235 cell num smart IO sessions using passthru mode due to user               64
       236 cell num smart IO sessions using passthru mode due to cellsrv            64
       237 cell num smart IO sessions using passthru mode due to timezone           64
       238 cell num smart file creation sessions using rdbms block IO mode          64
       239 cell physical IO interconnect bytes returned by smart scan               64
       240 cell session smart scan efficiency                                       64
       359 cell scans                                                               64
       360 cell blocks processed by cache layer                                    128
       361 cell blocks processed by txn layer                                      128
       362 cell blocks processed by data layer                                     128
       363 cell blocks processed by index layer                                    128
       364 cell commit cache queries                                                64
       365 cell transactions found in commit cache                                  64
       366 cell blocks helped by commit cache                                       64
       367 cell blocks helped by minscn optimization                                64
       368 cell blocks skipped due to chained rows                                  64
       369 cell simulated physical IO bytes eligible for predicate offload         192
       370 cell simulated physical IO bytes returned by predicate offload          192
       371 cell simulated session smart scan efficiency                            192
       372 cell CUs sent uncompressed                                               64
       373 cell CUs sent compressed                                                 64
       374 cell CUs sent head piece                                                 64
       375 cell CUs processed for uncompressed                                      64
       376 cell CUs processed for compressed                                        64
       377 cell IO uncompressed bytes                                               64
       394 cell index scans                                                         64
       571 cell flash cache read hits                                                8

tidbit:DB2:drop schema

Simple way to drop DB2 schema:

Db2 drop table ERRORSCHEMA.ERRORTABLE

Db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA('DG', NULL, 'ERRORSCHEMA', 'ERRORTABLE')"

Oracle:Windows:Database Upgrade Fall-back

Recently I was approached by one of the major customers to assist in creation of database fall-back procedure for a Windows based Oracle 10g RAC system, that is using OCFS to store database data.

The objective was to upgrade clusterware and database from Oracle 10.2.0.2 to 10.2.0.4 and on top of it apply patch 38. Obviously, both ORACLE_HOMES and database itself were to be upgraded.

In a large company world, I would suggest to create separate RAC cluster, setup standby database there and during the brief outage copy control files and redo logs, start-up database and be done with it. This way the fall-back is very simple (start using old system) and most of the work (installing and patching software) can be done without any production impact.

The problem was that customer engaged us only after their fall-back test totally failed and all outage process was based on actually upgrading production system (software and database).

The major issue was that Oracle provided fall-back failed, as downgrade did not worked as expected, as well as nothing was said about proper downgrade of ORACLE_HOMES.

At the end, we arrived to below fall-back procedure:

1) Shutdown all database related Windows services
2) Shutdown clusterware related Windows services
3) Kill ons.exe processes - they sometimes left hanging
4) Take system state backup (IMPORTANT)
5) Backup ocfs and oraclefence drivers
6) Backup database and clusterware Oracle homes
7) Backup Oracle Inventory
8) Backup control files and redo logs
9) Backup OCR using ocrconfig -export <exp_ocr_file.bkp>
10) Backup Voting disk using 'ocopy <voting_file> <exp_voting_file.bkp>'
11) Backup config files (init/tnsnames/listener/sqlnet) (just in case)

During fall-back exercise, we were able to:

1) Restore system state backup in 3 minutes
2) Restore drivers in 3 min (manual copy)
3) Oracle Homes and Inventory Restore was done using rename (1 minute)
4) OCR was restored using "ocrconfig -import" (3 minutes), no need to restore voting disk
5) Database restore from RMAN took 40-50 minutes
6) Redo restore (50GB) took 15 minutes in parallel with database restore
7) Control file restore took 1 minute
8) Database recovery and startup took 5 minutes

Fall-back was 100% successful

Re: Exadata Oracle performance troubleshooting

Just stumbled upon a great article related to Oracle problem troubleshooting on Exadata:

Issue is related to "TABLE ACCESS STORAGE FULL" in exec plan (smart scans) and tables with the very large number of columns, which is typical for any serious production environment.

See below:

http://tech.e2sn.com/oracle/exadata/performance-troubleshooting/exadata-smart-scan-performance

tidbit:Oracle:How long should be my oracle password

First of all: You need to prevent brute force attempts or make it almost impossible to brute-force.

Hackers today use FPGA and GPU based cracking systems, capable of processing 60,000,000 passwords (right - 60 million) per second per FPGA/GPU unit.

So, the answer is:

1) Make your user name very long, as together with the password, user name is used to create your hashed password sequence, which is used as a start by hackers

2) Considering that SYS is a short name, it's password MUST be very long

Overall: Password: no less than 12-15 characters and include mix of special characters in addition to alphanumerics

Sill, password can be long and make sense, like: "Th1s_Is+MyW1reLess"

To see what hackers are capable of using FPGA units, consider below database security URL:

http://conus.info/ops/ - with much respect to the author, he is touching the ground in regards to brute-force simulation, but reality is grimmer.

tidbit:Windows:list all perfmon statistics

Issue Command:  Typeperf -q

C:\WINDOWS\system32>Typeperf -q

\.NET CLR Data\SqlClient: Current # pooled and nonpooled connections
\.NET CLR Data\SqlClient: Current # pooled connections
\.NET CLR Data\SqlClient: Current # connection pools
\.NET CLR Data\SqlClient: Peak # pooled connections
\.NET CLR Data\SqlClient: Total # failed connects
\.NET CLR Data\SqlClient: Total # failed commands
\.NET CLR Networking(*)\Connections Established
\.NET CLR Networking(*)\Bytes Received
\.NET CLR Networking(*)\Bytes Sent
\.NET CLR Networking(*)\Datagrams Received
\.NET CLR Networking(*)\Datagrams Sent
\.NET Data Provider for Oracle\HardConnectsPerSecond
\.NET Data Provider for Oracle\HardDisconnectsPerSecond
\.NET Data Provider for Oracle\SoftConnectsPerSecond
\.NET Data Provider for Oracle\SoftDisconnectsPerSecond
\.NET Data Provider for Oracle\NumberOfNonPooledConnections
\.NET Data Provider for Oracle\NumberOfPooledConnections
\.NET Data Provider for Oracle\NumberOfActiveConnectionPoolGroups
\.NET Data Provider for Oracle\NumberOfInactiveConnectionPoolGroups
\.NET Data Provider for Oracle\NumberOfActiveConnectionPools
\.NET Data Provider for Oracle\NumberOfInactiveConnectionPools
\.NET Data Provider for Oracle\NumberOfActiveConnections
\.NET Data Provider for Oracle\NumberOfFreeConnections
\.NET Data Provider for Oracle\NumberOfStasisConnections
\.NET Data Provider for Oracle\NumberOfReclaimedConnections
\.NET Data Provider for SqlServer\HardConnectsPerSecond
\.NET Data Provider for SqlServer\HardDisconnectsPerSecond
\.NET Data Provider for SqlServer\SoftConnectsPerSecond
\.NET Data Provider for SqlServer\SoftDisconnectsPerSecond
\.NET Data Provider for SqlServer\NumberOfNonPooledConnections
\.NET Data Provider for SqlServer\NumberOfPooledConnections
\.NET Data Provider for SqlServer\NumberOfActiveConnectionPoolGroups
\.NET Data Provider for SqlServer\NumberOfInactiveConnectionPoolGroups
\.NET Data Provider for SqlServer\NumberOfActiveConnectionPools

Tidbit: Tired of logging each time w/ complicated password on your server?

Tired of logging in each time w/ complicated password on your server?

Download PuTTY, Pageant and PuTTYgen from PuTTY Download Page and put in your $WIN_HOME

Run PuTTYgen to create your public and private keys and save. Login with putty to your server and

create a ".ssh" directory’ with an "authorized_keys" file in your home directory.

Chmod .ssh to 700 and authorized_keys to 600. Make sure you own both .ssh and authorized_keys file.

Copy public key to your "authorized_keys" file. Add private key to Pageant.

With pageant running, you can now login w/o entering your password when you connect to server via putty.

If you have passphrase, pageant will ask you once per session upon login.

If you have multiple servers, you can just use one passphrase and one private key to access them all.

Oracle 11G: Reading data directly from oracle instance memory using "oradebug direct_access"

Use Oradebug direct_access to directly read data from x$tables

SQL> oradebug help direct_access

DIRECT_ACCESS  <set/enable/disable command | select query> Fixed table access

oradebug direct_access [enable/disable] trace - to start/stop writing data to trace file

Select Sample - getting information about library cache:

SQL> oradebug setmypid
Statement processed.

SQL> oradebug direct_access SELECT * FROM X$KSMLRU
ADDR    = D28F85C
INDX    = 0
INST_ID = 1
KSMLRIDX        = 1
KSMLRDUR        = 0
KSMLRSHRPOOL    = 1
KSMLRCOM        =
KSMLRSIZ        = 0
KSMLRNUM        = 0
KSMLRHON        =
KSMLROHV        = 0
KSMLRSES        = 0
KSMLRADU        = 0
KSMLRNID        = 0
KSMLRNSD        = 0
KSMLRNCD        = 0
KSMLRNED        = 0

"Where" clause is not supported:

SQL> oradebug direct_access SELECT * FROM X$KSMLRU WHERE KSMLRSIZ > 0
ORA-00933: SQL command not properly ended

Clouds everywhere

Too much empty cloud talk - we need to start classifying clouds, think what are they good for and start using them.

Amazon EC2, Rackspace, GoGrid, number of others are in fact IaaS - infrastructure as a service

Clouds like GoogleApps, Azure or Force - PaaS - platform as a service

IaaS vs. PaaS are totally different animals. Practically speaking, from a business perspective, I am very skeptical about generic PaaS (like GoogleApps or Azure).

Serious business users would use IaaS clouds due to it's scalability and procurement benefits, but who (in it's right mind) would have resources to start rewriting existing applications to run under Azure or GoogleApps?

I understand a young developer who would like to develop something for PaaS, but let's say this small development becomes a growing successful website - Can it be run under PaaS - what I hear in the industry today is "no way".

For example, DBAs complain a lot about SQL Azure Database, it's backup issues, scalability and visibility issues when it comes to problem root cause analysis.

From an economical perspective - also, no way, - right now, running fast growing web site on PaaS is too expensive and moving from it would be a pain.

As a note - I do see a great future for a specialized PaaS, such as Force - in this case, it is based on a solid and thriving existing community of business users with clearly defined needs and ability to pay for a right solution.

It would be great to see a PeopleSoft clouds or SAP clouds, but right now it is too far in the future.

I am an active cloud user and will share more about my practical cloud experiences. My company (Enteros) created white papers together with IBM about cloud scalability (which is also a big and misunderstood issue).

More later...

Great Oracle Internals book

Oracle8i Internal Services for Waits, Latches, Locks, and Memory
Out of number of great "Oracle internals" book this definitely stands out. Ignore 8i - wealth of information in this book deserves it to be read and reread.

I met author in 2001-2002 and he is a shy and very smart DBA from Australia. His website http://www.ixora.com.au/ is a treasure of Oracle internals information and should be studied and used by every reasonable good Oracle DBA.

This Book explain in a great depth internal work of different latches and locks - knowledge which DBA must have to resolve and identify root causes of very complex contentions.

Starting a Startup (part 1)

Being an engineer all my life transition to startup founder is not an easy thing, especially at the initial phase, when you have a good idea and working on a prototype of the product that will become an initial startup technology. I did it on my "moonlight" time - while being crazy loaded at work and having a beautiful growing family.

This is a real challenge and family support is critical - in fact all this work is done on out-of-work time, which is taken from your family - this is a great sacrifice on their behalf.

Initially there is one challenge - to create prototype of technology or web-site, but very quickly this is changing toward many operational and organizational activities.

I can list few and every one of them deserves separate entry in it's due time:

1) Incorporate your company - need to know what is good for taxation,your situation, etc. Many start with LLC and convert it later to S-Corp or C-Corp - I can recommend some good books on this that helped me.

2) Think about IP - need to file patent pending application almost immediately - very important to know what to file - sending bunch of prints is useless and will not protect your invention - again books are your friends here - I'll share my experience separately

3) Get legal help - lawyers like doctors, you need them and they are separate per your need - there are Corporate lawyers, IP lawyers, litigators (hopefully you would not need them right away, but best is to know one as in a life of your company you'll be surely sued)

IMPORTANT - this is a major money burner!!! - you should be very careful with the legals people time and use it appropriately. I'll share my cost-containment tactics later - this is very important how to deal with the lawyers. Lawyers are great (honest), but they love to talk and this will cost you.

4) Create your website - this includes not just a web-site coding, but also getting SEO running, forms coded

5) Start marketing - blogging, links, live marketing, etc

6) Set-up company communication channels - phone-line/PBX, web conference, phone-conference, physical company address, mail communication

7) Start networking, go to entrepreneur meetings (like SVASE and free classes)

8) Decide on your partner (if you'd want to have one) - VERY CAREFULLY - this can make or break your company

9) Test Water / Start thinking who can be potential future beta customers
......


As you can see, many things can be done on a shoe-string, without VC or angels money.

The main idea is to do as much as you can yourself to raise company valuation as much as possible before you'd start searching for outside investment. Don't be afraid to do new things - this is only helping you to learn more about how to run your own business and how to interact with people.

Also, as soon as you'd involve more people they will consume your time, which you do not have at this phase. Even interaction with Web designer will take more time that it will take it for you to make a web-site fix.

Will continue in a next post...

Technical areas I will write about

I was lucky to start working in database technology since very early - early versions of Oracle (v6), DB2 (2.1 on mainframe), SQL Server (v 6), MySQL (v3), Sybase and Teradata.

Arrival of scalable  NOSQL databases and a cloud puts a new life into database technology - what we can do with such databases as Hadoop, Simple DB, MongoDb, Cassandra opens new horizons!

I also start working with Internet technologies very early - sill remember using alpha version of Netscape over PPP and slirp. Was a beta user of Java (still holding SunSoft developer box with the first version of java) and prior to leaving to US (from Israel, mid 1995) was approached by Israeli SUN subsidiary to lead JDBC development (to create JDBC that was not existent at that time).

Using database and java technology while working with large insurance companies (Principal Financial Group, Allied Insurance, Nationwide Insurance), large Internet companies (eBay and Yahoo), supporting major Banks (like SVB) and Casinos (Like WYNN) creates great perspective and knowledge of what is working and not working under very high production load, what architectures are scalable and what is not scalable and should be broken into scalable pieces ASAP and how to handle critical production problems in real time.  All this I believe is worth sharing.

I also accumulated huge library of technical tidbits that can be very useful to DBAs, Application Server Admins and well as to System Admins.

Recently I work with data masking technology and find it to be very interesting area to cover as well.

I am sure I'll add more later - but this looks like a good start

Starting to Blog

I thought about blogging for quite a while - why would I blog? Who would be my readers? What good will it do? What can I write about?

I decided to blog, as I think I can share quite a wealth of information - I worked for a great companies, like eBay, founded by own bootstrap startup (Enteros), earned millions in revenue, lived in interesting countries - USSR, Israel, USA, so I guess there is something I can share both from technology and life perspective.

I do believe in sharing - I believe that by sharing I can empower people to get some useful information that in a microscopic doses will help us all, and will help my country that I love - USA