Sunday 17 May 2015

Nobody reads your blog!


Yes, nobody!

I mean: REALLY read it; going through all the words and sentences and paragraphs and get through to the end.

During the time that I was working for BBC World Service (2002-2012) one of the more important guidelines for online content producers was: "All you have to play with is the first page, and in fact, first paragraph of your piece. You have to grab your audience's attention there, and say whatever you want to say in the top part of the page, before the user needs to scroll down, because they rarely do!"

There has been plenty of evidence supporting this theory (see likes of which in here, here and here) and as far as I know, this is still what's being preached in BBC, and many prominent international media organizations.

So what should we do?

Your blog post is a product

Your post should be a matchbox:
simple, small, to the point

And considering the evidence (read their conclusion as: "global short attention-span syndrome") you need to come up with a product like a matchbox, rather than Swiss army knife. Simple, Small, to the point.

As a product you need to think about two main aspects:

a. Content (the idea itself, what is it that your "product" supposed to do)
b. Form (packaging, how can you make sure that the "product" is bought, and also used)

a. Content


You are a technical blogger (an assumed audience of this post) and have something to write about. I believe you need to answer these questions, before putting pen on paper (or fingertip on keyboard... whatever)

1. What is the purpose of your post? Are you trying to spread the knowledge? educating people? or just blowing off steam about some bug you've come across? Do you actually want your post to be read by as many people as possible? or you're happy as long as one or two like-minded people read it?

1.5. Consistency always raises your profile. Are you happy with being a part-time educator, part-time bug nagger? Or you actually prefer to be known by only one of them?

2. Have you thought about your audience? Who are they? Are they "Oracle Ace directors?" Are they supposed to be technically as able as you are? How you considered the fact that even among Oracle Ace Directors people have different abilities? Not everybody is an "Optimizer guru," "Recovery master," or "Replication genius", and they all might still be Oracle Ace Directors. Do you care if someone reads your post and doesn't understand it?

3. Are you true to yourself regarding your own -possible- attempt at a shot at fame? If you want to be known as THE educator, or THE expert in a subject, it needs a lot of effort, not only to be that person, but also to make people notice you (i.e. how to package/sell your "product"). It needs commitment to presentation/marketing fundamentals, and awareness of the market characteristics and trends.

I found Tim Hall's advice very insightful on many other matters concerning management of this creative process.

b. Form


You can find plenty of style-guides on the internet (here and here, for example). They seem to have a number of key elements in common:
A Bad blog post!
  1. Choose intriguing yet short titles.

    Most of the blog posts are advertised via Twitter. So more compact title provides you with more room for hashtags to advertise your Tweet (and therefore your blog post) properly.

    Do consider concepts such as "impact" and "controversy" when you come up with a title. Avoid questions in titles; that's generally frowned upon in the world of "proper" journalism.

  2. Start with an abstract/introduction.

    Don't waste your readers' time by not stating the aim of your post, because if you make them go through it and they find out that it's not what they were looking for, they will hesitate to come back to your blog.

    Also people usually don't scroll down! If you don't present your idea in an exciting manner in the first two or three sentences, they'll simply leave your post!

  3. ALWAYS write short sentences, and short paragraphs.

    BBC World Service's guideline was "one sentence is enough in a paragraph," and if you read news on BBC's website, you can see how it looks.

    Nobody likes going through tens of uninterrupted lines of writing; especially on technical blogs which are usually wider than normal to avoid line-wraps when including programming code (or "execution plans" among Oracle bloggers).

  4. Break your post into little pieces.

    I advise using sub-headers religiously. It can make the scanning eyes of your readers stop, and pay attention to what you want them to pay attention to.

    Also think about the flow of your post. If you can use "ordered lists" as your sub-headers, do it.

  5. Use any tool that's available at your disposal to make your post less boring: images, links, unordered lists, Alex Gorbatchev's SyntaxHighlighter.

    You might think that your audience are too sophisticated for such "marketing tricks," but unfortunately we all (at least the more fortunate ones) can only read with our eyes. If the eyes don't like something they see, they try not to see it!

  6. A "Conclusion" part is as important as the "abstract/introduction."

  7. Audience is ALWAYS thinking: "I don't care who you think you are; I decide who you are!"

    So don't think they'll keep reading your posts no matter how long it is. They simply won't. Keep it short! (Unlike what I've done here, in my post!)

  8. Remove distractions from your blog post, as much as possible.

    So you are an OCA and an OCP and an OCM and an Oracle Certified Expert on something and you get tons of traffic that tens of companies want to advertise through your website? Sure, but every time someone comes into your page and the content of your post isn't the first thing that their eyes meet, you've effectively lost a reader.

  9. Making your blog Mobile-Aware increases your exposure
    Make your post as accessible/visible as you can.

    Use meta tags or anything else that can help your post appear in search engines when looking for a related word/phrase.

    Also I need to echo Julian Dyke's advice (thanks to Martin Widlake for reminding me of it): For better exposure you need to make your blog mobile-aware. Google announced on 26th February, 2015  how they're changing their search engine's criteria to give higher rankings to Mobile-aware blogs and sites. Also guidelines to test, and the definition of "mobile-awareness" is included in the statement.

Conclusion


Technical bloggers seem to read blogs in two circumstances, generally:
  1. When they're looking for a solution to their own problem (i.e. when they read a blog post they keep saying in their head: "cut the bullshit!")
  2. At night, after they're finished with day to day stuff. (i.e. their eyes and brains are too tired to spend a lot of energy to go through your "jewel of a post" line by line, and word by word.)
If you don't engage with them as soon as they start reading the title of your post, and don't make the whole experience of reading your post as comforting (and informative) at the same time, You have effectively wasted your time!

Thursday 14 May 2015

How to keep track of execution in multi-file/part Oracle scripts

disclaimer: here

Installation scripts can be a pain.

With multiple anonymous PL/SQL blocks here and there, and scripts calling other scripts and bind variables being initiated through running some other scripts and.... You get the image.

So if -God forbid- we want to check the result of an activity which might have happened in the earlier parts of this "spaghetti of codes," we need to kill a bunch of those precious gray cells and find a way!

Must of the time decisions can be based on "footprints in the data;" e.g. something is supposed to happen later in the script based on whether we have deleted a row or not. But If the script is supposed to be re-runnable (such as an installation/upgrade/patch script) how can you judge whether THAT rows has been dropped in this run, or a previous one?

Well, there is a solution after all.

Conditional Compilation Flags to the rescue!


As long as your script is running on an 10gR2 or later released instance, and your whole script runs in one session, you can use the session variables, aka Conditional Compilation Flags.

Their biggest advantage is that unlike the bind variables (set by DEFINE command), you can set/unset your session variables inside OR outside a PL/SQL block.

Either

ALTER SESSION SET PLSQL_CCFLAGS = 'flagName:value[,flag2Name:value2, ...]';

or inside a block:

EXECUTE IMMEDIATE 'ALTER SESSION SET PLSQL_CCFLAGS = ''flagName:value[,flag2Name:value2, ...]''';

Example Scenario


Imagine the following "install.sql" script (positioned in the same directory as "one.sql" and "two.sql"

CONN / AS SYSDBA
@@one.sql
@@two.sql
EXIT

"one.sql"

This sample script has two parts. The first anonymous block simulates an activity that whose result affect the operation (i.e. code-path) in the second script in the same file, and consequently the operation in a second file.

The first script populates a flag named "activityResult" (Note: Conditional Compilation Flags are case-sensitive) with either 0 or 1, depending on the results of the simulated operation.

The second script shows how can you check the value of the flag and create alternative code-paths.

$IF ... $THEN $ELSIF $ELSE $END is THE construct when it comes to Conditional Compilation.

The flags themselves are referred to as $$flagName.

SET SERVEROUTPUT ON
DECLARE
        v_RandomActivityResult  NUMBER := CEIL(DBMS_RANDOM.VALUE(0,6));
BEGIN
        dbms_output.put_line('script one - first block');
        dbms_output.put_line('Random Activity Result:' || to_char(v_RandomActivityResult));

        IF v_RandomActivityResult < 3 THEN
                -- Failure
                EXECUTE IMMEDIATE 'alter session set PLSQL_CCFLAGS = ''activityResult:0''';
        ELSE
                -- Success
                EXECUTE IMMEDIATE 'alter session set PLSQL_CCFLAGS = ''activityResult:1''';
        END IF;
END;
/

BEGIN
        dbms_output.put_line('script one - second block');
$IF $$activityResult = 1 $THEN
        dbms_output.put_line('The activity in the first block was successful!');
        dbms_output.put_line('Carrying out related activity...');
        EXECUTE IMMEDIATE 'alter session set PLSQL_CCFLAGS = ''relatedActivity:1''';
$ELSE
        dbms_output.put_line('The activity in the first block failed!');
        EXECUTE IMMEDIATE 'alter session set PLSQL_CCFLAGS = ''relatedActivity:0''';
$END
END;
/

"two.sql"

The "one.sql" showed that it is possible to access a session flag inside an anonymous block which hasn't populated/initiated the flag. The following script simply provides an example to clarify what does "session" mean!

SET SERVEROUTPUT ON
BEGIN
        dbms_output.put_line('script two');
$IF $$relatedActivity = 1 $THEN
        dbms_output.put_line('Related activity was carried out.');
$ELSE
        dbms_output.put_line('Related activity was not carried out.');
$END
END;
/

A sample output:


"two.sql" is merely another file which (as can be seen above) is run as part of "install.sql" so it's still in the same session, and can access the "relatedActivity" flag that was initiated in the second block inside "one.sql."

$ sqlplus /nolog @install

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 14 14:20:00 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected.

Session altered.

script one - first block
Random Activity Result:1

PL/SQL procedure successfully completed.

script one - second block
The activity in the first block failed!

PL/SQL procedure successfully completed.

script two
Related activity was not carried out.

PL/SQL procedure successfully completed.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
$

Conclusion


This post was an extremely simple implementation of what's being offered by Oracle through the concept of Conditional Compilation, and the directives are not limited to what were used in this post.

You can find Oracle's reference on the subject here.

Saturday 2 May 2015

DBMS_XPLAN can lie!

disclaimer: here

"Don't trust what you see!"

A cliche in spy movies, this sentence can also have a less impressive interpretation: "Don't be over-excited by any finding!"

I came to believe in this interpretation when I was studying for Oracle SQL Tuning Expert exam.

Going through experiments on using different DOP for INSERT...SELECT statement (a more comprehensive post coming up soon), I came across something which looked like a behavior change between 11.2.0.4 and 12.1.0.2 and -more importantly- seemed to contradict the documentation.

All I heard in my brain was this: "Shit! This is going to put me on the map! OakTable! Here I come!"

I got in touch with Martin Widlake, who was kind enough to go through the results of my experiment, and advised me to publish the results -which I'm extremely grateful for it.

DOP in INSERT..SELECT


Just for clarification, I'd like to rewrite the rule for calculation of DOP in INSERT..SELECT as below:

After the decision to parallelize the SELECT or INSERT operation is made, one parallel directive is picked for deciding the DOP of the whole statement, using the following precedence rule:

1.       Insert hint directive
2.       then Session
3.       then Parallel declaration specification of the inserting table
4.       and then Maximum query directive.

Oracle states: "In this context, maximum query directive means that among multiple tables and indexes, the table or index that has the maximum DOP determines the parallelism for the query operation."

(You can find 11gR2's original documentation here and 12.1.0.2's here.)

But the results of the experiment below in 12.1.0.2 appears to suggest that rule 4 overrides the rule 3, in this version.

Tables:

SQL> CREATE TABLE SOURCE_DATA NOLOGGING PARALLEL 4 AS
SELECT ROWNUM ID , MOD(ROWNUM,5) PART_ID, ROUND(DBMS_RANDOM.VALUE(1,ROWNUM)*5,5) VALUE
FROM DUAL
CONNECT BY LEVEL < 1000001;

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SOURCE_DATA');

SQL> CREATE TABLE DESTINATION_DATA AS
SELECT * FROM SOURCE_DATA WHERE 1=2;

Session
:

SQL> ALTER SESSION ENABLE PARALLEL DML;


The experiment


SQL> ALTER TABLE DESTINATION_DATA PARALLEL 3;

As you can see below, there are no hints on the statement, and Parallel DML is merely enabled on he session. We are doing a simple direct-path insert into a table with parallel declaration of 3, and selecting from a table which has a parallel declaration of 4. So based on Oracle documentation we expect the DOP to be 3 (i.e. "Parallel declaration specification of the inserting table") despite the higher DOP on the source table.

Using DBMS_XPLAN.DISPLAY_CURSOR, we get:

PLAN_TABLE_OUTPUT                                                                             
-----------------------------------------------------------------------------------------------
SQL_ID  cwrqam2gbx7ds, child number 0                                                         
-------------------------------------                                                         
INSERT /*+ append TEST3 */ INTO DESTINATION_DATA SELECT * FROM                                
SOURCE_DATA                                                                                   
                                                                                              
Plan hash value: 2115782672                                                                   
                                                                                              
-----------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |             |       |        |      |            |
|   1 |  PX COORDINATOR                    |             |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000    |  1000K|  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)|             |       |  Q1,00 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING |             |  1000K|  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR             |             |  1000K|  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL            | SOURCE_DATA |  1000K|  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------
                                                                                              
Predicate Information (identified by operation id):                                           
---------------------------------------------------                                           
                                                                                              
   6 - access(:Z>=:Z AND :Z<=:Z)                                                              
                                                                                              
Note                                                                                          
-----                                                                                         
   - Degree of Parallelism is 4 because of table property                                     
                                                                                              
 28 rows selected

WHAT?! DOP is 4?!

Not so fast though ... #sigh


During the comparison of the behavior between 11g and 12c, I kept checking the V$SQL.PX_SERVERS_EXECUTIONS, mainly because the DBMS_XPLAN.DISPLAY_CURSOR was not giving any hints on the employed DOP.

I hadn't done it for 12.1.0.2 because the output seemed to have enough information in that regard. But Martin Widlake suggested I do the same, to be consistent.

SQL> SELECT sql_id, child_number, PX_SERVERS_EXECUTIONS
FROM v$sql
WHERE sql_text LIKE '%TEST3%';

SQL_ID        CHILD_NUMBER PX_SERVERS_EXECUTIONS
------------- ------------ ---------------------
cwrqam2gbx7ds            0                     3

There you go! Oracle's documentation was not wrong after all. The DOP is in fact equal to the parallel declaration on the inserting table (i.e. 3).

One last blow came from OEM. You can clearly see the parallel servers involved in the INSERT...SELECT.


Just a note that seeing only one set of parallel servers proves Oracle's statement that when the decision is made to do both INSERT & SELECT in parallel, ONE parallel directive is used to carry out the operation; i.e. both operation use the same DOP.


Conclusion


Don't be deceived by the glamor of the extra information which is presented in 12.1.0.2. DBMS_XPLAN can lie.

Here I have to mention that I had also sought Jonathan Lewis' opinion about the post.

I'd like to quote this from Jonathan's response, when I got back to him to say that I was wrong:

 "There's always one more thing to check."

Disclaimer!

Dear Reader!

I hereby declare that the material published in this blog have no other purpose, but to remind fellow colleagues about some aspects of Oracle Database's behavior that 1st) I have come across, and 2nd) I've felt that they're not some of the commonly known aspects of Oracle's behavior.

Also I hope that the posts could help finding problems and [possible] solutions easier on the web, by simply spreading the word, and increasing the exposure of posts' subjects to search engines such as Google.

Therefore I would be grateful if the reader could remind me of a neglected [perhaps earlier] reference to the subject, by leaving a comment.

Most importantly, the posts should by no means be regarded as a manifestation of knowledge, nor experience. They are merely results of experiments that I have been forced to carry out, mainly by chance, and influenced by the projects that I'm involved in, exams I've taken, or curiosity.

It's up to you, the reader, to re-establish what's being presented in this blog as facts, and I humbly ask you to leave a comment, if you find an incorrect or incomplete piece of information on a post.

Regards,
Babak Tourani