1 2 Previous Next 25 Replies Latest reply on Jan 14, 2016 10:46 AM by Martin Preiss Go to original post
      • 15. Re: Re: Slow access on v$sql
        Martin Preiss

        Hi Stefan,

         

        I will certainly take a closer look at your DOAG paper - but the kernel version in the system is 2.6.18, so I guess perf is not an option (p.7: "available with kernel version 2.6.31 or higher"). But I think I will find there another tool to do the job.

         

        Regarding _memory_imm_mode_without_autosga: if I understand https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=397277363245232&id=1269139.1 correctly then the parameter seems to prevent the resize operations between the different components. Actually there are resize operations shown in v$sga_resize_ops but they include only small shifts between db_cache_size and shared_pool_size and the range of changes is limited:

        -- v$memory_dynamic_components

        COMPONENT                          CURRENT_SIZE        MIN_SIZE        MAX_SIZE

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

        DEFAULT buffer cache                16441671680      15435038720      16777216000

        PGA Target                          10468982784      10468982784      10468982784

        SGA Target                          19595788288      19595788288      19595788288

        java pool                              67108864         67108864         67108864

        large pool                             67108864         67108864         67108864

        shared pool                          2483027968       2147483648       3489660928

        streams pool                           67108864         67108864         67108864

        But I am rather concerned by the small size of the library cache currently using just 46M of the >2G in the shared pool; and by Jonathan's hint that KGH: NO ACCESS actually means that the shared pool itself is occupied by the buffer cache: KGH: NO ACCESS allocations in V$SGASTAT – buffer cache within shared pool! | Tanel Poder's Performance & Troubleshooting… As with the AMM use of the classical memory parameters (as shared_pool_size) as minimum values I would like to set a minimal size for the library cache to make sure I find the plans and the statistics for the queries there. It is not the parsing that annoys me but the problem, that I have to ask AWR where I would like to get answers from v$sql.

         

        Regards

        Martin

        • 16. Re: Re: Slow access on v$sql
          Martin Preiss

          Jonathan,

           

          the SQL Area information is indeed from v$librarycache and not from a current snapshot (sorry, I forgot to attribute the numbers).

           

          Regarding the parse time information: here are some numbers from a (1 hour) AWR-snapshot:

          Statistic            Total   per Second  per Trans

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

          parse time cpu      58,956        16.36      51.31

          parse time elapsed  59,482        16.51      51.77

          The same snapshot shows the following current values for gets, misses etc. in SQL AREA. So in this case there are more reloads than invalidations - and lots of misses:

          Namespace   Get Requests    Pct Miss    Pin Requests    Pct Miss    Reloads Invalidations

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

          SQL AREA          24,977        8.60       3,591,915       65.75      2,655         2,448

           

          The x$ queries bring the following results:

          SQL> select count(*) from x$kglcursor_child;

            COUNT(*)

          ----------

                2287

          Elapsed: 00:00:24.48


          SQL> select count(kglobt03) from x$kglcursor_child;

          COUNT(KGLOBT03)

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

                     2287

          Elapsed: 00:00:25.25


          SQL> select count(*) from x$kglob;

            COUNT(*)

          ----------

              510497

          Elapsed: 00:00:25.15


          SQL> select count(*) from x$kgllk;

           

            COUNT(*)

          ----------

                4101

           

          Elapsed: 00:00:00.62


          SQL> select count(*) from x$kglpn;

           

            COUNT(*)

          ----------

                  23

           

          Elapsed: 00:00:00.37

          So the problem seems (at least) to affect x$kglcursor_child and x$kglob.

           

          Regards

           

          Martin

          • 17. Re: Re: Slow access on v$sql
            Martin Preiss

            Hi Suntrupth,

             

            I created the trace with "alter session set sql_trace = true", but 10046 with explicit level 12 shows a similar result:

            PARSING IN CURSOR #47411281896816 len=39 dep=0 uid=118 oct=3 lid=118 tim=1450904238106726 hv=1229107383 ad='71ce62c58' sqlid='1z3s32t4n5c5r'

            select /* test 2 */ count(*) from v$sql

            END OF STMT

            PARSE #47411281896816:c=4999,e=59135,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=1001747286,tim=1450904238106725

            EXEC #47411281896816:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1001747286,tim=1450904238106840

            WAIT #47411281896816: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1450904238106940

             

            *** 2015-12-23 21:57:42.663

            FETCH #47411281896816:c=24552268,e=24556587,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1001747286,tim=1450904262663560

            STAT #47411281896816 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 time=24556582 us)'

            STAT #47411281896816 id=2 cnt=2292 pid=1 pos=1 obj=0 op='FIXED TABLE FULL X$KGLCURSOR_CHILD (cr=0 pr=0 pw=0 time=35432 us cost=0 size=13 card=1)'

            WAIT #47411281896816: nam='SQL*Net message from client' ela= 833 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1450904262667056

            FETCH #47411281896816:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1001747286,tim=1450904262667115

            WAIT #47411281896816: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1450904262667139

             

            *** 2015-12-23 21:57:52.188

            WAIT #47411281896816: nam='SQL*Net message from client' ela= 9520807 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1450904272187974

            CLOSE #47411281896816:c=0,e=282,dep=0,type=0,tim=1450904272188373

            The CPU usage is changing but rarely extreme - while the performance of the select on v$sql is always the same. I also could imagine that a bigger shared pool could reduce the problem, but it is not "my" database so this is not my decision. Using errorstack/short_stack could be another step, but I am not Mr. Poder (Advanced Oracle Troubleshooting Guide, Part 9 – Process stack profiling from sqlplus using OStackProf | Tanel Poder's Pe…) and don't know if I am able to get to conclusions with the results...

             

            Regards

            Martin

            • 18. Re: Re: Slow access on v$sql
              Jonathan Lewis

              KGlhd and kglh0 are part of the library cache too; but yours seem large compared to your squares (SQLA). The large number of PIN misses seems very odd, too, given the (relatively) small number of get requests. The reloads doesn't seem too bad given the invalidations.

               

              I wonder if you've got very large numbers of dead child cursors - select sql_id, count(*) from v$sql - or whether you're hitting bug 13250244.

               

              Regards

              Jonathan Lewis

               

               

              EDIT. Ignore the child cursor comment, your total is only 2,200.

              1 person found this helpful
              • 19. Re: Re: Re: Slow access on v$sql
                Martin Preiss

                the AWR retention is just 30 days but in this range the size of KGLHD shows rather a decrease:

                select round(bytes/1024/1024) mb

                    , s.snap_id

                    , begin_interval_time START_TIME

                  from dba_hist_sgastat g, dba_hist_snapshot s

                where name='KGLHD'

                  and pool='shared pool'

                  and s.snap_id = g.snap_id

                  and mod(s.snap_id, 30) = 0

                order by 2

                 

                  MB     SNAP_ID START_TIME

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

                  161      41400 25-NOV-15 12.00.12.113 AM

                  162      41430 26-NOV-15 06.00.06.179 AM

                  163      41460 27-NOV-15 12.00.17.135 PM

                  162      41490 28-NOV-15 06.00.24.228 PM

                  163      41520 30-NOV-15 12.00.26.641 AM

                  163      41550 01-DEC-15 06.00.00.553 AM

                  165      41580 02-DEC-15 12.00.41.119 PM

                  164      41610 03-DEC-15 06.00.10.577 PM

                  164      41640 05-DEC-15 12.00.39.175 AM

                  165      41670 06-DEC-15 06.00.31.819 AM

                  166      41700 07-DEC-15 12.00.55.638 PM

                  166      41730 08-DEC-15 06.00.50.436 PM

                  166      41760 10-DEC-15 12.00.30.345 AM

                  168      41790 11-DEC-15 06.00.18.390 AM

                  168      41820 12-DEC-15 12.00.45.657 PM

                  168      41850 13-DEC-15 06.00.56.517 PM

                  169      41880 15-DEC-15 12.00.31.359 AM

                  168      41910 16-DEC-15 06.00.50.132 AM

                  151      41940 17-DEC-15 12.00.44.030 PM

                  151      41970 18-DEC-15 06.00.55.670 PM

                  152      42000 20-DEC-15 12.00.50.501 AM

                  152      42030 21-DEC-15 06.01.00.044 AM

                  152      42060 22-DEC-15 12.01.00.858 PM

                  152      42090 23-DEC-15 06.00.45.542 PM

                But bug 13250244 still looks interesting for its mentioning of stored outlines: the system did run with create_stored_outlines=true for a long time creating almost 20M outlines - though (as far as I can see) use_stored_outline has not been set on session level and so the outlines have not been used.

                 

                Regards

                Martin

                • 20. Re: Re: Re: Slow access on v$sql
                  Stefan Koehler

                  Hi Martin,

                   

                  > but the kernel version in the system is 2.6.18, so I guess perf is not an option (p.7: "available with kernel version 2.6.31 or higher"). But I think I will find there another tool to do the job.

                  No problem … just check page 9 of my paper and post the result here. This is available with any kernel release. It is not as good as "Perf", but the only thing you can do on your system right now.


                  > Regarding _memory_imm_mode_without_autosga: if I understand https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=397277363245232&id=1269139.1 correctly then the parameter seems to prevent the resize operations between the different components

                  Yes, but only for immediate memory requests. This was also another reason why i asked for the request types (e.g. MOS ID #1434975.1) of your resize operations.

                   

                  > and by Jonathan's hint that KGH: NO ACCESS actually means that the shared pool itself is occupied by the buffer cache: KGH: NO ACCESS allocations in V$SGASTAT – buffer cache within shared pool! | Tanel Poder's Performance & Troubleshooting…

                  Yes, but this "occupied" shared pool memory "area" (KGH: NO ACCESS) is caused by such memory shifts from shared pool to buffer cache. This is also explained in the last part of Tanel's blog post.

                  The answer is ASMM – Automatic Shared Memory Management, the manageability thing controlled with SGA_TARGET parameter and the need to increase buffer cache at the expense of shared pool.

                  When MMAN tries to get rid of a shared pool granule it obviously can't just flush and throw away all the object in it. As long as anybody references chunks in this granule, it cannot be completely deallocated.

                   

                  Oracle has faced a decision what to do in this case:

                  1) wait until all chunks aren't in use anymore - this might never happen

                  2) suspend the instance, relocate chunks somewhere else and update all SGA/PGA/UGA/CGA structures for all processes accordingly - this would get very complex

                  3) flush as many chunks from this shared pool granule as possible, mark them as "KGH: NO ACCESS" that nobody else would touch them, mark corresponding entry to DEFERRED in V$SGA_RESIZE_OPS and notify buffer cache manager about the new memory locations being available for use.

                   

                   

                  Oracle has gone with option 3 as option 1 wouldn't satisfy us and 2 would be very complex to implement, and it would mean a complete instance hang for seconds to minutes.

                   

                  So, Oracle can share a granule between shared pool and buffer cache data. This sounds like a mess, but there is not really a better way to do it (if leaving the question, why the heck do you want to continuously reduce your shared pool size anyway, out).

                   

                  Best Regards

                  Stefan Koehler

                   

                  Freelance Oracle performance consultant and researcher

                  1 person found this helpful
                  • 21. Re: Re: Re: Re: Slow access on v$sql
                    Martin Preiss

                    Hi Stefan,

                     

                    thanks again.

                    Stefan Koehler wrote:

                     

                    Yes, but this "occupied" shared pool memory "area" (KGH: NO ACCESS) is caused by such memory shifts from shared pool to buffer cache. This is also explained in the last part of Tanel's blog post.

                    ah I see: perhaps I should actually read the links and not just copy them... - this sheds more light on the behaviour:

                    select component

                        , oper_type

                        , oper_mode

                        , parameter

                        , final_size

                        , start_time

                        , end_time

                      from gv$sga_resize_ops

                    where start_time > sysdate - 4

                    order by start_time

                            , end_time

                     

                    COMPONENT                      OPER_TYPE    OPER_MODE PARAMETER                            FINAL_SIZE START_TIME          END_TIME

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

                    DEFAULT buffer cache           SHRINK        DEFERRED  db_cache_size                      16374562816 20.12.2015 20:06:54 20.12.2015 20:06:57

                    shared pool                    GROW          DEFERRED  shared_pool_size                    2550136832 20.12.2015 20:06:54 20.12.2015 20:06:57

                    DEFAULT buffer cache           GROW          DEFERRED  db_cache_size                      16441671680 20.12.2015 20:16:24 20.12.2015 20:16:25

                    shared pool                    SHRINK        DEFERRED  shared_pool_size                    2483027968 20.12.2015 20:16:24 20.12.2015 20:16:25

                    shared pool                    GROW          DEFERRED  shared_pool_size                    2550136832 20.12.2015 20:16:55 20.12.2015 20:16:57

                    DEFAULT buffer cache           SHRINK        DEFERRED  db_cache_size                      16374562816 20.12.2015 20:16:55 20.12.2015 20:16:57

                    DEFAULT buffer cache           GROW          DEFERRED  db_cache_size                      16441671680 20.12.2015 20:21:25 20.12.2015 20:21:25

                    shared pool                    SHRINK        DEFERRED  shared_pool_size                    2483027968 20.12.2015 20:21:25 20.12.2015 20:21:25

                    shared pool                    GROW          DEFERRED  shared_pool_size                    2550136832 20.12.2015 20:21:55 20.12.2015 20:22:07

                    DEFAULT buffer cache           SHRINK        DEFERRED  db_cache_size                      16374562816 20.12.2015 20:21:55 20.12.2015 20:22:07

                    shared pool                    SHRINK        DEFERRED  shared_pool_size                    2483027968 20.12.2015 20:51:26 20.12.2015 20:51:26

                    DEFAULT buffer cache           GROW          DEFERRED  db_cache_size                      16441671680 20.12.2015 20:51:26 20.12.2015 20:51:26

                    shared pool                    GROW          DEFERRED  shared_pool_size                    2550136832 21.12.2015 03:10:59 21.12.2015 03:11:00

                    DEFAULT buffer cache           SHRINK        DEFERRED  db_cache_size                      16374562816 21.12.2015 03:10:59 21.12.2015 03:11:00

                    shared pool                    SHRINK        DEFERRED  shared_pool_size                    2483027968 21.12.2015 03:15:29 21.12.2015 03:15:29

                    DEFAULT buffer cache           GROW          DEFERRED  db_cache_size                      16441671680 21.12.2015 03:15:29 21.12.2015 03:15:29

                    I guess the DEFERRED is a sign that point 3) from Tanel's list could be happening here.

                    Stefan Koehler wrote:

                     

                    just check page 9 of my paper and post the result here. This is available with any kernel release. It is not as good as "Perf", but the only thing you can do on your system right now.

                    I will certainly do - but right now I have a tree to decorate as it seems...

                     

                    Regards

                     

                    Martin

                    • 22. Re: Slow access on v$sql
                      Martin Preiss

                      and once again my cordial thanks to all the contributors - it is awesome to see how much help and information I can get here even on the 24. Dezember.

                      • 23. Re: Slow access on v$sql
                        Jonathan Lewis

                        It's possible that a single session temporarily enabled the feature, and (once upon a time, at least) if a single outline was called for then the entire outline data set was loaded in the library cache.

                        The other code referenced in that bug was 49, which is the SQL Tuning Base Object cache, so have you enabled profiles, set a non-default category, etc.

                         

                        One other thought - about the pin misses - if you take a snapshot of v$librarycache then run the count(*) from v$sql, then take a second snapshot, could check to see if the PINS count has gone up by somwething like 1M while the pinhits has hardly changed.

                         

                        Regards

                        Jonathan Lewis

                        • 24. Re: Re: Slow access on v$sql
                          Martin Preiss

                          the differences are rather small when I compare the v$librarycache snapshots from before and after the execution on the count(*) from v$sql (in another session):

                          NAMESPACE                            PINS    PINHITS       GETS    GETHITS    RELOADS INVALIDATIONS

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

                          SQL AREA                              794        598         76         67          0             0

                          But ADDM is active and some profiles have been created (all in the default category):

                          SQL> select category, count(*) from dba_sql_profiles group by category;

                           

                          CATEGORY                         COUNT(*)

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

                          DEFAULT                               178

                          So the bug (or something similar) could be involved.

                           

                          Regards

                           

                          Martin

                          • 25. Re: Slow access on v$sql
                            Martin Preiss

                            a late addition: the system will be replaced in some weeks so the decision was made to not investigate the issue much further. That's not completely satisfying, but pragmatic.

                             

                            Thanks again for the contributions.

                             

                            Martin Preiss

                            1 2 Previous Next