Useful Metalink Notes for Query Tuning and Optimizations

1. How to Use AWR Reports to Diagnose Database Performance Issues (Doc ID 1359094.1)
2. TROUBLESHOOTING: Tuning a New Query (Doc ID 372431.1)
3. TKPROF Basic Overview (Doc ID 41634.1)
4. SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)
5. TKProf Interpretation (9i and above) (Doc ID 760786.1)
6. Interpreting Raw SQL_TRACE output (Doc ID 39817.1)
7. Diagnosing and Understanding Why a Query is Not Using an Index (Doc ID 67522.1)
8. Why is my Hint Ignored? (Doc ID 69992.1)
9. Optimizing statements that contain views or subqueries (Doc ID 199070.1)
10. Optimization of Large Inlists / Multiple OR Conditions (Doc ID 62153.1)
11. Troubleshooting a Server Upgrade Resulting in Slow Query Performance (Doc ID 160089.1)
12. Diagnosing and Understanding Why a Query is Not Using an Index (Doc ID 67522.1)

Parsing delimited string for IN operator in SQL

SQL> create or replace type myTableType as table of number;
    /
Type created.

SQL> create or replace function str2tbl( p_str in varchar2 ) return myTableType
   as
       l_str   long default p_str || ',';
       l_n        number;
       l_data    myTableType := myTabletype();
   begin
       loop
           l_n := instr( l_str, ',' );
           exit when (nvl(l_n,0) = 0);
           l_data.extend;
           l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
           l_str := substr( l_str, l_n+1 );
       end loop;
       return l_data;
   end;
   /

Function created.

SQL>
SQL> select * from all_users
   where user_id in ( select *
     from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual ) )
   /

Space Information In a Database

select nvl(b.tablespace_name,
             nvl(a.tablespace_name,'UNKOWN')) name,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
       trunc(((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100,2) pct_used,
       nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name

Generate dummy data in Oracle table (Courtesy asktom.oracle.com)

create or replace procedure do_dummy_data( p_tname in varchar2,
p_records in number )
 authid current_user
 as
     l_insert long;
     l_rows   number default 0;
 begin

    execute immediate 'create table clone_' || p_tname ||
                      ' as select * from ' || p_tname ||
                      ' where 1=0';

     l_insert := 'insert into clone_' || p_tname ||
                 ' select ';

     for x in ( select data_type, data_length,
                 rpad( '9',data_precision,'9')/power(10,data_scale) maxval
                  from user_tab_columns
                 where table_name = 'CLONE_' || upper(p_tname)
                 order by column_id )
     loop
         if ( x.data_type in ('NUMBER', 'FLOAT' ))
         then
             l_insert := l_insert || 'dbms_random.value(1,' || x.maxval || '),';
         elsif ( x.data_type = 'DATE' )
         then
             l_insert := l_insert ||
                   'sysdate+dbms_random.value+dbms_random.value(1,1000),';
         else
             l_insert := l_insert || 'dbms_random.string(''A'',' ||x.data_length || '),';
         end if;
     end loop;
     l_insert := rtrim(l_insert,',') ||
                   ' from all_objects where rownum <= :n';

     loop
         execute immediate l_insert using p_records - l_rows;
         l_rows := l_rows + sql%rowcount;
         exit when ( l_rows >= p_records );
     end loop;
 end;
 /

Query to display redundant indexes on a table

Below query will display redundant indexes on a table

select  *
from
( select index_name,
         max(decode(p, 1,     c,NULL)) ||
         max(decode(p, 2,', '||c,NULL)) ||
         max(decode(p, 3,', '||c,NULL)) ||
         max(decode(p, 4,', '||c,NULL)) ||
         max(decode(p, 5,', '||c,NULL)) ||
         max(decode(p, 6,', '||c,NULL)) ||
         max(decode(p, 7,', '||c,NULL)) ||
         max(decode(p, 8,', '||c,NULL)) ||
         max(decode(p, 9,', '||c,NULL)) ||
         max(decode(p,10,', '||c,NULL)) ||
         max(decode(p,11,', '||c,NULL)) ||
         max(decode(p,12,', '||c,NULL)) ||
         max(decode(p,13,', '||c,NULL)) ||
         max(decode(p,14,', '||c,NULL)) ||
         max(decode(p,15,', '||c,NULL)) ||
         max(decode(p,16,', '||c,NULL)) index_cols
    from (select index_name, substr(column_name,1,30) c, column_position p
               from user_ind_columns )
   group by index_name  ) A,
( select index_name,
         max(decode(p, 1,     c,NULL)) ||
         max(decode(p, 2,', '||c,NULL)) ||
         max(decode(p, 3,', '||c,NULL)) ||
         max(decode(p, 4,', '||c,NULL)) ||
         max(decode(p, 5,', '||c,NULL)) ||
         max(decode(p, 6,', '||c,NULL)) ||
         max(decode(p, 7,', '||c,NULL)) ||
         max(decode(p, 8,', '||c,NULL)) ||
         max(decode(p, 9,', '||c,NULL)) ||
         max(decode(p,10,', '||c,NULL)) ||
         max(decode(p,11,', '||c,NULL)) ||
         max(decode(p,12,', '||c,NULL)) ||
         max(decode(p,13,', '||c,NULL)) ||
         max(decode(p,14,', '||c,NULL)) ||
         max(decode(p,15,', '||c,NULL)) ||
         max(decode(p,16,', '||c,NULL)) index_cols
    from (select index_name, substr(column_name,1,30) c, column_position p
               from user_ind_columns )
   group by index_name  ) B
where a.index_name <> b.index_name
  and a.index_cols like b.index_cols || '%'

Table Sizing

Try to size the tables 1st...

number_of_rows/(((100-pctfree)*(blocksize of database-90))
/(100*(avg_row_length + 3 + number_of_columns
+ number_of_cols_over_250_bytes))) = Blocks_Required

You will have to estimate the average row size for each table
(Assumptions here ... I usually guess 75% fill for varchar columns...
Add all table sizes together and multiply by 2 or 3 times ... (allow for
growth) ... you will have to understand how the data will grow in order
to make a "rough guess" at the factor to multiply by ...

If you know what you will need for indexes ... size each index

select number_of_rows/(((100-pctfree)*(blocksize of database-161))
/(100*(avg_length_of_indexed_cols+8+number_of_index ed_cols
+ num_of_ind_cols_over_127_bytes))) = Blocks_Required
Again ... allow for growth by multipling by a factor...

Rollback segments ... you should always assume large rollbacks ...
you need to know how many users, type of database activity etc ...
I would assume 4 users / rollback seg ... with each segment 5-100mg
depending on the type of db activity ...

Temporary tablespace - I always start with 500mg tablespace here
unless I know the application will require a "LOT" of sorting and grouping.

System tablespace - I always use a MIN of 250mg.

Query to determine type of lock

Create below function

create or replace function enqueue_decode( l_p1 in number ) return varchar2
as
l_str varchar2(25);
begin
select chr(bitand(l_p1,-16777216)/16777215)||
chr(bitand(l_p1, 16711680)/65535) || ' ' ||
decode( bitand(l_p1, 65535),
          0, 'No lock',
          1, 'No lock',
          2, 'Row-Share',
          3, 'Row-Exclusive',
          4, 'Share',
          5, 'Share Row-Excl',
          6, 'Exclusive' )
 into l_str
 from dual;
 return l_str;
end;

Then pass P1 value for a wait event to determine type of lock

select  enqueue_decode(P1) from dual;

Table Sizing Information

1) Get an estimate of the number of records for the table, both now, and
   projected to 6 months from now
2) Find the average length of each record. If you cannot do this
   perfectly, then try to make a good guess.
3) Determine how often the table is updated/inserted/deleted. If it is
   changed often (and the record sizes vary), then the PCTUSED would be
   low, something like 40-60. If the table is static, then I'd make the
   table PCTUSED 85 or 90.
4) Determine my database's block size:
   select value from v$parameter where name = 'db_block_size';

Now I have all of the information needed to size my table:

Records per block = (block size - 110 bytes for overhead) * (PCTUSED/100)/
                    Average Record Size

Total blocks = (records in table) / (records per block)
Total table size = blocks * block size
   
Give a little fudge factor and make this your INITIAL extent size.
Your NEXT will be determined by your 6 month growth projection.

Oracle Query to produce UNIX cal command like calendar

break on month skip 1
set linesize 20
column month 20
select lpad( Month, 20-(20-length(month))/2 ) month,
       "Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"
  from (
select to_char(dt,'fmMonthfm YYYY') month,
       to_char(dt+1,'iw') week,
       max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su",
       max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo",
       max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu",
       max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We",
       max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th",
       max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr",
       max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
  from ( select trunc(sysdate,'y')-1+rownum dt
           from all_objects
          where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') )
 group by to_char(dt,'fmMonthfm YYYY'), to_char( dt+1, 'iw' )
       )
 order by to_date( month, 'Month YYYY' ), to_number(week)

Generate intermediate values using subquery factoring

Today I wrote a query generate missing values for example table bank which contained data as show below
ACCNO    DOD              AMT
100          02-JAN-10     2000
200          10-JAN-10     1000
100          08-JAN-10     4000


If you observe for accno 100 there are no entries after 02-JAN-2010 till 08-JAN-2010 and amt in this account will be 2000 till 07-JAN-2010 but if we require to generate intermediate values then following query is written to generate values 

with date_range as
(
select max(dod) maxdt,min(dod) mindt from bank
),
date_seq as
(
select mindt+level-1 final_date from date_range
connect by level  <= (maxdt-mindt)+1
)
select to_number( substr(
            max( case when accno is not null
                      then to_char(date_seq.final_date,'yyyymmdd')||accno
                  end ) over (order by date_seq.final_date)
           , 9 ) ) accno,date_seq.final_date
,to_number( substr(
            max( case when amt is not null
                      then to_char(date_seq.final_date,'yyyymmdd')||amt
                  end ) over (order by date_seq.final_date)
           , 9 ) ) amt
from bank,date_seq where date_seq.final_date=bank.dod(+) order by final_date