Kevin Kempf's Blog

July 29, 2011

How to make an iPhone work in a corporate environment

Filed under: iPhone — kkempf @ 10:10 am

Can we make this relationship work?

Cellular tether

It’s just an accepted fact in IT that you’re going to be contacted after hours.  Recently, my Blackberry Tour started giving me grief during data (internet) tethering with my Ubuntu desktop.  The bluetooth connection would drop itermittently, or not even be recognized by Ubuntu.  This wasn’t really acceptable, and I’d seen how slick the interface was on a colleagues iPhone with regard to the ease of creating a personal wi-fi hotspot.  Since I’ve never had a problem connecting my notebook to a wi-fi network, I reluctantly bid my Blackberry adieu and got the iPhone 4.  

What Blackberry does well

The Blackberry is an awesome workhorse for the business place. 

  • The battery lasts forever.  I could easily make a 3 day weekend without ever putting it on the charger.
  • Filters.  No offense, Oracle, but I don’t want my phone to wake me up at 4am because some analyst updated my SR.  Basically, if the email wasn’t from my company’s domain, it didn’t get to my Blackberry.
  • Sound Profiles.  I had an awesome little app for the Blackberry called profiler pro.  What this allowed me to do was set the phone to vibrate during the workday and loud at night.  In other words, alerts got to me when I needed them (and woke me up if necessary), and when I was at work, I assumed I’d already be aware of problems via email.
  • Keyboard.  This is so easy to type on.  I’m not part of the texting generation, I need keys in the right place and a tactile feel to them.

So the iPhone had to stack up against all of these requirements. 

First Impressions of the iPhone4

  • A few bumps with AD, but integrated to my work email via active sync
  • Beautiful display
  • Slick interface
  • How can the battery be that low already?
  • Why do I have to kill an application after I use it, shouldn’t it just shutdown?
  • Lots of apps
  • Will the email make a ding if the screen is locked?  Sleeping?  What about if the app isn’t running? 
  • Where’s the volume/ringtone control for incoming email?  Oh.  There isn’t one?  Great.  That little ding is supposed to wake me up?
  • Glad you put a rington control on texts.  I can choose 27 different rings for that. 
  • Where’s the filter?   I got this phone right in the middle of the memory upgrade and the subsequent DRCP fix noted in my last posting.  So I had to keep a close watch on email/alerts
    • I love your work, Steven Chan, but your updates are always at odd hours on the east coast
    • Why is someone adding me as a friend on Facebook at 2am?  They live in the same timezone as me.  Wait.  Why is my phone dinging to tell me this?
    • Oh look, Oracle closed my SR at 5:37am.   2 emails to tell me this.

Can I really be the only one in IT who needs an iPhone to be businesslike?

I’m beginning to see why this phone wasn’t immediately embraced by the corporate community.   It’s more geared toward consumer use, to be sure.  I don’t think I’ll be doing any face time with anyone.  Ever. 

I began looking at other options, such as buying apps to make up for what I think are pretty basic shortcomings.  I even read up on jailbreaking.  I was about to go that route when an old colleague illuminated a way to fix most of my grievances.  Hat tip to Rich for that. 

I should start out by explaining that my provider is Verizon wireless.  They create an email address for you which really sends a text to your phone.  So 1234567890@vtext.com would send a text to your phone if you had the number 123-456-7890.

So basically, to resolve the filter issue, I created exchange rules to forward messages from Enterprise manager and anyone in the IT department to my vtext.com email.  This, therefore, sends a text to my iPhone.  I set the text sound to an annoying tone which has a decent shot of waking me up.  Then I silenced the already nearly inaudible ding I used to get when I received an email by just turning it off in the settings.

This really killed 2 birds with one stone.  The only thing I don’t have resolved is how to silence the phone while I’m at work .  I mean, I remember to do it, once I get my first annoyingly loud message in the morning, but there should be an automatic way.  This is a computer, after all!  Suggestions would be welcomed. 

 

Advertisements

Oracle Database Resident Connection Pooling (DRCP)

Filed under: 11g, 11i — kkempf @ 9:34 am

This better work!

What is Oracle DRCP?

In a nutshell, it’s a pool of pre-allocated connections which can be invoked by a special call in the tnsnames.ora file.  In this manner, you “pay up front” once, and subsequently the “cost” of doing a connection to the database is dramatically reduced later.   A simple example is a web page.  By having a pool of connections already available, and recycling timed out connections, you can ensure your performance will be consistent regardless of volume (when the pool is properly tuned). I recommend this pageas a setup guide; it’s what I used.

A Bad Day

So we upgraded our main production server form 32gb of RAM to 64gb.  Happy day, right?  I mean I get to double my SGA, things should be smoking fast now right?  Kind of. The database would have been fine if only the 11i front end accessed it.  Unfortunately, we have a bolt-on mobile supply chain solution called Highjump, and it’s the software bane of my existence.  It pretty much constantly connects to the database via ODBC, in fact it connects and disconnects many times just to perform a simple action through the interface tables.  We have about 60 Highjump users in the company, it’s in use pretty much 24×7. The problem came in when the host CPU spiked after the memory upgrade.  Analyzing the nature of the CPU use, it wasn’t easy to explain, but it was clear that no conventional query was spinning the CPU.  There was just a mystery gap between DB time and CPU time:

Description Latest Collection Reference Collection
DB Time (seconds) 6626.19 5146.15
CPU Time (sec) 329.78 236.72
User I/O Wait Time (seconds) 2.88 29.98
Application Wait Time (seconds) 0.42 0.93
Cluster Wait Time (seconds) 0.00 0.00
Concurrency Wait Time (seconds) 6.11 2.88

So it turns out, the actual issue was connection creation. Doubling the SGA seems to have effectively made the creation of a dedicated server process connection about twice as expensive as it used to be. While this we were aware of this before, it wasn’t really problematic. At this point, however, it was killing our whole ERP.

Oh Behave!

This solution could be used to make any poorly written application (which connects to Oracle) behave, in addition to classic web server type examples.  So here’s a thumbnail sketch of the fix, and then I’ll go into detail.

  1. Start the Database Resident Connection Pool on the RDBMS
  2. Configure the DRCP on the RDBMS
  3. Install & Configure the client side
  4. Confirm it’s working as expected
  5. Since DRCP is now a requirement for operation, put a custom metric in EM to ensure the DRCP is running

Starting DRCP on the database

This part is really simple.  As sys just type EXECUTE DBMS_CONNECTION_POOL.START_POOL(); and you’re done.  My understanding is that once enabled (and not explicitly stopped) this pool will start with all subsequent database startups.  Trust but verify, right?  I mean Oracle has never lied to me before.

Within about 2-3 minutes of creating the pool the first time (with minimum-type parameters) the listener registered the service automagically.  If you have a problem getting it to show up, it seems you might try to register the local listener:

alter system set local_listener=’DEV’;
alter system register;
show parameter local_listener;

Anyways, type lsnrctl services SID:

lsnrctl services PROD

“N000” established:0 refused:0 current:0 max:40000 state:ready

CMON <machine: hostname.domainname.com, pid: 29590>

(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.domainname.com)(PORT=33161))

You will see this n000 background process on the OS, as well:

ps -ef|grep n000

oraprod  11191     1  0 Jul27 ?        Ss     0:07 ora_n000_PROD

Incidentally, to stop the pool, you issue EXECUTE DBMS_CONNECTION_POOL.STOP_POOL();

Configuring the DRCP on the RDBMS

This was a lot of conjecture, and in my case I chose to error on the side of conservative, allocating too much up front and cutting it back later. Straight out of Oracle’s DBA Guide, the following parameters are configurable

MINSIZE The minimum number of pooled servers in the pool. The default value is 4.
MAXSIZE The maximum number of pooled servers in the pool. The default value is 40.
INCRSIZE The number of pooled servers by which the pool is incremented if servers are unavailable when a client application request is received. The default value is 3.
SESSION_CACHED_CURSORS The number of session cursors to cache in each pooled server session. The default value is 20.
INACTIVITY_TIMEOUT The maximum time, in seconds, the pooled server can stay idle in the pool. After this time, the server is terminated. The default value is 300.This parameter does not apply if the pool is at MINSIZE.
MAX_THINK_TIME The maximum time of inactivity, in seconds, for a client after it obtains a pooled server from the pool. After obtaining a pooled server from the pool, if the client application does not issue a database call for the time specified by MAX_THINK_TIME, the pooled server is freed and the client connection is terminated. The default value is 30.
MAX_USE_SESSION The number of times a pooled server can be taken and released to the pool. The default value is 5000.
MAX_LIFETIME_SESSION The time, in seconds, to live for a pooled server in the pool. The default value is 3600.
NUM_CBROK The number of Connection Brokers that are created to handle client requests. The default value is 1.Creating multiple Connection Broker processes helps distribute the load of client connection requests if there are a large number of client applications.
MAXCONN_CBROK The maximum number of connections that each Connection Broker can handle.The default value is 40000. But if the maximum connections allowed by the platform on which the database is installed is lesser than the default value, this value overrides the value set using MAXCONN_CBROK. Set the per-process file descriptor limit of the operating system sufficiently high so that it supports the number of connections specified by MAXCONN_CBROK.

All of these are your best guess, at least at first. To change a parameter, use: EXECUTE DBMS_CONNECTION_POOL.ALTER_PARAM (”,’MINSIZE’,’10’); and to revert to defaults use: EXECUTE DBMS_CONNECTION_POOL.RESTORE_DEFAULTS(); Parameters persist through pool shutdown.

Install and Configure the Client Side

DRCP was introduced in RDBMS 11.1.0.6.  Therefore, it shouldn’t be a big surprise that you need client 11.1.0.6 or better installed to take advantage of this feature.  Once installed, all you need do is adjust your tnsnames.ora file a bit:

Old

PROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydomain.com)(PORT = 1521))

(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD) ) )

New

PROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydomain.com)(PORT = 1521))

(CONNECT_DATA = (SERVER = POOLED) (SERVICE_NAME = PROD) ) )

Yup, that’s it.  Simply change SERVER=DEDICATED|SHARED to SERVER=POOLED and you’re done.

Confirm it’s working as expected

Well this is actually the fun part.  There’s several ways to confirm your connection is in the new pooled connection.

First, log into the database from the 11.1.0.6+ client with the new tnsnames.ora file specifying server=pooled

1. Sit there and do nothing after logging in.  Assuming you didn’t change the parameter MAX_THINK_TIME, in 30 seconds (more or less if you changed it) you will be disconnected:

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 28 14:56:34 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved. Enter

password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

(wait 30 seconds)

SQL> select * from dual;

select * from dual * ERROR at line 1:

ORA-03113: end-of-file on communication channel Process

ID: 13184 Session ID: 801 Serial number: 54251

2. Check out the listener on the database before and after you make a connection

lsnrctl services PROD (look for N000 current connections)

Service “PROD” has 2 instance(s).

Instance “PROD”, status UNKNOWN, has 1 handler(s) for this service…

Handler(s): “DEDICATED” established:2 refused:0

LOCAL SERVER

Instance “PROD”, status READY, has 2 handler(s) for this service…

Handler(s): “N000” established:1 refused:0 current:1 max:40000 state:ready

CMON <machine: myhost.mydomain.com, pid: 6241>

(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mydomain.com)(PORT=44404))

“DEDICATED” established:5062 refused:0 state:ready

LOCAL SERVER

3. Log in, do one query, and wait for INACTIVITY_TIMEOUT seconds to kill you.  Assuming you didn’t change the parameter INACTIVITY_TIMEOUT, in 5 minutes (more or less if you changed it) you will be disconnected:

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 28 15:03:54 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved. Enter

password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from dual 2  ;

D

X

(wait 5 minutes and try it again)

SQL> /

select * from dual * ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 13184 Session ID: 742 Serial number: 36743

Use the data dictionary views to see what’s going on (straight from the Oracle doc)

DBA_CPOOL_INFO
Contains information about the connection pool such as the pool status, the maximum and minimum number of connections, and timeout for idle sessions.
V$CPOOL_STATS
Contains pool statistics such as the number of session requests, number of times a session that matches the request was found in the pool, and total wait time for a session request.
V$CPOOL_CC_STATS
Contains connection class level statistics for the pool.

Put a Custom Metric in Enterprise Manager

There’s really nothing exotic here, but I thought I’d show the screenshot of my setup

Checking for Active status on the connection pool

How DRCP Saved my bacon

As you can see by the command below, before 24 hours had passed, the DRCP had serviced just shy of 30,000 connections (7000+ per broker).

Before implementing DRCP, but with the memory added and the SGA at 48gb,  we were able to confirm that a dedicated server process connection took between 1 and 1.5 seconds each to establish with sqlplus.  After DRCP, connections take, effectively, no time.

Kentucky windage, admittedly, but it can’t be too far off: assume a savings of 1 second per connection (which is all CPU) and we’re at over 8 hours of CPU time saved from serving connections in under 24 hours.

lsnrctl services PROD

Service “PROD” has 2 instance(s).
Instance “PROD”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:1 refused:0
LOCAL SERVER
Instance “PROD”, status READY, has 5 handler(s) for this service…
Handler(s):
“DEDICATED” established:132211 refused:0 state:ready
LOCAL SERVER
“N000” established:7539 refused:0 current:13 max:40000 state:ready
CMON <machine:myhost.mydomain.com, pid: 29590>
(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mydomain.com)(PORT=33161))
“N003” established:7250 refused:0 current:12 max:40000 state:ready
CMON <machine: myhost.mydomain.com, pid: 11203>
(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mydomain.com)(PORT=59497))
“N002” established:7241 refused:0 current:16 max:40000 state:ready
CMON <machine: myhost.mydomain.com, pid: 11197>
(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mydomain.com)(PORT=55356))
“N001” established:7266 refused:0 current:11 max:40000 state:ready
CMON <machine: myhost.mydomain.com, pid: 11191>
(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mydomain.com)(PORT=48000))

July 13, 2011

Things support does which makes my head want to explode

Filed under: Uncategorized — kkempf @ 3:32 pm

Sorry, this is a quick post from the “I’m speechless” category:

Didn't Kirk manage to kill some super advanced computer with a logical argument like this?

July 7, 2011

More fun and games with EM 11g (Collection Errors)

Filed under: EM to monitor 11i, Enterprise Manager — kkempf @ 2:38 pm

Rarely Resilient

The other day I went into my custom metrics and to my surprise, saw most of the metrics were broken with Collection Error status as shown here:

Damage Control!

Obviously the problem here, is that aside from catastrophic database or general host problems, I’ve lost alerts on all the things Oracle didn’t bother to put in EM11g which are relevant to 11i.  Don’t get me started on the apps management pack.  You can try it for yourself.

Technical Fix

Finding nothing on the world’s slowest support site, I restarted agents, restarted the entire EM tier, but nothing helped.  Finally I went into each metric, typed in the apps password, and hit test:

 

Amazing!  It is working, except it’s not working.  Not sure why it just stopped trying. Regardless, hit OK and you can do the next one, and the next one, and the next one.

End State

About 15 minutes after I did this to all custom metrics, they were all working except for two, which I redid as described above and eventually they snapped into shape also

As normal as it gets

Blog at WordPress.com.