NoCOUG 11/9

Attended the "North California Oracle Users Group" held in the Computer History Museum at Mountain view that my colleague recommended, and was so not disappointed at all! It was very informative and fun, with networking and socializing blended in.

Lot of us overlook the role physiology and psychology plays in writing good code. Playing games helps develop rationale and good debugging skills. Asking for help from colleagues is good, and should be encouraged. There is no such thing as too many questions posed. Helping each other builds team values, boosts confidence and mutual respect within the team. Senior people in the team should set an example by involving junior members in problem solving and asking for "help" proving that everyone is fallible and its okay to lend hand when need be.

There are lot of inbuilt features in Oracle we should exploit to our advantage. Function Result cache, available in Enterprise edition of Oracle helps improve performance significantly. Usage of varchar2(n) should be treated as a code bug, use Type and Subtype instead. Avoid hardcoding at multiple places eg: size limit, error code etc. Use Forall as opposed to For for better execution. Monitor your PGA usage even as Oracle manages it.

Oracle handles errors well, but doesnt report it clear enough. These issues can be addressed by raising bugs, and/or working around them. Any exception in the Begin block will be trapped by the following Exception block, if it exists. If the same happens within declare, it will be thrown out of the block to the exception handler, if it exists. Oracle initializes packages only once unless the users logout and log back in. This can get a little baffling to see the errors show up once and be not reproducible again. Use dbms utility error handling functions like call stack and error stack to log more information. Oracle can build error tables that can be used to log issues. These tables can be modified and maintained like any other database table.

See toadworld.com/sf for more.

Errors with Buffers is a pretty common scenario that DBAs face. Buffers can be designed as CBC, LRU, or WL, they can be data or index type. Buffers can be in any of these states at any common point- Free, Pinned or Dirty. When faced with a concurrency issue, it becomes imperative to diagnose the pattern in the file number, block number spit out by querying v$session. If you find a table/data block contention, moving data to the buffer cache can help reduce number of reads and help mitigate the issue. If it turns out to be table/segment header contention, we may need to free some blocks to make them available. If it is undo/segment header contention, adding some redo blocks will help. In case of Index/Root block contention, partitioning may be a good idea. With Index/Leaf block contention, try Reverse key indices. Usage of Reverse keyword while creating the index hints the optimizer to reverse the key index and not opt for serialized insertion that might impact performance.

SQL Monitoring is a pretty powerful tool that is available by default with Oracle. By default, it monitors the queries that run for 5+ secs. One can choose to provide a Monitor hint to alter this.

With Enterprise Manager, we can get a visual view of the report. Monitoring is always on, the number of reads, IO waits, Stack calls, Execution Plans can be viewed real time even as the query executes. With TopSQL, we can identify the worse performing SQLs. The optimizer can only help in bits to rectify the problem. DBOptimizer can analyze the queries and joins to give a visual diagram with the select columns, joins, and filters. Always start the join with the table with least rows. Even if the filter columns are indexed, the order in which these are joined will have an impact on the performance. By default, if joining more than 3 tables, Oracle optimizer joins 2 tables and then joins the result with the third one and so on. It never will join 2 tables, join 2 other tables and join the results. However, we can formulate our SQL to force this behavior if need be.

Some pics that I took in the museum, its the First harddrive ever used and the second one is a 10 digit adder-

Talk cloudy

I attended a "Cloudy" conference in San Jose, 9/24 (didnt get a chance to blog yet!)- Great sessions with speakers from the industry on Cloud Management helped me hone my Cloud Fu, socializing and networking alongside awesome food, overall a good Saturday afternoon well spent.

There is no silver bullet to solve all the complex problems on performance, scalability, security, speed etc. More, faster and efficient Hardware is the closest to a magic bullet. However, choosing the right cloud platform can help us leverage many of the built-in advantages and features for free - Bulletproofing, easy access, dealing with contention, redundancy, reliablity etc. The decision of deploying the application on a Public vs Private cloud, or even going
with a Hybrid approach, Build vs buy, can vary with multiple parameters including Performance and Cost. The backend choice of Mongo, clusters, Hadoop, Greenplum technologies depends on the Ease of
Absorption within the organization, Speed, and Size of the data we deal with on the cloud.

As a buyer shopping in the Cloud for Paas/Iaas, network is our prime concern besides other factors like storage, servers, tools and virtualization. We would like to know the incident policy of the provider, their fraud detection mechanisms, and the turn-around time to handle customer complaints.

Here is a link of the schedule, each of them has a summary on the session and the speaker associated with it. The presentation slides will be shared on the site as well-
http://talkcloudy2011fall.sched.org/