Oracle Scratchpad

October 29, 2012

Help Yourself

Filed under: Non-technical — Jonathan Lewis @ 5:17 pm BST Oct 29,2012

When people ask for help on (for example) OTN, they are often asked to supply further information – sometimes in the form of requests for results from SQL queries. If you are ever in this position, you may find that you don’t understand what the query does, or why the information is useful – nevertheless you can still do something to make it as easy as possible for your potential saviour to help you.

Here’s an example to show you how NOT to do it:


Request for information: “Please format the contents of v$sga_dynamic_components.”
Response:

SQL> select * from v$sga_dynamic_components;

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
  MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER
---------- ---------- ------------------- ---------- ------------- ---------
LAST_OPER GRANULE_SIZE
--------- ------------
shared pool                                                         872415232
 872415232          0                   0          0 STATIC
              16777216

large pool                                                           16777216
         0          0                   0          1 GROW          IMMEDIATE
17-JUL-12     16777216

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
  MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER
---------- ---------- ------------------- ---------- ------------- ---------
LAST_OPER GRANULE_SIZE
--------- ------------

java pool                                                            16777216
  16777216          0                   0          0 STATIC
              16777216

streams pool                                                                0
         0          0                   0          0 STATIC

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
  MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER
---------- ---------- ------------------- ---------- ------------- ---------
LAST_OPER GRANULE_SIZE
--------- ------------
              16777216

DEFAULT buffer cache                                               4194304000
4194304000          0                   0          1 INITIALIZING  IMMEDIATE
17-JUL-12     16777216

KEEP buffer cache                                                           0

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
  MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER
---------- ---------- ------------------- ---------- ------------- ---------
LAST_OPER GRANULE_SIZE
--------- ------------
         0          0                   0          0 STATIC
              16777216

RECYCLE buffer cache                                                        0
         0          0                   0          0 STATIC
              16777216

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
  MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER
---------- ---------- ------------------- ---------- ------------- ---------
LAST_OPER GRANULE_SIZE
--------- ------------
DEFAULT 2K buffer cache                                                     0
         0          0                   0          0 STATIC
              16777216

DEFAULT 4K buffer cache                                                     0
         0          0                   0          0 STATIC
              16777216

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
  MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER
---------- ---------- ------------------- ---------- ------------- ---------
LAST_OPER GRANULE_SIZE
--------- ------------

DEFAULT 8K buffer cache                                                     0
         0          0                   0          0 STATIC
              16777216

DEFAULT 16K buffer cache                                                    0
         0          0                   0          0 STATIC

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
  MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER
---------- ---------- ------------------- ---------- ------------- ---------
LAST_OPER GRANULE_SIZE
--------- ------------
              16777216

DEFAULT 32K buffer cache                                                    0
         0          0                   0          0 STATIC
              16777216

ASM Buffer Cache                                                            0

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
  MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER
---------- ---------- ------------------- ---------- ------------- ---------
LAST_OPER GRANULE_SIZE
--------- ------------
         0          0                   0          0 STATIC
              16777216

Every row of the output is spread across three lines, and 43% of the output is in repeated headings. I’m just not going to try to read that.

One the plus side, this user did at least present the information in a fixed format style, rather than cutting and pasting from an HTML or GUI output – so it is relatively straightforward to get the output to an easily readable state if you’re prepared to spend a few minutes deleting lines and joining lines.

On the extra-minus side: the request said “please FORMAT the output…” – if the person who wanted help isn’t able to read and respond to such a simple request, why should anyone believe that they will be prepared to put in a little extra effort when things get a little harder.

Think about a few column declarations, perhaps to limit the length of character columns and introduce comma separators for very large numbers; set a linesize so that single rows don’t wrap excessively or inconveniently; set a pagesize so that the output is a reasonable (but not extreme) length between repeated headings.

A simple acid test before you post – do you think YOU would find it easy to read and if you don’t, why would anyone else bother to try.

The more thoughtful you are about supplying information the more likely it is that people will be happy to help you.

7 Comments »

  1. In a similar vein, when someone has posted a problem, I have asked for reproducible test cases where the situation warrants, mostly with SQL and PL/SQL related questions.
    I’ve found from my own experience that when I do that myself, I sometimes get a better understanding of the problem, and answer the question myself.

    If unable to create a reproducible test case, it usually means I don’t fully understand the problem.

    Comment by Jared — October 29, 2012 @ 8:06 pm BST Oct 29,2012 | Reply

  2. Excellent subject, Jonathan. Thanks for bringing this up. Indeed, there should be an (unwritten?) rule to provide better information when asking for help.
    As an example: a lot of folks complain about the time it takes to resolve issues with Oracle support. If only they provided more information upfront then maybe things would be easier/faster?
    I’ve now made it a point to add upfront the output of an “opatch lsinventory” to every SR I open with MOS. It’s simple and fast and provides straight away all the info a support person needs to get a proper context for further problem analysis. It’s the little things that count!

    Comment by Noons — October 29, 2012 @ 10:18 pm BST Oct 29,2012 | Reply

    • Noons,

      And Oracle are trying to make it easier to help them help us with the “Test Case Builder”.
      For reference – there’s a little write up and a couple of links at this URL.

      Comment by Jonathan Lewis — December 13, 2012 @ 3:09 pm BST Dec 13,2012 | Reply

    • Noons, that works both ways.

      On several occasions I have provided well formatted, easy to read information with the pertinent details, only to be asked questions that would be easily answered if the analyst just read the SR.

      There was a time when Oracle was making a point of requesting RDA reports with a new SR – I dutifully would install RDA and upload the report.
      RDA reports are very informative, but again there would be questions (that were wasting time) that could have easily been answered by reading the RDA report.

      One of my most outstanding non-support support issues was not with Oracle, but ETrade.
      I wrote a comprehensive and well organized report – screen shots showing the problem and explanatory text.
      It was 3 pages, but with screenshots it only took 5 minutes to read and understand.
      The analyst just plain never looked at it.
      ( ETrade BTW is the source of my 1st and 2nd worst technical support experiences)

      So, the jaded DBA (me in this case) gets a little tired of writing documentation that will not be read, resulting in a positive feedback loop with negative consequences.

      This is not true of all support analysts of course, as some are very good, but we all recognize the stalling techniques employed at times by some support techs.

      In the example given in the article, the analyst might have gone to the trouble of providing the script, if he could judge that perhaps the DBA at the other end was not quite up to the task of doing so.

      Food for thought.

      Comment by Jared — December 13, 2012 @ 6:05 pm BST Dec 13,2012 | Reply

  3. It would probably help if such instructions were in the FAQ. The fixed format style you mention is all that is in the FAQ. I don’t think the (otherwise excellent) BluShadow’s How To posts says what you suggest either, and it’s buried in a comment in Randolf’s. It’s easy to think they only apply to the sql/pl sql forum since that’s where they are.

    Even saying “make it so you could read it” is a bit suspect – not everyone has gone through British schooling. Some people are well practiced at reading output that is Dalek Ecrin.

    Comment by jgarry — October 29, 2012 @ 10:22 pm BST Oct 29,2012 | Reply

  4. Jonathan,

    Would you please consider writing something about bloom filters and how they are implemented in Oracle.

    Kind Regards

    Comment by Vijay Manthena — November 2, 2012 @ 5:03 am BST Nov 2,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,514 other followers