SQL Developer and Cloud.oracle.com

Cloud accounts are trickling out so figured I'd do a quick post showing how to connect SQL Developer to the Database Service. Check out http://cloud.oracle.com for details on the service offerings and to sign up for a trial.

Once your service is activated, connecting SQL Developer is pretty easy. You have to go into the Application Express and create new user. To get to the screens, simply choose Create user off the Administration menu.

The key is that the User Groups listed at the bottom of the page have to all be granted. This is to allow SQL Developer an OAuth2 client to connect to the RESTful calls we have created. I'll get into that in another blog post.

Now in SQL Developer, enter the username just created and the URL of the apex service into the URL field. Also the SFTP credential can be entered for doing data deployment ( also in another blog post coming soon ).

Then you simply use the tree widget as normal. Expand the tree, expand the tables and see the ones we seeded in for initial tinkering.

These are the clouds you are looking for.

APEX Listener 2.0 EA1 is out !

The new APEX Listener that will be powering the Oracle Cloud is out for an Early Access.  There have been huge changes so be sure to check the documentation.  The first change is that there's a more robust command line now.

Give it a try with this:
java –jar apex.war help


This new version supports multiple databases.  Be sure to check out "Configuring Multiple Databases" there's lots of flexibility on how to route which requests to which database.  Here's a jump start to getting it up and going.

To setup the database configuration information:
 java -jar apex.war setup --database sales_db

Then the how to route what to where has to be done.  There's 2 choices for this entire base URL or just the base path.

1) Base Path :  This will route anything /apex/sales/* to the sales_db

  java -jar apex.war map-url --type base-path --workspace-id sales_rest /sales sales_db



2) Base URL :  This will ONLY route exact matches of the enture url to the sales_db.  This include http vs. https.  This example would give a 404 for any https request.

java -jar apex.war map-url --type base-url --workspace-id sales_rest http://example.com/apex/sales sales_db

Coming soon....

Here's a screenshot of the upcoming configuration screens in sqldev to give an idea of some of the changes in the listener.  Everything is still driven off xml config files so everything in this screenshot is there today to try out.

KScope DB Symposium Slides / Thirsty Games

Here's all the slides from ODTUG's Kscope12 DB Symposium which took place on Sunday.  The flow of the day was meant to be from a blank or reversed diagram in the modeler and the through deploying and tuning.

If you missed it, be sure to check out the slides it was a great set of speakers.  Be sure to check out the video referenced in Tom Kyte's talk.  Anyone that is using a connection pool where the knee jerk reaction to performance is to increase the size of the pool will find it useful.

SQL Developer Data Modeler  
    - Kris Rice, Oracle Corporation
SQL Developer  
    - Jeff Smith, Oracle Corporation
Continuous Integration with Database Projects 
    - Kris Rice, Oracle Corporation
How to "Tune" a Query 
    - Tom Kyte, Oracle Corporation
    - Referenced YouTube video from the talk
SQLDeveloper with Enterprise Manager: Integrating Changes from Developer to DBA 
    - Jagan Athreya, Oracle Corporation
Oracle Optimizer - Top Tips to get Optimal SQL Execution All the Time 
    - Maria Colgan, Oracle Corporation

Can't talk about how great kscope was without mentioning the Thirsty Games.  Team Dev Tools represented us very well.

Here's @thatjeffsmith v.  @martindsouza 

And here's Ashley Chen v. Barbara Morris

New OBE series

I'm sure most people know by now that we have a VM we build and run the OTN Developer Day events based on those VMs.  The key to running the events is the Oracle By Example labs.  They are a great way to learn the tools and software in the VM.

What everyone may not know is all the OBEs are online as well.  The are runnable anywhere not just in the VM we build.  The Oracle Learning Library has TONS of great labs to learn.  The labs are tagged to make it very easy to find what you are looking for.

The latest addition is something we kicked around and everyone liked.  That's a series where the labs build upon the previous lab to learn more than 1 topic for 1 hour then switch to another topic.

What we came up with is the Application Developer Days Workshop Series .  This series starts in the SQL Developer Data modeler, then writing plsql in SQL Developer, then creating an app in APEX.  So a soup-to-nuts idea.  Starting from a clean slate and progressing into a functioning application.

Give it a try!

Apex Listener jdbc/security setup

In talking to a few customers, I realized lots of people run with the default settings. So, I thought I should point out a few things that should be conscious decisions vs. accept the defaults.  The defaults are some limits that are safe and will run a modest size app just fine.  When the app gets a few long running spots or  high concurrency it's time to look at changing those defaults.

1.  There are fewer and fewer reasons to use the OCI jdbc driver over thin but it is something that if there's a reason you can swap out.

<entry key="apex.jdbc.DriverType">thin</entry>

2. The pool of connections has a few things that can be adjusted.
a)  The initial size of the pool.  This is when you first start up the listener how many connections to establish so that user #1,2,3 will not be waiting on a new connection to be established.
<entry key="apex.jdbc.InitialLimit">3</entry>

b) The min is how low the pool is allowed.  If things like inactivity is set to idle down the connection this many will stay available.
<entry key="apex.jdbc.MinLimit">1</entry>

c) The max is what it sounds like the most connections that will be allowed.  For a system with high concurrent or some long running pages this will need to be increased.  The effect if not is that user #11 ( when set to 10 ) will be watching a browser spin.  Then the training we all have that the web is fast kicks in, the user cancels the page, and requests again thinking something was wrong and surly it'll be faster the next time.  That causes the next request.  Pretty soon there's a lot of queue requests waiting.  That all turns into phone calls/ emails to the sys admin , dba , and developers that the app is slow or broken.
    Talking to one user, he upped this to 100 and it made a huge difference no more queuing.  Another had to go well over 100 due to a combination of slow queries and high concurrency.  There's not 1 setting that covers everything.

<entry key="apex.jdbc.MaxLimit">10</entry>

d) InactivityTimeout will idle the connection pool back down to the minLimit over time as the connections are idle for the value provided.
<entry key="apex.jdbc.InactivityTimeout">1800</entry>

e) This one covers in case the connection gets lost due to anything at all.  If the connection pool doesn't get the connection back for some reason for this amount of time and is idle for this time, it will be reclaimed automatically
<entry key="apex.jdbc.AbandonedConnectionTimeout">900</entry>

f) Lastly for jdbc, this is how many request a connection will service before it is closed and new one opened.
<entry key="apex.jdbc.MaxConnectionReuseCount">50000</entry>


3) For an APEX install, the security function should always be set. This blocks non-apex procedures.  These could be procedures that happen to be granted to public by accident or intentionally.


<entry key="apex.security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>