4 Replies Latest reply on Jan 19, 2020 12:59 AM by Brian K

    TSCO 11.5 - Ideas on SQL query which randomly hangs

    Brian K
      Share This:

      Hello All,

       

      I have an SQL query using SQL developer to run against our TSCO database.

       

      SELECT pv_system.name,

            pv_sys_data_detail.avgvalue as USED_SPACE_PCT,

            to_char(pv_sys_data_detail.ts,'MM-DD-YYYY HH24:MI:SS') AS DATETIME,

            pv_sys_metric.submetricname as FILESYSTEM

       

      FROM user.pv_system

       

      INNER JOIN user.pv_sys_metric on user.pv_sys_metric.sysid = user.pv_system.sysid

      INNER JOIN user.pv_sys_data_detail on user.pv_sys_data_detail.sysmetricid = user.pv_sys_metric.sysmetricid

       

      WHERE to_char(pv_sys_data_detail.ts,'HH24:MI') = '23:00'

           AND to_char(pv_sys_data_detail.ts,'MM-DD-YYYY') BETWEEN  '10-30-2019' and '10-31-2019'

           AND METRIC = 'BYFS_USED_SPACE_PCT'

           AND pv_system.name like 'z%'

       

      When I run this script sometimes it will just hang for approximately an hour then get a data extraction attempt ended.  If I do this WHERE statement instead

       

           AND pv_system.name like 'z123abc%'

       

        ...it works lightning fast.

       

          AND pv_system.name like 'z123%'

       

        ...also works.

       

      Those more fine selections always work. Its only when I make the filter a one character search that it will randomly hang. I know off the bat when the query will be successful because the script output view will start filling up immediately with the data found.  On the occurrences when it hangs, no data at all fills up the script output.  Looking for advice on whether it may be a database connection thing, a tuning thing, etc?

       

      Thanks in advance...

        • 1. Re: TSCO 11.5 - Ideas on SQL query which randomly hangs
          Arif Alibay

          Hello Brian,

          pv_system.name column is issued from   sys_def table

          Actually there is no index on the "name" column of of sys_def table.

          so a query with a where clause will scan all the table. And a query with with a "where like 'z%' will scan n times the table , n is the number of match of 'z%' in sys_def table.

          Hopefully that helps.

          Regards,

          Arif

          • 2. Re: TSCO 11.5 - Ideas on SQL query which randomly hangs
            Brian K

            Thank you Arif for your reply.

             

            I was indeed researching to find out what tables/fields were indexed before posting my question. Since "name" is not indexed and the query times out, its just a matter of too large a table for this query to process efficiently? I guess I will have to break this down into different smaller defined queries then.

            • 3. Re: TSCO 11.5 - Ideas on SQL query which randomly hangs
              Paul Nolan

              Hi Brian

               

              If your objective is to report file system utilisation for a specific day I would be more inclined to use the pv_sys_data_d view rather than the details view.

               

              In the original query I suspect that using '%z' changes the sql optimiser path due to a larger number of potential matches, meaning that the date comparisons are performed first, which supports my theory that your date selection is sub-optimal and results in date conversions being applied to a massive number of rows.

               

              WHERE to_char(pv_sys_data_detail.ts,'HH24:MI') = '23:00'

                   AND to_char(pv_sys_data_detail.ts,'MM-DD-YYYY') BETWEEN  '10-30-2019' and '10-31-2019'

                   AND METRIC = 'BYFS_USED_SPACE_PCT'

                   AND pv_system.name like 'z%'

               

              Extracting samples with a specific timestamp should be much faster as follows as no conversion is required on the actual data row :-

               

                  and pv_sys_data_detail.ts = to_date('30-OCT-2020 23:00:00','DD-MON-YYYY HH24:MI:SS')

               

              Hope this helps...

              • 4. Re: TSCO 11.5 - Ideas on SQL query which randomly hangs
                Brian K

                Paul, thank you very much for the reply. This was the solution. Both going against the pv_sys_data_d instead of _detail, and using the specific date without conversion, worked like a charm. Every iteration going back for months completed 100% of the time and took less than a minute.