3 Replies Latest reply on Oct 17, 2019 4:40 AM by Maaz Khan

    Query performance - Custom table

    ARIV

      Hi

      I have a custom table that holds 1800 rows but takes too long to query against.

      A replica table created to test the issue is working just fine.

      Table structure and elapsed times given below.

      What could be the reason?

       

      Regards

      Ariv

       

       

       

      desc mpl_fms_details_tab
      Name          Null Type         
      ------------- ---- -------------
      PROJECT_ID         VARCHAR2(100)
      PROJECT_NAME       VARCHAR2(250)
      LOCATION           VARCHAR2(150)
      FACILITY_NAME      VARCHAR2(250)
      SEAT_COUNT         NUMBER       
      LOCATION_CODE      VARCHAR2(50) 

       

       

      > select count(1) from apps.mpl_fms_details_tab

        COUNT(1)

      ----------

            1794

       

      Elapsed: 00:00:20.998

      > create table dummytab

      as

      select * from apps.mpl_fms_details_tab

      table DUMMYTAB created.

      Elapsed: 00:00:03.394

      > select count(1) from apps.dummytab

        COUNT(1)

      ----------

            1794

       

      Elapsed:
      00:00:00.015

      > drop table dummytab

      table DUMMYTAB dropped.

      Elapsed: 00:00:00.149

        • 1. Re: Query performance - Custom table
          Maaz Khan

          Hi Ariv,

           

          Do you have heavy dml (inserts/updates/deletes) happening on your custom table? Ideally custom tables should be stored under a custom schema (separate custom top).

           

          1. Try to gather statistics on table.

          2. You can then use below query to get details of high-water mark.

          set verify off

          column owner format a10

          column alcblks heading 'Allocated|Blocks' just c

          column usdblks heading 'Used|Blocks'      just c

          column hgwtr heading 'High|Water'         just c

          break on owner skip page

           

          select

              a.owner,

              a.table_name,

              b.blocks                        alcblks,

              a.blocks                        usdblks,

              (b.blocks-a.empty_blocks-1)     hgwtr

          from

              dba_tables a,

              dba_segments b

          where

              a.table_name=b.segment_name

              and a.owner=b.owner

              and a.owner not in('SYS','SYSTEM')

              and a.blocks <> (b.blocks-a.empty_blocks-1)

              and a.owner like upper('&owner')||'%'

              and a.table_name like upper('&table_name')||'%'

          order by 1,2;

           

           

          3. Perform online shrink and shrink compact to further see if there is any improvement.

           

          Regards,

          Maaz

          • 2. Re: Query performance - Custom table
            ARIV

            Hello Maaz

            Thanks for your response.

            The table is indeed housed in a custom schema

            In the test instance, there is no DML against this table.

             

            1. Stats were gathered a week ago

            2. High-water mark Info (Thx for the query )

                 OWNER        TABLE_NAME              ALCBLKS USDBLKS   HGWTR

                    MPL     MPL_FMS_DETAILS_TAB     50280      50280         50279

            3. Perform online shrink and shrink compact to further see if there is any improvement.

                 Can you point to any resource for this please?

             

            Something else that caught my eye

             

            Please suggest.

             

            Regards

            Ariv

            • 3. Re: Query performance - Custom table
              Maaz Khan

              Hello Ariv,

               

              Assuming stats are fresh and High-water mark, we can further compare execution plans for both queries.

               

               

              EXPLAIN PLAN FOR
              select count(1) from apps.mpl_fms_details_tab;

              SET LINESIZE 130
              SET PAGESIZE 0
              SELECT * FROM table(DBMS_XPLAN.DISPLAY);

               

               

               

              EXPLAIN PLAN FOR
              select count(1) from apps.dummytab;

              SET LINESIZE 130
              SET PAGESIZE 0
              SELECT * FROM table(DBMS_XPLAN.DISPLAY);

               

               

               

              Regards,

              Maaz