Analytics 2.0 - Part II

Yet another neat addition to the family of Analytics in 11g Release 2.
With this new function , I can look up the value of nth row .

The following gives the 4th salary from the dept 30 ( look at the column nv) .

1 SELECT empno,
2 ename,
3 deptno,
4 sal,
5 first_value(sal) over(PARTITION BY deptno ORDER BY empno) fv,
6 nth_value((sal), 4) over(PARTITION BY deptno ORDER BY empno) nv,
7 last_value(sal) over(PARTITION BY deptno ORDER BY empno
8 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv
9 FROM emp
10* WHERE deptno = 30


SQL> /


EMPNO      ENAME      DEPTNO     SAL        FV        NV          LV
---------- ---------- ---------- ---------- ---------- ---------- ----------
7499       ALLEN       30        1600        1600                  950
7521       WARD        30        1250        1600                  950
7654       MARTIN      30        1250        1600                  950
7698       BLAKE       30        2850        1600        2850      950
7844       TURNER      30        1500        1600        2850      950
7900       JAMES       30         950        1600        2850      950


6 rows selected.

Good luck ...

Size DOES Matter

We should rightly size the column definition . I have seen in various implemantations ,
defining right size , right data type  has taken back seat .

The wrong size would impact data integrity  , memory usage , network traffic and
runtime performance.

Let us take a look at runtime performance .

I am a creating two tables from the same source . The only difference is that the second
column varies by size .


SQL> desc t_normal
Name        Type         Nullable Default Comments
----------- ------------ -------- ------- --------
OBJECT_ID   NUMBER                                
OBJECT_NAME VARCHAR2(30)                          

SQL> desc t_4000
Name        Type           Nullable Default Comments
----------- -------------- -------- ------- --------
OBJECT_ID   NUMBER                                  
OBJECT_NAME VARCHAR2(4000)                          

SQL> truncate table t_normal;

Table truncated

SQL> truncate table t_4000;

Table truncated

SQL> insert /*+ APPEND */ into t_normal select object_id , object_name from all_objects
  2  ;

629424 rows inserted

SQL> insert /*+ APPEND */ into t_4000 select object_id , object_name from all_objects;

629424 rows inserted

SQL> commit;

Commit complete

Let us set the arraysize to 100 and time it .


SQL> set arraysize 100
SQL> set timing on

SQL> exec dbms_stats.gather_table_stats(user , 'T_NORMAL');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09

SQL> exec dbms_stats.gather_table_stats(user , 'T_4000');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10

SQL>select object_id , object_name from t_normal;
......
......
.....


629424 rows selected.

Elapsed: 00:02:00.78

SQL>select object_id , object_name from t_4000;
.....
.....
....


629424 rows selected.

Elapsed: 00:15:03.40

Look at the run time . There is a huge difference between the first run and the second run.

Honey, I shrunk the table!

Let us say , you  have a table where there are frequent deletes / inserts  and your application reads this table ( via full table scan )  for processing the business results . You are  likely  to have performance issue , as Oracle has to read all the empty blocks till HWM .  In 10g and above , you can reset the HWM as below .

SQL> drop table t ;
Table dropped
SQL> create table t as select * from all_objects;
Table created
SQL> select sum(bytes) from user_segments where segment_name = 'T';
SUM(BYTES)
----------
99614720


SQL> select count(*) from t;
COUNT(*)
----------
629079


SQL> delete from t where owner ='SYS';
23157 rows deleted

SQL> select count(*) from t;
COUNT(*)
----------
605922


SQL> select sum(bytes) from user_segments where segment_name = 'T';
SUM(BYTES)
----------
99614720



Even after deleting few records , the space is not released.
But , when you shrink the table as below , you can reclaim the space.

 

SQL> alter table t ENABLE row movement ;
Table altered
SQL> alter table t shrink space compact;
Table altered
SQL> alter table t shrink space;
Table altered

SQL> select sum(bytes) from user_segments where segment_name = 'T';
SUM(BYTES)
----------
88932352

If you are not sure of which objects can be the candiates for shrinking , you can refer to the findings from segment advisor . Either you can use segment advisor from EM or PL /SQL . You can get the relevant sql commands as shown below .


Select * from

(
select c3 from table (dbms_space.asa_recommendations())
union ALL
select c2 from table (dbms_space.asa_recommendations())
union all
select c1 from table (dbms_space.asa_recommendations())
)
where c3 is not null

dbms_space.asa_recommendations()  provides the estimated space savings . I have asked the accuracy of this value to Tom Kyte in his forum . Please see his response at
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2049277600346543592#2049993400346447818

Please send in your comments.

Native Compilation

11g Release 1 and above introduced  the real "native" compilation for PL/SQL  objects . Prior to this release ,  when we compile the objects in "native" format  , it was converted into "c" code . Now additional layer is gone .

By default , the objects are in INTERPRETED format . This is good for most of the applications .
But , if  your PL/ SQL objects are arithmetic intensive , native compilation may give better performance .

This native compilation applies to PL/SQL only ... does not have any impact on SQL.

In my test , the performance was 12% better than it was in INTERPRETED format.

( your mileage may vary )


I tried this exercise in Windows version of 11g R1 . Though  I compiled in native format  , not all PL/SQL were converted into NATIVE . Some package bodies / functions were still in the INTERPRETED format.

In RHEL , it worked perfectly fine .

Here are the steps .


SQL> alter system set plsql_code_type=native;

System altered.

SQL> alter system set plsql_optimize_level=3;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

You will be need to start the instance in upgrade mode.

SQL> startup upgrade;

....

SQL> @$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql
.....
.....

Upon completion of the above script , you will see the following message .

DOC>#######################################################################
DOC>   dbmsupgnv.sql completed successfully. All PL/SQL procedures,
DOC>   functions, type bodies, triggers, and type bodies objects in the
DOC>   database have been invalidated and their settings set to native.
DOC>
DOC>   Shut down and restart the database in normal mode and
DOC>   run utlrp.sql to recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 3240239104 bytes
Fixed Size                  2148760 bytes
Variable Size            1577059944 bytes
Database Buffers         1644167168 bytes
Redo Buffers               16863232 bytes
Database mounted.
Database opened.
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

.....

Upon completion of the above script , you will see the following message .

SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> SET serveroutput on
SQL> EXECUTE dbms_registry_sys.validate_components;
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error

PL/SQL procedure successfully completed.

SQL> SET serveroutput off.

SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>

You can check the compilation mode for all the objects in the database using the following sql . 

  1     SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*)
  2     FROM DBA_PLSQL_OBJECT_SETTINGS
  3     WHERE PLSQL_CODE_TYPE IS NOT NULL
  4     GROUP BY TYPE, PLSQL_CODE_TYPE
  5*    ORDER BY TYPE, PLSQL_CODE_TYPE
SQL> /

FUNCTION        NATIVE                 386
PACKAGE         NATIVE                1288
PACKAGE BODY    NATIVE                1225
PROCEDURE       NATIVE                 655
TRIGGER         NATIVE                 488
TYPE            INTERPRETED           2288
TYPE            NATIVE                 321
TYPE BODY       NATIVE                 224

8 rows selected.

Bear in mind , TYPES will not in NATIVE mode , as it does not have a executable code.

Good luck.

Analytics 2.0

Jumping ahead from basic analytics to analytics 2.0 ( new feature in 11g R2) 

One simple yet  neat , nicer addition to analytics family in 11g R2.
There is a new analytic function called LISTAGG  , where I could concatenate the list of the data values in a
particular group .

Prior to this release , we would have used hierarchical  query ... SYS_PATH to a achieve this result set.


Let us proceed with a simple example


SQL> Create table  t as select * from all_objects;

Table created

SQL>
SQL>         Select owner , object_type , listagg(object_name , '~')
  2           within group
  3           (order by object_name )
  4           from t
  5           where owner in ('SCOTT' , 'OUTLN')
  6           group by owner , object_type
  7           order by object_type
  8  ;

OWNER                          OBJECT_TYPE         LISTAGG(OBJECT_NAME,'~')WITHIN
------------------------------ ------------------- --------------------------------------------------------------------------------
OUTLN                          INDEX               OL$HNT_NUM~OL$NAME~OL$NODE_OL_NAME~OL$SIGNATURE
SCOTT                          INDEX               PK_DEPT~PK_EMP
OUTLN                          PROCEDURE           ORA$GRANT_SYS_SELECT
OUTLN                          TABLE               OL$~OL$HINTS~OL$NODES
SCOTT                          TABLE               BONUS~DEPT~EMP~SALGRADE


Note : ~ is the delimiter

Bear in mind , the result set is of datatype varchar2( unless otherwise the columns defined in the group are RAW) , so you may be hit the limitation of varchar2(4000).

SQL>        Select owner , object_type , listagg(object_name , '~')
  2           within group
  3           (order by object_name )
  4           from t
  5           where owner in ('SCOTT' , 'SYS')
  6           group by owner , object_type
  7           order by object_type
  8  ;

Select owner , object_type , listagg(object_name , '~')
         within group
         (order by object_name )
         from t
         where owner in ('SCOTT' , 'SYS')
         group by owner , object_type
         order by object_type

ORA-01489: result of string concatenation is too long

Have a good time with analytcs.
Please refer to http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm for additional informaiton .

Database developments DON'Ts

This is my first blog.
Based on your feedback , I will be updating this blog , post new blogs .Thanks for looking into this .I started my career as a PowerBuilder / Sybase or Oracle Developer ; later on transitioned into DB Administration .
Now, I have seen in my places, where the programmers were given training in / asked to learn in Java / .NET technologies but they forget to get trained on database fundamentals.They treat the database as data storage. If they read through the basics of db development , I am sure it will benefit everybody.
There are few things a developer can do. Though the examples given below for Oracle. It is equally applicable for SQL Server, DB2, Sybase, so on.

Avoid Literals. Use Bind variables in an OLTP system
By using bind variables
1) shared pool is efficiently used .
2) SQL injection can be avoided .
Let us say , if you issue the following statements , you will see two versions of theSQL in v$sql . Every statement has to hard parsed , ….

SELECT object_name into obj_name from t where object_name = 'T';
SELECT object_name into obj_name from t where object_name = 'ICOL$';

Every statement has to hard parsed , ….Some shops overcome this by setting the variable CURSOR_SHARING to either SIMILAR or FORCE . But this has to be short term fix.
This topic could alone run into a separate volume.


Use UNION ALL , instead of UNION if applicable
If your logic could support distinct values or if you are sure your query retrieves distinct values , use UNION ALL.

When you use UNION ALL , you can cut down on the sorting step .


Explain Plan for
Select object_name
from t
Where object_type = 'TABLE'
union
Select object_name
from t
Where object_type = 'PROCEDURE';

----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------
0 SELECT STATEMENT 3559 99652 321 (51) 00:00:04
1 SORT UNIQUE 3559 99652 321 (51) 00:00:04
2 UNION-ALL
* 3 TABLE ACCESS FULL T 3491 97748 159 (1) 00:00:02
* 4 TABLE ACCESS FULL T 68 1904 159 (1) 00:00:02
----------------------------------------------------------------------------


Explain Plan for
Select object_name
from t
Where object_type = 'TABLE'
union all
Select object_name
from t
Where object_type = 'PROCEDURE';

Plan hash value: 1078996545
---------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------
0 SELECT STATEMENT 3559 99652 319 (51) 00:00:04
1 UNION-ALL
* 2 TABLE ACCESS FULL T 3491 97748 159 (1) 00:00:02
* 3 TABLE ACCESS FULL T 68 1904 159 (1) 00:00:02
---------------------------------------------------------------------------


Avoid DISTINCT , if you can.
Again If your logic could avoid DISTINCT , please do so.
I have seen in my work assignment , some of the developers worked on the "side effects of DISTINCT" . Prior to 10g Release 2 , Oracle was doing SORT JOIN , so the results were ordered . Not any more ... Starting from Oracle 10g Release 2 , Oracle does HASH JOIN ( avoiding sort is good for performance reason ) . If you want the results to be sorted , use the ORDER BY clause . Otherwise , there is no gurantee , your results would be ordered.

Explain Plan for
Select object_name from t;
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 50433 837K 159 (1) 00:00:02
1 TABLE ACCESS FULL T 50433 837K 159 (1) 00:00:02
--------------------------------------------------------------------------
Explain Plan for
Select distinct object_name from t;
-----------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
-----------------------------------------------------------------------------------
0 SELECT STATEMENT 50433 837K 443 (2) 00:00:06
1 HASH UNIQUE 50433 837K 2392K 443 (2) 00:00:06
2 TABLE ACCESS FULL T 50433 837K 159 (1) 00:00:02
l-----------------------------------------------------------------------------------


Avoid ORDER BY , if you can.

If you could avoid ORDER BY , please do so . Quick execution plan will show the additional overhead in ordering

Explain Plan for
Select object_name from t
order by object_name;
-----------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
-----------------------------------------------------------------------------------
0 SELECT STATEMENT 50433 837K 443 (2) 00:00:06
1 SORT ORDER BY 50433 837K 2392K 443 (2) 00:00:06
2 TABLE ACCESS FULL T 50433 837K 159 (1) 00:00:02
-----------------------------------------------------------------------------------