R Dataframe in the eyes of SQL developer.

R has gained lot of momentum in the last few years  for Data Science. At first , for a SQL professional , this may be bit daunting ; however there are lot of similarities  between the RDBMS concepts and R concepts , that will make the learning curve tad easier. One of the similarity is the data frame. 


Data frame is one of the important component in R to capture the data from the external data sources ( aka importing from CSV , loading from RDMS  , and so on ) . 

It is conceptually same as the a table in a RDBMS system.

In the following , I have created a data frame with  3 elements and 6 rows. 
In RDBMS , this is the same as creating a table 'emp' and inserting 6 records. 

emp <- data.frame="" span="">
  name=c("Zahir","Farook","Hameed","Basheer","Aslam","Suhaib"),
  deptno=c(10,20,30,30,20,20),
 city=c("Monroe","Trichy","Kilakarai","Kilakarai","Chennai","Chennai"))  
















When  the data frame is referenced at the prompt , it returns the entire data set. This is similar to "SELECT * FROM EMP",




The function "rbind" is used to insert a record into the existing dataset. 
This is similar to "INSERT INTO EMP values ('Karady' , 100 , 'Colombo') "

emp <- arady="" data.frame="" deptno="c(100),city=c(" emp="" name="c(" olombo="" rbind="" span="">

 

The function "nrow" is used to get the record count of the dataset.
This is similar to " SELECT count(*) from EMP". 


The function "ncol" is used to get the record count of the columns.
This is similar to " SELECT count(*) from information_schema.columns where table_name =EMP'" .


 With the following example , we are filtering the records that have deptno = 30 . This is similar to
"SELECT *  FROM EMP WHERE DEPTNO= 30'.



 We can add , additional filter with the pipe function . Pipe is used for 'OR' condition. 
This is similar to "SELECT *  FROM EMP WHERE DEPTNO= 30 ORCITY ='Chennai'.





As we can see , there  are lot of similarites in with the concept of Table (tuple) and the dataframe. 
This could be a starting point to get familiar with R  for a SQL professional . 

I understand , I have just scratched the surface on the data frame and its functions.

As of now , Oracle and MS SQL Server has incoprated 'R' into their offerings.

Comments Welcome.

RMAN Enhancements in 12c - Part 1

To execute a SQL statement in RMAN prompt , we would need to prefix  "sql" in  Oracle 11g and below . In Oracle 12c , we can issue sqls directly from RMAN prompt.   This enhancement is helpful in scripting RMAN backup jobs.

Here are the examples.

C:\>rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Oct 20 11:13:56 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST12DB (DBID=367699687)

RMAN> create user backupuser identified by bkup1user ;

using target database control file instead of recovery catalog
Statement processed

RMAN> alter user backupuser default tablespace users;

Statement processed

RMAN> alter user backupuser quota unlimited on users;

Statement processed


RMAN> select banner from v$version ;

using target database control file instead of recovery catalog
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

Color / Colour the SQL

We use AWR and ASH to troubleshoot performance ( provided we licensed diagostic pack option )  .
Typically , only the top consumers ( SQLs ) is displayed in AWR. 

You can color / colour the SQL to  see the execution plans and other metrics of a SQL , 
even if the SQL does not qualify as top consumer ( if the SQL is in the cache at the snapshot time) . 
The utility is ADD_COLORED_SQL  in DBMS_WORKLOAD_REPOSITORY.  

As a side note , I found a documentation bug in oracle 11g documentation . It was wrongly documented as DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML , instead of ADD_COLORED_SQL.  But this is corrected in Oracle 12c documentation. 

  
Here is an example. 

I am using "ZAHIR_COLOR_SQL" as comment just for clarity . You need not comment to get the SQL id. 


Session 1 :
---------

SQL> show user
USER is "SCOTT"
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> Select /* ZAHIR_COLOR_SQL */ empno , ename from  emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.


Go to another session and determine the SQL ID of this SQL and color it. 

SQL> select sql_id , sql_text  from v$sql where sql_text like '%ZAHIR_COLOR%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
dfjudnqgk5c11
select sql_id , sql_text  from v$sql where sql_text like '%ZAHIR_COLOR%'

drfh6qd1ctrb7
Select /* ZAHIR_COLOR_SQL */ empno , ename from  emp


SQL> exec DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(sql_id  => 'drfh6qd1ctrb7');

PL/SQL procedure successfully completed.


SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.


We can look at the execution plan using dbms_xplan or via AWR from Enterprise Manager or AWR  by executing awrrpt.sql . 

Here is an example of getting the execution plan from AWR as shown below. 


SQL> select * from table(dbms_xplan.display_awr('drfh6qd1ctrb7'))
  2  ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID drfh6qd1ctrb7
--------------------
Select /* ZAHIR_COLOR_SQL */ empno , ename from  emp

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


13 rows selected.



Comments Welcome.