12 Replies Latest reply on Jul 17, 2019 12:51 PM by Bill Robinson

    Bladelogic Stats Gather question

    noah arca
      Share This:

      Hi

       

      I am running the ghenerated gather schema stats script (gather_schema_stats_autogen_2019_06_07_18_41_30.sql). I find some tables still are not analysed.

       

      SNAPSHOT_AUTH_DEPLOY_JOB_RUNS,2019-05-06 23:16:50,203601833,1188475,9744416768

      JOB_RUN_EVENT,2016-04-21 17:37:20,185553140,6134898,187456028672

      SNAPSHOT_GENERIC_ASSET,2018-08-09 00:30:17,65408906,1282746,10520363008

       

      What does this indicate?

       

      I also find when I run :

       

      ./dbdiagnostics runDiag  diadId=1000006

      Run diagnostic id=1000006, and got execution id=2000080

       

      ./dbdiagnostics getResLastExec diagId=1000006

      diagId=1000006

      execDiagId=2000080

      execStartTime=2019-06-10 09:22:52.0

      messageLevel=INFO

      message=DBMS_STATS_CHK: DBMS_STATS on the Database ran 1145 days ago, which is NOT OK. The Expected running of DBMS_STATS is once in 15 days. Please run BL_GATHER_SCHEMA_STATS PROC for this schema.

       

      Why does it show this if the script was just run?

        • 1. Re: Bladelogic Stats Gather question
          Bill Robinson
          I am running the ghenerated gather schema stats script (gather_schema_stats_autogen_2019_06_07_18_41_30.sql). I find some tables still are not analysed.

          are the missing tables in the sql ?

           

          Why does it show this if the script was just run?

          because stats haven't run against some of the tables ?

           

          why are you generating a sql script to run stats vs calling the procedure directly (which will hit all tables) ?

          • 2. Re: Bladelogic Stats Gather question
            noah arca

            are the missing tables in the sql ?

             

            Yes as shown below:

            exec dbms_stats.gather_table_stats(estimate_percent=>25,tabname=>'JOB_RUN_EVENT',ownname=>'BMC_OWNER',cascade=>true,degree=>DBMS_STATS.AUTO_DEGREE,no_invalidate=>true,force=>true,method_opt=>'for all columns size 254');

            exec dbms_stats.gather_table_stats(estimate_percent=>25,tabname=>'SNAPSHOT_AUTH_DEPLOY_JOB_RUNS',ownname=>'BMC_OWNER',cascade=>true,degree=>DBMS_STATS.AUTO_DEGREE,no_invalidate=>true,force=>true,method_opt=>'for all columns size 254');

            exec dbms_stats.gather_table_stats(estimate_percent=>25,tabname=>'SNAPSHOT_GENERIC_ASSET',ownname=>'BMC_OWNER',cascade=>true,degree=>DBMS_STATS.AUTO_DEGREE,no_invalidate=>true,force=>true,method_opt=>'for all columns size 254');

             

             

            why are you generating a sql script to run stats vs calling the procedure directly (which will hit all tables) ?

            All the scripts run are standard BMC provided utilities for DBAs to run against the BSA schema. This should work just as blcli DBManager blGatherSchemaStats.The scripts are gather_bl_schema_stats.sql and generate_gather_schema_stats_script.sql

            Are they not reliable, should not be used?

            • 3. Re: Bladelogic Stats Gather question
              Bill Robinson

              it seems like work to generate a sql script and then run it vs just calling the stored procedure directly.

               

               

              run this:

              blcli DBManager blGatherSchemaStats

              when that's done, run

              blcli Utility generateDashboardReport

              then go look on the file server in 'dashboard/reports/<today>/dbLastAnalyzedStats.csv'

              are there any tables listed there not analyzed when you ran stats ?

               

               

              • 4. Re: Bladelogic Stats Gather question
                noah arca

                it seems like work to generate a sql script and then run it vs just calling the stored procedure directly.

                 

                Actually it is much simpler for us as we can look at the sql script and see what is happening. As I have shown all the tables are in the generated script. The database is quite sizeable, so have to run in less busier periods. With scripts we can look at the sql query paths to see if there are issues which can be remedied. With blcli we cannot do this, so the script option is much more desirable. I am sure blcli is doing same thing under the covers.

                 

                We do not have dashboard reporting enabled currently, so I don't think the utility will work.

                 

                Why do you think tables are not analysed by the gather schema stats even though the procedure  is present in the database when it is called by the sql script?

                • 5. Re: Bladelogic Stats Gather question
                  Bill Robinson
                  Actually it is much simpler for us as we can look at the sql script and see what is happening.

                  so running a single command on a scheduled basis is more complicated that manually generating a sql script and then running it ?  i mean, i guess.

                   

                  As I have shown all the tables are in the generated script

                  then maybe the diagnostic utility is not working correctly or looking at something different.

                   

                  The database is quite sizeable, so have to run in less busier periods

                  the database is quite sizeable at most of our customers and they seem to be able to schedule it to run regularly...

                   

                  With scripts we can look at the sql query paths to see if there are issues which can be remedied.

                  seems very labor intensive... for little pay back given that probably none of our other customers seem to need to do this nor report any issues w/ it scheduled. 

                   

                  With blcli we cannot do this, so the script option is much more desirable.

                  to  have extra work ?

                   

                  We do not have dashboard reporting enabled currently,

                  why not ? 

                   

                  Why do you think tables are not analysed by the gather schema stats even though the procedure  is present in the database when it is called by the sql script?

                  are you asking me ?  i mean, this is your question right ?  why the diagnostic utility is reporting a problem ?

                   

                  since you want to inspect everything, run this:

                  set pagesize 1000;

                  ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd HH24:mi:ss';

                  set line 1000;

                  spool lastAnalyzed.csv;

                  SELECT ut.table_name ||','||

                         ut.last_analyzed ||','||

                         ut.num_rows ||','||

                         ut.blocks ||','||

                         us.bytes

                  FROM user_tables ut

                       INNER JOIN user_segments us ON ut.table_name = us.segment_name

                  ORDER BY table_name;

                  spool off;

                  as the bladelogic db user and attach the result.

                  • 6. Re: Bladelogic Stats Gather question
                    noah arca

                    so running a single command on a scheduled basis is more complicated that manually generating a sql script and then running it ?  i mean, i guess.

                     

                    We have not scheduled it due to the extra workload we use it only as a diagnostic resource currently . We are looking to schedule it

                    Dashboard will be enabled but not as yet.

                     

                     

                    Lastanalysed attached

                    • 7. Re: Bladelogic Stats Gather question
                      Bill Robinson

                      the table stats are current.  so probably there is something not working correctly w/ the diag command.

                       

                      the dashboard runs the same query i sent you and will indicate if stats are out of date, among other things.  we can look at why the diag command isn't working, but most customers are using the dashboard to track important housekeeping stuff - and a lot more things are looked at in the dashboard than w/ the diag commands.

                      • 8. Re: Bladelogic Stats Gather question
                        noah arca

                        Classification: For internal use only

                        Did you notice some of the table stats not being current? Most of the table stats are current but a handful are not. Will this affect the dbdiagnostics not showing the recent running of stats gather?

                        • 9. Re: Bladelogic Stats Gather question
                          noah arca

                          Bill do you have the dashboard installation zip bundle? If so can you post here please.

                          • 10. Re: Bladelogic Stats Gather question
                            Bill Robinson

                            there's no separate installer for the dashboard anymore - it's been part of the product since 8.6.  what version are you using ?

                             

                            ASSET_ATTRIBUTE_VALUE,2019-05-04 15:12:53,161968410,761111,6245318656

                            AUDIT_TRAIL,2019-05-26 23:25:18,113371262,2385389,21258829824

                            BL_VALUE,2019-05-09 23:30:21,362686647,1161237,9526312960

                            COMPLIANCE_RULE_RESULT,2019-05-04 01:21:46,87556971,1779612,14596177920

                            COMP_ENTRY_RESULT,2019-05-04 02:37:30,1289876789,4874788,39978008576

                            COMP_ENTRY_RESULT_DETAIL,2019-05-04 01:55:53,224184416,3411422,27978104832

                            COMP_RULE_GROUP_RULE_RESULT,2019-05-04 00:51:35,87556971,300288,2466250752

                            JOB_RUN_EVENT,2016-04-21 17:37:20,185553140,6134898,187456028672

                            RANGE_BL_VALUE,2019-02-07 22:24:46,199293194,733491,6501171200

                            SNAPSHOT_AUTH_DEPLOY_JOB_RUNS,2019-05-06 23:16:50,203601833,1188475,9744416768

                            SNAPSHOT_GENERIC_ASSET,2018-08-09 00:30:17,65408906,1282746,10520363008

                             

                            that is a little odd.  is this after the run of the full stats ?  or the generated sql script ?

                            • 11. Re: Bladelogic Stats Gather question
                              noah arca

                              That output was after the generated sql script.

                               

                              Using version 8.9.03.

                               

                              Does the Live Reporting installation bundle all the dashboards ?

                              • 12. Re: Bladelogic Stats Gather question
                                Bill Robinson

                                fwiw, i'm not seeing the same thing.  i ran the generate_gather_schema_stats_script.sql which gave me the attached.  i ran that yesterday and this morning i ran the diag:

                                 

                                 

                                # ./dbdiagnostics runDiag  diagId=1000006

                                Run diagnostic id=1000006, and got execution id=2000021

                                # ./dbdiagnostics getResLastExec diagId=1000006

                                diagId=1000006

                                execDiagId=2000021

                                execStartTime=2019-07-17 13:41:28.0

                                messageLevel=INFO

                                message=DBMS_STATS_CHK: DBMS_STATS on the Database ran 1 days ago, which is OK. The Expected running of DBMS_STATS is once in 15 days.

                                messageTime=2019-07-17 13:41:32.0

                                 

                                maybe there was a problem updating the stats on these tables ?  that's the only reason i can think they aren't being updated, given they are in the generated sql script.

                                SNAPSHOT_AUTH_DEPLOY_JOB_RUNS,2019-05-06 23:16:50,203601833,1188475,9744416768

                                JOB_RUN_EVENT,2016-04-21 17:37:20,185553140,6134898,187456028672

                                SNAPSHOT_GENERIC_ASSET,2018-08-09 00:30:17,65408906,1282746,10520363008