[Search for users] [Overall Top Noters] [List of all Conferences] [Download this site]

Conference orarep::nomahs::dectrace_v20

Title:DECtrace V2.0 and All-in-1 Perf Rpts conf.
Notice:Kits+Doc, 2 | Patches, 3
Moderator:OMYGOD::LAVASH
Created:Mon Apr 26 1993
Last Modified:Mon Jun 02 1997
Last Successful Update:Fri Jun 06 1997
Number of topics:467
Total number of notes:2058

448.0. "Linking REQUEST_ACTUAL with EPC$1_253_PROCEDURE_CALL" by BROKE::BASTINE () Fri Mar 07 1997 11:59

I have another question from a customer.  He is using ACMS and would like
to link the REQUEST_ACTUAL table with EPC$1_253_PROCEDURE_CALL table.  He is 
having trouble finding a column to use in each that would work as a key to 
linking them together.  He specified using the transaction starts and ends,
but wasn't sure that would work... are there any columns that would work 
to link them together?

Thanks,
Renee
T.RTitleUserPersonal
Name		DateLines
448.1Have them try thisOOTOOL::CRAIGMon Mar 10 1997 11:3115
	Hi Renee,

	I found this in note 253.1. George said it looked feasible.
	Have them try this and let us know if it meets his needs (and works).
 
        SELECT CROSS_FAC_2_START, CROSS_FAC_7_START, SQL_ID
            FROM EPC$1_221_REQUEST_ACTUAL R, EPC$1_221_TRANSACTION T
            WHERE R.TRANS_ID_END    = T.TRANS_ID_START   AND
                  R.STREAM_ID_END   = T.STREAM_ID_START  AND
                  R.IMAGE_RECORD_ID = T.IMAGE_RECORD_ID  AND
              CROSS_FAC_2_START = 7 AND  CROSS_FAC_7_START = 1
            GROUP BY CROSS_FAC_2_START, CROSS_FAC_7_START, SQL_ID;

	Thanks,
	Sheri
448.2Thanks, but not sure that'll work...BROKE::BASTINEMon Mar 10 1997 11:5815
Hi Sheri,


   I saw this too, but this is crossing the REQUEST_ACTUAL table with 
the TRANSACTION table.  The customer I have specifically stated wanting
to cross REQUEST_ACTUAL with the EPC$1_253_PROCEDURE_CALL table.  That is
a different table than the TRANSACTION table.

   I could offer them this option, I'll re-read the note to see what 
information is gained from this query, but I don't think that is what they
were after.


Thanks,
Renee
448.3Check note 99.3OOTOOL::CRAIGMon Mar 10 1997 13:0324
	Hi Renee,

	Sorry, about that.

	Relate what it says in Note 99.3 (see attached)
	to what they want Request actual and procedure call. 
	Let me know if that is helpful to them.

99.3:
   
    Here's a really useful report that you can generate for ACMS and Rdb.
    The report shows all Rdb Transactions grouped by procedure call (join)
    by ACMS server name/index. 
    
    $ COLLECT REPORT ACMS$RDB /STATISTICS=ALL/OUTPUT=ACMS_RDB.TXT-
    /TYPE=SUMMARY /WIDTH=132 /OPTIONS
    EVENT PROCEDURE_CALL /FAC=ACMS -
     /group_by=(PROCEDURE_NAME,SERVER_NAME)
    EVENT TRANSACTION /FAC=RDBVMS/group_by=(RDBVMS.TRANSACTION.CROSS_FAC_7)
    JOIN  ACMS.PROCEDURE_CALL.CROSS_FAC_2=RDBVMS.TRANSACTION.CROSS_FAC_2
    EXIT

	Thanks,
	Sheri
448.4Here is the customers query...BROKE::BASTINEWed Mar 12 1997 08:34136
Below is a query the customer sent me that he is trying to use to get the
information he requests from the REQUEST_ACTUAL table and the PROCEDURE_CALL
table.  

Here is what he is trying to do:

!Renee,  Here are the tables that I wish to join.  This appears to work fine
!        after specifying D.INDEX_COUNTER IS NOT NULL (same number of rows
!        selected before and after joining in the EPC$1_221_DATABASE D table).
!
!        I am not sure however that this join is absolutely correct.
!        It seems to make since that the timestamp begin/end of a 
!        request_actual row should fall between the timestamp begin/end of a 
!        procedure_call row.
!
!        The reason I need a report like this is to identify specific sql
!        statements within a task step (program execution) that is doing 
!        excessive I/O.  Many of our programs have numerous sql statements
!        accessing upwards to 30 tables.
!
!        This procedure lists information about each individual sql execution
!        that does > 20 I/O.  The report is ordered by database ascending and
!        I/O descending.  Before executing this procedure:
!	     1) DEFINE EPC$FORMAT_DB [trace db name]
!            2) @EPC$EXAMPLES:EPC_BLR_TO_SQL_CONVERTER.COM
!            3) ALTER TABLE EPC$1_221_REQUEST_ACTUAL ADD COLUMN DIO INTEGER;
!            4) UPDATE EPC$1_221_REQUEST_ACTUAL SET DIO = DIO_END - DIO_START;
!            5) CREATE INDEX DIO ON EPC$1_221_REQUEST_ACTUAL
!                     (DIO DESC, IMAGE_RECORD_ID);
!            6) $SET TERM/WIDTH=132
!

The customer would like to know if there is a better way to get the information
he requests without getting duplicate rows.  For now, using 
D.INDEX_COUNTER IS NOT NULL 
seems to be working, but is there another way?  Thanks!

Renee

!$SET TERM/WIDTH=132
!$SQL
!AT 'F ACMS_DIR:DECTRACE_MAR07_ALL';
!SET T READ O;

SET OUT DIO_GT_20.LIS  

SELECT   '            DIO: '||CAST(R.DIO AS CHAR(8))||' '||
         '      DBS_READS: '||CAST(R.DBS_READS_END-R.DBS_READS_START AS CHAR(8))||' '||
         '     DBS_WRITES: '||CAST(R.DBS_WRITES_END-R.DBS_WRITES_START AS CHAR(8))||' '||
         '      RUJ_READS: '||CAST(R.RUJ_READS_END-R.RUJ_READS_START AS CHAR(8))||' '||
         '     RUJ_WRITES: '||CAST(R.RUJ_WRITES_END-R.RUJ_WRITES_START AS CHAR(8))||
         '     AIJ_WRITES: '||CAST(R.AIJ_WRITES_END-R.AIJ_WRITES_START AS CHAR(8))||' '||
         '     ROOT_READS: '||CAST(R.ROOT_READS_END-R.ROOT_READS_START AS CHAR(8))||' '||
         '    ROOT_WRITES: '||CAST(R.ROOT_WRITES_END-R.ROOT_WRITES_START AS CHAR(8))||' '||
         '   BUFFER_READS: '||CAST(R.BUFFER_READS_END-R.BUFFER_READS_START AS CHAR(8))||' '||
         '   GET_VM_BYTES: '||CAST(R.GET_VM_BYTES_END-R.GET_VM_BYTES_START AS CHAR(8))||
         '  FREE_VM_BYTES: '||CAST(R.FREE_VM_BYTES_END-R.FREE_VM_BYTES_START AS CHAR(8))||' '||
         '      LOCK_REQS: '||CAST(R.LOCK_REQS_END-R.LOCK_REQS_START AS CHAR(8))||' '||
         ' REQ_NOT_QUEUED: '||CAST(R.REQ_NOT_QUEUED_END-R.REQ_NOT_QUEUED_START AS CHAR(8))||' '||
         '     REQ_STALLS: '||CAST(R.REQ_STALLS_END-R.REQ_STALLS_START AS CHAR(8))||' '||
         '  REQ_DEADLOCKS: '||CAST(R.REQ_DEADLOCKS_END-R.REQ_DEADLOCKS_START AS CHAR(8))||
         ' PROM_DEADLOCKS: '||CAST(R.PROM_DEADLOCKS_END-R.PROM_DEADLOCKS_START AS CHAR(8))||' '||
         '      LOCK_RELS: '||CAST(R.LOCK_RELS_END-R.LOCK_RELS_START AS CHAR(8))||' '||
         'LOCK_STALL_TIME: '||CAST(R.LOCK_STALL_TIME_END-R.LOCK_STALL_TIME_START AS CHAR(8))||' '||
         '    D_FETCH_RET: '||CAST(R.D_FETCH_RET_END-R.D_FETCH_RET_START AS CHAR(8))||' '||
         '    D_FETCH_UPD: '||CAST(R.D_FETCH_UPD_END-R.D_FETCH_UPD_START AS CHAR(8))||
         '     D_LB_ALLOK: '||CAST(R.D_LB_ALLOK_END-R.D_LB_ALLOK_START AS CHAR(8))||' '||
         'D_LB_GBNEEDLOCK: '||CAST(R.D_LB_GBNEEDLOCK_END-R.D_LB_GBNEEDLOCK_START AS CHAR(8))||' '||
         '  D_LB_NEEDLOCK: '||CAST(R.D_LB_NEEDLOCK_END-R.D_LB_NEEDLOCK_START AS CHAR(8))||' '||
         '    D_LB_OLDVER: '||CAST(R.D_LB_OLDVER_END-R.D_LB_OLDVER_START AS CHAR(8))||' '||
         '  D_GB_NEEDLOCK: '||CAST(R.D_GB_NEEDLOCK_END-R.D_GB_NEEDLOCK_START AS CHAR(8))||
         '    D_GB_OLDVER: '||CAST(R.D_GB_OLDVER_END-R.D_GB_OLDVER_START AS CHAR(8))||' '||
         '  D_NOTFOUND_IO: '||CAST(R.D_NOTFOUND_IO_END-R.D_NOTFOUND_IO_START AS CHAR(8))||' '||
         ' D_NOTFOUND_SYN: '||CAST(R.D_NOTFOUND_SYN_END-R.D_NOTFOUND_SYN_START AS CHAR(8))||' '||
         '    S_FETCH_RET: '||CAST(R.S_FETCH_RET_END-R.S_FETCH_RET_START AS CHAR(8))||' '||
         '    S_FETCH_UPD: '||CAST(R.S_FETCH_UPD_END-R.S_FETCH_UPD_START AS CHAR(8))||
         '     S_LB_ALLOK: '||CAST(R.S_LB_ALLOK_END-R.S_LB_ALLOK_START AS CHAR(8))||' '||
         'S_LB_GBNEEDLOCK: '||CAST(R.S_LB_GBNEEDLOCK_END-R.S_LB_GBNEEDLOCK_START AS CHAR(8))||' '||
         '  S_LB_NEEDLOCK: '||CAST(R.S_LB_NEEDLOCK_END-R.S_LB_NEEDLOCK_START AS CHAR(8))||' '||
         '    S_LB_OLDVER: '||CAST(R.S_LB_OLDVER_END-R.S_LB_OLDVER_START AS CHAR(8))||' '||
         '  S_GB_NEEDLOCK: '||CAST(R.S_GB_NEEDLOCK_END-R.S_GB_NEEDLOCK_START AS CHAR(8))||
         '    S_GB_OLDVER: '||CAST(R.S_GB_OLDVER_END-R.S_GB_OLDVER_START AS CHAR(8))||' '||
         '  S_NOTFOUND_IO: '||CAST(R.S_NOTFOUND_IO_END-R.S_NOTFOUND_IO_START AS CHAR(8))||' '||
         ' S_NOTFOUND_SYN: '||CAST(R.S_NOTFOUND_SYN_END-R.S_NOTFOUND_SYN_START AS CHAR(8))||' '||
         '  D_ASYNC_FETCH: '||CAST(R.D_ASYNC_FETCH_END-R.D_ASYNC_FETCH_START AS CHAR(8))||' '||
         '  S_ASYNC_FETCH: '||CAST(R.S_ASYNC_FETCH_END-R.S_ASYNC_FETCH_START AS CHAR(8))||
         ' D_ASYNC_READIO: '||CAST(R.D_ASYNC_READIO_END-R.D_ASYNC_READIO_START AS CHAR(8))||' '||
         ' S_ASYNC_READIO: '||CAST(R.S_ASYNC_READIO_END-R.S_ASYNC_READIO_START AS CHAR(8))||' '||
         '  AS_READ_STALL: '||CAST(R.AS_READ_STALL_END-R.AS_READ_STALL_START AS CHAR(8))||' '||
         ' AS_BATCH_WRITE: '||CAST(R.AS_BATCH_WRITE_END-R.AS_BATCH_WRITE_START AS CHAR(8))||' '||
         ' AS_WRITE_STALL: '||CAST(R.AS_WRITE_STALL_END-R.AS_WRITE_STALL_START AS CHAR(8))||
         '            BIO: '||CAST(R.BIO_END-R.BIO_START AS CHAR(8))||' '||
         '     PAGEFAULTS: '||CAST(R.PAGEFAULTS_END-R.PAGEFAULTS_START AS CHAR(8))||' '||
         '   PAGEFAULT_IO: '||CAST(R.PAGEFAULT_IO_END-R.PAGEFAULT_IO_START AS CHAR(8))||' '||
         '            CPU: '||CAST(R.CPU_END-R.CPU_START AS CHAR(8))||' '||
         '   CURRENT_PRIO: '||CAST(R.CURRENT_PRIO_END-R.CURRENT_PRIO_START AS CHAR(8))||
         '   VIRTUAL_SIZE: '||CAST(R.VIRTUAL_SIZE_END-R.VIRTUAL_SIZE_START AS CHAR(8))||' '||
         '        WS_SIZE: '||CAST(R.WS_SIZE_END-R.WS_SIZE_START AS CHAR(8))||' '||
         '     WS_PRIVATE: '||CAST(R.WS_PRIVATE_END-R.WS_PRIVATE_START AS CHAR(8))||' '||
         '      WS_GLOBAL: '||CAST(R.WS_GLOBAL_END-R.WS_GLOBAL_START AS CHAR(8))||' '||
         '            DIO: '||CAST(R.DIO AS CHAR(8)), 
         R.TIMESTAMP_START,R.TIMESTAMP_END,
         SUBSTRING(D.DB_NAME FROM 24 FOR 5)||' '||
         CAST(R.IMAGE_RECORD_ID AS CHAR(06))||' '||
         CAST(P.PROCESS_NAME AS CHAR(16))||' '||
         CAST(P.NODE AS CHAR(6))||' '||
         CAST(P.USERNAME AS CHAR(12))||' '||
 	 CAST(T.SERVER_NAME_START AS CHAR(26))||' '|| 
 	 CAST(T.TASK_NAME_START AS CHAR(26))||' '||
 	 CAST(T.STEP_NAME_START AS CHAR(26)),
         SQL_STRING,
         '---------------------------------------------------'

FROM     EPC$1_221_REQUEST_ACTUAL R, 
         EPC$SQL_QUERIES Q, 
         EPC$PROCESS P, 
         EPC$IMAGE I,
	 EPC$1_253_PROCEDURE_CALL T,
         EPC$1_221_DATABASE D
WHERE    Q.SQL_ID            = R.SQL_ID
AND      P.PROCESS_RECORD_ID = I.PROCESS_RECORD_ID
AND      T.IMAGE_RECORD_ID   = R.IMAGE_RECORD_ID
AND      R.IMAGE_RECORD_ID   = I.IMAGE_RECORD_ID
AND      R.DIO               > 20	!Added field/index: dio_end - dio_start
AND      R.TIMESTAMP_START BETWEEN T.TIMESTAMP_START AND T.TIMESTAMP_END
AND      R.TIMESTAMP_END   BETWEEN T.TIMESTAMP_START AND T.TIMESTAMP_END
AND      D.IMAGE_RECORD_ID   = R.IMAGE_RECORD_ID
AND      D.CONTEXT_NUMBER    = R.CONTEXT_NUMBER
AND      D.STREAM_ID         = R.STREAM_ID_END
AND      D.INDEX_COUNTER IS NOT NULL
AND      I.IMAGE_NAME NOT CONTAINING 'SQR'  !this is optional of course
ORDER BY D.DB_NAME ASC, R.DIO DESC;
448.5add the transaction tableOMYGOD::LAVASHSame as it ever was...Wed Mar 12 1997 16:0214
Renee,

They should add the  EPC$1_221_TRANSACTION table and
join it across the procedure call table via image_id and cross_fac_7 in 
both tables.

Then join the EPC$1_221_TRANSACTION table to the EPC$1_221_REQUEST 
table by trans_id, stream_id.

This should get you the exact requests under the exact transactions for the 
given procedures.


George
448.6More questions....BROKE::BASTINEThu Mar 13 1997 09:2629
Thanks George... I gave the customer your suggestions and he called this 
morning with the following questions.

In the transaction table there are the following columns:

trans_id_start
trans_id_start_str_id
stream_id_start

In the request_actual table these are the columns:

trans_id_end
trans_id_end_str_id
stream_id_end

He wanted to me sure he should be setting the 
transaction.trans_id_start=request_actual.trans_id_end

Since that appeared to be the "obvious" choice, he tried to run his query
with the starts=ends and the query ran a long long time.  He ended up killing
it.  He wasn't sure if he needed to add more indexes on the query or not.

If you can just verify that the starts should = the ends, I'll get what his
new query looks like so we can try and figure out why it runs so long, or 
would you expect this query to run a long time?

Thanks,
Renee
448.7more infoOMYGOD::LAVASHSame as it ever was...Thu Mar 13 1997 10:1615
Wasn't quite clear in my previous message.

They should remove the timestamp part of the query when they add the
trans_id and cross_fac_7 qualifiers to the query.

That might help with the response time...

Also building an index with image_id,stream_id_start,trans_id_start,
cross_fac_7_start on the transaction table and an 
index with  image_id,stream_id,trans_id_end,sql_id on the request_table should
really improve performance.

Building that index may take some time but the query response should improve.

George