venerdì 7 marzo 2014

Kill session in Sap Hana

In Oracle an easy trick to kill a session is to run the following query:

 SELECT 'exec sys.kill_session(' || a.sid || ',' || a.serial# || ');'  
    ,osuser  
    ,terminal  
    ,machine  
    ,program  
    ,a.*  
  FROM v$session a  
 WHERE status = 'ACTIVE' AND username = USER;  

Of course you have to be careful on the way you use it... You should be sure about what is running in the correspondent session.
You can use for example the tool offered by SQL Developer (you need to have the rights on the table v$session).




What about SAP HANA? The basic command you need is:

   ALTER SYSTEM CANCEL SESSION 'conn_id';  



Where conn_id is in the table M_CONNECTIONS, column CONNECTION_ID.
You can retrieve the complete command with the following query:

 SELECT   
    'alter system cancel session ''' || C.CONNECTION_ID || ''';' command,   
    C.CONNECTION_ID,   
    PS.STATEMENT_STRING  
 FROM M_CONNECTIONS C JOIN M_PREPARED_STATEMENTS PS  
     ON C.CONNECTION_ID = PS.CONNECTION_ID   
     AND C.CURRENT_STATEMENT_ID = PS.STATEMENT_ID  
 WHERE C.CONNECTION_STATUS = 'RUNNING'   
  AND C.CONNECTION_TYPE = 'Remote';  
    


giovedì 6 marzo 2014

Execution plan in SAP HANA

Do you have a slow query in Hana?
Do you want to improve the performances of a query?

First step is to retrieve the execution plan:


 DELETE FROM explain_plan_table WHERE statement_name = 'marco test nr 1';  
 DELETE FROM explain_plan_table WHERE statement_name = 'marco test nr 1';  
 EXPLAIN PLAN SET STATEMENT_NAME = 'marco test nr 1' FOR  
 select * from dummy; --put here your query!!!  
   
   
   
 SELECT 
   table_size,   
   to_number(output_size, 10, 2) as output_size,   
   to_number(subtree_cost, 10, 2) cost,   
   operator_name,   
   operator_details,   
   table_name  
 FROM explain_plan_table  
 WHERE statement_name = 'marco test nr 1'  
 order by operator_id;    


The output of the last select will look like this in your SAP HANA STUDIO:


In order to read the execution plan, I believe that the most important column is the "Operator Name". 
This is the column telling you how the DBMS is approaching the problem. 
Here you will see if it is doing something stupid. 
The question is: what is the meaning of the content of this column? Here is the set of possible values: 



Operator NameDescription
ROW SEARCHStarting position of row engine operators. OPERATOR_DETAILS lists projected columns.
LIMITOperator for limiting number of output rows
ORDER BYOperator for sorting output rows
HAVINGOperator for filtering with predicates on top of grouping and aggregation
GROUP BYOperator for grouping and aggregation
MERGE AGGREGATIONOperator for merging the results of multiple parallel grouping and aggregations
DISTINCTOperator for duplicate elimination
FILTEROperator for filtering with predicates
UNION ALLOperator for producing union-all of input relations
MATERIALIZED UNION ALLOperator for producing union-all of input relations with intermediate result materialization
BTREE INDEX JOINOperator for joining input relations through B-tree index searches. Join type suffix can be added. For example, B-tree index join for left outer join is shown as BTREE INDEX JOIN (LEFT OUTER). Join without join type suffix means inner join.
CPBTREE INDEX JOINOperator for joining input relations through CPB-tree index searches. Join type suffix can be added.
HASH JOINOperator for joining input relations through probing hash table built on the fly. Join type suffix can be added.
NESTED LOOP JOINOperator for joining input relations through nested looping. Join type suffix can be added.
MIXED INVERTED INDEX JOINOperator for joining an input relation of row store format with a column table without format conversion using an inverted index of the column table. Join type suffix can be added.
BTREE INDEX SEARCHTable access through B-tree index search
CPBTREE INDEX SEARCHTable access through CPB-tree index search
TABLE SCANTable access through scanning
AGGR TABLEOperator for aggregating base table directly
MONITOR SEARCHMonitoring view access through search
MONITOR SCANMonitoring view access through scanning


Source of information: 
https://help.sap.com/hana/html/sql_explain_plan.html


mercoledì 5 marzo 2014

Oracle: easy access the file system from PLSQL


How to easily access the file system from PLSQL ?
The goal is to perform an simple command like:
- DOS: dir
- LINUX: ls

and get the result as select.

Very good tool is offered by:
http://plsqlexecoscomm.sourceforge.net/


You need to download the PLSQL package from the web site above, install the PLSQL script and then perform the simple select:

 select *   
 from table(file_pkg.get_file_list(file_pkg.get_file('/')))  

martedì 4 marzo 2014

Oracle: Measure disk space used


You need to know how much space your schema is using:

 -- estimated bytes per table used:  
 select   
   table_name,   
   avg_row_len*num_rows/1024/1024 size_mbytes   
 from user_tables   
 order by 2 desc; 
 
 --top table-segments for bytes used:  
 select   
      owner,   
      segment_name,   
      partition_name,   
      segment_type,   
      round(bytes/1024/1024) size_MB   
 from dba_segments   
 where owner like '%MY_FAVOURITE_NAME%'  
 order by bytes desc;  

 --top table-segments for bytes used: (per Segment_name)  
 select   
      owner,   
      segment_name,   
      sum(size_MB) size_MB   
 from (  
  select   
       owner,   
       segment_name,   
       partition_name,   
       segment_type,   
       round(bytes/1024/1024) size_MB   
  from   
       dba_segments   
 )  
 where owner like '%MY_FAVOURITE_NAME%'  
 group by owner, segment_name  
 order by size_MB desc;

 -- top owner for bytes used:  
 select   
      owner,   
      round(sum(bytes/1024/1024)) size_MB   
 from dba_segments   
 where owner like '%MY_FAVOURITE_NAME%'  
 group by owner   
 order by 2 desc;   

-- disk free  
 select   
      tablespace_name,   
      round(sum(bytes)/(1024*1024*1024),0) GB_free   
 from dba_free_space   
 group by tablespace_name   
 order by 2 desc;  

lunedì 3 marzo 2014

Responsive HTML5 menu

I need a menu that is adaptative, so much that if I open it from a laptop that it is already exploded but if I open it from a mobile device, then it needs to be clicked and exploded.

Such tool I used on my web site. If I see my resume section:
http://marcoandolfi.eu5.org/cv.html

you have this view from your laptop:

Instead if you open it from your iPhone you should have a view like this:

First view:



When Opened:



Ok, nice! How? 

First of all you need a JS which is the main engine 
(on my website is a separate file "dropdown-menu.js"): 
   
 if(window.addEventListener){   
      window.addEventListener("load", doLoad, false);  
 }else if(window.attachEvent){   
      window.attachEvent("onload", doLoad);  
 }else if(window.onLoad){  
   window.onload = doLoad;  
 }  
   
function doLoad(){  
        var nav = document.getElementsByTagName("NAV")[0];  
        var childs = nav.childNodes;  
        for(var i = 0; i < childs.length; i++){  
        var child = childs[i];  
        if(child.tagName === "H2"){  
          child.onclick = function(){toogleMenu(nav)};  
          child.onfocus = function(){toogleMenu(nav)};  
          break;  
        }  
}  
   
      var elClass = nav.className;  
      var arrClasses = elClass.split(" ");  
      for(var i = 0; i < arrClasses.length; i++){  
         if(arrClasses[i] === "expanded") arrClasses.splice(i, 1);  
      }  
      nav.className = arrClasses.join(" ");  
      nav.className += " non-expanded";  
      return false;  
 };  
   
 function toogleMenu(el){  
      var elClass = el.className;  
      var arrClasses = elClass.split(" ");  
      for(var i = 0; i < arrClasses.length; i++){  
        if(arrClasses[i] === "expanded"){  
          arrClasses.splice(i, 1);  
          arrClasses[length + 1] = "non-expanded";  
        }  else if(arrClasses[i] === "non-expanded"){  
          arrClasses.splice(i, 1);  
          arrClasses[length + 1] = "expanded";  
        }  
      }  
      el.className = arrClasses.join(" ");  
      return false;  
 }  




Then a bit of CSS 
   
 nav h2, aside h2{  
      position: absolute;  
      left: -999em;  
 }  
   
 nav {  
      clear: left;  
      margin: 0 0 2em;  
 }  
   
 nav ol{  
      border: 1px solid #e6e6e6;  
      padding-left: 0;  
 }  
   
 nav li {  
      border-bottom: 1px solid #eee;  
      line-height: 2em;  
      list-style: none;  
      margin-left: 0;  
 }  
   
 nav li:last-child {  
   border-bottom: 0;  
 }  
   
 nav a {  
      color: #999;  
      display: block;  
      padding: .5em .8em;  
      text-decoration: none;  
 }  
   
 nav .current {  
      font-weight: bold;  
 }  
   
 .dropdown-header {  
      font-size: 14px;  
      font-weight: bold;  
      color: #000000;  
 }  
   
 @media screen and (max-width: 37.499em){ /* base: 16px -> [< 600px] */  
   
      nav {  
           margin: 0 0 1.5em;  
           overflow: hidden;  
           position: relative;  
      }  
   
      nav h2 {  
           cursor: pointer;  
           font-size: 0.5em;  
           height: 4.2em;   
           left: auto;  
           margin: 0;  
           right: 0;  
           position: absolute;  
           text-indent: -999em;  
           top: 0;  
           width: 4em;  
      }  
   
      nav.non-expanded h2 {  
           background: #ccc url(../img/check_icon.png) no-repeat -35px 45%;  
      }  
   
      nav.expanded h2 {  
           background: #ccc url(../img/check_icon.png) no-repeat 0px 45%;  
      }  
   
      nav h2{  
           background: none;  
      }  
   
      nav a {  
           padding-right: 3em;  
      }  
   
      nav li {  
           clear: left;  
           line-height: 1em;  
      }  
   
      nav.non-expanded li{  
           display: none;  
      }  
   
      nav.expanded li{  
           display: block;  
      }  
   
      nav li.current {  
           border-bottom-width: 0;  
           display: block;  
      }  
   
      nav.expanded li.current {  
           border-bottom-width: 1px;  
      }  
   
 }  
(on my website is a separate file "dropdown-menu.css"): 





and now the HTML:

 <!DOCTYPE html>  
 <html lang="en">  
  <head>  
   [...]  
   <!-- Bootstrap core CSS -->  
   <link href="css/bootstrap.min.css" rel="stylesheet">  
   <!-- CSS for drop down menú adaptive -->  
   <link href="css/dropdown-menu.css" rel="stylesheet">  
   [...]  
  </head>  
  <body>  
    [...]          
       <nav>  
        <h2>Documents</h2>  
         <ol>  
           <li class="dropdown-header"> Professional </li>  
           <li class="selectable" id="CV"><a href="javascript:loadFrame('CV');">Curriculum Vitae</a></li>  
           <li class="selectable" id="ProjectList"><a href="javascript:loadFrame('ProjectList');">Project List</a></li>  
           <li class="selectable" id="Recommendation"><a href="javascript:loadFrame('Recommendation');">Recommendation</a></li>  
           <li class="dropdown-header"> Education </li>  
           <li class="selectable" id="HighSchoolDiploma"><a href="javascript:loadFrame('HighSchoolDiploma');">High School Diploma</a></li>  
           <li class="selectable" id="Bachelor"><a href="javascript:loadFrame('Bachelor');">Bachelor Degree</a></li>  
           <li class="selectable" id="Master"><a href="javascript:loadFrame('Master');">Master Degree</a></li>  
           <li class="selectable" id="Paper"><a href="javascript:loadFrame('Paper');">Scientific publication</a></li>  
           <li class="dropdown-header"> Certification </li>  
           <li class="selectable" id="TUV"><a href="javascript:loadFrame('TUV');">PM Certification</a></li>  
           <li class="selectable" id="Scrum"><a href="javascript:loadFrame('Scrum');">Scrum Master Certification</a></li>  
           <li class="selectable" id="HTML5"><a href="javascript:loadFrame('HTML5');">HTML5 Certification</a></li>  
           <li class="selectable" id="SAPHANA"><a href="javascript:loadFrame('SAPHANA');">SAP HANA Certification</a></li>  
         </ol>  
       </nav>  
   [...]  
   <!-- Bootstrap core JavaScript  
   ================================================== -->  
   <!-- Placed at the end of the document so the pages load faster -->  
   <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>  
   <script src="js/dropdown-menu.js"></script>  
   <script src="js/bootstrap.min.js"></script>  
   [...]  
  </body>  
 </html>  

eh voilá!


Constraint: 
The JS is based on the search of the tag <nav>.
This means: if you have more than one <nav> tag potentially it will not work. You need to have only one <nav>. It is easy to extend to code and change the search of the tag not on the tag type, but on the tag id.

sabato 1 marzo 2014

Clear comunication

"A contract company out here was asked to stencil on the side of a fuel tanker: 'Diesel Fuel' in Arabic and 'No Smoking' in Arabic. This is what came back..."



Source: 
http://www.huffingtonpost.co.uk/2012/02/06/arabic-fuel-tanker-fail_n_1256624.html