tag:blogger.com,1999:blog-5309604992043123290.post6429009970145861295..comments2024-03-24T11:22:17.354+01:00Comments on berxblog: comparison between SQL*TRACE and ASH - get the right informationMartin Bergerhttp://www.blogger.com/profile/16504572924713610305noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-5309604992043123290.post-75137023974982652152020-06-22T15:52:37.284+02:002020-06-22T15:52:37.284+02:00Hi Mikhail,
thank you for your great comment.
I ...Hi Mikhail, <br />thank you for your great comment. <br />I like the way, ECID is handed back & forth between applications and databases to indicate an end-to-end flow. <br />Unfortunately, it's not written into SQL*TRACE right now. <br />That's probably the reason, Cary didn't mention it in his list ;-) <br />There is an entry in <b>Database Ideas</b> section of Oracles forum: <a href="https://community.oracle.com/ideas/24076" rel="nofollow">Write ECID to the tracefile when enabling Oracle Extended Trace</a>. <br />When Oracle ever decides to implement it, ECID can be used in both areas: SQL*Trace and ASH/AWR. <br /> best regards, <br /> MartinMartin Bergerhttps://www.blogger.com/profile/16504572924713610305noreply@blogger.comtag:blogger.com,1999:blog-5309604992043123290.post-10377452506087804092020-06-21T21:12:01.849+02:002020-06-21T21:12:01.849+02:00Hi Martin,
You wrote:
If the application set a d...Hi Martin,<br /><br />You wrote:<br /><br />If the application set a distinct client ID for each business task execution ...<br /><br />I have seen such applications. I consider it is very unnatural and I highly discourage such usage. Why is that? Oracle already provides the Execution Context ID to do the same. That is what I have been using in the shops I have worked for.<br />It is exposed in GV$SESSION, GV$ACTIVE_SESSION_HISTORY:<br /><br />SQL> declare<br /> 2 v_ecid raw(16) := sys_guid();<br /> 3 v_count int;<br /> 4 begin<br /> 5 dbms_session.set_context('E2E_CONTEXT', 'ECID_UID', v_ecid);<br /> 6 -- run something<br /> 7 select count(*)<br /> 8 into v_count<br /> 9 from dba_objects o1,<br /> 10 dba_objects o2<br /> 11 where rownum <= 1e8;<br /> 12 dbms_session.set_context('E2E_CONTEXT', 'ECID_UID', null);<br /> 13 dbms_output.put_line(v_ecid);<br /> 14 end;<br /> 15 /<br />A89D7CE0FD3C2F66E053B51F13ACE87E<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> select count(*) from v$active_Session_history where ecid='A89D7CE0FD3C2F66E053B51F13ACE87E';<br /><br /> COUNT(*)<br />----------<br /> 6<br /><br /><br />JDBC applications can set it in addition to other E2E attributes you mentioned: MODULE, ACTION, CLIENT_ID - it would not require an extra network roundtrip which looks especially appealing.<br /><br />Apparently, the ECID functionality was initially introduced for application servers since I remember it can be used as a search parameter in OC4J Application servers.<br /><br />My current shop uses APEX extensively and I asked our developers to specifically identify all business operations with an ECID - a business operation is an application level term and it can include a plenty of database operations, such as SELECT, INSERT, etc.. It would look a teensy bit clunky to use CLIENT_ID for that. I would rather see those CLIENT_ID as APEX populates them by default which is something like: 'SCOTT:123456' in which 'SCOTT' is a username, '123456' is an APEX session ID.<br /><br />The ECID is a natural choice when an end-to-end flow control is required. For instance, say you have a classic three tier architecture. ECID can be generated on the application server side and all database calls can contain it. When something goes awry and a post-morted analysis is requested, it is enough to know the ECID of a problem operation to see where it spent most time - the app server vs the DB server.<br /><br />I used it in one shop to address users complaining about performance - poor performance from user's perspective is not always a database issue - ECIDs can be used to identify what components contribute to the most time.<br />A further extension of this idea is to generate ECIDs as close to the end-users as possible, i.e. in JavaScript, and pass it through all the layers up to the database.<br /><br />On the other day, I implemented an automatic performance analysis tool based on those ECIDs. The stakeholders wanted to improve user experience and they asked me to identify any database operations of online users that took longer than 5 seconds. It was a breeze since I was already using ECIDs to answer intermittent questions related to unusual application slowdowns and higher than expected latencies.<br />Obviously, those ECIDs were generated at the app server first and passed to the database, which was called 'blackbox' by the app server team. ECIDs allowed to spot the light on what database was doing and cross-reference it with the app server logs - ECIDs were written there as well and allowed to connect the dots between the DB and the App server.<br /><br />I think ECID is one of the most underrated features in Oracle database.<br />There may be the instrumentation maturity grade somewhere that could look as below:<br />1. no services or the default one - SYS$USERS<br />2. dedicated services are used<br />3. as above + modules<br />4. as above + actions<br />5. as above + client_ids<br />6. as above + ECIDs<br /><br />CLIENT_INFO and Long-Ops is not in the list but it is also nice to have, however, they are not in ASH.<br /><br />Regards,<br />Mikhail.<br />Mikhail Velikikhhttps://www.blogger.com/profile/12809585068205118395noreply@blogger.com