Monday, September 20, 2010

STS Drop Caused error

select name,STATEMENT_COUNT from dba_sqlset where owner = 'VAZEP';

exec dbms_sqltune.drop_sqlset('STS_BSZVN56GN9RWJ');

If you get below error while dropping STS
"ORA-13757: “SQL Tuning Set” “sts_test_01″ owned by user “badger” is active."

Then Check

select description,sqlset_name
from DBA_SQLSET_REFERENCES
where owner = 'VAZEP';

And drop Sql Tuning task associate with STS(from above query)

DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'RNM_TT');

Then Drop STS

exec dbms_sqltune.drop_sqlset('STS_BSZVN56GN9RWJ');

Friday, September 10, 2010

Sql Profile Export&Import

Create Staging Table
==================
   exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'&stage_tab_name', schema_name=>'&Schema_Name');

Pack SQL Profile
==================
  EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => '&Stage_tab_name',profile_name=>'&Profile_name');

   Add more profile if you want with same command above. For multiple profiles, pl-sql code can be written to pack all profiles in one shot.

Export Staging Table
====================
    exp vazep/abc tables=SPM_0715,SPM_0719 file=spm_0719_exp.dmp log=spm0719_exp.log

Import Staging table on Target DB
=========================
    imp system/password file= file_name.dmp full=y log=mylog.lis

Unpack SQL Profile

    EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(staging_table_name => '&Stage_Tab_Name');
    EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => '&Stage_Tab_Name');

Sql Profile Notes

select * from dba_sql_profiles;

exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_0129d2c93fe60000');

The current parameter values are displayed using the %_ADVISOR_PARAMETERS views.
COLUMN parameter_value FORMAT A30

SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name IN ('ACCEPT_SQL_PROFILES',
'MAX_SQL_PROFILES_PER_EXEC',
'MAX_AUTO_SQL_PROFILES');

The following code shows how the SET_TUNING_TASK_PARAMETER procedure is used to turn on acceptance of automatically generated SQL profiles.
BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter(
task_name => 'SYS_AUTO_SQL_TUNING_TASK',
parameter => 'ACCEPT_SQL_PROFILES',
value => 'TRUE');
END;
/

The REPORT_AUTO_TUNING_TASK function of the DBMS_SQLTUNE package returns a CLOB containing a report from the specified automatic tuning task. Setting the BEGIN_EXEC and END_EXEC parameters to NULL produces a report from the most recent execution.
VARIABLE l_report CLOB;
BEGIN
:l_report := DBMS_SQLTUNE.report_auto_tuning_task(
begin_exec => NULL,
end_exec => NULL,
type => DBMS_SQLTUNE.type_text, -- 'TEXT'
level => DBMS_SQLTUNE.level_typical, -- 'TYPICAL'
section => DBMS_SQLTUNE.section_all, -- 'ALL'
object_id => NULL,
result_limit => NULL);
END;
/

SET LONG 1000000
PRINT :l_report

Sql Tuning Advisor-Manual Steps

SET SERVEROUTPUT ON


--For a SQL statement
exec DBMS_SQLTUNE.CREATE_SQLSET('STS_AUG18_5KR');

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(655,658,
'sql_id='||CHR(39)||'5kr1m2rx3ns2g'||CHR(39)||' and plan_hash_value=287817245',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('STS_AUG18_5KR', baseline_ref_cursor);
end;

-----------------------------------------

--For a ALL SQL statement from snapshot range
exec DBMS_SQLTUNE.CREATE_SQLSET('&STS_NAME');

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(655,658,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('&STS_NAME', baseline_ref_cursor);
end;

-----------------------------------------------


--For a top 10 SQL statement from snapshot range

exec DBMS_SQLTUNE.CREATE_SQLSET('&STS_NAME');

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(655,658,
NULL,NULL,'ELAPSED_TIME',NULL,NULL,1,10,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('&STS_NAME', baseline_ref_cursor);
end;

-----------------------------------------------


--Note := Last parameter "ALL" is required to capture plan in STS . By default its BASIC, which dont capture plan. This is
-- required for STS which in turn used for SPM load. OEM by default set this as BASIC.




-- Tuning task created for specific a statement from the AWR.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 764,
end_snap => 938,
sql_id => '19v5guvsgcd1v',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '19v5guvsgcd1v_AWR_tuning_task',
description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created for specific a statement from the cursor cache.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '&SQL_ID',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '&TASK_NAME',
description => 'Tuning task for statement 19v5guvsgcd1v.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created from an SQL tuning set.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sqlset_name => '&STS_NAME',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '&STA_NAME',
description => 'Tuning task for an SQL tuning set.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created for a manually specified statement.
DECLARE
l_sql VARCHAR2(500);
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql := 'SELECT e.*, d.* ' ||
'FROM emp e JOIN dept d ON e.deptno = d.deptno ' ||
'WHERE NVL(empno, ''0'') = :empno';

l_sql := 'Select receiver_fund, nvl(arf,0), price_date, price, last_update_date '||
'FROM VALU_PEN_UV_CHANGES_14 WHERE last_update_date > '':SYS_B_1'' '||
'AND receiver_fund = '':SYS_B_2'' ';

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text => l_sql,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'scott',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'emp_dept_tuning_task',
description => 'Tuning task for an EMP to DEPT join query.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/


SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='&STS_NAME';

select sql_id, substr(sql_text,1, 15) text
from dba_sqlset_statements
where sqlset_name = 'STS_AUG18_5KR'
order by sql_id;


Run Tuning Task
===========

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&TASK_NAME');
During the execution phase you may wish to pause and restart the task, cancel it or reset the task to allow it to be re-executed:
-- Interrupt and resume a tuning task.
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'emp_dept_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'emp_dept_tuning_task');

-- Cancel a tuning task.
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'emp_dept_tuning_task');

-- Reset a tuning task allowing it to be re-executed.
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'emp_dept_tuning_task');


The status of the tuning task can be monitored using the DBA_ADVISOR_LOG view:
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';


Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function:
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('&TASK_NAME') AS recommendations FROM dual;
SET PAGESIZE 24


If the recommendations of the SQL tuning advisor include a suggested profile you can choose to accept it using the ACCEPT_SQL_PROFILE procedure:
SET SERVEROUTPUT ON
DECLARE
l_sql_tune_task_id VARCHAR2(20);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
task_name => '&TASK_NAME',
name => '&Profile_name');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
The NAME parameter is used to specify a name for the profile. If it is not specified a system generated name will be used.


The STATUS, NAME, DESCRIPTION, and CATEGORY attributes of an SQL profile can be altered using the ALTER_SQL_PROFILE procedure:
BEGIN
DBMS_SQLTUNE.alter_sql_profile (
name => '&Profile_Name',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/
Existing SQL profiles can be dropped using the DROP_SQL_PROFILE procedure:
BEGIN
DBMS_SQLTUNE.drop_sql_profile (
name => 'emp_dept_profile',
ignore => TRUE);
END;
/

Display Plan for last executed SQL

Display Plan for last executed SQL
==========================================

1) Execute SQL

2) Run Below to get plan.

select * from table(dbms_xplan.display_cursor);

Display Plan for Any SQL in Cursor Cache
=============================================

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'&YOUR_SQL_ID'));

STS Notes

Display Contents of a STS
=============================
SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='test3';


select sql_id, substr(sql_text,1, 15) text from dba_sqlset_statements where sqlset_name = 'test3' order by sql_id;

To Verify the execution Plan of a SQL_ID in the STS for an user sql.
======================================================================

SELECT * FROM table ( DBMS_XPLAN.DISPLAY_SQLSET( '&STS_NAME','&SQL_ID'));

SPM Notes

select count(*) from dba_sql_plan_baselines;

select count(distinct sql_handle) from dba_sql_plan_baselines;

select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines
where sql_handle='SYS_SQL_32814757f4b84f6b';


Display SQL_HANDLE having more than one baseline AKA SQL_PLAN
================================================================
select sql_handle from dba_sql_plan_baselines group by sql_handle
having count(*) > 1

select sql_handle,plan_name,OPTIMIZER_COST,enabled,accepted from dba_sql_plan_baselines where sql_handle in
(select sql_handle from dba_sql_plan_baselines group by sql_handle having count(*) > 1)
and PARSING_SCHEMA_NAME='&SCHEMA_NAME'
order by 1 asc,5 desc;


Displaying SQL Plan Baselines
=================================
SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_d90440b9ed3324c0'));

SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'&P_Plan_name'));

SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle=>'&P_sql_handle'));



To Evolve Plan and Acctpt it after VERIFY
===========================================
-- set long as below to get evolve, displayed on sql-plus
set long 9999

SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9') from dual;

--Specify COMMIT=>'NO' , if we need to just verify and report But Not to accept.

SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9',VERIFY=>'YES',COMMIT=>'NO') from dual;

SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&SQL_HANDLE',VERIFY=>'YES',COMMIT=>'NO') from dual;

SELECT DBMS_SPM.evolve_sql_plan_baseline(plan_name => '&plan_name',VERIFY=>'YES',COMMIT=>'NO') from dual;

Load Baseline Plan for A SQL from Cursor Cache
===================================
1) Find SQL_id from v$SQL

SELECT sql_id
FROM v$sql
WHERE sql_text LIKE '%spm_test_tab%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%'
AND sql_text NOT LIKE '%EXPLAIN%';

2) Load SQL_ID into Baseline from Cursor Cache

SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => 'gat6z1bc6nc2d');

DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
Plans Loaded: 1



Load the Sql Plan Baseline from STS.
========================================

set serveroutput on
declare
my_integer pls_integer;
begin
my_integer := dbms_spm.load_plans_from_sqlset (
sqlset_name => '&STS_NAME',
sqlset_owner => 'SYS',
fixed => 'NO',
enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_integer);
end;
/


set serveroutput on
declare
my_integer pls_integer;
begin
my_integer := dbms_spm.load_plans_from_sqlset (
sqlset_name => '&STS_NAME');
DBMS_OUTPUT.PUT_line(my_integer);
end;
/

To Check Cache for SPM Baseline USAGE
========================================
select decode(SQL_PLAN_BASELINE,null,'NO','YES'),count(*) from V$SQL
group by decode(SQL_PLAN_BASELINE,null,'NO','YES');



disbale Plan for specific sql_handle with specific plan_name
=============================================================
declare
pbsts varchar2(30);
begin
pbsts := dbms_spm.alter_sql_plan_baseline('&SQL_HANDLE','&Plan_Name','accepted','NO');
end;
/

Enable Plan for specific sql_handle with specific plan_name
=============================================================
declare
pbsts varchar2(30);
begin
exec :pbsts := dbms_spm.alter_sql_plan_baseline('SYS_SQL_7de69bb90f3e54d2','SYS_SQL_PLAN_0f3e54d254bc8843','accepted','YES');
end;
/

To drop all the plans associated with a sql_handle.
====================================================
declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_32814757f4b84f6b',plan_name=>null);
END;
/

To drop only a specific sql_handle with specific plan_name
=========================================================
declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_32814757f4b84f6b',plan_name=>'SYS_SQL_PLAN_f4b84f6bbce35b3e');
END;
/

The following PL/SQL code will drop the entire SQL PLAN MANAGEMENT Repositry
============================================================================
connect / as sysdba
select count(*) from dba_sql_plan_baselines;

declare
pgn number;
sqlhdl varchar2(30);
cursor hdl_cur is
select distinct sql_handle from dba_sql_plan_baselines;
begin
open hdl_cur;

loop
fetch hdl_cur into sqlhdl;
exit when hdl_cur%NOTFOUND;

pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>sqlhdl);
end loop;

close hdl_cur;
commit;
end;
/

select count(*) from dba_sql_plan_baselines;



The following PL/SQL code will drop the non-accepted PLANs
============================================================================
connect / as sysdba
select count(*) from dba_sql_plan_baselines where accepted = 'NO' and parsing_schema_name='&SCHEMA_NAME';

declare
pgn number;
sqlhdl varchar2(100);
planname varchar2(100);
cursor hdl_cur is
select distinct sql_handle,plan_name from dba_sql_plan_baselines where accepted = 'NO' and parsing_schema_name='&SCHEMA_NAME';

begin
open hdl_cur;

loop
fetch hdl_cur into sqlhdl,planname;
exit when hdl_cur%NOTFOUND;

pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>sqlhdl,plan_name=>planname);
end loop;

close hdl_cur;

commit;
end;
/

select count(*) from dba_sql_plan_baselines where accepted = 'NO' and parsing_schema_name='&SCHEMA_NAME';




The following PL/SQL code will drop baseline except for specified PARSING_SCHEMA's
============================================================================
connect / as sysdba
select count(*) from dba_sql_plan_baselines ;

declare
pgn number;
sqlhdl varchar2(100);
planname varchar2(100);
cursor hdl_cur is
select distinct sql_handle from dba_sql_plan_baselines where parsing_schema_name not in ('&SCHEMA_NAME1','&SCHEMA_NAME2');

begin
open hdl_cur;

loop
fetch hdl_cur into sqlhdl;
exit when hdl_cur%NOTFOUND;

pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>sqlhdl);
end loop;

close hdl_cur;

commit;
end;
/

select count(*) from dba_sql_plan_baselines ;




To PAck SPM BaseLine And unpack on Different/same database
==========================

Step1==> Create staging table
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => '&Table_nam',
table_owner => 'VAZEP',
tablespace_name => 'USERS' );
END;
/


BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => '&Table_nam' );
END;
/

Step2==>Pack Baseline in staging table
SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
BEGIN
l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
table_name => '&Table_Nam',
table_owner => 'VAZEP');

DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/

SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
BEGIN
l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
table_name => '&Table_Nam');

DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/

SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
BEGIN
l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
table_name => '&Table_Nam',
sql_handle => '&SQL_HANDLE');

DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/

Step3==>Transfer table to destination database using DB link or exp/imp

exp vazep/Nationwide1 tables=SPM_0715 file=spm_0715_exp.dmp log=spm0715_exp.log

imp vazep/Nationwide1 file=spm_0715_exp.dmp full=y log=spm0715_imp.log

Step4==>Unpack staging table into SPM

SET SERVEROUTPUT ON
DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name => 'spm_0709',
table_owner => 'VAZEP',
creator => 'VAZEP');

DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/

SET SERVEROUTPUT ON
DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name => '&Table_nam' );
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/

tempfile operations

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

select username,TEMPORARY_TABLESPACE from dba_users order by 1

select file_name,bytes from dba_temp_files;


ALTER USER scott TEMPORARY TABLESPACE temp;



Monitoring tempfile
====================
select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER; 

Adding tempfile
======================
ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;

Dropping tempfile
==================
ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING DATAFILES;

Various other operations on tempfile
========================================
CREATE TEMPORARY TABLESPACE temp; --for OMF

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;




Case in-sensitive search

To enable case in-sensitive search
============================
alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT=BINARY_CI

ABove would cause CBO to use full table scans on some tables (as indexes are not case sensitive by default), so performance may have big impact.

Workaround
============
Create linguistics indexes on such table columns, so that CBO would use such indexes for case in-sensitive queries. e.g

CREATE INDEX nls_tst2_ind on TST2(nlssort(f1,'NLS_SORT=BINARY_CI'));


CREATE INDEX nls_ind_tst on APRIMO.aud_member_ext_attributes(nlssort(ExtValue3906,'NLS_SORT=BINARY_CI'));

CREATE INDEX APRIMO.aud_mem_ext_attr_3906_ind on APRIMO.aud_member_ext_attributes(nlssort(ExtValue3906,'NLS_SORT=BINARY_CI'));

Wednesday, September 8, 2010

Enable STored Outline DB level

Stored Outline is replaced by SPM in 11g. Stored Outline is valid only pre 11g version.

If cursor_sharing=FORCE then outline is disabled.


Stored outlines can be generated for a single
statement or at the session or the instance level

ALTER SESSION set create_stored_outlines = {TRUE|category|FALSE}
ALTER SYSTEM set create_stored_outlines = {TRUE|category|FALSE} [NOOVERRIDE]
CREATE OUTLINE outline [FOR CATEGORY category] ON statement


Exporting Oulines

===============

EXP OUTLN/outln_password FILE=exp-file TABLES= 'OL$' 'OL$HINTS' 'OL$NODES'
QUERY='WHERE CATEGORY="problemcat"'

A Stored Outline will only be used when the parameter USE_STORED_OUTLINES is set persistently. If parameter is set using alter session or by defining in pfile/spfile the change is not permanent and will no longer be set after database has been restarted.

A STARTUP trigger needs to be defined in order to make this setting persistent after restart of database

Solution

Create the following trigger to be run on startup of the database:

create or replace trigger enable_outlines_trig
after startup on database
begin
execute immediate('alter system set use_stored_outlines=true');
end;

Tuesday, September 7, 2010

Windows Invisible folder

To hide completely(not even explorer or command line can see it)
==============
attrib "folder name" +s +h

To Unhide folder
==================
attrib "folder name" -s -h

Friday, September 3, 2010

find stale stat objects

DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_schema_stats(ownname=>'VIDSDDL',objlist=>ObjList, options=>'LIST STALE');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/

DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_schema_stats(USER,objlist=>ObjList, options=>'LIST STALE');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/


--Below will gather stats(AUTO) and lists objects for which stat gathered(stale)

set serveroutput on
declare
mystaleobjs1 dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(USER, options=>'GATHER AUTO',method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE,objlist=>mystaleobjs1);
dbms_output.put_line('COPROD Stale Stat refresh Completed for Below Objects. Total Objects: '||mystaleobjs1.count);
dbms_output.put_line('===========================================================================');
dbms_output.put_line('');
for i in 1 .. mystaleobjs1.count loop
dbms_output.put_line(mystaleobjs1(i).ownname||'==>'||mystaleobjs1(i).objtype||'==>'||mystaleobjs1(i).objname||'==>'||mystaleobjs1(i).partname);
end loop;
end;
/

Thursday, September 2, 2010

linux Tips

To Change case(Translate command
=================================
$echo ${ORAID} | tr 'A-Z' 'a-z'
$ tr 'A-Z' 'a-z' <> newfile
To translate braces into parentheses, enter:
$tr '{}' '()' <> newfile


Run Command as diffrent user
$su orartp "-c crontab -l"
$sudo -u orartp crontab -l

Serach files and move them to seperate folder
==============================================

find . -mtime +7 -exec ls -ltr {} \; ==>Display files

find /usr/tivoli/cat_maint/log -mtime +2 -exec ls -ltr {} \;

find . -mtime +7 -exec mv {} /usr/tivoli/cat_maint/abc \;==>Moves files to diff folder

find . -mtime +7 -exec rm {} \; ==>Find and remove files.
AWK script Examples
=====================

Display 7th and 4th field seperated by
===================================
$df -g | grep oraarch | awk '{print $7 "==>" $4}'

$df -g | grep oraarch | awk '{print $7 "==>" $4 " Used ==>Free GB==>" $3}'

$ awk -f myscript.awk myfile.in

To change Permission file from find command
============================================
find . -type f -exec chmod -x '{}' \;

chmod -R o+x *

chmod -R g+x *

To display line which does not contain "/" at the start in a file
======================================================================

$cat init.ora |grep -v "^/"
$cat init.ora |grep -v "^#"

Searching for a particular Text in the directory and sub-directories under that
===================================================================================

$find . -name "*" -exec grep -il {} \; | tee

Find all files except owned by a specific user
=================================================
Will list the file names containing the search string.

$find . ! -user oracle9i -print

‘ ! ‘ negates the expression. The other example will list all the files not owned by user oracle9i.

Find files having some specific permission
============================================

$find . –name *.* -perm 664 -print


Searching for links in all the folders under the directory and output to a text file to create a script to change the links.
================================================================================================================================

$find . -type l -print -exec ls -trl {} \; | grep -i uat | awk '{ print $11, $9 }' | tee change_link.sh



Linux OS Service 'ntpd'
=========================
This service is handled by init.d script /etc/init.d/ntp.

Start the service as follows:

#/sbin/service ntpd start
# /sbin/service ntpd condrestart ==>Start it if not running

Stop the service as follows:

# /sbin/service ntpd stop

Check if the serivce is started or stopped:

# /sbin/service ntpd status

Determine which system run levels the service is active:

# /sbin/chkconfig --list ntpd

Configuration file is located @ /etc/sysconfig/ntpd

TO check what options its running with currently
#ps -o args -p `cat /var/run/ntpd.pid`

RPM Download
==================

http://ftp.riken.go.jp/Linux/cern/slc41/x86_64/yum/updates/repodata/repoview/binutils-0-2.15.92.0.2-25.html


To Run command as different User from root
===============================================

sudo -u orapid /home/orapid/1.sh

sudo -u orapid /usr/tivoli/cat_maint/cat_maint.sh

sudo -u orapid /usr/tivoli/cat_maint/cat_maint.sh PID



To display Server Info
=====================

$grep MemTotal /proc/meminfo
$uname -r
$fdisk -l ==>To list raw devices configured on the server

Display status of Raw Devices
=============================

#service rawdevices status


Linux Filesystems
====================

/etc/fstab displays filesystems on server which can be be mounted with mount command.


Running job in background and nohup.(nohup.out in current folder displays output of running process)
====================================

$nohup ./costress_daily_stat.sh &


Killing Stubborn Unix Tasks
==============================

$cat /dev/null > /dev/ttyname kill –9

Remarks: This command is indispensable when killing stubborn UNIX tasks.

To see When the Server was started
==========================================

$uptime

To see the previous logons and logoffs
======================================

$last

# last shutdown

# last root console

To shutdown the Server
===========================

$shutdown –Fr

Show number of active Oracle dedicated Connection Users
===========================================================

$ps –ef | grep $ORACLE_SID | grep –v grep | grep –v ora_ | wc -l

Determining the type of the file
====================================

$file or file *



Taking TAR Backup on Internal (Default) Tape Drive
==========================================================

Tested on AIX

$mt -f /dev/rmt1 rewind
cd /
$tar -cvf /dev/rmt0 ./prodr11i ./prod_dat ./prod_idx ./prod_msc

To view the list of files backed up

$tar –tvf /dev/rmt0

Installing, upgrading, removing, querying package using rpm in redhat
=========================================================================

To install a package (i=install v=verbose h=show hash marks) :-
#rpm -ivh

To uninstall (erase) a package
#rpm -e

To upgrade a package
#rpm -Uvh

To test a package to see how it would install (without installing, also checks dependencies)
#rpm -Uvh --test

To query whether a package has been installed or not
#rpm -qa | grep


To Open or block ports on RedHat Linux
=============================================

$/usr/sbin/lokkit


Displaying Allocated RAM Memory Segments in Unix
==================================================

$ipcs –pm (Inter process control system)



Export/Zip and Unzip/Import using UNIX Pipes
=================================================
To do this, you would create a pipe (using the unix mknod command), run the programs (in either order), with the first one in the background (using "&" at the end), which use that pipe like a file, and then remove the pipe. Below shows a full export to a zipped dump file:

$mknod /tmp/exp_pipe p
$gzip -cNf prod.dmp.gz &
$exp system/systempassword file=/tmp/exp_pipe full=y compress=n log=prod.dmp.log
$rm -f /tmp/exp_pipe

Below shows a full import with that zipped dump file:

$mknod /tmp/imp_pipe p
$gunzip -c prod.dmp.gz >/tmp/imp_pipe &
$imp system/systempassword file=/tmp/imp_pipe full=y ignore=y buffer=1024000 commit=y \
log=prod.log
$rm -f /tmp/imp_pipe

Manual STS-FROM-AWR load into SPM

--For a SQL statement
exec DBMS_SQLTUNE.CREATE_SQLSET('STS_AUG18_5KR');

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(655,658,
'sql_id='||CHR(39)||'5kr1m2rx3ns2g'||CHR(39)||' and plan_hash_value=287817245',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('STS_AUG18_5KR', baseline_ref_cursor);
end;

-----------------------------------------

--For a ALL SQL statement from snapshot range
exec DBMS_SQLTUNE.CREATE_SQLSET('&STS_NAME');

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(655,658,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('&STS_NAME', baseline_ref_cursor);
end;

-----------------------------------------------


--For a top 10 SQL statement from snapshot range

exec DBMS_SQLTUNE.CREATE_SQLSET('&STS_NAME');

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(655,658,
NULL,NULL,'ELAPSED_TIME',NULL,NULL,1,10,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('&STS_NAME', baseline_ref_cursor);
end;

-----------------------------------------------


--Note := Last parameter "ALL" is required to capture plan in STS . By default its BASIC, which dont capture plan. This is
-- required for STS which in turn used for SPM load. OEM by default set this as BASIC.


SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='&STS_NAME';

select sql_id, substr(sql_text,1, 15) text
from dba_sqlset_statements
where sqlset_name = 'STS_AUG18_5KR'
order by sql_id;


SELECT * FROM table (
DBMS_XPLAN.DISPLAY_SQLSET(
'STS_AUG18_5KR','5kr1m2rx3ns2g'));


select count(*) from dba_sql_plan_baselines;

set serveroutput on
declare
my_integer pls_integer;
begin
my_integer := dbms_spm.load_plans_from_sqlset (
sqlset_name => 'STS_AUG18_5KR',
sqlset_owner => 'VAZEP',
fixed => 'NO',
enabled => 'YES');
DBMS_OUTPUT.PUT_line('Count of Plans Loaded '||to_char(my_integer));
end;
/


select count(*) from dba_sql_plan_baselines;

SQL bind extract

To check bind info from v$sql

SELECT dbms_sqltune.extract_bind(bind_data,1).value_string FROM V$SQL WHERE SQL_ID=’8j9q64g6ctqbq’;
SELECT dbms_sqltune.extract_bind(bind_data,2).value_string FROM V$SQL WHERE SQL_ID=’8j9q64g6ctqbq’;

To check bind info from AWR
========================
create sql tuning set from awr, which includes target sql statements
check dba_sqlset_statements, table for sql and bind availability(bind_data,bind_captured columns)
then use dbms_sqltune.EXTRACT_BIND function to get bind info


This is available only in 11g