Loading...

Follow Josip Pojatina Blog on Feedspot

Continue with Google
Continue with Facebook
or

Valid

Recently I had an interesting case when I’ve been asked how to change the execution plan of one complex query without changing the code.

I’ve already had a lecture with similar title at 2012 in Oracle User Group Conference – more details you can find at the following link:

How to change the execution plan without changing the code

 

(sorry, It’s on Croatian, but should be understandable for a wider audience)

Prerequisite for using that method is Oracle 11g database, as I’m using SQL Plan Baseline feature in that example.

In this case I have Oracle 10g database which was released long time ago (in 2003).

Naturally, someone may ask why you are running production database for which you have to pay extremely expensive Extended Premium Support, instead of just upgrading to the newest Oracle database 19c where you can use thousands of new features and significantly lower down Oracle Support costs (because of Standard Oracle Support), significantly increase database performance (that leads to further cutting down required number of Oracle licenses) and use many of the new HA (High Availability) features, but that is another story.

Another issue is in that it’s not possible to change the problematic SQL code either because your PL/SQL package in constant use (meaning if you try to change the package and compile it, you’ll get a lot of latches – and blocking session) or because code has been generated by the client application (e.g. Oracle Forms or Java or .NET).

Furthermore, there is a dependency change issue – even if I change the problematic package, several other dependent packages will get the invalid state status.

That leads to issue with developing store procedures, especially in cases where you have intensive ESB (e.g. Kafka, Tibco, RabbitMQ, OSB, MQ Series) and Microservice architecture in place, but you haven’t performed exception handling thoroughly to cover all cases.

In case you are running Oracle 11g, you have solution for the first – PL/SQL package compilation case by using Edition Based Redefinition feature of Oracle database.

 

To summarize:

– you are running Oracle 10g in production where the latest release is 19c

– it’s not possible to deploy tuned code in production as the problematic package is in constant use + dependency chain (there are several other packages that depends on problematic one)

 

Thankfully 10g database introduced SQL Profiler feature which should replace well known Outline which is first introduced with Oracle 8i back in 1998.

Here is how you can change the execution plan without touching the code that works even in Oracle 10g.

--assume this is a bad execution plan (index unique scan)
select /*+ test01 */ * from emp e where e.empno = :a;

SQL_ID  6t1432yzy00sv, child number 0
-------------------------------------
select /*+ test01 */ * from emp e where e.empno = :a
 
Plan hash value: 2949544139
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------





--assume this is a good plan (Full Table Scan)
select /*+ test02 full(e) */ * from emp e where e.empno = :a;

SQL_ID  927gwyxf104xy, child number 0
-------------------------------------
select /*+ test02 full(e) */ * from emp e where e.empno = :a
 
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      DB_VERSION('18.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */




--fixing the plan 
declare 
sqltext varchar2(4000);
h SYS.SQLPROF_ATTR;
begin 

sqltext := 'select /*+ test01 */ * from emp e where e.empno = :a';

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('18.1.0')]',
q'[DB_VERSION('18.1.0')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "E"@"SEL$1")]',
q'[END_OUTLINE_DATA]');

DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sqltext,
profile => h,
name => 'custom_profile_test',
description => 'applying custom profile ',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => FALSE );  
end;


--fixed plan 
SQL_ID  6t1432yzy00sv, child number 1
-------------------------------------
select /*+ test01 */ * from emp e where e.empno = :a
 
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Note
-----
   - SQL profile custom_profile_test used for this statement

Note that the latest plan has the same sql_id as troublesome query, but the child number is 1 instead of 0.

Also note that SQL Profile has been used for this statement, which is what I wanted.

I had to tune the problematic plan, and by using the anonymous procedure, all I have to do is to import SQL Profile and to assocoate it with the problematic SQL.

In case you have a more complex SQL, this is the code you can use (example from one of my engagements with Oracle Retail):

DECLARE
  sql_txt CLOB;
  h       SYS.SQLPROF_ATTR;
  PROCEDURE wa (p_line IN VARCHAR2) IS
  BEGIN
    SYS.DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
  END wa;
BEGIN
  SYS.DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
  SYS.DBMS_LOB.OPEN(sql_txt, SYS.DBMS_LOB.LOB_READWRITE);
  -- SQL Text pieces below do not have to be of same length.
  -- So if you edit SQL Text (i.e. removing temporary Hints),
  -- there is no need to edit or re-align unmodified pieces.
  wa(q'[MERGE INTO RPM_PROMO_ITEM_LOC_EXPL_GTT TARGET USING (SELECT PRIC]');
  wa(q'[E_EVENT_ID, TO_ITEM ITEM, TO_DIFF_ID DIFF_ID, TO_ITEM_PARENT ITE]');
  wa(q'[M_PARENT, DEPT, CLASS, SUBCLASS, TO_LOCATION LOCATION, TO_ZONE_N]');
  wa(q'[ODE_TYPE ZONE_NODE_TYPE, TO_ZONE_ID ZONE_ID, PROMO_START_DATE, P]');
  wa(q'[ROMO_END_DATE, PROMO_ID, PROMO_DISPLAY_ID, PROMO_SECONDARY_IND, ]');
  wa(q'[PROMO_COMP_ID, COMP_DISPLAY_ID, PROMO_DTL_ID, TYPE, CUSTOMER_TYP]');
  wa(q'[E, DETAIL_SECONDARY_IND, DETAIL_START_DATE, DETAIL_END_DATE, DET]');
  wa(q'[AIL_APPLY_TO_CODE, DETAIL_CHANGE_TYPE, DETAIL_CHANGE_AMOUNT, DET]');
  wa(q'[AIL_CHANGE_CURRENCY, DETAIL_CHANGE_PERCENT, DETAIL_CHANGE_SELLIN]');
  wa(q'[G_UOM, DETAIL_PRICE_GUIDE_ID, EXCEPTION_PARENT_ID, PROMO_ITEM_LO]');
  wa(q'[C_EXPL_ID, MAX_HIER_LEVEL, TO_CUR_HIER_LEVEL CUR_HIER_LEVEL, TIM]');
  wa(q'[EBASED_DTL_IND FROM (SELECT /*+ ORDERED */ T.PRICE_EVENT_ID, T.T]');
  wa(q'[O_ITEM, T.TO_DIFF_ID, T.TO_ITEM_PARENT, ILEX.DEPT, ILEX.CLASS, I]');
  wa(q'[LEX.SUBCLASS, T.TO_LOCATION, T.TO_ZONE_NODE_TYPE, T.TO_ZONE_ID, ]');
  wa(q'[ILEX.PROMO_START_DATE, ILEX.PROMO_END_DATE, ILEX.PROMO_ID, ILEX.]');
  wa(q'[PROMO_DISPLAY_ID, ILEX.PROMO_SECONDARY_IND, ILEX.PROMO_COMP_ID, ]');
  wa(q'[ILEX.COMP_DISPLAY_ID, ILEX.PROMO_DTL_ID, ILEX.TYPE, ILEX.CUSTOME]');
  wa(q'[R_TYPE, ILEX.DETAIL_SECONDARY_IND, ILEX.DETAIL_START_DATE, ILEX.]');
  wa(q'[DETAIL_END_DATE, ILEX.DETAIL_APPLY_TO_CODE, ILEX.DETAIL_CHANGE_T]');
  wa(q'[YPE, ILEX.DETAIL_CHANGE_AMOUNT, ILEX.DETAIL_CHANGE_CURRENCY, ILE]');
  wa(q'[X.DETAIL_CHANGE_PERCENT, ILEX.DETAIL_CHANGE_SELLING_UOM, ILEX.DE]');
  wa(q'[TAIL_PRICE_GUIDE_ID, ILEX.EXCEPTION_PARENT_ID, ILEX.PROMO_ITEM_L]');
  wa(q'[OC_EXPL_ID, ILEX.MAX_HIER_LEVEL, T.TO_CUR_HIER_LEVEL, ILEX.TIMEB]');
  wa(q'[ASED_DTL_IND, T.RANK, MAX(RANK) OVER (PARTITION BY T.PRICE_EVENT]');
  wa(q'[_ID, ILEX.PROMO_DTL_ID, T.TO_ITEM, T.TO_DIFF_ID, T.TO_LOCATION, ]');
  wa(q'[T.TO_ZONE_NODE_TYPE) MAX_RANK FROM ( SELECT /*+ CARDINALITY(ids ]');
  wa(q'[10) USE_NL(IDS) ORDERED */ DISTINCT 0 RANK, RPI.DEPT, RPI.PRICE_]');
  wa(q'[EVENT_ID, RPI.ITEM FROM_ITEM, NULL FROM_DIFF_ID, RPL.LOCATION FR]');
  wa(q'[OM_LOCATION, RPL.ZONE_NODE_TYPE FROM_ZONE_NODE_TYPE, RPI.ITEM TO]');
  wa(q'[_ITEM, RPI.DIFF_ID TO_DIFF_ID, NULL TO_ITEM_PARENT, RPL.LOCATION]');
  wa(q'[ TO_LOCATION, RPL.ZONE_NODE_TYPE TO_ZONE_NODE_TYPE, NULL TO_ZONE]');
  wa(q'[_ID, :B5 TO_CUR_HIER_LEVEL FROM TABLE(CAST(:B1 AS OBJ_NUMERIC_ID]');
  wa(q'[_TABLE)) IDS, RPM_BULK_CC_PE_ITEM RPI, RPM_BULK_CC_PE_LOCATION R]');
  wa(q'[PL WHERE RPI.BULK_CC_PE_ID = :B4 AND RPI.PRICE_EVENT_ID = VALUE(]');
  wa(q'[IDS) AND RPL.PRICE_EVENT_ID = VALUE(IDS) AND RPI.MERCH_LEVEL_TYP]');
  wa(q'[E = :B3 AND RPI.PE_MERCH_LEVEL = :B3 AND RPL.BULK_CC_PE_ID = RPI]');
  wa(q'[.BULK_CC_PE_ID AND RPL.PRICE_EVENT_ID = RPI.PRICE_EVENT_ID AND R]');
  wa(q'[PL.ITEMLOC_ID = RPI.ITEMLOC_ID AND RPL.ZONE_NODE_TYPE = :B2 AND ]');
  wa(q'[ROWNUM >= 1 UNION ALL SELECT /*+ CARDINALITY(ids 10) USE_NL(IDS)]');
  wa(q'[ ORDERED */ DISTINCT 0 RANK, RPI.DEPT, RPI.PRICE_EVENT_ID, RPI.I]');
  wa(q'[TEM FROM_ITEM, NULL FROM_DIFF_ID, RZ.ZONE_ID FROM_LOCATION, :B2 ]');
  wa(q'[FROM_ZONE_NODE_TYPE, RPI.ITEM TO_ITEM, NULL TO_DIFF_ID, NULL TO_]');
  wa(q'[ITEM_PARENT, RPL.LOCATION TO_LOCATION, RPL.ZONE_NODE_TYPE TO_ZON]');
  wa(q'[E_NODE_TYPE, RZ.ZONE_ID TO_ZONE_ID, :B9 TO_CUR_HIER_LEVEL FROM T]');
  wa(q'[ABLE(CAST(:B1 AS OBJ_NUMERIC_ID_TABLE)) IDS, RPM_BULK_CC_PE_ITEM]');
  wa(q'[ RPI, RPM_BULK_CC_PE_LOCATION RPL, RPM_MERCH_RETAIL_DEF_EXPL RMR]');
  wa(q'[DE, RPM_ZONE RZ, RPM_ZONE_LOCATION RZL WHERE RPI.BULK_CC_PE_ID =]');
  wa(q'[ :B4 AND RPI.PRICE_EVENT_ID = VALUE(IDS) AND RPL.PRICE_EVENT_ID ]');
  wa(q'[= VALUE(IDS) AND RPI.MERCH_LEVEL_TYPE = :B8 AND RPI.PE_MERCH_LEV]');
  wa(q'[EL = :B8 AND RPL.BULK_CC_PE_ID = RPI.BULK_CC_PE_ID AND RPL.PRICE]');
  wa(q'[_EVENT_ID = RPI.PRICE_EVENT_ID AND RPL.ITEMLOC_ID = RPI.ITEMLOC_]');
  wa(q'[ID AND RZL.LOCATION = RPL.LOCATION AND RPL.ZONE_NODE_TYPE IN (:B]');
  wa(q'[7 , :B6 ) AND RPL.ZONE_ID IS NULL AND RMRDE.DEPT = RPI.DEPT AND ]');
  wa(q'[RMRDE.CLASS = RPI.CLASS AND RMRDE.SUBCLASS = RPI.SUBCLASS AND RM]');
  wa(q'[RDE.REGULAR_ZONE_GROUP = RZ.ZONE_GROUP_ID AND RZ.ZONE_ID = RZL.Z]');
  wa(q'[ONE_ID AND ROWNUM >= 1 UNION ALL SELECT /*+ CARDINALITY(ids 10) ]');
  wa(q'[USE_NL(IDS) ORDERED */ DISTINCT 0 RANK, RPI.DEPT, RPI.PRICE_EVEN]');
  wa(q'[T_ID, IM.ITEM_PARENT FROM_ITEM, NULL FROM_DIFF_ID, RPL.LOCATION ]');
  wa(q'[FROM_LOCATION, RPL.ZONE_NODE_TYPE FROM_ZONE_NODE_TYPE, RPI.ITEM ]');
  wa(q'[TO_ITEM, RPI.DIFF_ID TO_DIFF_ID, IM.ITEM_PARENT TO_ITEM_PARENT, ]');
  wa(q'[RPL.LOCATION TO_LOCATION, RPL.ZONE_NODE_TYPE TO_ZONE_NODE_TYPE, ]');
  wa(q'[NULL TO_ZONE_ID, :B11 TO_CUR_HIER_LEVEL FROM TABLE(CAST(:B1 AS O]');
  wa(q'[BJ_NUMERIC_ID_TABLE)) IDS, RPM_BULK_CC_PE_ITEM RPI, RPM_BULK_CC_]');
  wa(q'[PE_LOCATION RPL, ITEM_MASTER IM WHERE RPI.BULK_CC_PE_ID = :B4 AN]');
  wa(q'[D RPI.PRICE_EVENT_ID = VALUE(IDS) AND RPL.PRICE_EVENT_ID = VALUE]');
  wa(q'[(IDS) AND RPI.MERCH_LEVEL_TYPE = :B10 AND RPI.PE_MERCH_LEVEL = :]');
  wa(q'[B10 AND RPL.BULK_CC_PE_ID = RPI.BULK_CC_PE_ID AND RPL.PRICE_EVEN]');
  wa(q'[T_ID = RPI.PRICE_EVENT_ID AND RPL.ITEMLOC_ID = RPI.ITEMLOC_ID AN]');
  wa(q'[D RPL.ZONE_NODE_TYPE = :B2 AND RPL.ZONE_ID IS NULL AND IM.ITEM =]');
  wa(q'[ RPI.ITEM AND ROWNUM >= 1 UNION ALL SELECT /*+ CARDINALITY(ids 1]');
  wa(q'[0) USE_NL(IDS) ORDERED */ DISTINCT 1 RANK, RPI.DEPT, RPI.PRICE_E]');
  wa(q'[VENT_ID, IM.ITEM_PARENT FROM_ITEM, RPI.DIFF_ID FROM_DIFF_ID, RPL]');
  wa(q'[.LOCATION FROM_LOCATION, RPL.ZONE_NODE_TYPE FROM_ZONE_NODE_TYPE,]');
  wa(q'[ RPI.ITEM TO_ITEM, RPI.DIFF_ID TO_DIFF_ID, IM.ITEM_PARENT TO_ITE]');
  wa(q'[M_PARENT, RPL.LOCATION TO_LOCATION, RPL.ZONE_NODE_TYPE TO_ZONE_N]');
  wa(q'[ODE_TYPE, NULL TO_ZONE_ID, :B11 TO_CUR_HIER_LEVEL FROM TABLE(CAS]');
  wa(q'[T(:B1 AS OBJ_NUMERIC_ID_TABLE)) IDS, RPM_BULK_CC_PE_ITEM RPI, RP]');
  wa(q'[M_BULK_CC_PE_LOCATION RPL, ITEM_MASTER IM WHERE RPI.BULK_CC_PE_I]');
  wa(q'[D = :B4 AND RPI.PRICE_EVENT_ID = VALUE(IDS) AND RPL.PRICE_EVENT_]');
  wa(q'[ID = VALUE(IDS) AND RPI.MERCH_LEVEL_TYPE = :B10 AND RPI.PE_MERCH]');
  wa(q'[_LEVEL = :B10 AND RPL.BULK_CC_PE_ID = RPI.BULK_CC_PE_ID AND RPL.]');
  wa(q'[PRICE_EVENT_ID = RPI.PRICE_EVENT_ID AND RPL.ITEMLOC_ID = RPI.ITE]');
  wa(q'[MLOC_ID AND RPL.ZONE_NODE_TYPE = :B2 AND RPL.ZONE_ID IS NULL AND]');
  wa(q'[ IM.ITEM = RPI.ITEM AND ROWNUM >= 1 UNION ALL SELECT /*+ CARDINA]');
  wa(q'[LITY(ids 10) USE_NL(IDS) ORDERED */ DISTINCT 0 RANK, RPI.DEPT, R]');
  wa(q'[PI.PRICE_EVENT_ID, RPI.ITEM FROM_ITEM, NULL FROM_DIFF_ID, RZ.ZON]');
  wa(q'[E_ID FROM_LOCATION, :B2 FROM_ZONE_NODE_TYPE, RPI.ITEM TO_ITEM, R]');
  wa(q'[PI.DIFF_ID TO_DIFF_ID, NULL TO_ITEM_PARENT, RPL.LOCATION TO_LOCA]');
  wa(q'[TION, RPL.ZONE_NODE_TYPE TO_ZONE_NODE_TYPE, RZ.ZONE_ID TO_ZONE_I]');
  wa(q'[D, :B12 TO_CUR_HIER_LEVEL FROM TABLE(CAST(:B1 AS OBJ_NUMERIC_ID_]');
  wa(q'[TABLE)) IDS, RPM_BULK_CC_PE_ITEM RPI, RPM_BULK_CC_PE_LOCATION RP]');
  wa(q'[L, RPM_MERCH_RETAIL_DEF_EXPL RMRDE, RPM_ZONE RZ, RPM_ZONE_LOCATI]');
  wa(q'[ON RZL WHERE RPI.BULK_CC_PE_ID = :B4 AND RPI.PRICE_EVENT_ID = VA]');
  wa(q'[LUE(IDS) AND RPL.PRICE_EVENT_ID = VALUE(IDS) AND RPI.MERCH_LEVEL]');
  wa(q'[_TYPE = :B3 AND RPI.PE_MERCH_LEVEL = :B3 AND RPL.BULK_CC_PE_ID =]');
  wa(q'[ RPI.BULK_CC_PE_ID AND RPL.PRICE_EVENT_ID = RPI.PRICE_EVENT_ID A]');
  wa(q'[ND RPL.ITEMLOC_ID = RPI.ITEMLOC_ID AND RPL.ZONE_NODE_TYPE IN (:B]');
  wa(q'[7 , :B6 ) AND RPL.ZONE_ID IS NULL AND RMRDE.DEPT = RPI.DEPT AND ]');
  wa(q'[RMRDE.CLASS = RPI.CLASS AND RMRDE.SUBCLASS = RPI.SUBCLASS AND RM]');
  wa(q'[RDE.REGULAR_ZONE_GROUP = RZ.ZONE_GROUP_ID AND RZ.ZONE_ID = RZL.Z]');
  wa(q'[ONE_ID AND RZL.LOCATION = RPL.LOCATION AND RZL.LOC_TYPE = RPL.ZO]');
  wa(q'[NE_NODE_TYPE AND ROWNUM >= 1 UNION ALL SELECT /*+ CARDINALITY(id]');
  wa(q'[s 10) USE_NL(IDS) ORDERED */ DISTINCT 1 RANK, RPI.DEPT, RPI.PRIC]');
  wa(q'[E_EVENT_ID, RPI.ITEM FROM_ITEM, RPI.DIFF_ID FROM_DIFF_ID, RZ.ZON]');
  wa(q'[E_ID FROM_LOCATION, :B2 FROM_ZONE_NODE_TYPE, RPI.ITEM TO_ITEM, R]');
  wa(q'[PI.DIFF_ID TO_DIFF_ID, NULL TO_ITEM_PARENT, RPL.LOCATION TO_LOCA]');
  wa(q'[TION, RPL.ZONE_NODE_TYPE TO_ZONE_NODE_TYPE, RZ.ZONE_ID TO_ZONE_I]');
  wa(q'[D, :B12 TO_CUR_HIER_LEVEL FROM TABLE(CAST(:B1 AS OBJ_NUMERIC_ID_]');
  wa(q'[TABLE)) IDS, RPM_BULK_CC_PE_ITEM RPI, RPM_BULK_CC_PE_LOCATION RP]');
  wa(q'[L, RPM_MERCH_RETAIL_DEF_EXPL RMRDE, RPM_ZONE RZ, RPM_ZONE_LOCATI]');
  wa(q'[ON RZL WHERE RPI.BULK_CC_PE_ID = :B4 AND RPI.PRICE_EVENT_ID = VA]');
  wa(q'[LUE(IDS) AND RPL.PRICE_EVENT_ID = VALUE(IDS) AND RPI.MERCH_LEVEL]');
  wa(q'[_TYPE = :B3 AND RPI.PE_MERCH_LEVEL = :B3 AND RPL.BULK_CC_PE_ID =]');
  wa(q'[ RPI.BULK_CC_PE_ID AND RPL.PRICE_EVENT_ID = RPI.PRICE_EVENT_ID A]');
  wa(q'[ND RPL.ITEMLOC_ID = RPI.ITEMLOC_ID AND RPL.ZONE_NODE_TYPE IN (:B]');
  wa(q'[7 , :B6 ) AND RPL.ZONE_ID IS NULL AND RMRDE.DEPT = RPI.DEPT AND ]');
  wa(q'[RMRDE.CLASS = RPI.CLASS AND RMRDE.SUBCLASS = RPI.SUBCLASS AND RM]');
  wa(q'[RDE.REGULAR_ZONE_GROUP = RZ.ZONE_GROUP_ID AND RZ.ZONE_ID = RZL.Z]');
  wa(q'[ONE_ID AND RZL.LOCATION = RPL.LOCATION AND RZL.LOC_TYPE = RPL.ZO]');
  wa(q'[NE_NODE_TYPE AND ROWNUM >= 1 UNION ALL SELECT DISTINCT 1 RANK, I]');
  wa(q'[L.DEPT, IL.PRICE_EVENT_ID, IL.ITEM FROM_ITEM, NULL FROM_DIFF_ID,]');
  wa(q'[ IL.LOCATION FROM_LOCATION, IL.ZONE_NODE_TYPE FROM_ZONE_NODE_TYP]');
  wa(q'[E, IL.ITEM TO_ITEM, IL.DIFF_ID TO_DIFF_ID, NULL TO_ITEM_PARENT, ]');
  wa(q'[IL.LOCATION TO_LOCATION, IL.ZONE_NODE_TYPE TO_ZONE_NODE_TYPE, ZL]');
  wa(q'[.ZONE_ID TO_ZONE_ID, :B12 TO_CUR_HIER_LEVEL FROM (SELECT /*+ CAR]');
  wa(q'[DINALITY(ids 10) USE_NL(IDS) ORDERED */ RPI.PRICE_EVENT_ID, RPI.]');
  wa(q'[DEPT, RPI.ITEM, RPL.LOCATION, RPL.ZONE_NODE_TYPE, RPI.DIFF_ID, R]');
  wa(q'[MRDE.REGULAR_ZONE_GROUP FROM TABLE(CAST(:B1 AS OBJ_NUMERIC_ID_TA]');
  wa(q'[BLE)) IDS, RPM_BULK_CC_PE_ITEM RPI, RPM_BULK_CC_PE_LOCATION RPL,]');
  wa(q'[ RPM_MERCH_RETAIL_DEF_EXPL RMRDE WHERE RPI.BULK_CC_PE_ID = :B4 A]');
  wa(q'[ND RPI.PRICE_EVENT_ID = VALUE(IDS) AND RPL.PRICE_EVENT_ID = VALU]');
  wa(q'[E(IDS) AND RPI.MERCH_LEVEL_TYPE = :B3 AND RPI.PE_MERCH_LEVEL = :]');
  wa(q'[B3 AND RPL.BULK_CC_PE_ID = RPI.BULK_CC_PE_ID AND RPL.PRICE_EVENT]');
  wa(q'[_ID = RPI.PRICE_EVENT_ID AND RPL.ITEMLOC_ID = RPI.ITEMLOC_ID AND]');
  wa(q'[ RPL.ZONE_NODE_TYPE IN (:B7 , :B6 ) AND RPL.ZONE_ID IS NULL AND ]');
  wa(q'[RMRDE.DEPT = RPI.DEPT AND RMRDE.CLASS = RPI.CLASS AND RMRDE.SUBC]');
  wa(q'[LASS = RPI.SUBCLASS AND ROWNUM > 0) IL, (SELECT RZ.ZONE_GROUP_ID]');
  wa(q'[, RZ.ZONE_ID, RZL.LOCATION FROM RPM_ZONE RZ, RPM_ZONE_LOCATION R]');
  wa(q'[ZL WHERE RZL.ZONE_ID = RZ.ZONE_ID AND ROWNUM > 0) ZL WHERE IL.RE]');
  wa(q'[GULAR_ZONE_GROUP = ZL.ZONE_GROUP_ID (+) AND IL.LOCATION = ZL.LOC]');
  wa(q'[ATION (+) AND ROWNUM >= 1 UNION ALL SELECT /*+ CARDINALITY(ids 1]');
  wa(q'[0) USE_NL(IDS) ORDERED */ DISTINCT 0 RANK, RPI.DEPT, RPI.PRICE_E]');
  wa(q'[VENT_ID, IM.ITEM_PARENT FROM_ITEM, NULL FROM_DIFF_ID, RZ.ZONE_ID]');
  wa(q'[ FROM_LOCATION, :B2 FROM_ZONE_NODE_TYPE, RPI.ITEM TO_ITEM, RPI.D]');
  wa(q'[IFF_ID TO_DIFF_ID, IM.ITEM_PARENT TO_ITEM_PARENT, RPL.LOCATION T]');
  wa(q'[O_LOCATION, RPL.ZONE_NODE_TYPE TO_ZONE_NODE_TYPE, RZ.ZONE_ID TO_]');
  wa(q'[ZONE_ID, :B13 TO_CUR_HIER_LEVEL FROM TABLE(CAST(:B1 AS OBJ_NUMER]');
  wa(q'[IC_ID_TABLE)) IDS, RPM_BULK_CC_PE_ITEM RPI, RPM_BULK_CC_PE_LOCAT]');
  wa(q'[ION RPL, ITEM_MASTER IM, RPM_MERCH_RETAIL_DEF_EXPL RMRDE, RPM_ZO]');
  wa(q'[NE RZ, RPM_ZONE_LOCATION RZL WHERE RPI.BULK_CC_PE_ID = :B4 AND R]');
  wa(q'[PI.PRICE_EVENT_ID = VALUE(IDS) AND RPL.PRICE_EVENT_ID = VALUE(ID]');
  wa(q'[S) AND RPI.MERCH_LEVEL_TYPE = :B10 AND RPI.PE_MERCH_LEVEL = :B10]');
  wa(q'[ AND IM.ITEM = RPI.ITEM AND RPL.BULK_CC_PE_ID = RPI.BULK_CC_PE_I]');
  wa(q'[D AND RPL.PRICE_EVENT_ID = RPI.PRICE_EVENT_ID AND RPL.ITEMLOC_ID]');
  wa(q'[ = RPI.ITEMLOC_ID AND RMRDE.DEPT = RPI.DEPT AND RMRDE.CLASS = RP]');
  wa(q'[I.CLASS AND RMRDE.SUBCLASS = RPI.SUBCLASS AND RMRDE.REGULAR_ZONE]');
  wa(q'[_GROUP = RZ.ZONE_GROUP_ID AND RZ.ZONE_ID = RZL.ZONE_ID AND RZL.L]');
  wa(q'[OCATION = RPL.LOCATION AND RZL.LOC_TYPE = RPL.ZONE_NODE_TYPE AND]');
  wa(q'[ RPL.ZONE_NODE_TYPE IN (:B7 , :B6 ) AND RPL.ZONE_ID IS NULL AND ]');
  wa(q'[ROWNUM >= 1 UNION ALL SELECT /*+ CARDINALITY(ids 10) USE_NL(IDS)]');
  wa(q'[ ORDERED */ DISTINCT 1 RANK, RPI.DEPT, RPI.PRICE_EVENT_ID, IM.IT]');
  wa(q'[EM_PARENT FROM_ITEM, RPI.DIFF_ID FROM_DIFF_ID, RZ.ZONE_ID FROM_L]');
  wa(q'[OCATION, :B2 FROM_ZONE_NODE_TYPE, RPI.ITEM TO_ITEM, RPI.DIFF_ID ]');
  wa(q'[TO_DIFF_ID, IM.ITEM_PARENT TO_ITEM_PARENT, RPL.LOCATION TO_LOCAT]');
  wa(q'[ION, RPL.ZONE_NODE_TYPE TO_ZONE_NODE_TYPE, RZ.ZONE_ID TO_ZONE_ID]');
  wa(q'[, :B13 TO_CUR_HIER_LEVEL FROM TABLE(CAST(:B1 AS OBJ_NUMERIC_ID_T]');
  wa(q'[ABLE)) IDS, RPM_BULK_CC_PE_ITEM RPI, RPM_BULK_CC_PE_LOCATION RPL]');
  wa(q'[, ITEM_MASTER IM, RPM_MERCH_RETAIL_DEF_EXPL RMRDE, RPM_ZONE RZ, ]');
  wa(q'[RPM_ZONE_LOCATION RZL WHERE RPI.BULK_CC_PE_ID = :B4 AND RPI.PRIC]');
  wa(q'[E_EVENT_ID = VALUE(IDS) AND RPL.PRICE_EVENT_ID = VALUE(IDS) AND ]');
  wa(q'[RPI.MERCH_LEVEL_TYPE = :B10 AND RPI.PE_MERCH_LEVEL = :B10 AND IM]');
  wa(q'[.ITEM = RPI.ITEM AND RPL.BULK_CC_PE_ID = RPI.BULK_CC_PE_ID AND R]');
  wa(q'[PL.PRICE_EVENT_ID = RPI.PRICE_EVENT_ID AND RPL.ITEMLOC_ID = RPI.]');
  wa(q'[ITEMLOC_ID AND RMRDE.DEPT = RPI.DEPT AND RMRDE.CLASS = RPI.CLASS]');
  wa(q'[ AND RMRDE.SUBCLASS = RPI.SUBCLASS AND RMRDE.REGULAR_ZONE_GROUP ]');
  wa(q'[= RZ.ZONE_GROUP_ID AND RZ.ZONE_ID = RZL.ZONE_ID AND RZL.LOCATION]');
  wa(q'[ = RPL.LOCATION AND RZL.LOC_TYPE = RPL.ZONE_NODE_TYPE AND RPL.ZO]');
  wa(q'[NE_NODE_TYPE IN (:B7 , :B6 ) AND RPL.ZONE_ID IS NULL AND ROWNUM ]');
  wa(q'[>= 1 UNION ALL SELECT DISTINCT 1 RANK, IL.DEPT, IL.PRICE_EVENT_I]');
  wa(q'[D, IL.ITEM_PARENT FROM_ITEM, NULL FROM_DIFF_ID, IL.LOCATION FROM]');
  wa(q'[_LOCATION, IL.ZONE_NODE_TYPE FROM_ZONE_NODE_TYPE, IL.ITEM TO_ITE]');
  wa(q'[M, IL.DIFF_ID TO_DIFF_ID, IL.ITEM_PARENT TO_ITEM_PARENT, IL.LOCA]');
  wa(q'[TION TO_LOCATION, IL.ZONE_NODE_TYPE TO_ZONE_NODE_TYPE, ZL.ZONE_I]');
  wa(q'[D TO_ZONE_ID, :B13 TO_CUR_HIER_LEVEL FROM (SELECT /*+ CARDINALIT]');
  wa(q'[Y(ids 10) USE_NL(IDS) ORDERED */ RPI.PRICE_EVENT_ID, RPI.DEPT, R]');
  wa(q'[PI.DIFF_ID, IM.ITEM_PARENT, RPL.LOCATION, RPL.ZONE_NODE_TYPE, RP]');
  wa(q'[I.ITEM, RMRDE.REGULAR_ZONE_GROUP FROM TABLE(CAST(:B1 AS OBJ_NUME]');
  wa(q'[RIC_ID_TABLE)) IDS, RPM_BULK_CC_PE_ITEM RPI, RPM_BULK_CC_PE_LOCA]');
  wa(q'[TION RPL, ITEM_MASTER IM, RPM_MERCH_RETAIL_DEF_EXPL RMRDE WHERE ]');
  wa(q'[RPI.BULK_CC_PE_ID = :B4 AND RPI.PRICE_EVENT_ID = VALUE(IDS) AND ]');
  wa(q'[RPL.PRICE_EVENT_ID = VALUE(IDS) AND RPI.MERCH_LEVEL_TYPE = :B10 ]');
  wa(q'[AND RPI.PE_MERCH_LEVEL = :B10 AND IM.ITEM = RPI.ITEM AND RPL.BUL]');
  wa(q'[K_CC_PE_ID = RPI.BULK_CC_PE_ID AND RPL.PRICE_EVENT_ID = RPI.PRIC]');
  wa(q'[E_EVENT_ID AND RPL.ITEMLOC_ID = RPI.ITEMLOC_ID AND RPL.ZONE_NODE]');
  wa(q'[_TYPE IN (:B7 , :B6 ) AND RPL.ZONE_ID IS NULL AND RMRDE.DEPT = R]');
  wa(q'[PI.DEPT AND RMRDE.CLASS = RPI.CLASS AND RMRDE.SUBCLASS = RPI.SUB]');
  wa(q'[CLASS AND ROWNUM > 0) IL, (SELECT RZ.ZONE_GROUP_ID, RZ.ZONE_ID, ]');
  wa(q'[RZL.LOCATION FROM RPM_ZONE RZ, RPM_ZONE_LOCATION RZL WHERE RZL.Z]');
  wa(q'[ONE_ID = RZ.ZONE_ID AND ROWNUM > 0) ZL WHERE IL.REGULAR_ZONE_GRO]');
  wa(q'[UP = ZL.ZONE_GROUP_ID (+) AND IL.LOCATION = ZL.LOCATION (+) AND ]');
  wa(q'[ROWNUM >= 1 UNION ALL SELECT /*+ CARDINALITY(ids 10) USE_NL(IDS)]');
  wa(q'[ ORDERED */ DISTINCT 2 RANK, RPI.DEPT, RPI.PRICE_EVENT_ID, RPI.I]');
  wa(q'[TEM FROM_ITEM, RPI.DIFF_ID FROM_DIFF_ID, RZ.ZONE_ID FROM_LOCATIO]');
  wa(q'[N, :B2 FROM_ZONE_NODE_TYPE, RPI.ITEM TO_ITEM, RPI.DIFF_ID TO_DIF]');
  wa(q'[F_ID, IM.ITEM_PARENT TO_ITEM_PARENT, RPL.LOCATION TO_LOCATION, R]');
  wa(q'[PL.ZONE_NODE_TYPE TO_ZONE_NODE_TYPE, RZ.ZONE_ID TO_ZONE_ID, :B13]');
  wa(q'[ TO_CUR_HIER_LEVEL FROM TABLE(CAST(:B1 AS OBJ_NUMERIC_ID_TABLE))]');
  wa(q'[ IDS, RPM_BULK_CC_PE_ITEM RPI, RPM_BULK_CC_PE_LOCATION RPL, ITEM]');
  wa(q'[_MASTER IM, RPM_MERCH_RETAIL_DEF_EXPL RMRDE, RPM_ZONE RZ, RPM_ZO]');
  wa(q'[NE_LOCATION RZL WHERE RPI.BULK_CC_PE_ID = :B4 AND RPI.PRICE_EVEN]');
  wa(q'[T_ID = VALUE(IDS) AND RPL.PRICE_EVENT_ID = VALUE(IDS) AND RPI.ME]');
  wa(q'[RCH_LEVEL_TYPE = :B10 AND RPI.PE_MERCH_LEVEL = :B10 AND RPL.BULK]');
  wa(q'[_CC_PE_ID = RPI.BULK_CC_PE_ID AND RPL.PRICE_EVENT_ID = RPI.PRICE]');
  wa(q'[_EVENT_ID AND RPL.ITEMLOC_ID = RPI.ITEMLOC_ID AND RPL.ZONE_NODE_]');
  wa(q'[TYPE IN (:B7 , :B6 ) AND RMRDE.DEPT = RPI.DEPT AND RMRDE.CLASS =]');
  wa(q'[ RPI.CLASS AND RMRDE.SUBCLASS = RPI.SUBCLASS AND RMRDE.REGULAR_Z]');
  wa(q'[ONE_GROUP = RZ.ZONE_GROUP_ID AND RZ.ZONE_ID = RZL.ZONE_ID AND RZ]');
  wa(q'[L.LOCATION = RPL.LOCATION AND RZL.LOC_TYPE = RPL.ZONE_NODE_TYPE ]');
  wa(q'[AND RPL.ZONE_ID IS NULL AND RPI.ITEM = IM.ITEM AND ROWNUM >= 1 U]');
  wa(q'[NION ALL SELECT DISTINCT 2 RANK, IL.DEPT, IL.PRICE_EVENT_ID, IL.]');
  wa(q'[ITEM_PARENT FROM_ITEM, IL.DIFF_ID FROM_DIFF_ID, IL.LOCATION FROM]');
  wa(q'[_LOCATION, IL.ZONE_NODE_TYPE FROM_ZONE_NODE_TYPE, IL.ITEM TO_ITE]');
  wa(q'[M, IL.DIFF_ID TO_DIFF_ID, IL.ITEM_PARENT TO_ITEM_PARENT, IL.LOCA]');
  wa(q'[TION TO_LOCATION, IL.ZONE_NODE_TYPE TO_ZONE_NODE_TYPE, ZL.ZONE_I]');
  wa(q'[D TO_ZONE_ID, :B13 TO_CUR_HIER_LEVEL FROM (SELECT /*+ CARDINALIT]');
  wa(q'[Y(ids 10) USE_NL(IDS) ORDERED */ RPI.PRICE_EVENT_ID, RPI.DEPT, I]');
  wa(q'[M.ITEM_PARENT, RPI.DIFF_ID, RPL.LOCATION, RPL.ZONE_NODE_TYPE, RP]');
  wa(q'[I.ITEM, RMRDE.REGULAR_ZONE_GROUP FROM TABLE(CAST(:B1 AS OBJ_NUME]');
  wa(q'[RIC_ID_TABLE)) IDS, RPM_BULK_CC_PE_ITEM RPI, RPM_BULK_CC_PE_LOCA]');
  wa(q'[TION RPL,..
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Suppose you want to analyze data set by using your favorite tools (Pandas/NumPy).

By reading my previous article:

How to efficiently load data into Python from the Oracle RDBMS

, you should realize how important is to do as much of data processing at the database SQL engine layer to get out dataset suitable for further analysis.

Once you finish with all aggregations/filtering/grouping/windowing on SQL, you can import resulting dataset into the Pandas framework.

Probably you’ll start full of enthusiasm with a familiar set of Python packages (Pandas), but soon you realize your Linux laptop become unresponsive.

Problem is that SQL result set is too large to fit in memory/swap, and Pandas package was not able to perform data wrangling tasks at satisfactory speed.

There are several issues with Pandas framework I want to cover:

  • Single thread execution
    Vanilla Pandas package is not capable to run in parallel.
    For that reason it’s unable to scale-up. 
    There are several ways how to get parallelism (like CuDF, splitting Pandas dataset…), but in this article I’ll describe distributed approach that will cover majority of cases.
  • Memory (in)efficiency
    Pandas always use the maximum size of some data type.

    Even if data from the relational database fit within byte type, Pandas will use Int64 as can be seen in the following example:

sql = 'select prod_id from sales'

df_sales = pd.read_sql(sql, con)

print("PROD_ID min value: ", df_sales['PROD_ID'].min())
print("PROD_ID max value: ", df_sales['PROD_ID'].max())
print("Number of records: ", len(df_sales.values))
print(df_sales.info())

PROD_ID min value:  13
PROD_ID max value:  148
Number of records:  918843
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 918843 entries, 0 to 918842
Data columns (total 1 columns):
PROD_ID    918843 non-null int64
dtypes: int64(1)
memory usage: 7.0 MB

As you can observer, to load 1 million records into the Pandas framework, for just one column I need 7 MB of memory.

If I select all columns (e.g. 50), I’ll have 50×7 = 350 MB for just 1 million records.

In the next example I’m going to use the most efficient type for this case (uint that ranges from 0..255).

#df_sales = pd.read_sql(sql, con)
df_sales = pd.DataFrame(np.random.randint(low = 13, high = 148, size = 918843, dtype= 'uint8'), columns =['PROD_ID'])


print("PROD_ID min value: ", df_sales['PROD_ID'].min())
print("PROD_ID max value: ", df_sales['PROD_ID'].max())
print("Number of records: ", len(df_sales.values))
print(df_sales.info())

PROD_ID min value:  13
PROD_ID max value:  147
Number of records:  918843
Number of records:  918843
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 918843 entries, 0 to 918842
Data columns (total 1 columns):
PROD_ID    918843 non-null uint8
dtypes: uint8(1)
memory usage: 897.4 KB

For DataFrame of the same size, memory consumption is down from 7 MB –> 0.89 MB. which is 7.86x lower.

  • Unable to process larger data set needed for Machine Learning & similar tasks

    If you have a larger data set to analyze (e.g. 100 million in my case on my laptop, but that can be many billions of rows), due to the large memory consumption you’ll end up with Error when memory/swap will be exhausted, as Linux Out Of Memory killer will kick off and kill your apps.

    In the previous example you can observe how rapidly Pandas framework, when connecting to the database, consumes all available memory (64GB in my case) and swap.

    As a consequence, you can’t scale up and analyze bigger data set which is needed for data science tasks as dataset is limited by the available RAM & Swap space.

    On the other hand, even if I would have a huge amount of memory available (e.g. 3 TB of RAM), as Pandas framework is single-threaded, any king of data wrangling & analysis would be slow as I’m using only one CPU core.

    But Pandas framework has so many great features, it’s not a miracle it enjoy a huge popularity among Python users.

    To satisfy all sides, one of the possible solutions is to use one of distributed framework available.

    Within the Python ecosystem there are more than one solution that provides distributed computing, but Dask is probably one of the most popular one.

    On the following picture you can check what is happening if I try to process 100 million records with Pandas on my Laptop.

If I replace Pandas with Dask distributed framework, I’m able to analyze the same amount of data successfully.

More on Dask you’ll find in one of my future posts.

On the following figures you can see 8 node Dask Cluster in action.

The post Processing billions of records with Python & Oracle appeared first on Josip Pojatina.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

After I’ve installed one older application on Oracle Linux 7, next time I’ve tried to start it, the server refused to start.

Fortunately it’s Linux, so there are many ways to recover it.

The following approach should work for most cases.

  1. Try to boot into the terminal mode by editing Grub
  2. In case that previous receipt doesn’t help, choose older kernel version to boot.
  3. If either receipt 1 or 2 is working, you should proceed in the following way:
#As root user execute the following command to disable Graphical mode: 
systemctl set-default multi-user.target
 
#reboot and start the latest (problematic) kernel. 

#Enter username/password for the oracle user (or any user you've created). 

login as: oracle 
password: ****

#Switch to the root user: 
su - 

#execute one of the following to troubleshoot: 
journalctl 
dmesg
/var/log

After inspecting those log files, I’m able to figure out what caused the boot problem.

In this case I’ve installed one application that requires several dependencies to be installed as well.

On of dependencies have caused GNOME issue.

To fix the problem, you should revert those changes:

#check all recent yum transactions 
yum history 

#get more info from the problematic transaction
yum history info 12

#revert all changes done by problematic transaction 
yum history undo 12

#reboot your system 
reboot

After that you should be able to boot.

 

For more severe cases you’ll need to boot the system into rescue mode.

To do that you’ll need to have the binary DVD ready to start the server from DVD.

  1. Boot from a source like DVD image/netboot.
  2. In the boot menu select started from DVD select Rescue
  3. When prompted, select option 1 – Continue to mount the disks for the target system to be rescued. This will mount existing system under the /mount/sysimage
  4. Press ENTER to dive into shell and execute:
df

to check if dev/sys/proc filesystems are mounted under the existing installation env (/mnt/sysimage).

If they are not mounted, you’ll need to mount them by executing:

mount -o bind /dev /mnt/sysimage/dev
mount -o bind /sys /mnt/sysimage/sys
mount -t proc /proc /mnt/sysimage/proc
mount -o bind /dev/shm /mnt/sysimage/dev/shm

     5. In the next step, change root to already installed root fs by executing:

$ chroot /mnt/sysimage

Now you can fix or repair your files from the backup.

At the end, you’ll need to exit from the chroot environment and reboot by executing the following:

$ exit

$ exit

 

In the worst case when you have corrupted /boot directory, you’ll need to recreate it (vmlinuz & initramfs).

As this won’t happen very often, procedure of how to fix that case won’t be covered here.

The post Troubleshooting boot issues in Linux appeared first on Josip Pojatina.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Python has many different ways to fetch the data needed to do some processing.

Although majority of examples you can find around are using CSV file load, fetching a data from the database is still the most common way in practice.

For all tests I’ll use SALES table from the Oracle SH sample schema.

 

1.

In this example, classic iteration over cx_Oracle cursor result set will be used:

import cx_Oracle
import os
import datetime
import pandas as pd
from numpy.distutils.system_info import dfftw_info

os.environ["ORACLE_HOME"] = "/path/to/oracle/home"

con = cx_Oracle.connect(user='sh', password = 'xxx', dsn = '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(host = localhost) (Port = 9999))) (CONNECT_DATA = (SID = orcl)))')

sql = 'select * from sales'
cur = con.cursor()
cur.arraysize = 1000

counter = 0

beginTime = datetime.datetime.now()

try:
    cur.execute(sql)
    for row in cur:
        counter +=1

except cx_Oracle.DatabaseError as ora_exception:
    error, = ora_exception.args
    print("Oracle Error Code: ", error.code)
    print("Oracle Error Message: ", error.message)

print('elapsed_time = ', datetime.datetime.now() - beginTime)
print(counter)

cur.close()
con.close()

elapsed_time =  0:00:01.591582
918843

Elapsed time for processing approximately 1 million records (918.843) is 1.59 seconds.

 

2.

In this example, instead of iterating over a result set, Fetchall method will be used.

This is the part of code that I’ve changed:

r = cur.execute(sql).fetchall()
counter = len(r)

elapsed_time =  0:00:01.588170
918843

Elapsed time is almost identical as in the first case.

 

3.

In the third example I’ll use Pandas instead of cursor. There are several variation named as a subcase with a letter a – c.

a) Getting the number of records by using PROD_ID column name.

df_sales = pd.read_sql(sql, con)
counter = df_sales['PROD_ID'].size

elapsed_time =  0:00:04.065327
918843

Elapsed time to process the same amount of data is 4.06 sec, which is almost 3 times slower comparing with a cursor approach (cases 1 & 2).

b) Several other ways to get the number of records that are running roughly at the same speed as 3a case. 

#uncomment one of the following options
#counter = len(df_sales.index)
#counter = df_sales.shape[0]
#counter = len(df_sales.axes[0])

c) Here is the slowest way to get the number of records as I’m using values instead of key (index)

counter = len(df_sales.values) 
elapsed_time =  0:00:06.027399
918843

 

4.

Pandas case – selecting only the first column (instead of all columns) from the SALES table

sql = 'select prod_id from sales'

df_sales = pd.read_sql(sql, con)
counter = df_sales['PROD_ID'].size

elapsed_time =  0:00:03.744749
918843

Elapsed time is 3.74 sec which is by far the best result I can get by using the Pandas framework for client side processing.

 

5.

Leaving to SQL engine to do its job of calculating the number of records – example with cursor:

sql = 'select count(*) as cnt from sales'

cur = con.cursor()
cur.execute(sql)
counter = cur.fetchone()[0]

elapsed_time =  0:00:00.004216
918843

 

6.

Leaving to SQL engine to do its job of calculating the number of records – example with Pandas:

sql = 'select count(*) as cnt from sales'

a)
counter = df_sales.iloc [0]['CNT']

elapsed_time =  0:00:00.013347
918843

b)
counter = df_sales.iat[0,0]

elapsed_time =  0:00:00.009238
918843

c)
counter = df_sales.at[0, 'CNT']

elapsed_time =  0:00:00.002658
918843

 

Summary:

 

When you need to process data from a database, the best way to do that is to leave database SQL engine to do a job.

Results with SQL engine (cases 5 & 6) are many time (600 times or more) faster comparing with procedural logic.

That is expected (relational databases are created for that purpose), although you can still very often find a code logic where client side processing is used to do database engine job, which is a very wrong approach.

Another interesting point is that Pandas package is about 2.5 times slower comparing with a cursor (result set).

In cases where cursor approach is covering all needed functionality, you shouldn’t use Pandas framework as it’s about 2.5 times slower comparing with a cursor (result set) approach.

If you need to use Pandas, the best you can do is to leave all heavy processing to the SQL database engine, and fetch and manipulate only with necessary data volume.

The post How to efficiently load data into Python appeared first on Josip Pojatina.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In the last post I’ve explained how to install nVidia Toolkit 10.1 on Ubuntu 18.04 LTS.

Details can be found on the following pages:

https://www.josip-pojatina.com/en/how-to-install-cuda-toolkit-on-ubuntu-18-04-lts/

or

https://www.performatune.com/en/how-to-install-cuda-toolkit-on-ubuntu-18-04-lts/

In this article I’ll explain the most important card details you need to know.

Prerequisite for this article (besides nVidia drivers and Toolkit 10.1 Toolkit installed) is to have all samples compiled.

All you need to do is to cd into the Samples directory and execute the following:

user@hostname:~/Downloads/NVIDIA_CUDA-10.1_Samples/bin/x86_64/linux/
deviceQuery Starting...

 CUDA Device Query (Runtime API) version (CUDART static linking)

Detected 1 CUDA Capable device(s)

Device 0: "Quadro M2200"
  CUDA Driver Version / Runtime Version          10.1 / 10.1
  CUDA Capability Major/Minor version number:    5.2
  Total amount of global memory:                 4044 MBytes (4240179200 bytes)
  ( 8) Multiprocessors, (128) CUDA Cores/MP:     1024 CUDA Cores
  GPU Max Clock rate:                            1036 MHz (1.04 GHz)
  Memory Clock rate:                             2754 Mhz
  Memory Bus Width:                              128-bit
  L2 Cache Size:                                 1048576 bytes
  Maximum Texture Dimension Size (x,y,z)         1D=(65536), 2D=(65536, 65536), 3D=(4096, 4096, 4096)
  Maximum Layered 1D Texture Size, (num) layers  1D=(16384), 2048 layers
  Maximum Layered 2D Texture Size, (num) layers  2D=(16384, 16384), 2048 layers
  Total amount of constant memory:               65536 bytes
  Total amount of shared memory per block:       49152 bytes
  Total number of registers available per block: 65536
  Warp size:                                     32
  Maximum number of threads per multiprocessor:  2048
  Maximum number of threads per block:           1024
  Max dimension size of a thread block (x,y,z): (1024, 1024, 64)
  Max dimension size of a grid size    (x,y,z): (2147483647, 65535, 65535)
  Maximum memory pitch:                          2147483647 bytes
  Texture alignment:                             512 bytes
  Concurrent copy and kernel execution:          Yes with 2 copy engine(s)
  Run time limit on kernels:                     Yes
  Integrated GPU sharing Host Memory:            No
  Support host page-locked memory mapping:       Yes
  Alignment requirement for Surfaces:            Yes
  Device has ECC support:                        Disabled
  Device supports Unified Addressing (UVA):      Yes
  Device supports Compute Preemption:            No
  Supports Cooperative Kernel Launch:            No
  Supports MultiDevice Co-op Kernel Launch:      No
  Device PCI Domain ID / Bus ID / location ID:   0 / 1 / 0
  Compute Mode:
     < Default (multiple host threads can use ::cudaSetDevice() with device simultaneously) >

deviceQuery, CUDA Driver = CUDART, CUDA Driver Version = 10.1, CUDA Runtime Version = 10.1, NumDevs = 1
Result = PASS

Here are the list with short explanation of the most important specs needed for CUDA tuning:

 

1.

Detected 1 CUDA Capable device(s)

In this case I have only one CUDA device. In reallity you might have 2, 3 or more on one machine.

 

2.

Device 0: “Quadro M2200”

This is a my nVidia device name. If I need to search for some information, I need to know full card name.

To get the full card name, you can execute the following:

user@hostname:~>lspci | grep -e VGA
01:00.0 VGA compatible controller: NVIDIA Corporation GM206GLM [Quadro M2200 Mobile] (rev a1)

It’s also good to know installed driver version of your card.

You can find that by executing the following:

jp@performatune.com:~>nvidia-smi 
Mon Mar 25 17:28:46 2019       
+-----------------------------------------------------------------------------+
| NVIDIA-SMI 418.43       Driver Version: 418.43       CUDA Version: 10.1     |
|-------------------------------+----------------------+----------------------+
| GPU  Name        Persistence-M| Bus-Id        Disp.A | Volatile Uncorr. ECC |
| Fan  Temp  Perf  Pwr:Usage/Cap|         Memory-Usage | GPU-Util  Compute M. |
|===============================+======================+======================|
|   0  Quadro M2200        Off  | 00000000:01:00.0  On |                  N/A |
| N/A   44C    P0    N/A /  N/A |   1631MiB /  4043MiB |      0%      Default |
+-------------------------------+----------------------+----------------------+
                                                                               
+-----------------------------------------------------------------------------+
| Processes:                                                       GPU Memory |
|  GPU       PID   Type   Process name                             Usage      |
|=============================================================================|
|    0      1833      G   /usr/libexec/Xorg                             89MiB |
|    0      2022      G   /usr/bin/gnome-shell                          44MiB |
|    0      2397      G   /usr/libexec/Xorg                            695MiB |
|    0      2522      G   /usr/bin/gnome-shell                         252MiB |
|    0      2754      G   ...are/jetbrains-toolbox/jetbrains-toolbox   401MiB |
|    0      2762      G   cairo-dock                                     9MiB |
|    0      3712      G   ...-token=5C40E868867C4C7F98ABDA339E4D154C   129MiB |
+-----------------------------------------------------------------------------+

 

3.

CUDA Driver Version / Runtime Version 10.1 / 10.1

This is important to know in case you want to install GPU related packages (like a packages from the nVidia RAPIDS projects) that are not available in the Anaconda default repo.

 

4.

CUDA Capability Major/Minor version number: 5.2

With this information you can open the following link:

https://docs.nvidia.com/cuda/cuda-c-programming-guide/index.html

 

and search for “Table 14 Technical Specifications per Compute Capability”.

In my case I can find that for my graphic card which has CUDA Capability 5.2, maximum number of resident blocks per multiprocessor is 32.

This is the first limitation when tuning CUDA parameters you need to be aware.

 

5.

Total amount of global memory: 4044 MBytes (4240179200 bytes)

This information will play a role to properly size the maximum size of arrays or matrix that you want to load into your Graphic card.

You need to combine a memory size information with array data type (byte/int/float) and type precision (e.g. int8 or float64) to calculate your graphic card memory limits, thus avoiding OOM (Out of memory) errors in your application.

 

6.

( 8) Multiprocessors, (128) CUDA Cores/MP: 1024 CUDA Cores

There are two important data you can observe.

My graphic card has 8 Multiprocessors, and each multiprocessor has 128 CUDA cores.

In total if I multiply 8×128 = 1024, which is a total number of CUDA cores available.

Each streaming multiprocessor contains:

  • memory registers
  • memory caches
  • thread scheduler
  • CUDA cores

 

7.

GPU Max Clock rate: 1036 MHz (1.04 GHz)

This is similar to CPU. Faster clock rate means faster GPU processing.

 

8.

Memory Clock rate: 2754 Mhz

Memory Bus Width: 128-bit

L2 Cache Size: 1048576 bytes

Memory parameters are important to speed up data transfer from the main RAM memory to GPU device, and back from the GPU to CPU.

As I’ll explain in one of the future articles, data transfer can take 50% of the total elapsed time for GPU processing.

 

9.

Maximum number of threads per multiprocessor: 2048

This is related to point 6 and imposes another limitation when tuning  CUDA parameters (point 4 is the first limit).

In this case I have 8 multiprocessors and each multiprocessors can have up to 2048 threads.

 

10.

Maximum number of threads per block: 1024

This is the third limit when tuning the CUDA cores.

 

11.

Warp size: 32

Group of 32 threads in this case is called warp and represent the smallest unit that can be scheduled.

It means that the size of a thread block is always multiple of 32 threads.

 

To summarize, we have 3 main limits:

  • from point 4 I can have at max 32 blocks per multiprocessor
  • from point 6 I have 8 multiprocessors and 1024 CUDA cores in total (128 per multiprocessor)
  • from point 9 I can have at max 2048 threads per multiprocessor

 

To get the optimal performance:

  • try to use all symmetric multiprocessing units
  • try to use all cores in symmetric multiprocessing units busy
  • optimize shared memory and registers
  • optimize device memory
  • optimize device memory access

Threads are grouped into blocks which are grouped into a grid.

Grid can execute only one kernel function (code that will be executed on GPU) and have the same dimension.

 

This is a very brief explanation of CUDA architecture, but to explain how it works you better purchase one of the books that are available.

 

The following example might help to describe what you need to know about CUDA as a developer.

Let’s assume I have 1 million integers in array.

When you call a kernel function, you only need to find the optimal number of threads per block.

You can start with warp size (smallest unit that can be scheduled) which is in my case 32, and then you can perform several tests with 64, 128, 256, 512 and 1024 (maximum value) threads per block.

Common values for a number of threads per block are somewhere between 32 and 512 (depends on GPU model, data types, array/matrix characteristics…).

By knowing the number of threads and the array size, you can calculate a number of blocks.

block_cnt = int(math.ceil(float(N) / thread_cnt))

N is a number of elements in array/matrix, while thread_cnt is a number of threads per block that I have from the previous step (e.g. 64).

 

Summary:

To get the best results, try to avoid all imposed limits and to have enough number of threads and blocks to keep all CUDA cores busy.

In the next blog I’ll take a look at a CUDA performances versus optimized Numba Python code execution.

The post CUDA Tuning – GPU card details appeared first on Josip Pojatina.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

This is the second part of CUDA-GPU tuning series where I’ll explain how to install CUDA Toolkit 10.1 on Ubuntu 18.04 LTS.

Although you can find more info about CUDA Toolkit installation at the following page:

https://docs.nvidia.com/cuda/cuda-installation-guide-linux/

there are a few important steps that are not mentioned there.

For that reason I want to further clarify installation process.

------------------------------------------------------------------------------------------------------------------------------------------------

#Check Ubuntu version: 

user@hostname:~$ lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 18.04.2 LTS
Release:	18.04
Codename:	bionic
------------------------------------------------------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------------------------------------------------------
#Determine Graphics card model 

user@hostname:~$ lspci | grep -e VGA
00:02.0 VGA compatible controller: Intel Corporation HD Graphics P630 (rev 04)
01:00.0 VGA compatible controller: NVIDIA Corporation GM206GLM [Quadro M2200 Mobile] (rev a1)

#you can also use 
user@hostname:~>nvidia-smi 
------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------
#nVIDIA drivers install is described on the following two links: 

How to install nvidia driver on Ubuntu 18.04 LTS
How to install nvidia driver on Ubuntu 18.04 LTS
#Alternatively you can execute the following steps root@hostname:/home/user/Downloads# ubuntu-drivers devices == /sys/devices/pci0000:00/0000:00:01.0/0000:01:00.0 == modalias : pci:v000010DEd00001436sv0000103Csd00008275bc03sc00i00 vendor : NVIDIA Corporation model : GM206GLM [Quadro M2200 Mobile] driver : nvidia-driver-390 - distro non-free recommended driver : xserver-xorg-video-nouveau - distro free builtin ubuntu-drivers autoinstall ------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------ #Nouveau disabling (open source graphical drivers) root@hostname:/etc/modprobe.d# bash -c "echo blacklist nouveau > /etc/modprobe.d/blacklist-nvidia-nouveau.conf" root@hostname:/etc/modprobe.d# bash -c "echo options nouveau modeset=0 >> /etc/modprobe.d/blacklist-nvidia-nouveau.conf" root@hostname:/etc/modprobe.d# cat /etc/modprobe.d/blacklist-nvidia-nouveau.conf blacklist nouveau options nouveau modeset=0 ------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------ #Wayland disabling loginctl user@hostname:~>loginctl SESSION UID USER SEAT TTY 3 0 root pts/0 c1 121 gdm seat0 tty1 2 1000 jpext seat0 tty2 user@hostname:~>loginctl show-session 2 -p Type Type=x11 root@hostname:~# vi /etc/gdm3/custom.conf # Uncoment the line below to force the login screen to use Xorg #WaylandEnable=false ---------> WaylandEnable=false ------------------------------------------------------------------------------------------------------------------------------------------------ #Verify You Have a Supported Version of Linux uname -m && cat /etc/*release root@hostname:/etc/apt/sources.list.d>uname -m && cat /etc/*release x86_64 DISTRIB_ID=Ubuntu DISTRIB_RELEASE=18.04 DISTRIB_CODENAME=bionic DISTRIB_DESCRIPTION="Ubuntu 18.04.2 LTS" NAME="Ubuntu" VERSION="18.04.2 LTS (Bionic Beaver)" ID=ubuntu ID_LIKE=debian PRETTY_NAME="Ubuntu 18.04.2 LTS" VERSION_ID="18.04" HOME_URL="https://www.ubuntu.com/" SUPPORT_URL="https://help.ubuntu.com/" BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/" PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy" VERSION_CODENAME=bionic UBUNTU_CODENAME=bionic #Verify the System Has gcc Installed user@hostname:~$ gcc --version gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0 Copyright (C) 2017 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. #Verify the System has the Correct Kernel Headers and Development Packages Installed user@hostname:~$ uname -r 4.15.0-29-generic #The kernel headers and development packages for the currently running kernel can be installed with: root@hostname:~>apt-get install linux-headers-$(uname -r) Reading package lists... Done Building dependency tree Reading state information... Done linux-headers-4.15.0-29-generic is already the newest version (4.15.0-29.31). 0 upgraded, 0 newly installed, 0 to remove and 14 not upgraded. #Choose an Installation Method The CUDA Toolkit can be installed using either of two different installation mechanisms: distribution-specific packages (RPM and Deb packages), or a distribution-independent package (runfile packages). The distribution-independent package has the advantage of working across a wider set of Linux distributions, but does not update the distribution's native package management system. The distribution-specific packages interface with the distribution's native package management system. It is recommended to use the distribution-specific packages, where possible. --> deb #Download the NVIDIA CUDA Toolkit http://developer.nvidia.com/cuda-downloads https://developer.nvidia.com/cuda-downloads?target_os=Linux&target_arch=x86_64&target_distro=Ubuntu&target_version=1804&target_type=deblocal Download Verification https://developer.download.nvidia.com/compute/cuda/10.1/Prod/docs/sidebar/md5sum.txt 8d1b233120c0d05ff6f6d5e37906faf5 cuda-repo-ubuntu1804-10-1-local-10.1.105-418.39_1.0-1_amd64.deb $ md5sum <file> user@hostname:~/Downloads>md5sum cuda-repo-ubuntu1804-10-1-local-10.1.105-418.39_1.0-1_amd64.deb 8d1b233120c0d05ff6f6d5e37906faf5 cuda-repo-ubuntu1804-10-1-local-10.1.105-418.39_1.0-1_amd64.deb #Package Manager Installation #Ubuntu #Perform the pre-installation actions. #Install repository meta-data $ sudo dpkg -i cuda-repo-<distro>_<version>_<architecture>.deb #Installing the CUDA public GPG key When installing using the local repo: $ sudo apt-key add /var/cuda-repo-<version>/7fa2af80.pub #Update the Apt repository cache $ sudo apt-get update #Install CUDA sudo apt-get install cuda user@hostname:~/Downloads>sudo dpkg -i cuda-repo-ubuntu1804-10-1-local-10.1.105-418.39_1.0-1_amd64.deb [sudo] password for user: Selecting previously unselected package cuda-repo-ubuntu1804-10-1-local-10.1.105-418.39. (Reading database ... 135800 files and directories currently installed.) Preparing to unpack cuda-repo-ubuntu1804-10-1-local-10.1.105-418.39_1.0-1_amd64.deb ... Unpacking cuda-repo-ubuntu1804-10-1-local-10.1.105-418.39 (1.0-1) ... Setting up cuda-repo-ubuntu1804-10-1-local-10.1.105-418.39 (1.0-1) ... The public CUDA GPG key does not appear to be installed. To install the key, run this command: sudo apt-key add /var/cuda-repo-10-1-local-10.1.105-418.39/7fa2af80.pub user@hostname:~/Downloads>sudo apt-key add /var/cuda-repo-10-1-local-10.1.105-418.39/7fa2af80.pub OK user@hostname:~/Downloads>sudo apt-get update Get:1 file:/var/cuda-repo-10-1-local-10.1.105-418.39 InRelease Ign:1 file:/var/cuda-repo-10-1-local-10.1.105-418.39 InRelease Get:2 file:/var/cuda-repo-10-1-local-10.1.105-418.39 Release [574 B] Get:2 file:/var/cuda-repo-10-1-local-10.1.105-418.39 Release [574 B] Get:3 file:/var/cuda-repo-10-1-local-10.1.105-418.39 Release.gpg [833 B] Get:3 file:/var/cuda-repo-10-1-local-10.1.105-418.39 Release.gpg [833 B] Get:4 file:/var/cuda-repo-10-1-local-10.1.105-418.39 Packages [24,0 kB] Get:5 http://security.ubuntu.com/ubuntu bionic-security InRelease [88,7 kB] Hit:6 http://hr.archive.ubuntu.com/ubuntu bionic InRelease Hit:7 http://ppa.launchpad.net/linuxuprising/java/ubuntu bionic InRelease Get:8 http://hr.archive.ubuntu.com/ubuntu bionic-updates InRelease [88,7 kB] Get:9 http://hr.archive.ubuntu.com/ubuntu bionic-backports InRelease [74,6 kB] Get:10 http://hr.archive.ubuntu.com/ubuntu bionic-updates/main amd64 DEP-11 Metadata [278 kB] Get:11 http://security.ubuntu.com/ubuntu bionic-security/main amd64 DEP-11 Metadata [204 B] Get:12 http://hr.archive.ubuntu.com/ubuntu bionic-updates/main DEP-11 48x48 Icons [66,6 kB] Get:13 http://hr.archive.ubuntu.com/ubuntu bionic-updates/main DEP-11 64x64 Icons [123 kB] Get:14 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 DEP-11 Metadata [20,8 kB] Get:15 http://security.ubuntu.com/ubuntu bionic-security/universe DEP-11 48x48 Icons [12,2 kB] Get:16 http://security.ubuntu.com/ubuntu bionic-security/universe DEP-11 64x64 Icons [50,1 kB] Get:17 http://hr.archive.ubuntu.com/ubuntu bionic-updates/universe amd64 DEP-11 Metadata [202 kB] Get:18 http://security.ubuntu.com/ubuntu bionic-security/multiverse amd64 DEP-11 Metadata [2 464 B] Get:19 http://hr.archive.ubuntu.com/ubuntu bionic-updates/universe DEP-11 48x48 Icons [190 kB] Get:20 http://hr.archive.ubuntu.com/ubuntu bionic-updates/universe DEP-11 64x64 Icons [346 kB] Get:21 http://hr.archive.ubuntu.com/ubuntu bionic-updates/multiverse amd64 DEP-11 Metadata [2 464 B] Get:22 http://hr.archive.ubuntu.com/ubuntu bionic-backports/universe amd64 Packages [3 468 B] Get:23 http://hr.archive.ubuntu.com/ubuntu bionic-backports/universe i386 Packages [3 460 B] Get:24 http://hr.archive.ubuntu.com/ubuntu bionic-backports/universe amd64 DEP-11 Metadata [7 352 B] Fetched 1 560 kB in 1s (1 177 kB/s) Reading package lists... Done user@hostname:~/Downloads>sudo apt-get install cuda ... *** LICENSE AGREEMENT *** By using this software you agree to fully comply with the terms and conditions of the EULA (End User License Agreement). The EULA is located at /usr/local/cuda-10.1/doc/EULA.txt. The EULA can also be found at http://docs.nvidia.com/cuda/eula/index.html. If you do not agree to the terms and conditions of the EULA, do not use the software. ... A modprobe blacklist file has been created at /etc/modprobe.d to prevent Nouveau from loading. This can be reverted by deleting the following file: /etc/modprobe.d/nvidia-graphics-drivers.conf A new initrd image has also been created. To revert, please regenerate your initrd by running the following command after deleting the modprobe.d file: `/usr/sbin/initramfs -u` ***************************************************************************** *** Reboot your computer and verify that the NVIDIA graphics driver can *** *** be loaded. *** ***************************************************************************** ... nvidia: Running module version sanity check. - Original module - No original module exists within this kernel - Installation - Installing to /lib/modules/4.15.0-29-generic/updates/dkms/ nvidia-modeset.ko: Running module version sanity check. - Original module - No original module exists within this kernel - Installation - Installing to /lib/modules/4.15.0-29-generic/updates/dkms/ nvidia-drm.ko: Running module version sanity check. - Original module - No original module exists within this kernel - Installation - Installing to /lib/modules/4.15.0-29-generic/updates/dkms/ nvidia-uvm.ko: Running module version sanity check. - Original module - No original module exists within this kernel - Installation - Installing to /lib/modules/4.15.0-29-generic/updates/dkms/ depmod... DKMS: install completed. ... #Post-installation Actions #Mandatory Actions #Environment Setup The PATH variable needs to include /usr/local/cuda-10.1/bin and /usr/local/cuda-10.1/NsightCompute-<tool-version>. <tool-version> refers to the version of Nsight Compute that ships with the CUDA toolkit, e.g. 2019.1. To add this path to the PATH variable: $ export PATH=/usr/local/cuda-10.1/bin:/usr/local/cuda-10.1/NsightCompute-2019.1${PATH:+:${PATH}} #Recommended Actions #Install Persistence Daemon user@hostname:~/Downloads/NVIDIA_CUDA-10.1_Samples>/usr/bin/nvidia-persistenced --verbose nvidia-persistenced failed to initialize. Check syslog for more details. #Install Writable Samples $ cuda-install-samples-10.1.sh <dir> user@hostname:~/Downloads>cuda-install-samples-10.1.sh /home/user/Downloads/ Copying samples to /home/jpext/Downloads/NVIDIA_CUDA-10.1_Samples now... Finished copying samples. drwxrwxr-x 12 jpext jpext 4096 ožu 11 09:38 NVIDIA_CUDA-10.1_Samples #Verify the Installation #Verify the Driver Version user@hostname:~/Downloads>cat /proc/driver/nvidia/version NVRM version: NVIDIA UNIX x86_64 Kernel Module 390.116 Sun Jan 27 07:21:36 PST 2019 GCC version: gcc version 7.3.0 (Ubuntu 7.3.0-27ubuntu1~18.04) #Compiling the Examples user@hostname:~/Downloads/NVIDIA_CUDA-10.1_Samples>nvcc -V nvcc: NVIDIA (R) Cuda compiler driver Copyright (c) 2005-2019 NVIDIA Corporation Built on Fri_Feb__8_19:08:17_PST_2019 Cuda compilation tools, release 10.1, V10.1.105 user@hostname:~/Downloads/NVIDIA_CUDA-10.1_Samples>make make[1]: Entering directory '/home/jpext/Downloads/NVIDIA_CUDA-10.1_Samples/0_Simple/simpleAssert_nvrtc' g++ -I../../common/inc -I/usr/local/cuda-10.1/include -o simpleAssert.o -c simpleAssert.cpp g++ -o simpleAssert_nvrtc simpleAssert.o -L/usr/local/cuda-10.1/lib64/stubs -lcuda -lnvrtc mkdir -p ../../bin/x86_64/linux/release cp simpleAssert_nvrtc ../../bin/x86_64/linux/release make[1]: Leaving directory '/home/jpext/Downloads/NVIDIA_CUDA-10.1_Samples/0_Simple/simpleAssert_nvrtc' make[1]: Entering directory '/home/jpext/Downloads/NVIDIA_CUDA-10.1_Samples/0_Simple/clock_nvrtc' g++ -I../../common/inc -I/usr/local/cuda-10.1/include -o clock.o -c clock.cpp g++ -o clock_nvrtc clock.o -L/usr/local/cuda-10.1/lib64/stubs -lcuda -lnvrtc mkdir -p ../../bin/x86_64/linux/release cp clock_nvrtc ../../bin/x86_64/linux/release make[1]: Leaving directory '/home/jpext/Downloads/NVIDIA_CUDA-10.1_Samples/0_Simple/clock_nvrtc' make[1]: Entering directory '/home/jpext/Downloads/NVIDIA_CUDA-10.1_Samples/0_Simple/simpleTexture' /usr/local/cuda-10.1/bin/nvcc -ccbin g++ -I../../common/inc -m64 -gencode arch=compute_30,code=sm_30 -gencode arch=compute_35,code=sm_35 -gencode arch=compute_37,code=sm_37 -gencode arch=compute_50,code=sm_50 -gencode arch=compute_52,code=sm_52 -gencode arch=compute_60,code=sm_60 -gencode arch=compute_61,code=sm_61 -gencode arch=compute_70,code=sm_70 -gencode arch=compute_75,code=sm_75 -gencode arch=compute_75,code=compute_75 -o simpleTexture.o -c simpleTexture.cu simpleTexture.cu: In function ‘void runTest(int, char**)’: simpleTexture.cu:178:75: warning: ‘cudaError_t cudaMemcpyToArray(cudaArray_t, size_t, size_t, const void*, size_t, cudaMemcpyKind)’ is deprecated [-Wdeprecated-declarations] checkCudaErrors(cudaMemcpyToArray(cuArray, ... ^ make[1]: Leaving directory '/home/jpext/Downloads/NVIDIA_CUDA-10.1_Samples/7_CUDALibraries/boxFilterNPP' Finished building CUDA samples #Running the Binaries user@hostname:~>cd Downloads/NVIDIA_CUDA-10.1_Samples/bin/x86_64/linux/release/ user@hostname:~/Downloads/NVIDIA_CUDA-10.1_Samples/bin/x86_64/linux/release>deviceQuery user@hostname:~/Downloads/NVIDIA_CUDA-10.1_Samples/bin/x86_64/linux/release>deviceQuery deviceQuery Starting... CUDA Device Query (Runtime API) version (CUDART static linking) cudaGetDeviceCount returned 100 -> no CUDA-capable device is detected Result = FAIL #After adding LD_LIBRARY_PATH & Vulkan drivers & reboot user@hostname:~>vi .profile export PATH=/usr/local/cuda-10.1/bin:/usr/local/cuda-10.1/NsightCompute-2019.1:.:$PATH export LD_LIBRARY_PATH=/usr/local/cuda-10.1/lib64:$LD_LIBRARY_PATH Terminal --> Profiles --> Command --> Enable "Run command as a login shell" user@hostname:~/Downloads/NVIDIA_CUDA-10.1_Samples/bin/x86_64/linux/release>deviceQuery deviceQuery Starting... CUDA Device Query (Runtime API) version (CUDART static linking) Detected 1 CUDA Capable device(s) Device 0: "Quadro M2200" CUDA Driver Version / Runtime Version 10.1 / 10.1 CUDA Capability Major/Minor version number: 5.2 Total amount of global memory: 4044 MBytes (4240179200 bytes) ( 8) Multiprocessors, (128) CUDA Cores/MP: 1024 CUDA Cores GPU Max Clock rate: 1036 MHz (1.04 GHz) Memory Clock rate: 2754 Mhz Memory Bus Width: 128-bit L2 Cache Size: 1048576 bytes Maximum Texture Dimension Size (x,y,z) 1D=(65536), 2D=(65536, 65536), 3D=(4096, 4096, 4096) Maximum Layered 1D Texture Size, (num) layers 1D=(16384), 2048 layers Maximum Layered 2D Texture Size, (num) layers 2D=(16384, 16384), 2048 layers Total amount of constant memory: 65536 bytes Total amount of shared memory per block: 49152 bytes Total number of registers available per block: 65536 Warp size: 32 Maximum number of threads per multiprocessor: 2048 Maximum number of threads per block: 1024 Max dimension size of a thread block (x,y,z): (1024, 1024, 64) Max dimension size of a grid size (x,y,z): (2147483647, 65535, 65535) Maximum memory pitch: 2147483647 bytes Texture alignment: 512 bytes Concurrent copy and kernel execution: Yes with 2 copy engine(s) Run time limit on kernels: Yes Integrated GPU sharing Host Memory: No Support host page-locked memory mapping: Yes Alignment requirement for Surfaces: Yes Device has ECC support: Disabled Device supports Unified Addressing (UVA): Yes Device supports Compute Preemption: No Supports Cooperative Kernel Launch: No Supports MultiDevice Co-op Kernel Launch: No Device PCI Domain ID / Bus ID / location ID: 0 / 1 / 0 Compute Mode: < Default (multiple host threads can use ::cudaSetDevice() with device simultaneously) > deviceQuery, CUDA Driver = CUDART, CUDA Driver Version = 10.1, CUDA Runtime Version = 10.1, NumDevs = 1 Result = PASS #Installing Vulkan root@hostname:~>apt install libvulkan1 mesa-vulkan-drivers vulkan-utils Reading package lists... Done Building dependency tree Reading state information... Done The following packages were automatically installed and are no longer required: libbsd0:i386 libdrm-amdgpu1:i386 libdrm-intel1:i386 libdrm-nouveau2:i386 libdrm-radeon1:i386 libdrm2:i386 libedit2:i386 libelf1:i386 libexpat1:i386 libffi6:i386 libgl1:i386 libgl1-mesa-dri:i386 libglapi-mesa:i386 libglvnd0:i386 libglx-mesa0:i386 libglx0:i386 libllvm7:i386 libnvidia-common-390 libpciaccess0:i386 libsensors4:i386 libstdc++6:i386 libwayland-client0:i386 libwayland-server0:i386 libx11-6:i386 libx11-xcb1:i386 libxau6:i386 libxcb-dri2-0:i386 libxcb-dri3-0:i386 libxcb-glx0:i386 libxcb-present0:i386 libxcb-sync1:i386 libxcb1:i386 libxdamage1:i386 libxdmcp6:i386 libxext6:i386 libxfixes3:i386 libxshmfence1:i386 libxxf86vm1:i386 Use 'apt autoremove' to remove them. The following NEW packages will be installed: libvulkan1 mesa-vulkan-drivers vulkan-utils 0 upgraded, 3 newly installed, 0 to remove and 15 not upgraded. Need to get 1 569 kB of archives. After this operation, 6 692 kB of additional disk space will be used. Get:1 http://hr.archive.ubuntu.com/ubuntu bionic-updates/universe amd64 libvulkan1 amd64 1.1.70+dfsg1-1ubuntu0.18.04.1 [93,4 kB] Get:2 http://hr.archive.ubuntu.com/ubuntu bionic-updates/universe amd64 mesa-vulkan-drivers amd64 18.2.2-0ubuntu1~18.04.2 [1 387 kB] Get:3 http://hr.archive.ubuntu.com/ubuntu bionic-updates/universe amd64 vulkan-utils amd64 1.1.70+dfsg1-1ubuntu0.18.04.1 [88,7 kB] Fetched 1 569 kB in 1s (1 837 kB/s) Selecting previously unselected package libvulkan1:amd64. (Reading database ... 150232 files and directories currently installed.) Preparing to unpack .../libvulkan1_1.1.70+dfsg1-1ubuntu0.18.04.1_amd64.deb ... Unpacking libvulkan1:amd64 (1.1.70+dfsg1-1ubuntu0.18.04.1) ... Selecting previously unselected package mesa-vulkan-drivers:amd64. Preparing to unpack .../mesa-vulkan-drivers_18.2.2-0ubuntu1~18.04.2_amd64.deb ... Unpacking mesa-vulkan-drivers:amd64 (18.2.2-0ubuntu1~18.04.2) ... Selecting previously unselected package vulkan-utils. Preparing to unpack .../vulkan-utils_1.1.70+dfsg1-1ubuntu0.18.04.1_amd64.deb ... Unpacking vulkan-utils (1.1.70+dfsg1-1ubuntu0.18.04.1) ... Setting up libvulkan1:amd64 (1.1.70+dfsg1-1ubuntu0.18.04.1) ... Setting up mesa-vulkan-drivers:amd64 (18.2.2-0ubuntu1~18.04.2) ... Setting up vulkan-utils (1.1.70+dfsg1-1ubuntu0.18.04.1) ... Processing triggers for libc-bin (2.27-3ubuntu1) ... Processing triggers for man-db (2.8.3-2ubuntu0.1) ...

 

In the next article I’ll describe how you can get all details from your nVidia graphical card that I’ll use to optimize CUDA GPU operations.

The post How to install CUDA Toolkit on Ubuntu 18.04 LTS appeared first on Josip Pojatina.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

CUDA, OpenCL and GPU programming in general are hot topic in today’s IT world.

Main reason for that is a rise of Data Science, Artificial Intelligence and Deep Science, and many new technologies that relies on it like self driving cars, robotics etc.

Traditionally main consumers of graphical cards are:

  • gaming industry
  • video editing professionals
  • graphical designers

Today, on nVidia web you can barely find something about classical consumer video cards, as the whole site aim at Data Science (Artificial Intelligence / Machine Learning / Deep Learning) and High Performance Computing (e.g. simulations) industry.

The article on the following link (taken from the ZDNet) describes nVidia’s plans in detail:

https://www.zdnet.com/article/nvidia-looking-to-surf-data-science-wave-into-the-datacentre/

With a boom in Data Science space, graphical cards are usual guest in all modern Data Centers around the world.

All big names in a server/data center market have already certified their products with powerful graphical card manufacturers.

In addition, all top 10 Supercomputers are based on GPU processing power, and all Cloud vendors are offering GPU instances in their portfolio.

Mainly due to inertia, it is expected that the same trend will follow enterprises in their Data Centers.

nVidia is one of the pioneers in exposing GPU power to developers, while other two manufacturers (AMD and Intel) are on the same path.

Although OpenCL (Open Computing Language) is an open, hardware agnostic approach (works on all – nVidia, AMD and Intel GPU while CUDA works only on nVidia GPU), mainly due to a maturity of nVidia drivers, developer tools, better support and performances, I’ll give a slight advantage to their CUDA technology.

To programming with CUDA, you need to know C, C++ or Python language (API is only available for those three languages).

In this article: How to install nvidia driver on Ubuntu, I’ll explain how to setup nvidia driver on Ubuntu to start your CUDA tuning journey.

First decision you should make is about the operating system.

If you are planning to use CUDA for Data Science stuff, I suggest to use Ubuntu as it’s dominant platform on server side computing in a Cloud.

See more at the following two links:

https://thecloudmarket.com/stats#/by_platform_definition

and

https://www.zdnet.com/article/ubuntu-linux-continues-to-rule-the-cloud/

If you enable “install third party proprietary drivers” option during the Ubuntu installation, Ubuntu nvidia drivers should already be installed on your system and there is nothing else to do.

In case you miss to do that or you are installing Ubuntu Server without GUI, it’s easy to install drivers later by executing the following command:

#1. Delete installed Nvidia drivers

sudo apt-get purge nvidia*

#2. Head to the following page to check the latest driver version: 
https://www.nvidia.com/object/unix.html

#Linux x86_64/AMD64/EM64T
#Latest Long Lived Branch Version: 418.56

#3. Add nVidia repo
sudo add-apt-repository ppa:graphics-drivers

#4. Update your system 
sudo apt-get update

#5. Install appropriate nvidia driver: 
sudo apt-get install nvidia-XYXYX

#As of 22nd March 2019, driver you need to install is: 
sudo apt-get install nvidia-418.56


#6. Check if your system is using nouveau open source driver: 
lsmod | grep nouveau


#If nouveau is in use, you need to disable it before reboot. 

#Nouveau disabling 
user@hostname:/etc/modprobe.d# bash -c "echo blacklist nouveau > /etc/modprobe.d/blacklist-nvidia-nouveau.conf"
user@hostname:/etc/modprobe.d# bash -c "echo options nouveau modeset=0 >> /etc/modprobe.d/blacklist-nvidia-nouveau.conf"

user@hostname:/etc/modprobe.d# cat /etc/modprobe.d/blacklist-nvidia-nouveau.conf
blacklist nouveau
options nouveau modeset=0



#7. Reboot
reboot


#8. Check your driver installation: 

nvidia-smi

#or 

lsmod | grep nvidia

In case you’ll need to find out driver version of your nVidia card, this is how you can check it:

user@hostname:~>nvidia-smi 
Fri Mar 22 08:20:39 2019       
+-----------------------------------------------------------------------------+
| NVIDIA-SMI 418.43       Driver Version: 418.43       CUDA Version: 10.1     |
|-------------------------------+----------------------+----------------------+
| GPU  Name        Persistence-M| Bus-Id        Disp.A | Volatile Uncorr. ECC |
| Fan  Temp  Perf  Pwr:Usage/Cap|         Memory-Usage | GPU-Util  Compute M. |
|===============================+======================+======================|
|   0  Quadro M2200        Off  | 00000000:01:00.0  On |                  N/A |
| N/A   45C    P0    N/A /  N/A |   1654MiB /  4043MiB |      0%      Default |
+-------------------------------+----------------------+----------------------+
                                                                               
+-----------------------------------------------------------------------------+
| Processes:                                                       GPU Memory |
|  GPU       PID   Type   Process name                             Usage      |
|=============================================================================|
|    0      1925      G   /usr/libexec/Xorg                             89MiB |
|    0      2108      G   /usr/bin/gnome-shell                          44MiB |
|    0      2486      G   /usr/libexec/Xorg                            776MiB |
|    0      2611      G   /usr/bin/gnome-shell                         323MiB |
|    0      2861      G   ...are/jetbrains-toolbox/jetbrains-toolbox   401MiB |
|    0      2876      G   cairo-dock                                    12MiB |
+-----------------------------------------------------------------------------+


user@hostname:~>cat /proc/driver/nvidia/version 
NVRM version: NVIDIA UNIX x86_64 Kernel Module  418.43  Tue Feb 19 01:12:11 CST 2019
GCC version:  gcc version 8.3.1 20190223 (Red Hat 8.3.1-2) (GCC) 

#or you can use the following command to get the same info 
user@hostname:~>modinfo nv

This is the first part of CUDA-GPU tuning series I’ll use extensively as a basis for future articles.

In the next post I’ll describe how to install nVidia Toolkit to have your system ready for a Data Science tasks.

The post How to install nvidia driver on Ubuntu 18.04 LTS appeared first on Josip Pojatina.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this article I’ll describe how you can automate previously created Unit Test.

I believe that many of the Oracle SQL Developer users use only core functionality of the tool, and have never become familiar with its more advanced features.

I’ve already describes in several articles some of its advanced features (you can use search functionality of this site).

Here I’ll described command line interface of SQL Developer.

You shouldn’t mix SQL Developer command line interface (sdcli) with SQLcl which is basically SQL*Plus on steroids.

 

SQL Developer sdcli command line interface allows you to automate many boring tasks that can be also scheduled.

Here I’m going to use sdcli to automate test that I’ve created in the previous article.

If you run just sdcli, you’ll get all available options you can execute from the command line.

test@test.com:/opt/sqldeveloper/sqldeveloper/bin>./sdcli

Oracle SQL Developer
Copyright (c) 2005, 2018, Oracle and/or its affiliates. All rights reserved.

Available features:
cart: Database Cart Batch Tasks
dba: Basic Batch DBA Tasks
format: Format Task
migration: Database Migration Tasks
reports: Basic Batch Reporting Tasks
unittest: Unit Testing Batch Tasks
utility: Utility Import Task

In this case I’m interested for “unittest” option, which is for automating Unit Testing Batch Tasks.

I’ll execute sdcli with unittest option to get all available options.

test@test.com:/opt/sqldeveloper/sqldeveloper/bin>./sdcli unittest

Oracle SQL Developer
Copyright (c) 2005, 2018, Oracle and/or its affiliates. All rights reserved.


unittest ?
unittest -run ?
unittest -exp ?
unittest -imp ?

Command Completed.

For more information about Unit Test command line interface, I’ll execute the following command:

test@test.com:/opt/sqldeveloper/sqldeveloper/bin>./sdcli unittest -run ?

Oracle SQL Developer
Copyright (c) 2005, 2018, Oracle and/or its affiliates. All rights reserved.


unittest -run -test (-id <id>|-name <name>} -repo <connection name>
	 -db <connection name> {-return <return id>} {-log <0,1,2,3>}
unittest -run -suite (-id <id>|-name <name>} -repo <connection name>
	 -db <connection name> {-return <return id>} {-log <0,1,2,3>}

To run my Unit Test, i need to find my test name (parameter -name), repo name (-repo switch) and database connection name (-db parameter).

Optionally I can specify log level, where the level 3 will return the most information about the test execution.

Parameter -db is clear. It’s my demo user connection which is in my case unit_test_demo.

Be careful! -db is connection name in SQL Developer, not the database user/schema name.

The same apply for the -repo parameter, where you need to specify repo connection name (not the repo database user/schema name).

The easiest way to find out -name is to export my Unit Test into a XML file, open that file and search for “name=” string.

In this case, name parameter is “ORDER_MANIPULATION.INSERT_ORDER” that you can check from the following XML file:

<?xml version = '1.0' encoding = 'windows-1250'?>
<UT>
   <object >
      <test id="b0868eac-6483-4bef-9493-8038e64f506a" name="ORDER_MANIPULATION.INSERT_ORDER" obj_name="ORDER_MANIPULATION" obj_owner="DEMO" obj_type="PROCEDURE" obj_call="INSERT_ORDER" src_conn_name="IdeConnections%23orcl18c_unit_test_demo" coverage="false">
         <arg id="1542180f-2e0c-4135-a28d-99f4a0ab0e52" owner="DEMO" object_name="INSERT_ORDER" package_name="ORDER_MANIPULATION" object_id="76186" argument_name="P_ITEM_ID" position="1" sequence="1" data_level="0" data_type="NUMBER" in_out="IN" data_length="22" radix="10" pls_type="NUMBER" char_length="0" char_used="0"/>
         <arg id="83be572d-4dcc-4b07-9147-bfab2daa9221" owner="DEMO" object_name="INSERT_ORDER" package_name="ORDER_MANIPULATION" object_id="76186" argument_name="P_ACT_ID" position="2" sequence="2" data_level="0" data_type="NUMBER" in_out="IN" data_length="22" radix="10" pls_type="NUMBER" char_length="0" char_used="0"/>
         <startup id="048cbece-97a3-41ff-b510-02390191c326">
            <startup>
               <class>oracle.dbtools.unit_test.startup.TableCopyStartup</class>
               <name>Table or Row Copy</name>
               <description>"TMP$ORDER_MANIPULATION.INSERT_" := SELECT ROWID AS SRC$ROWID, s.* FROM "ORDERS" s </description>
               <source>"ORDERS"</source>
               <predicate/>
               <temp>"TMP$ORDER_MANIPULATION.INSERT_"</temp>
            </startup>
         </startup>
         <teardown id="2ba26a6f-aac3-4033-af81-c2a2394373dc">
            <teardown>
               <class>oracle.dbtools.unit_test.teardown.TableRestoreTeardown</class>
               <name>Table or Row Restore</name>
               <description>"TMP$ORDER_MANIPULATION.INSERT_" -> "ORDERS"</description>
               <source>"ORDERS"</source>
               <key>PRIMARY KEY</key>
               <temp>"TMP$ORDER_MANIPULATION.INSERT_"</temp>
               <predicate/>
               <dropTempTable>yes</dropTempTable>
            </teardown>
         </teardown>
         <test_impl id="7e41ac90-26ca-4308-b3bd-0305c9f487f7" test_id="b0868eac-6483-4bef-9493-8038e64f506a" name="Test Implementation 1" expected_ret="Exception" expected_err="20001">
            <parm argId="83be572d-4dcc-4b07-9147-bfab2daa9221" utiId="7e41ac90-26ca-4308-b3bd-0305c9f487f7" input_value="<?xml version = '1.0' encoding = 'UTF-8'?>
<DataValue name="P_ACT_ID">9999</DataValue>" test_out_val="true"/>
            <parm argId="1542180f-2e0c-4135-a28d-99f4a0ab0e52" utiId="7e41ac90-26ca-4308-b3bd-0305c9f487f7" input_value="<?xml version = '1.0' encoding = 'UTF-8'?>
<DataValue name="P_ITEM_ID">9999</DataValue>" test_out_val="true"/>
         </test_impl>
      </test>
   </object>
</UT>

Now when I know all parameters, I can safely execute my first Unit Test from the command line interface.

test@test.com:/opt/sqldeveloper/sqldeveloper/bin>./sdcli unittest -run -test -name "ORDER_MANIPULATION.INSERT_ORDER" -repo unit_test_demo -db unit_test_demo -log 3

Oracle SQL Developer
Copyright (c) 2005, 2018, Oracle and/or its affiliates. All rights reserved.

d2528666-0d81-4078-a50f-9f8345d67df9
UT_SUCCESS
null
Command Completed.

As you can see, the Unit Test has passed.

 

Summary:

Oracle SQL Developer is really great tool for performing many types of various tasks.

On the first view it looks like a pretty rudimentary piece of software, but actually it’s a beast when you look at it under the hood.

When you compare SQL Developer with its competitors, I’m not aware of any similar tool with such advanced features that is completely free.

One of such features allows me to execute complete unit test from the command line.

I can execute not only the single test, but the series of tests called suites as well.

The whole process can be automated, and just that one feature can reduce a substantial amount of time for testing.

With such an extensive number of options and automation that you can define, you can’t find an excuse any longer not to start using the Unit Tests, especially because you won’t need to ask anyone to approve the budget as all those features you have for free.

The post Starting with Oracle Unit Testing Framework – Part 3 Automation appeared first on Josip Pojatina.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

This is a second part of the Oracle Unit Testing Framework post.

In the first part I’ve created Unit Test repository and prepare everything for testing.

Details you can find on the following link:

https://www.josip-pojatina.com/en/starting-with-oracle-unit-testing-framework-part-1/

 

In this part I’ll perform actual test and show some of the numerous options that you have on disposal.

I’ll be using Oracle Demo schema that you can install by using one of the available Oracle scripts.

Details of how to install sample schemas you can find on the following link:

https://oracle-base.com/articles/misc/install-sample-schemas

 

I’ve also created a demo user with ORDERS table from the OE sample schema and simple package which will INSERT / DELETE orders.

First right click on Tests to create a new Unit test (I assume you already enable Unit Test window by clicking on the View –> Unit Test).

Alternative way of creating an Unit Test is to directly right-click on the procedure inside the package, and to choose Create Unit Test as described in the following figure.

In the next step you need to choose a connection (like demo or scott) that has rights to perform Unit tests.

Instructions of how to enable users to perform Unit Tests has been provided in the first part.

In the next step you can name your Unit Test.

I used the package + procedure name.

In this example it’s ORDER_MANIPULATION.INSERT_ORDER_EXCEPTION

Besides selecting the name of the test, you can choose between the following two options:

  • Create with a single Dummy implementation
  • Seed/Create implementations using lookup values

First option will create a single one-off implementation of an Unit Test, while the later one will create multiple implementations based on the lookup values.

Here I’ll leave default values and click on the next button.

Startup process

In the Specify Startup step, you can manage Startup actions (actions you want to perform before starting each test).

If you click on the + button, you’ll have a three options:

  • None
  • Table or Row Copy
  • User PL/SQL Code
Startup Process choice

Here I’ll choose Table or Row Copy as shown in the above figure.

New screen will pop-up where I can choose table that will be backed up before starting the Unit Test and to publish my Startup script to Library.

By publishing to Library, next time I can just pick my Startup script without choosing teh table again.

Another benefit of publishing is your Startup script becomes available to all other developers.

In the next step – Specify Parameters, you can specify the input parameters for the test.

Here you have a bunch of options and it’s not possible to cover them all.

You can use Default under the Lookup Category, or you can create your own lookup values.

As I didn’t create my own lookup category, I’ll use the default one.

Under the Expected results you can choose between two options:

  • Success
  • Exception

I’ve chosen Exception under the expected output, as two input parameters have value of 9999.

Thus I’m deliberately raise an exception, as such values don’t exists in the Oracle Sample schema.

Exception number I’ve set is 20001 (custom exception handling range is from -20000 to -20999).

Specify Parameters

In the next step you can Specify Validation.

Here you can observe a new seven options that I’ll list for a sake of completeness but I’ll won’t go into the detail, as complete coverage of the Unit Test would require an entire book.

Here are options that you have:

  • Boolean function
  • Compare Query Results
  • Compare Tables
  • None
  • Query returning no row(s)
  • Query returning row(s)
  • User PL/Sql Code

To simplify the whole process I’ll choose None and proceed.

In the last step – Teardown, there are also several options you can choose from.

  • None
  • Table Drop
  • Table or Row Restore
  • User PL/Sql Code
Teardown

You can choose Table or Row Restore as we defined backup table in the first step.

As you can observe, within each option there are many sub-options you can choose from.

 

The last screen is a Summary where you can inspect all steps.

After pressing a Finish button, you can view all created objects in the Unit Test Window.

Now you can double click on Test Implementation 1 to open a new window.

By pressing a Run button, you can finally start the test.

As you can see, test has passed.

Here is a list of database tables where test results have been stored.

You can find them within the repo schema.

Summary:

With this article I’ve barely scratch the surface, as the whole book can be written about Oracle Unit Test.

Still I hope you get some filling of what is possible and how powerful SQL Developer, as your free interface to perform Unit Testing, actually is.

There are many more advanced topics such as:

  • dynamic tests
  • exporting and importing tests among developers
  • version control
  • SQL Developer Command Line Interface (sdcli) for test automation
  • creating and running a suite of tests

I’ll leave that for some future article.

The post Starting with the Oracle Unit Testing Framework – Part 2 appeared first on Josip Pojatina.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

At the last regional Java and Open Source conference where I held the presentation named:

“Java Mission Control now in OpenJDK – best practice”, several questions have been raised about a way of testing JEE apps, influence of a GC (Garbage Collector) on the overall performance, new GC engines etc.

With OpenJDK 11 we have a new toy called Z Garbage Collector, while there are plenty of others still available like G1 etc.

Here are the questions that I’m going to answer:

 

  1. Is the load/stress test executed by using tool X realistic?

    Simple answer is: No.

    First you need to understand why are you using some GC, and check if you have a problem that can be solved by changing GC.

    Enterprise architecture consists of a many components.

    Even if you leave Big Data/ Machine Learning stack on side, you still have a many app servers in cluster, and on each server you have several JEE applications.

    You have to add ESB integration (either on the same server or on a separate domain) and keep in mind that each application have a different behaviour.

    All in all it will be very difficult and time consuming to create a realistic test.

    Load / stress tests are the most useful when you are targeting very specific business process and where the scope of testing is focused only on components that are involved to complete that process.

     

  2. What is the GC impact on overall performance in a real system?

    Instead of answering on that question, you should better take a look at the next picture taken from the OpenJDK Mission Control session that I’ve presented on the conference.

 As you can observe, in 35 minutes (duration of the test), less than 1.5 second has been spent on GC.

Thus even if you replace the GC with a “better one”, in this case the impact will be negligible.

 

Summary:

The most important knowledge, in case you want to build your career towards performance tuning, is to know how to interpret the results you are getting from some tool.

Today there are many tools available with different strengths and weakness, and it shouldn’t be difficult to get performance data out, but don’t be pushed in the trap describes by an old wisdom: “A fool with a tool is just a fool”.

Thus tool might help you with some graphics and automation of a boring stuff, but to interpret the results and fix the problem you need to have a knowledge of how technology is working under the hood, and that involves knowing of system & software architecture along with all individual components.

The post Impact of the JVM Garbage Collector on the realistic workload appeared first on Josip Pojatina.

Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview