Friday, February 27, 2015

Mongo db

Connecting to MongoDB
MongoDB shell version: 2.2.2
connecting to:
mongos> sh.status();
--- Sharding Status ---
  sharding version: { "_id" : 1, "version" : 3 }
        {  "_id" : "PPSHRD01",  "host" : "PPSHRD01:10001" }
        {  "_id" : "PPSHRD02",  "host" : "PPSHRD02:10002" }
        {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
        {  "_id" : "test",  "partitioned" : true,  "primary" : "PPSHRD01" }
        {  "_id" : "mktpdm",  "partitioned" : true,  "primary" : "PPSHRD02" }
        {  "_id" : "*",  "partitioned" : false,  "primary" : "PPSHRD01" }

D:\mongo\bin>mongod --dbpath=D:\mongo\bin\data\db
mongoimport --db test --collection gtin --type csv --file c:/poc.csv --fields item_sync_fact_id,sync_status,sync_cnt,unique_syncnt,frmcount,frmstatus,frmorgname,frmglobal,tocount,tostatus,torogname,toglobal,day,month,itemname,desc,brickcd,clscd

D:\mongo\bin>mongoimport --db test --collection gtin --type csv --file d:/mongo/
pocbck.csv --fields item_sync_fact_id,sync_status,sync_cnt,unique_syncnt,frmcoun
connected to:
imported 17430 objects


collectionName : 'gtin',


 emails.update({"solrid" : mongoid},{"$set": {"project":prtag, "customer":cutag,"phase":phtag,"topic":toptag,"importance":imtag,"expert":extag,"notes":notes,"tagged":'Y'}})

To go to mongo console


Wednesday, February 18, 2015

Oracle PL/SQL Performance Tuning Tips Part -2

Effective Coding Strategies
1. Use UPPER-lower case method to make code more readable.
PL/SQL code is made up of many different components:
PL/SQL data structures such as PL/SQL tables, bind
variables like Oracle Forms items, procedures, functions,
loops, declarations, control constructs, etc. All of these
elements break down roughly into two types of text:
reserved words and application-specific identifiers. The
PL/SQL compiler treats these two kinds of text very
differently. You can improve the readability of your code
greatly by reflecting this difference in the way the text is
displayed. I employ the following UPPER-lower rule to
highlight the distinction:
Enter all reserved words in UPPERCASE and all
application identifiers in lowercase.
Using a consistent mixture of upper- and lowercase
words increases the readability of your code by giving a
sense of dimension to the code. The eye can more easily
cruise over the text and pick the different syntactical
elements of each statement. The uppercase words act as
signposts directing the activity in the code. You can
focus quickly on the lowercase words for the
application-specific content.
There are other widely-used standards for use of case
in programming, most notably the Visual Basic style
of UsingInitCapInLongNames. Whichever you
approach you take, make sure it aids in readability --
and use it consistently!
2. Use consistent indentation.
You should use indentation -- in a ruthlessly consistent
manner -- to reflect the logical structure of your
program. All of the following statements should cause
indentation to another "level": IF statements, loops,
sections of PL/SQL Block, and exception handlers.
Watch out for indentations which are too short to
differentiate and too long to hold together related
statements. I recommend an indentation of three spaces.
3. Avoid hard-coded literals of any kind in your programs.
Every application has its own set of special or "magic"
values. These values might be configuration parameters
or user information. They are constants throughout the
application -- but that doesn’t mean they never change. In
fact, such values will almost always change eventually. To
avoid getting burned by literals which have been littered
throughout your code, follow these guidelines:
¥ Remove all literals (within reason) from your code.
Instead, declare constants which hold those literal values.
4. Make sure your name describes the module accurately.
A name can make a big difference in the usability of
your variables, modules and parameters. Your names
should improve the self-documenting nature of your
code. Follow these guidelines for procedures and
¥ A procedure is an executable statement, a command
to the PL/SQL compiler. Consequently, the grammar
of the procedure name should be similar to a
command: Verb_Subject.
¥ A function is used like an expression in an executable
statement. It returns or "represents" a value, so the
grammar of a function name should be a noun:
5. Test all of your assumptions as you debug your code.
Obvious, is it? Most of the time we aren’t even aware of all
the assumptions we make about our programs and the
environment. I have often wasted hours trying to find the
source of a problem because I muttered to myself "All
right, I know that the value of that variable is set..." or
something like that. And after I have tested everything
else, I will finally say "Oh, what the heck! Might as check
that variable." And lo and behold, that was my problem.
Don’t assume anything. Test everything.
Data Structures
6. Use anchored declarations whenever possible.
You can use the %TYPE and %ROWTYPE declaration
attributes to anchor the datatype of one variable to that
of a previously-existing variable or data structure. The
anchoring data structure can be a column in a database
table, the entire table itself, a programmer-defined
record or a local PL/SQL variable. In the following
example I declare a local variable with the same
structure as the company name:
In this second example I declare a record based on a
cursor’s structure:
CURSOR company_cur IS
SELECT company_id, name, incorp_date
FROM company;
company_rec company_cur%ROWTYPE;
Anchored types offer the following benefits:
1. Synchronization with database columns. Many PL/SQL
variables "represent" database information inside the
program. By using %TYPE, I am guaranteed that the
local variable’s data structure matches that in the
database. If I instead hard-coded my declaration, the
program could get "out of synch" with my data
dictionary and generate errors. For example, if the
previous declaration had declare my_company as
VARCHAR2(30) and then I expanded the column size
in the table to 60, my program is likely to cause
VALUE_ERROR exceptions.
2. Normalization of local variables. You use PL/SQL
variables to store calculated values used throughout
the application. You can use %TYPE to base all
declarations of these variables against a single,
centralized variable datatype. If you need to modify
that datatype, perhaps to expand the maximum size
of a number to reflect higher revenue, you only need
to change that single declaration. All anchored
declarations will then pick up the new constraint
when the programs are recompiled.
with the record TYPE statement.
Records offer the following benefits:
¥ Write less code: instead of declaring, assigning and
referencing individual variables, you can work the
record as a whole.
¥ Protect your code from changes in database
structures. Cursor and table records automatically
adapt to the definitions of the columns in the
underlying query.
Consider the program shown below. I wrote in while
sitting in a coach airline seat. There was very little room to
maneuver and so I wanted to minimize my typing. I was
also unfamiliar with the details of the data structures, such
as column names and datatypes. As a result, I worked
almost exclusively with records and was able to focus on
the logical structure of the program. I filled in the details
when back on the ground.
FOR imp_rec IN imp_cur
/* Remove after compiles successfully! */
IF NVL (imp_rec.prft_ovrd_seq_nu, 0) = 0
get_site_prof_data (imp_rec, li_rec);
get_provrd_data (imp_rec, li_rec);
insert_line_001 (li_rec);
insert_line_062 (li_rec);
IF duplicate_pl_site (imp_rec)
(imp_rec, li_rec, dup_count, new_pl_seq_nu);

8. Optimize foreign key lookups with PL/SQL tables.
Given the normalized nature of our databases, you will
have to perform many foreign key lookups (given this
key, get the entity’s name) in your screens and reports.
You can use PL/SQL tables to cache already-retrieved
values and thereby avoid repetitive SQL access. This
will speed up your programs. You build the PL/SQL
table incrementally with each query against the
database. On subsequent calls, however, you check the
table first and use that value. Here is the pseudo-code
which describes the logical flow behind this approach:
1 FUNCTION company_name
2 (id_in IN company.company_id%TYPE)
4 IS
6 get-data-from-table;
7 return-company-name;
11 get-data-from-database-table;
12 store-in-PL/SQL-table;
13 return-company-name;
14 END;
9. Use Booleans to improve readability.
You can use Boolean variables and functions to greatly
improve the readability of your programs. When you have
complex Boolean expressions in IF statements and loops
(the WHILE loop condition and the simple loop’s EXIT
statement), hide that complexity behind either a local
PL/SQL variable or a call to a Boolean function. The name
of the variable/function will state clearly a summation of
all the complex logic. You can also more easily re-use that
logic by hiding behind this kind of interface.
To get a feel for the improvement, compare the
following two IF statements. In the first conditional
statement, a raise is triggered by a complex series of
Boolean expressions. It is hard to make sense of this
logic and, even worse, it exposes the formula directly in
this code. What if the business rule changes?
IF total_sal BETWEEN 10000 AND 50000 AND
emp_status (emp_rec.empno) = ‘N’ AND
(MONTHS_BETWEEN (emp_rec.hiredate, SYSDATE)
> 10)
give_raise (emp_rec.empno);
In this second IF statement, the details are hidden
behind the eligible_for_raise function. As a result, the
code is more readable and the business rule is
encapsulated within the module.
IF eligible_for_raise (emp_rec.empno)
give_raise (emp_rec.empno);
Built-in Functions and Packages
10. Leverage fully the built-in functions.
PL/SQL offers dozens of built-in functions to help you
get your job done with the minimum amount of code
and fuss possible. Some of them are straightforward,
such as the LENGTH function, which returns the length
of the specified string. Others offer subtle variations
which will aid you greatly -- but only when you are
aware of those variations.
Two of my favorites in this category of hidden talents
are INSTR and SUBSTR, both character functions.
SUBSTR returns a sub-portion of a string. Most
developers only use these functions to search forward
through the strings. By passing a negative starting
location, however, SUBSTR will count from the end of
the string. The following expression returns the last
character in a string:
SUBSTR (my_string, -1, 1)
INSTR returns the position in a string where a substring
is found. INSTR will actually scan in reverse
through the string for the Nth occurrence of a substring.
In addition, you can easily use INSTR to count the
number of times a substring occurs in a string, using a
loop of the following nature:
substring_loc :=
INSTR (string_in, substring_in, 1, return_value);
/* Terminate loop when no more occurrences are found.
EXIT WHEN substring_loc = 0;
/* Found match, so add to total and continue. */
return_value := return_value + 1;
RETURN return_value - 1;
11.Get familiar with the new built-in packages.
In addition to the many built-in functions provided by
PL/SQL, Oracle Corporation also offers many built-in
packages. These packages of functions, procedures and data
structures greatly expand the scope of the PL/SQL language.
It is no longer sufficient for a developer to become
comfortable simply with the basic PL/SQL functions
like TO_CHAR and ROUND and so forth. Those
functions have now become only the inner-most layer of
useful functionality. Oracle Corporation has built upon
those functions, and you should do the same thing.
Just to give you a taste of what the built-in packages
offer consider the following possiblities:
Returns the ealpsed time in 100ths of seconds since
an arbitrary time. You can use it to measure subsecond
response time -- and also analyze the impact
of your coding practices on application performance.
Blocks the current program from continuing
execution for the specified number of seconds.
Server. (PL/SQL Release 2.1 and above)
Read a line of text from an operating system file. And
use the PUT_LINE procedure to write text back out
to a file. (PL/SQL Release 2.3 only)
The possibilities and capabilities aren’t quite endless,
but they are getting there! With each new release of the
Oracle Server, we get new packages with which to
improve our own programs.
12. Take advantage of the cursor FOR loop.
The cursor FOR loop is one of my favorite PL/SQL
constructs. It leverages fully the tight and effective
integration of the procedural aspects of the language
with the power of the SQL database language. It
reduces the volume of code you need to write to fetch
data from a cursor. It greatly lessens the chance of
introducing loop errors in your programming Ñ and
loops are one of the more error-prone parts of a
program. Does this loop sound too good to be true?
Well, it isn’t Ñ it’s all true!
Suppose I need to update the bills for all pets staying
in my pet hotel, the Share-a-Din-Din Inn. The
example below contains an anonymous block that
uses a cursor, occupancy_cur, to select the room
number and pet ID number for all occupants at the
Inn. The procedure update_bill adds any new
changes to that pet’s room charges.
2 CURSOR occupancy_cur IS
3 SELECT pet_id, room_number
4 FROM occupancy
WHERE occupied_dt = SYSDATE;
5 occupancy_rec occupancy_cur%ROWTYPE;
7 OPEN occupancy_cur;
9 FETCH occupancy_cur
INTO occupancy_rec;
10 EXIT WHEN occupancy_cur%NOTFOUND;
11 update_bill
12 (occupancy_rec.pet_id,
14 CLOSE occupancy_cur;
15 END;
This code leaves nothing to the imagination. In addition
to defining the cursor (line 2), you must explicitly
declare the record for the cursor (line 5), open the cursor
(line 7), start up an infinite loop, fetch a row from the
cursor set into the record (line 9), check for an end-ofdata
condition with the cursor attribute (line 10), and
finally perform the update. When you are all done, you
have to remember to close the cursor (line 14).
If I convert this PL/SQL block to use a cursor FOR loop,
then I all I have is:
CURSOR occupancy_cur IS
SELECT pet_id, room_number
FROM occupancy WHERE occupied_dt =
FOR occupancy_rec IN occupancy_cur
update_bill (occupancy_rec.pet_id,
Here you see the beautiful simplicity of the cursor FOR
loop! Gone is the declaration of the record. Gone are the
OPEN, FETCH, and CLOSE statements. Gone is need to
check the %FOUND attribute. Gone are the worries of
getting everything right. Instead, you say to PL/SQL, in
effect:: ÒYou and I both know that I want each row and I
want to dump that row into a record that matches the
cursor. Take care of that for me, will you?" And PL/SQL
does take care of it, just the way any modern
programming language integrated with SQL should.
13. Don’t declare your FOR loop index.
Whether you use a numeric or cursor FOR loop, the
loop index is declared for you and implicitly by
PL/SQL. If you do declare a variable with the same
name as the loop index, that will be a different variable.
It will not be used by the loop and will likely introduce
bugs into your program. Consider, for example, the
anonymous block below. Even if I am the only person
stored in the emp table, I will never get a raise!
CURSOR emp_cur IS
SELECT empno, ename FROM emp;
emp_rec emp_cur%ROWTYPE;
FOR emp_rec IN emp_cur
display_emp (emp_rec.ename);
IF emp_rec.ename = ‘FEUERSTEIN’
(emp_rec.empno, 1000000);
14. Avoid unstructured exits from loops.
You should follow these guidelines for terminating a
¥ Let a FOR loop complete its specified number of
iterations. Do not use an EXIT statement to leave
¥ Always make sure you include an EXIT statement
within a simple loop.
¥ Never use EXIT to leave a WHILE loop.
¥ Do not issue RETURNs directly from within a loop.
Modular Code
15. Construct abstract data types with PL/SQL
The term "abstract data type" is about as dry and technicalsounding
as you can get. Yet the concept of an abstract
data type, or ADT, is something we apply -- or should
apply -- in every single one of our application efforts,
sometimes without even realizing that we are doing it.
An abstract data type is a collection of information and
operations which act on that information. When you
create an ADT, you work with objects as opposed to
variables, columns, and other computer-science items.
You perform an abstraction from the implementation
details to the "thing in itself" and work on a higher level.
In PL/SQL, this is best done with a package.
To give you a feel for the ADT, consider the following
"before and after" examples of a thermometer
implemented in Oracle Forms. The "before" shows how
to manage the thermometer as a sequence of separate
statements. The "after" shows the interface provided by
the progress package.
1. Set the thermometer to 20% completion.
:B_PROGRESS.PERCENT_DONE := '20 % Complete.';
SHOW_VIEW ('cv_progress');
After: (20, 2);
2. Hide the progress box when the program completed
and control was returned back to the user.
HIDE_VIEW ('cv_progress');
By treating the progress box as an object with rules
governing its use and appearance, I can reduce greatly
the volume of code required. The resulting statements
are also easier to understand and maintain.
Here are some guidelines you should follow when
designing an ADT:
1. Maintain a consistent level of abstraction. This is
probably the most important aspect of your ADT
implementation. All the modules you build to
represent your abstract data structure should operate
with the same level of data.
2. Provide a comprehensive interface to the abstract data
type. Make sure that the user (a programmer, in this
case) can perform all necessary operations on the
ADT without having to go around the interface you
build to the ADT. Hide all the implementational
details of your ADT behind calls to procedures and
modules -- without exception.
3. Use the package structure, the most natural repository
for ADT code. The ADT represents a thing by
presenting a layer of code which allows you to perform
operations on that thing as a whole, rather than its
individual components. The package joins related
objects together and so corresponds closely to the ADT.
The package clearly distinguishes between the public
and private parts of the code. The public objects make
up the interface to the ADT. The private objects contain
and hide the implementational details for the ADT.
16. Enhance scope control with nested blocks.
PROCEDURE delete_details
I can in this way use my nested blocks to allow my
PL/SQL program to continue past exceptions.
17. Overload modules to make your software smarter.
Within a package and within the declaration section of a
PL/SQL block, you can define more than module with
the same name! The name is, in other words,
overloaded. In the following example, I have overloaded
the value_ok function in the body of my check package,
which is used to validate or check values used in my
/* First version takes a DATE parameter. */
FUNCTION value_ok (date_in IN DATE)
RETURN date_in <= SYSDATE;
/* Second version takes a NUMBER parameter. */
FUNCTION value_ok (number_in IN NUMBER)
RETURN number_in > 0;
Now I can put both versions of value_ok to work in my
code as follows:
IF check.value_ok (hiredate) AND
check.value_ok (salary)
I have found overloading to be extremely useful when I
am building a layer of code which will be used by other
developers (my PL/SQL toolbox). I use module
overloading to hide complexities of the programmatic
interface from my users (other programmers). Instead of
having to know the six different names of procedures
used to, for example, display various kinds of data, a
developer can rely on a single module name. In this
fashion, overloading transfers the burden of knowledge
from the developer to the software.
18. Use local modules to reduce code volume and
improve readability.
A local module is a procedure or function which is
defined in the declaration section of a PL/SQL block
(anonymous or named). This module is considered local
because it is only defined within the parent PL/SQL
block. It cannot be called by any other PL/SQL blocks
defined outside of that enclosing block.
There are two key reasons to create local modules:
¥ Reduce the size of the module by stripping it of
repetitive code. This is the most common motivation to
create a local module. The code reduction leads to
higher-quality code since you have fewer lines to test
and fewer potential bugs. It takes less effort to maintain
the code, since there is simply less to maintain. When
you do have to make a change, you make it in one
place in the local module and the effects are felt
immediately throughout the parent module.
¥ Improve the readability of your code. Even if you do
not repeat sections of code within a module, you still
may want to pull out a set of related statements and
package them into a local module to make it easier to
follow the logic of the main body of the parent module.
Consider the following example, in which I calculate the
net present value for various categories and then format
the result for display purposes.
PROCEDURE display_values
(proj_sales_in IN NUMBER, year_in IN INTEGER)
('Total Sales: ' ||
TO_CHAR((npv ('total_sales', year_in) /
proj_sales_in * 100),'999.99'));
('Employee Labor: ' ||
TO_CHAR((npv ('labor_costs', year_in) /
proj_sales_in * 100),'999.99'));
In this approach, I have exposed the way I perform the
calculation and formatting. As a result, whenever a
change is required (different display format, different
formula, etc.) I must upgrade each distinct calculation. If,
on the other hand, I hide the calculation behind the
interface of a callable module, then the calculation is
coded only once.With the help of a local module, the
display_values procedure is transformed as shown below.
PROCEDURE display_values
(proj_sales_in IN NUMBER, year_in IN INTEGER)
/*------------------- Local Module -----------------*/
PROCEDURE display_npv (column_in IN VARCHAR2)
(INITCAP (REPLACE (column_in, '_', ' '))
|| ': ' ||
TO_CHAR((npv (column_in, year_in) /
* 100), '999.99'));
display_npv ('total_cost');
display_npv ('employee_labor');
I have found that few developers are aware of the
ability to create local modules. I have also found that
modules-within-a-module play an important role in
allowing me to write well-structured, even elegant
programs. Take a look at any of your more complex
programs and I guarantee you will quickly identify
segments of the code which would serve you better
bundled into a local module.
19. Code a single RETURN for successful function
The sole purpose of a function should be to return a
single value (this could be a PL/SQL table or other
composite structure, of course). To reinforce this singlemindedness,
you should code your function so that it
has only one RETURN statement. This RETURN should
also be the last statement in the function. I use the
following template to achieve this effect:
FUNCTION fname () RETURN datatype
return_value datatype;
RETURN return_value;
END fname;
20. Use Self-identifying Parameters
(Avoid Boolean Values)
A common parameter for a module is a flag which relies
on two-valued logic (TRUE or FALSE) to pass
information to the module. The temptation in such a
case is to declare the parameter to be type Boolean (or to
simulate a Boolean with a VARCHAR2 ’Y’ or ’N’ value).
With this approach, when you call the program, you
will pass either TRUE or FALSE. You will find in these
circumstances that while the specification for your
program is very readable, the way you call it in other
programs will be difficult to understand.
Consider the parameters for the following procedure,
which is used to generate reports. It offers flags to
control report printing and file clean-up.
PROCEDURE gen_report
(report_id_in IN NUMBER,
clean_up_in IN BOOLEAN,
print_in IN VARCHAR2);
In order to make gen_report more flexible, the
developer has provided two flag parameters:
clean_up_in TRUE if the procedure should clean up
log files, FALSE to keep the files in place,
usually for purposes of debugging.
print_in Pass ’Y’ if the output file of the report
should be printed, ’N’ to skip the print step.
When one glances over the procedure’s specification, the
purpose and usage of each parameter seems clear enough.
But take a look at how I would call this procedure:
gen_report (report_id, TRUE, 'Y');
As you can see, these arguments are not very
descriptive. Without the context provided by their
names, actual Boolean parameters cannot "selfdocument"
their effect. A maintainer of the code must
go back to the source to understand the impact of a
particular value. That defeats completely the
information hiding principle of modular programming.
A much better approach replaces Boolean and pseudo-
Boolean parameters with character parameters whose
acceptable values are descriptions of the action or
situation. With this change, a call to gen_report states
clearly its intentions:
gen_report (report_id, 'CLEANUP', 'PRINT');
gen_report (report_id, 'NO_CLEANUP', 'PRINT');
As you can see from these examples, I write my
procedures to accept an unambiguous affirmative value
("CLEANUP") and the most clearly-defined negative form
("NO_CLEANUP"). One complication to this style is that
you need to validate the parameter values; if you were
using a Boolean, the strong datatyping in PL/SQL would
guarantee that a legal value is passed. An even better
solution than either of these approaches is to use constants
which are stored in a package. In this way, you avoid
hard-coding values, get compile-time verification of
correctness and make your code more readable.
The package might look like this:
c_print CONSTANT VARCHAR2(5) := ‘print’;
c_noprint CONSTANT VARCHAR2(7) := ‘noprint’;
c_cleanup CONSTANT VARCHAR2(5) := ‘cleanup’;
c_nocleanup CONSTANT VARCHAR2(7) :=
END vals;
And the calls to gen_report would now look like this:
gen_report (report_id, vals.c_clean, vals.c_print);
This paper should provide you with lots of ideas on
how to improve your code.