5 Replies Latest reply: Jan 24, 2014 10:49 PM by ABOracle RSS

    What's the relationship between sid and transaction?

    ski123

      1. Is there any data dictionary table to record history of transactions data? For example, I want to look at past 10 days transactions.

       

      2. So far I know v$transaction and v$session, they only store current trans data, once transaction is done, the trans record in data dictionary table disappears.

      3. table V$SESSION_LONGOPS see following

       

      USERNAME                    SID OPNAME      TOTALWORK      SOFAR START_TIM TIME_REMAINING ELAPSED_SECONDS

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

      mysql1                                      979 Hash Join          93         93 24-JAN-14              0               8

      mysql1                                      979 Hash Join          30         30 24-JAN-14              0              16

      mysql1                                      979 Hash Join          60         60 24-JAN-14              0               8

      mysql_MI                                   979 Table Scan     914126     914126 24-JAN-14              0             206

      mysql_MI                                   979 Table Scan     914126     914126 24-JAN-14              0              17

       

       

       

      Can we say mysql1 has 3 transactions  with the same sid=979?

       

      Appreciate any help.

       

      Thanks

       

      S.

        • 1. Re: What's the relationship between sid and transaction?
          Baris Yildirim

          Hi,

           

          sid is "session identifier"  which represents a session    

           

          A logical entity in the database instance memory that represents the state of a current user login to a database. A single connection can have 0, 1, or moresessions established on it.

           

          Transaction

           

          A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either allcommitted, which means they are applied to the database, or all rolled back, which means they are undone from the database. Oracle Database assigns every transaction a unique identifier called a transaction ID.

           

          v$session_longops

           

          V$SESSION_LONGOPS displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.

          Regards

          • 2. Re: What's the relationship between sid and transaction?
            himmy

            Didn't completely get it but i am guessing you want details of a query how it ran in past 10 days

            you can get its sql_id from its current session or from the awr report during the duration in which it ran and then with the help of that sql_id

            you can pull awrsqrpt.sql report (ORACLE_HOME/rdbms/admin/awrsqrpt.sql) .

            http://docs.oracle.com/cd/B19306_01/server.102/b14211/autostat.htm

            • 3. Re: What's the relationship between sid and transaction?
              Alvaro
              1. Is there any data dictionary table to record history of transactions data? For example, I want to look at past 10 days transactions.

              By default, oracle does not store this history. Imagine if you have a very busy OTLP system with thousands of small transactions each day. The overhead alone of capturing and maintaining this, for a whole database would be nothing small.

               

              However, a number of features can track transaction history (DML or DDL) when enabled. You can use AUDIT to capture all transactions done against a table, or if you want to go further than that, you can also use flashback data archive. Flashback enables you to query the transaction history of a  table, but not only that, it enables you to query the table as if it was frozen at any time in the past. (depending on the retention you set)

               

              2. So far I know v$transaction and v$session, they only store current trans data, once transaction is done, the trans record in data dictionary table disappears.

              3. table V$SESSION_LONGOPS see following

               

              V$SESSION_LONGOPS does not show transaction information. It shows long duration operations (hence longops). It shows things like, full table scans, large joins being done in temp tablespace, large sorts, etc. This is good for monitoring long running queries and backups.

               

              V$TRANSACTION is the view that tracks active transactions on the system.

              • 4. Re: What's the relationship between sid and transaction?
                jgarry

                There's no data dictionary table.  You can audit, which is resource intensive and has to be set up, or you can use the feature that enables Oracle to redo transactions in case there is a crash, called redo.  If you have a proper production system, the redo logs are archived, and you can mine them with logminer.  You can also flashback as Alvaro mentioned, if you are set up for that.

                 

                What you really are looking for in those 10 days determines what is best for you.  Baris gave you the official definition of transaction, but many applications do not properly map transactions with what a business considers a transaction.  For one example, sometimes an app will allow a trial of many things being updated, then rollback.  There was a transaction active, but as far as the business was concerned there was no transaction.  And as far as the app is concerned afterwards, it never happened.  But the database would disagree.  Things can get real confusing when people throw in autonomous transactions.

                • 5. Re: What's the relationship between sid and transaction?
                  ABOracle

                  Read Transaction Flashback technology available in 11g

                   

                  Using Oracle Flashback Technology

                   

                  Thanks,