Créer un trigger sous oracle

-- -- Trigeer de mise à jour des formation et module -- create or replace TRIGGER "UPDATE_MODULE_FORMATION" AFTER INSERT OR UPDATE ON ACTION_FORMATION FOR EACH ROW WHEN (new.id_action_formation>0) DECLARE v_id_action_formation integer; v_id_action_formation_parent integer; BEGIN v_id_action_formation:= :New.id_action_formation; v_id_action_formation_parent:= :New.id_action_parent; IF v_id_action_formation_parent is not null THEN update action_formation set formation = (SELECT libelle FROM action_formation where id_action_formation=v_id_action_formation_parent) where id_action_formation=v_id_action_formation; update action_formation set module = (SELECT libelle FROM action_formation where id_action_formation=v_id_action_formation_parent) where id_action_formation=v_id_action_formation; else update action_formation set formation = libelle where id_action_formation=v_id_action_formation; update action_formation set module = null where id_action_formation=v_id_action_formation; end if; END;

Inline procedure oracle

DECLARE
v_id_parent integer;
v_id integer;
CURSOR CUR_ACTION_FORMATION IS
SELECT id_action_formation,id_action_parent FROM action_formation;
BEGIN
   OPEN CUR_ACTION_FORMATION;
   LOOP
      FETCH CUR_ACTION_FORMATION INTO v_id, v_id_parent;
  --    dbms_output.put_line(v_id_parent);
    update action_formation set crf_diffuseur = (SELECT max(id_centre) FROM centre_action_organisme
    where id_action_formation=v_id) where id_action_formation=v_id;
      EXIT WHEN CUR_ACTION_FORMATION%NOTFOUND;
   END LOOP;
   commit;
   CLOSE CUR_ACTION_FORMATION;
END;

Procedure stockee ORACLE

Pour rendre le debug possible:

set serveroutput on;

Voici une procedure stockée:

CREATE OR REPLACE PROCEDURE PROC_FILL_PARENT AS
v_id_parent integer;
v_id_grand_parent integer;
v_id integer;
CURSOR CUR_FILL_PARENT IS
SELECT id_action_formation,id_action_parent FROM action_formation;
BEGIN
   OPEN CUR_FILL_PARENT;
   LOOP
      FETCH CUR_FILL_PARENT INTO v_id, v_id_parent;
  --    dbms_output.put_line(v_id_parent);
  if(v_id_parent is not null) then
    dbms_output.put_line('update action_formation set id_action_grand_parent=(SELECT id_action_parent FROM action_formation where id_action_formation='||v_id_parent||') where id_action_formation='||v_id);
    update action_formation set id_action_grand_parent=(SELECT id_action_parent FROM action_formation where id_action_formation=v_id_parent) where id_action_formation=v_id;
  end if;
    commit;
      EXIT WHEN CUR_FILL_PARENT%NOTFOUND;
   END LOOP;
   CLOSE CUR_FILL_PARENT;
END;

Optimize hibernate with JMX bean in tomcat application server

In order to optimize your web application application, you shall always profile the database layer to see what are the bottle necks. You can do this by using the hibernate JMX component org.hibernate.jmx.StatisticsService (providing that you use the hibernate session factory)

In your tomcat, add the line:

JAVA_OPTS=-Dcom.sun.management.jmxremote to unable JMX bean

In your spring context file add the lines:

<bean id="exporter" class="org.springframework.jmx.export.MBeanExporter" lazy-init="false">
 <property name="beans">
  <map>
   <entry key="bean:name=hibernateStatBean" value-ref="hibernateStatBean"/>
  </map>
 </property>
</bean>

<bean id="hibernateStatBean" class="org.hibernate.jmx.StatisticsService">
 <property name="sessionFactory" ref="sessionFactory"/>
</bean>

  
In the hibernate spring configuration file add the following property to enable the statistics:

<bean id="hibernateProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
 <property name="properties">
  <props>
   <prop key="hibernate.show_sql">false</prop>
   <prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
   <prop key="hibernate.generate_statistics">true</prop>            
  </props>
 </property>
</bean>

If your tomcat application server starts without error, you can now observe hibernate parameters using JVisualVM utility bundled withi the JDK.

You must unable the JMX plugin in the menu Tools --> Plugin --> Available Plugins tab
install VisualVM-MBeans. You should have the following result.

Then goto the MBeans tab and choose the JMX bean HibernateStatBean.
The observed parameters are displayed in the Attributes tabs after you set statisticsEnabled to true and presh the refresh button in the attribute tab.

You may reset the MBean in the operation tab.


Some key parameters are:

StatisticsEnabled : you should check that it is set to true.
TransactionCount: Number of started transactions
QueryExecutionCount: Number of queries
QueryExecutionMaxTimeQueryString: Give your the request taking most time. The bottleneck.
EntityLoadCount: Give you the number of entity loaded