Wednesday, May 16, 2012

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!

Monday, May 14, 2012

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="">wwv_flow_epg_include_modules.authorize</entry>

Saturday, May 12, 2012

SQL Developer shared setup from any machine

It's been on the forums a few times how to get SQL Developer to put it's config setup on to a flash drive.  The same thing works with Dropbox/Google Drive/...

Here's all it takes.
1) edit sqldeveloper/bin/sqldeveloper.con
   - add the path to the Dropbox or where ever location
    AddVMOption -Duser.home=/Users/klrice/Dropbox/sqldev

2) Start it up.

The to check just look in that location and you'll find a .sqldeveloper directory where everything will be stored.

You'll notice bunches of directories where all the settings are being stored.

Then just go to any machine with your drop box installed and you'll have the exact same setup everywhere.

Thursday, May 03, 2012

ODTUG Kscope12 Sunday DB Symposium Agenda

I always enjoy attending and presenting at ODTUG.  So much that last year,  I volunteered to help the track leaders, David Schleis , Eddie Awad , and Chet Justice organize the DB Symposium on Sunday.   It will be a great day that is starting with a joint keynote with Mike Hichwa on tools generally.  Then we split into the DB track where it starts the progression of  blank slate , developing, testing , building , deploying and wrapping up with tuning the deployed code. 

8:45 - 9:45 Keynote  - Oracle Database Tools  Mike Hichwa 

Mike Hichwa, vice president of database tools, will provide an insight into the latest development of database tools and the Oracle Database Cloud Service. Everybody who is currently utilizing Oracle database tools should attend this session.

9:45-10:30 -SQL Developer Data Modeler Kris Rice
Blog ( you are reading it! )  , Twitter 
SQL Developer Data Modeler allows for easy modification of existing database objects. This session will cover how to reverse engineer, modify, and create a script of the changes. Collaboration is an important part of any development team, with built in subversion support the modeler makes collaboration possible.

10:30 - 11:00 BREAK

11:00 - 12:00 - SQL Developer  -Jeff Smith
SQL Developer provides the framework for your complete PL/SQL development process. It begins with developers working together to quickly and efficiently build new applications. Versioning your code, merging in changes from other developers, pushing out the latest and greatest to your QA teams or the Cloud is all a natural part of the tool.
Of course before you publish anything to anyone, you're testing your code, right? For the folks out there that feel guilty about having their users testing their code for them, you can actually build your own test suites and identify regressions.

12:00 - 1:00 LUNCH

1:00 - 1:45 - Continuous Integration with Database projects - Kris Rice
Blog ( you are reading it! )  , Twitter 
Continuous Integration facilitates frequent integrations of a project. This can help reduce turn around times for broken builds by alerting the development team sooner. Hudson along with SQL Developer's built in source code versioning integration can make a highly coupled solution for any database project.

1:45-2:45 - How to "tune" a query  - Tom Kyte
Blog , Ask Tom 
I am often asked how I tune a query, or more generally - how does one "tune" a query. In this session - we'll look at how query tuning is performed. You might be surprised at the answer as it often does not involve rewriting a single query.

2:45 - 3:15 BREAK 

3:15-4:00 - SQLDeveloper with Enterprise Manager: integrating changes from developer to DBA  - Jagan Athreya
Developers and DBAs have usually faced conflicting changes, delayed application upgrades and production downtime due to lack of integrated tools to manage the database change lifecycle. Come to this session to see and learn about the integration between SQL*Developer and Enterprise Manager Cloud Control 12c that enables developers to define changes in SQLDeveloper and allows DBAs to promote these changes to Test and Production seamlessly via Enterprise Manager thereby reducing errors, accelerating productivity and eliminating unplanned downtime.

4:00-4:45 Oracle Optimizer - Top Tips to get Optimal SQL Execution all the Time  - Maria Colgan
Blog,  Twitter
Simple system changes such as gathering optimizer statistics or changing a parameter value can affect SQL execution plans and therefore the performance and stability of a system. Managing such changes is an ongoing challenge for many customers. This session shows the process of analyzing and resolving the most common SQL execution performance problems including, poor cardinality estimations, bind peeking issues, selecting the wrong access method and more. With clear how-to examples you will learn how to identifying and quickly resolving these issues.