8 Replies Latest reply: Dec 15, 2012 1:10 AM by Nikolay Savvinov RSS

    Global temporary table and concurrent sessions

    fechen
      Hi,

      In my application I have a global temporary table created with "on commit preserve row". There are 200 concurrent sessions that do the same thing including populating and querying this global temporary table. In the AWR report I got a lot of "cursor: pin S wait on X" and "library cache: mutex X" wait which are on the query of the global temporary table. The SQL version number for the queries of the global temporary table is high too >20. I figured that since each session has its own data in the global temporary table, the cursor cannot be reused by other session thus caused the high sql version number, and further caused the contention on mutex. My question is if this analysis is correct and how to solve the problem?

      Thanks,
      -Fengting
        • 1. Re: Global temporary table and concurrent sessions
          damorgan
          Is there an actual performance issue or are you just chasing a number you consider to be too large?

          If there is a performance issue ... what is it? (and please be specific).
          • 2. Re: Global temporary table and concurrent sessions
            fechen
            Yes, there is performance issue. The procedure that can finish in 5 seconds in a single thread now takes 30 seconds or more in concurrent sessions (concurrency=200). Here is the awr report of the top 10 foreground event:

            Event     Waits     Total Wait Time (sec)     Wait Avg(ms)     % DB time     Wait Class
            cursor: pin S wait on X     676     1117.9     1654     14.2     Concurrency
            DB CPU          441.1          5.6     
            library cache: mutex X     133     413.6     3110     5.3     Concurrency
            direct path write temp     400     314.3     786     4.0     User I/O
            buffer busy waits     54     263.6     4882     3.4     Concurrency
            db file scattered read     7,580     98.2     13     1.3     User I/O
            read by other session     44     94.1     2138     1.2     User I/O
            log file sync     368     69.5     189     .9     Commit
            library cache load lock     155     46.5     300     .6     Concurrency
            direct path sync     200     31.1     155     .4     User I/O
            • 3. Re: Global temporary table and concurrent sessions
              rp0428
              >
              Yes, there is performance issue. The procedure that can finish in 5 seconds in a single thread now takes 30 seconds or more in concurrent sessions (concurrency=200).
              >
              That doesn't indicate a problem withe the GTT.

              Multiple sessions that populate and query the GTT suggest that those multiple sessions also query the same source tables for different data sets.

              It is more likely that the high volume of traffic on those source tables is what is causing the contention. We have no way to know unless you can post what those sessions are actually doing.
              • 4. Re: Global temporary table and concurrent sessions
                damorgan
                Exactly what I suspected ...

                To the OP:
                Don't focus on the GTT until you have sufficient evidence to point to it being the root cause which it appears you do not have.
                • 5. Re: Global temporary table and concurrent sessions
                  fechen
                  There are several reasons that made me suspect the GTT.

                  First, the query from the dynamic views shows that most of the "cursor: pin S wait on X" event are due to the queries on the GTT.

                  Secondly, Only the queries on the GTT has SQL version count greater than 20.

                  Then from the ADDM report:
                   Rationale
                        Database time for this SQL was divided as follows: 35% for SQL
                        execution, 65% for parsing, 0% for PL/SQL execution and 0% for Java
                        execution.
                     Rationale
                        SQL statement with SQL_ID "awsppc64yndjz" was executed 8 times and had
                        an average elapsed time of 0.0037 seconds.
                     Rationale
                        Waiting for event "cursor: pin S wait on X" in wait class "Concurrency"
                        accounted for 60% of the database time spent in processing the SQL
                        statement with SQL_ID "awsppc64yndjz".
                  The above query is on the GTT.

                  Another finding from ADDM:
                  Finding 4: Shared Pool Latches
                  Impact is 3 active sessions, 12.15% of total activity.
                  ------------------------------------------------------
                  Contention for latches related to the shared pool was consuming significant
                  database time.
                  Waits for "library cache: mutex X" amounted to 7% of database time.
                  Waits for "cursor: pin S wait on X" amounted to 4% of database time.
                  
                     Recommendation 1: Application Analysis
                     Estimated benefit is 3.01 active sessions, 12.15% of total activity.
                     --------------------------------------------------------------------
                     Action
                        Investigate the cause for latch contention using the given blocking
                        sessions or modules.
                     Rationale
                        The session with ID 76 and serial number 11 in instance number 1 was the
                        blocking session responsible for 14% of this recommendation's benefit.
                     Rationale
                        The session with ID 245 and serial number 25 in instance number 1 was
                        the blocking session responsible for 12% of this recommendation's
                        benefit.
                     Rationale
                        The session with ID 24 and serial number 21 in instance number 1 was the
                        blocking session responsible for 10% of this recommendation's benefit.
                  
                     Symptoms That Led to the Finding:
                     ---------------------------------
                        Wait class "Concurrency" was consuming significant database time.
                        Impact is 3.04 active sessions, 12.25% of total activity.
                  Finally, what the session is doing. It first inserts some data such as id, xml metadata from a user table into the GTT, do some calculation based on the xml data, then updates GTT's other columns with the calculation results, eventually it queries the GTT so as to find and read the blob data in the user table and write it into a temporary lob created in the session.

                  Any other possible contention?

                  Thanks.
                  • 6. Re: Global temporary table and concurrent sessions
                    rp0428
                    >
                    First, the query from the dynamic views shows that most of the "cursor: pin S wait on X" event are due to the queries on the GTT.

                    Secondly, Only the queries on the GTT has SQL version count greater than 20.
                    >
                    Still - neither of those is specific to GTT's.

                    You still haven't provided your 4 digit Oracle version (result of SELECT * FROM V$VERSION).

                    If you search the web for 'cursor: pin S wait on X' you will find numerous hits and many of them report issues due to bugs that have been patched in various Oracle versions.

                    Mutex waits. Part 1. “Cursor: Pin S” in Oracle 10.2-11.1. Invisible and aggressive.
                    http://andreynikolaev.wordpress.com/2011/07/09/mutex-waits-part-1-%E2%80%9Ccursor-pin-s%E2%80%9D-in-oracle-10-2-11-1-invisible-and-aggressive/

                    You might want to review some of those web docs and search MOS for bugs related to your Oracle version.
                    • 7. Re: Global temporary table and concurrent sessions
                      fechen
                      Thanks for the link of blog. It is very informative. I might use what I have learned there to tune my system. Even though the long wait might not be caused by the high version number of GTT, any possible explanation of high version count of query on GTT? The query on v$sql_shared_cursor has 'N' for all the columns. Only in the reason column, it says "Session Specific Cursor Session Mismatch".
                      • 8. Re: Global temporary table and concurrent sessions
                        Nikolay Savvinov
                        Hi,

                        1) library cache contention is to be expected when frequently executing same lightweight SQL from a large number of concurrent sessions. I consecutively tested performance of executing a simple stored procedure that inserted and selected rows to/from a table, using a GTT with preserve rows option, GTT with delete rows option and a regular heap table -- on my database (11.2.0.1) I got "cursor: pin S" as the leading wait event in all 3 cases

                        2) when posting top wait events from an AWR report, it's a good idea to format it using
                         tags to make it more readable, and also add the report header and load profile sections to give a little bit of context -- was was the duration of the report, how many sessions were running, how many of them were active, how many parse/execute/commit calls the database was processing per unit time etc.
                        
                        3) btw did you notice that the top timed events section you posted only accounts for about 1/3 of DB time? where did the rest go?
                        
                        4) AWR reports aggregate database stats for the entire database, so if there were other things running at the same time, then it's hard to interpret the results. Instead, you could set the client_identifier for the processes you're interested in and then monitor them with DBMS_MONITOR. E.g. you can trace them and process the output using trcsess, or you could simply enable stats collection for a specific client_id by DBMS_MONITOR.CLIENT_ID_STATS_ENABLE and check the results in V$CLIENT_STATS view
                        
                        Best regards,
                          Nikolay