Tuesday, August 23, 2005

My query is slow.... What we need before we can help

In a
comment to another blog on "How to be a good Newbie" I expressed the view that my personal bugbear was a request for help which amounted to 'I have a performance problem with this SQL:' and gives an SQL with no explain plan, no table sizes, no indexes.....
Seeing another of these today, I looked for a simple checklist of what is needed to look at an SQL performance problem. I couldn't find one, so here's mine.

1. What are you running ? Show us the SQL.
2. What should it be doing ?
It may be obvious to you, but if you leave it up to the respondant to guess, you may get a less than optimal result.
3. Give us an Explain Plan. That will tell us what the Optimizer thinks it should be doing.
4. Give us the sizes of the tables. I haven't found a query yet that runs slowly when all the tables are empty.
5. What columns are indexed ?
6. If there are views involved, we need the information in all the tables in the view
7. Tell us if there are 'exotica' such as object types, database links, partitions....
8. How long is it taking ? How long do you want it to take ? A performance problem may exist in a query that only takes 30 seconds, if it is run often enough. On the other hand, you may be happy for the query to take hours.
9. First Rows or All Rows. Do you want the shortest time to see the first rows out of the result set, or the shortest time to get them all ?

The following can be run as anonymous PL/SQL with the table names listed in the collection in line 3. While this may report on some table columns which are irrelevant to the issue, it will give all the necessary facts for an initial examination of the issue.

  t_tabs sys.DBMS_DEBUG_VC2COLL :=
  v_tab varchar2(100);
  v_own varchar2(100);
  v_typ varchar2(100);
  v_user varchar2(100) := user;
  v_long varchar2(32000);
  v_ind number;
  cursor c_obj is
   select object_type, owner
   from all_objects
   where object_name = v_tab
   and owner in ('PUBLIC',v_own)
   order by decode(owner,v_own,1,2);
  cursor c_syn is
   select table_owner, table_name
   from all_synonyms
   where synonym_name = v_tab
   and owner in ('PUBLIC',v_own)
   order by decode(owner,v_own,1,2);
  cursor c_vw is
   select text from all_views
   where owner = v_own and view_name = v_tab;
  cursor c_tab is
   select table_name, num_rows, blocks, avg_row_len, sample_size, last_analyzed
   from all_tables
   where table_name = v_tab
   and owner = v_own;
  cursor c_tcols is
   select column_name, data_type, num_distinct, density, num_nulls, num_buckets,
          last_analyzed, avg_col_len, rawtohex(low_value) low, rawtohex(high_value)
   from all_tab_columns
   where table_name = v_tab
   and owner = v_own;
  cursor c_ind is
   select index_name, index_type, leaf_blocks, distinct_keys,
          Clustering_Factor, num_rows, last_analyzed
   from all_indexes
   where table_name = v_tab
   and owner = v_own;
  cursor c_icols (p_iname varchar2) is
   select c.index_name, c.column_position, c.column_name, e.column_expression
   from all_ind_columns c, all_ind_expressions e
   where e.index_owner(+) = c.index_owner
   and e.index_name(+) = c.index_name
   and e.column_position(+) = c.column_position
   and c.table_name = v_tab
   and c.table_owner = v_own
   and c.index_name = p_iname
   order by 1,2;
  PROCEDURE sp_out (p_text IN VARCHAR2, p_wrap IN NUMBER DEFAULT 1) IS
        v_text VARCHAR2(32500);
        v_point NUMBER;
        e_overflow exception;
        pragma exception_init (e_overflow, -20000);
        v_temp_arr dbms_output.chararr;
        v_lines number := 150;
        v_text := p_text;
        --Break it down into lengths up 248 characters in length
        -- If p_wrap is 1, it will try to 'wrap' the line at word breaks
        WHILE v_text IS NOT NULL LOOP
            v_point := LENGTH(v_text)+1;
            IF v_point > 248 AND p_wrap = 1 THEN
                v_point := INSTR(SUBSTR(v_text,1,248),CHR(10),-1);
            END IF;
            IF v_point = 0 AND p_wrap = 1 THEN
                v_point := INSTR(SUBSTR(v_text,1,248),' ',-1);
            END IF;
            IF v_point = 0 AND p_wrap = 1 THEN
                v_point := INSTR(SUBSTR(v_text,1,248),'/',-1);
            END IF;
            IF v_point = 0 THEN
                v_point := 248;
            END IF;
                WHEN e_overflow THEN
                    IF SUBSTR(SQLERRM,12,9) ='ORU-10027' THEN
                        --Clear out the oldest 150 lines to make room
                        --in the DBMS_OUTPUT buffer
                    END IF;
            v_text := SUBSTR(v_text,v_point+1);
        END LOOP;
  v_ind := t_tabs.first;
  while v_ind is not null loop
      v_tab := t_tabs(v_ind);
      v_own := v_user;
      open c_obj;
      fetch c_obj into v_typ, v_own;
      if c_obj%notfound then
        close c_obj;
        raise no_data_found;
      end if;
      close c_obj;
      while v_typ = 'SYNONYM' loop
        open c_syn;
        fetch c_syn into v_own, v_tab;
        close c_syn;
        open c_obj;
        fetch c_obj into v_typ, v_own;
        close c_obj;
      end loop;
      if v_typ = 'VIEW' then
        open c_vw;
        fetch c_vw into v_long;
        close c_vw;
        for rec in c_tab loop
                 ' BLKS:'||rec.blocks||
                 ' AVGLEN:'||rec.avg_row_len||
                 ' SMPL:'||rec.sample_size||
                 ' ANAL:'||to_char(rec.last_analyzed,'DDMMYY'));
        end loop;
        for rec in c_tcols loop
                 ' TYP:'||rpad(rec.data_type,10)||
                 ' VALS:'||to_char(rec.num_distinct,'999,999,990')||
                 ' DENS:'||lpad(round(rec.density,4),8)||
                 ' NULLS:'||to_char(rec.num_nulls,'999,999,990')||
                 ' HIST:'||lpad(rec.num_buckets,4));
        end loop;
        for rec in c_ind loop
                 ' ITYP:'||rpad(rec.index_type,20)||
                 ' LBLKS:'||to_char(rec.leaf_blocks,'9999,999')||
                 ' KEYS:'||to_char(rec.distinct_keys,'999,999,990')||
                 ' CLUSTR:'||round(rec.Clustering_Factor,4));
                 ' ANAL:'||to_char(rec.last_analyzed,'DDMMYY'));
          for rec2 in c_icols (rec.index_name) loop
            v_long := rec2.column_expression;
            if v_long is not null then
              v_long := ' EXP:'||substr(v_long,1,150);
            end if;
                   ' COL:'||rpad(rec2.column_name,30)||v_long);
          end loop;
        end loop;
      end if;
      v_ind := t_tabs.next(v_ind);
  end loop;

No comments: