Oracle Performance Tips - cool videos

Concatenated Index explained

I have very simple requirements of indexing Oracle GL base tables. So taking the opportunity to write this post.

Table: GL_JE_LINES

First of all, checking the exisiting indexes by running this query.
Select * From All_Ind_Columns Where Table_Name = 'GL_JE_LINES';

Attribute19 in the table is keeping the link to the journal import file. It is used heavily for queries. I considred few more comlumns and created indexes as follows.

EXECUTE IMMEDIATE
   'CREATE INDEX XXGL.XXGL_PERF_gl_je_lines_n1 '||
      'ON gl.gl_je_lines (attribute19, ledger_id) '||
      'TABLESPACE APPS_TS_TX_IDX';

Execute Immediate
   'CREATE INDEX xxgl.xxgl_perf_gl_je_lines_n2 '||
      'ON gl.gl_je_lines (ledger_id, period_name, effective_date, status, code_combination_id, je_header_id) '||
      'TABLESPACE APPS_TS_TX_IDX';

Execute Immediate
   'CREATE INDEX XXGL.XXGL_PERF_GL_JE_LINES_N3 '||
      'ON GL.GL_JE_LINES(attribute19) '||
      'TABLESPACE APPS_TS_TX_IDX ';

How to check which index is being used while running select statements?
Run the following commands in order to see the execution plan that shows what index is going to be used by the select.
Explain Plan For Select * From gl_je_lines Where ledger_id = 2021 and period_name = '2015-01';
select * from table(dbms_xplan.display);

In a concatenated index, the first column is the primary sort criterion and the second column determines the order only if two entries have the same value in the first column and so on.

So if a select query is written as follows, its not going to use xxgl_perf_gl_je_lines_n2 index, instead will do a full table scan:
select * from gl_je_lines Where effective_date = '15-05-2015';

More explanation is provided in the link below, a very good and simple article to read.

use-the-index-luke.com

Happy indexing!

Back to Basics: Execute Immediate and SELECT statement

Execute Immediate statement prepares and executes the dynamic statement. For single row queries we can get the data using ‘INTO’ clause and store it in the variable. We can also use bind variable using ‘USING’ clause. Placeholder for the bind variables are defined as text, preceded by colon ( : ). In this blog post, we will show you how can we use Execute Immediate statement to retrieve the data. Following is the small PL/SQL stored procedure that demonstrates the use of Execute Immediate. Connect to the database via SQL*Plus using proper credentials and create the following procedure.

CREATE OR REPLACE PROCEDURE test_proc(p_object_Name IN VARCHAR2)
AS
v_object_Type user_objects.object_type%TYPE;
BEGIN
BEGIN
EXECUTE IMMEDIATE
‘SELECT object_Type ‘
|| ‘ FROM user_objects ‘
|| ‘ WHERE object_name = : obj_name ‘
INTO v_object_Type
USING p_object_Name;
dbms_output.put_line(‘Object Type is ‘ || v_object_Type);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20950, ‘object does not exist: ‘ || p_object_name);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20951, ‘Error retrieving oject name: ‘ || SQLERRM);
END;
END test_proc;
/
Once the procedure is successfully created, let us execute it to see the results.
SQL> exec test_proc(‘TEST_PROC’);
Object Type is PROCEDURE
PL/SQL procedure successfully completed.
Here we have used bind variable to improve the performance. It is used in ‘USING’ clause. During execution : obj_name placeholder value will be replaced with the value from p_object_name.
Couple of things we have to remember when dealing with dynamic sqls are
•  ;  is not placed at the end of the SELECT statement, but it is placed at the end of the Execute Immediate statement.
• Similar thing is also with INTO clause. INTO is outside of quoted SELECT statement.
As demonstrated above, Execute Immediate is the way to deal with dynamic sqls.

Source: http://decipherinfosys.wordpress.com/2008/09/11/back-to-basics-execute-immediate-and-select-statement/

Simple setup of TNS

Simple setup of TNS

You have multiple oracle home installed in your pc! One from Oracle Client that you installed, one from Oracle Express edition database and one from oracle workflow that you installed few months back. Now you are not sure which TNSNAMES.ora is being used or should be used. The situation sounds pretty similar... isn't it? Lets try this and get rid of the dilemma!

TNS Entry:
DEV = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dev.domain.ee)(PORT=1567)))(CONNECT_DATA=(SID=sdev1)(server=dedicated)))

I prefer to use oracle home from Oracle Client. In my case this is
C:\OraHome_1

So I have added the above TNS entry in tnsnames.ora file
C:\OraHome_1\network\ADMIN\TNSNAMES.ORA
Now let's cehck the existing status of my connectivity by executing tnsping:

Automatic Partition Management for Oracle 10g

If you have a moving window partitioning scheme then you should know that in Oracle 11g you now have a brand new way of creating new partitions. Oracle 11g creates the partitions in an automatic fashion, so that when the row arrives at the table, if the partition does not exist, Oracle will create it. This is awsome, but imagine two things:

* You can’t migrate to Oracle 11g on the fly. You’re stuck in 10g
* You also need to drop the oldest partition, because you have lack of storage issues

Then if you are in this situation we have the solution for you. It’s bullet-proof tested and proven at production sites.

Every night you need to add another partition and remove the oldest. Pure moving window stuff. The format of the partition name is: PXXXX_YYYY_MM_DD, where XXXX is a sequential number that increments every day. This is daily partitioning and should only have a job running this code everynight:

declare
x varchar2(90);
s varchar2(900);
begin
-- Fetchs the name of the oldest partition
select partition_name
into x
from sys.dba_tab_partitions
where partition_position = 1
and table_name = 'MYTABLE'
and table_owner = 'MYOWNER';
-- Builds the name-string
s := 'ALTER TABLE MYOWNER.MYTABLE DROP PARTITION '||x||' UPDATE INDEXES';
-- Uses a customized report and sends it by email
-- so you can see the partitions state before and after
MYOWNER.my_pkg.sends_email('REPORT6');
-- Drops the Partition
execute immediate s;
-- And now adds another
MYOWNER.add_partition;
MYOWNER.my_pkg.sends_email('REPORT6');
--dbms_output.put_line(s);
end;

The procedure “add_partition” is another piece of automatic code that you must create prior to the previous PL/SQL block:

CREATE OR REPLACE procedure MYOWNER.add_partition
is
next_part varchar2(40);
less_than_char varchar2(20);
comando_add varchar2(1000);
BEGIN

-- Generates the name of the partition
select 'P'||to_char(to_number(substr(partition_name,2,
         instr(partition_name,'_',1)-2))+1)||'_'||
         to_char(to_date(substr(partition_name,
         instr(partition_name,'_',1)+1),'yyyy_mm_dd')+1,'yyyy_mm_dd'),
         replace(to_char(to_date(substr(partition_name,
        instr(partition_name,'_',1)+1),'yyyy_mm_dd')+2,'yyyy_mm_dd'),'_','-')
into next_part,less_than_char
from dba_tab_partitions
where table_owner = 'MYOWNER'
and table_name = 'MYTABLE'
and partition_position = (select max(partition_position)
from dba_tab_partitions where table_owner = 'MYOWNER'
and table_name = 'MYTABLE');
-- Builds the statement string
comando_add :=              'ALTER TABLE MYOWNER.MYTABLE ADD PARTITION '||next_part;
comando_add := comando_add||' VALUES LESS THAN (to_date('||chr(39)||less_than_char;
comando_add := comando_add||chr(39)||','||chr(39)||
'yyyy-mm-dd'||chr(39)||')) TABLESPACE DATA_PARTITIONED';
-- Executes the statement
execute immediate(comando_add);
--dbms_output.put_line(comando_add);
end;
/

You will have permission issues that you can resolve reading this.

Source: http://ocpdba.wordpress.com/2009/10/12/automatic-partition-management-for-oracle-10g/

Reading parameterized cursor example

declare
p_eod_signal_type varchar2(29) := 'FAGIO';
p_eod_date varchar2(29) := '2011-08-09';
v_rec_count number := 0;
 CURSOR gl_eod_txns_c(p_eod_date IN VARCHAR2, p_eod_signal_type IN VARCHAR2) IS
SELECT je_header_id,
           period,
           je_line_num,
           effective_date,
           bank_day,
           accounted_cr,
           accounted_dr,
           is_credit,
           verification_number,
           source_nr,
           clnr,
           account_nr,
           rst,
           aggr_flag,
           accounting_sequence,
           ledger_id, --added by p950nle for RESTORE
           je_category_key --added for defect 1037
    FROM   xxogl_f04_gl_eod_txns_v xfgetv
    WHERE  xfgetv.eod_date = p_eod_date
    AND xfgetv.ledger_id in decode(p_eod_signal_type, 'CAL', '2047', 'FAGIO', '2044');
   
begin
  for l2 in gl_eod_txns_c(p_eod_date, p_eod_signal_type)
  loop
     dbms_output.put_line(gl_eod_txns_c%Rowcount || ' ' || l2.ledger_id);
  end loop;
  dbms_output.put_line('Executed');
end;

Tablespace usage query

Tablespace usage in percentage

select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from  dba_data_files group by tablespace_name union all
 select  tablespace_name || '  **TEMP**'
 , sum(bytes)/1024/1024 used_mb
 from  dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
 from  dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4

Source: http://www.shutdownabort.com/dbaqueries/Structure_Tablespace.php