RSS Feed

Method R Discussion Group

Hi,
I just bought the OOP book recently. And I tried the method R from our data warehouse server and I was successful in gathering the stats. The method R was very efficient in collecting the right information to improve the performance of the database warehouse. So, I decided to use the method R in our JDE ERP 8.0 test environment. I used the Toad program to collect the session id and serial number as suggested in the OOP book. I noticed once I login in JDE Html there are several sessions created. And the worst thing I found is I cannot exactly locate my session using my JDE user id. So, I used the pc clock time to identify my session. However, I still need to find the right session using Toad Browser session and find the session using the correct SQL statement for a particular module in the system (i.e. Enter Sales Order). Another problem is once I started tracing the user action and closing JDE Html I noticed again that the session is not disconnected. I used Toad to disconnect the session and stop the trace but unfortunately my trace file doesn’t have the STATS line which is also very helpful to me.

If you have some experience in dealing with this kind of problem, I would appreciate if you could give me some information on how to get the right session and STATS line in the trace file.

All the best to HOTSOS.COM

JDE's erp solution uses connection pooling. We have no ideal solution for collecting trace data for a business task when connection pooling is enabled. OOP discusses some isolation techniques that allow one to collect trace data but these techniques should be considered sufficient only for unit tests.

What's the version of the database. If you are on 10g and can find out a way to set the client_identifier to reflect the users name, then you can use dbms_monitor to start tracing. Does JDE set anyhting in the v$session client_identifier column? How about the client_info column in v$session and if so maybe a wrapper could be created to set to execute a dbms_session.set_identifier package and set a client identifier. At minimum, I have a trigger that I like to use to set a default client_identifier that might help you turn tracing on somewhat coarse level of granularity. However, this method only works if you are on 10g or greater. On less than 10g you will need to go through some pain to setup a special login where you can trace the end user on a dedicated connection. Here is the default trigger:
====================
CREATE OR REPLACE TRIGGER sys.client_id_logon_trg
  AFTER LOGON ON DATABASE
DECLARE
  my_service      SYS.V_$SESSION.SERVICE_NAME%TYPE;
  my_clientid    SYS.V_$SESSION.CLIENT_IDENTIFIER%TYPE;
  my_ip_address  SYS.V_$SESSION.TERMINAL%TYPE;
  my_os_user      SYS.V_$SESSION.OSUSER%TYPE;
  my_audsid      SYS.V_$SESSION.AUDSID%TYPE;
  my_program      SYS.V_$SESSION.PROGRAM%TYPE;
  CLIENT_ID_DELIM CHAR(1) := '~';
BEGIN
  IF USER NOT IN ('SYS') AND USER IS NOT NULL THEN
    my_clientid := SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER');
    IF my_clientid IS NULL THEN
      my_service    := SYS_CONTEXT('USERENV', 'SERVICE_NAME');
      my_ip_address := NVL(SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
                          SYS_CONTEXT('USERENV', 'TERMINAL'));
      my_os_user    := SYS_CONTEXT('USERENV', 'OS_USER');
      my_audsid    := TO_NUMBER(SYS_CONTEXT('USERENV', 'SESSIONID'));
      SELECT PROGRAM
        INTO my_program
        FROM SYS.V_$SESSION
      WHERE AUDSID = my_audsid
        AND ROWNUM = 1;
      DBMS_SESSION.SET_IDENTIFIER(my_os_user || CLIENT_ID_DELIM ||
                                  my_ip_address || CLIENT_ID_DELIM ||
                                  my_program || CLIENT_ID_DELIM ||
                                  my_service);
    END IF;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('client_id_logon_trg: Exception thrown');
END client_id_logon_trg;
======================
Hope this helps.
Regards,
Eric

Hi Jeff and Eric,
Thanks for your quick response.  Accept my apologies for forgetting the database info. We have Oracle 8.1.7 running on Windows 2000 server OS using an HP machine. Jeff, you are very right that JDE is using a connection pooling which is very visible when I use JDE html. I already tried several techniques and approach to trace the right session from JDE html as discussed in the book. However, when I use JDE fat client there is only one session created and I can definitely use the fat client instead of the html version to trace any particular session now. Thanks to your advices, wisdoms and sharing your expertise I have successfully test and trace a JDE session completely.

Eric, thank you also for sharing some of your great ideas to trace a session but like you said your script is best for Oracle 10g or higher only. But it would be exciting on my part if I could get your script running on 8i. And I hope someday we could find a better solution to trace a session from a JDE html version from Oracle 8i.

My only questions now are:
1.    My users are using JDE html version from their respective workstation and if I install a fat client in their workstation and use it for data gathering, can you sense at least any difference in gathering the data or a problem that I would encounter in optimizing the performance?
2.    My previous test with JDE html is not 100% complete bec I have no STATS line but I found lots of call from SQL* Net Message from Client from the trace file. Would it be sufficient to conclude that we have a problem with our connection pooling? Please see below some of the data I gathered. Btw, I use OraSRP to create the resource file.

Event            % Time    Seconds    Calls     
SQL*Net message  10.1%      25.9100s    1,548       
from client
SQL*Net message  2.0%        5.1500s    46             
from client [idle] 
EXEC calls [CPU]       1.1%        2.9100s    629           
...removed some lines...

I would love to purchase hotsos.com products but that would be my next big project to persuade my boss to invest on your products for the benefit our business. Maybe you could help me also in this area.

All the best to hotsos.com and more power!

Regards,
Ryam

If you have a "properly" scoed trace file then it could be a lot of round trips with most of the time being spent outside the database. Without looking at the skew it would be hard to determine if there is one SQL*Net message from client that lasted 25 seconds followed by 1,547 very fast round trips. Not sure what that "SQL*Net message from client [idle]" is. If it is think time then the threshold is pretty small since you can squeeze 46 calls in 5 seconds. Looks like you would want to spend your time reducing the round trips. BTW, you could still modify the trigger to have it set client_identifier at a minimum it will make looking at the v$session more interesting and future proof you if you upgrade to 10g.

Hi Eric,
You guess it right. I was just thinking if I could at least reduce the time of round trips outside the database it would help the performance.Anyway, I was reviewing your above script and I found that the client_identifier column is not existing in V$Session.Maybe I just missed something here that I really don't understand with your trigger. Sorry for the ignorance.

Thank you for your valuable time!