Skip to content
February 13, 2017 / doganay

SQL TUNING USING DBMS_SQLTUNE

--sqlid
--exec DBMS_SQLTUNE.DROP_TUNING_TASK('STT_TMP_1');
DEFINE v_sql_id='30nf3tqcuj758'

DECLARE
    v_sql_id       VARCHAR2 (13) := '&v_sql_id';
    my_task_name   VARCHAR2 (30);
BEGIN
    my_task_name :=
        DBMS_SQLTUNE.create_tuning_task (sql_id      => '&v_sql_id',
                                         task_name   => 'STT_TMP_1');

    DBMS_SQLTUNE.execute_tuning_task (task_name => my_task_name);
END;
/






--sqltext
--EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('STT_TMP_1');
DECLARE
    my_task_name   VARCHAR2 (30);
    my_sqltext     CLOB;
BEGIN
    my_sqltext := 'select 1 from dual';

    my_task_name :=
        DBMS_SQLTUNE.create_tuning_task (sql_text    => my_sqltext,
                                         task_name   => 'STT_TMP_1');

    DBMS_SQLTUNE.execute_tuning_task (task_name => my_task_name);
END;
/






--historic
SELECT *
  FROM (SELECT snap_id, begin_interval_time, end_interval_time
          FROM dba_hist_snapshot
        ORDER BY snap_id DESC)
 WHERE ROWNUM <= 15;

--EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('STT_TMP_1');

DEFINE v_sql_id='bjf05cwcj5s6p'
DECLARE     my_task_name   VARCHAR2 (30);
     my_sqltext     CLOB;
BEGIN
     my_task_name := DBMS_SQLTUNE.create_tuning_task
                                        (begin_snap   => 62478,
                                         end_snap     => 62479,
                                         sql_id       => '&v_sql_id');

    DBMS_SQLTUNE.execute_tuning_task (task_name => my_task_name);
END;
/



select * from DBA_ADVISOR_TASKS;


--print tuning advice
SET PAGES 1000
SET SERVEROUTPUT ON
ALTER SESSION SET nls_language=american;

DECLARE
    xml_out   XMLTYPE;

    PROCEDURE print_clob (p_clob IN CLOB)
    IS
        v_offset       NUMBER := 1;
        v_chunk_size   NUMBER := 4000;
        v_length       NUMBER;
    BEGIN
        v_length := DBMS_LOB.getlength (p_clob);

        LOOP
            EXIT WHEN v_offset > v_length;
            DBMS_OUTPUT.put_line (
                DBMS_LOB.SUBSTR (p_clob, v_chunk_size, v_offset));
            v_offset := v_offset + v_chunk_size;
        END LOOP;
    END print_clob;
BEGIN
    DBMS_OUTPUT.enable (NULL);
    print_clob (DBMS_SQLTUNE.report_tuning_task ('STT_TMP_1'));
END;
/

Advertisements
February 10, 2017 / doganay

HOW TO DBMS OUT HUGE CLOBS

thx ronald:

https://ronaldsoracle.wordpress.com/2015/04/30/printing-a-large-xmltype-value-using-dbms_output/

 

February 10, 2017 / doganay

ORA-31603: object “SP1” of type PROCEDURE not found in schema “USER1”

add:
Use uppercase, not lowercase.

Mustafa DOGANAY's Blog


Problem:

SQL> select dbms_metadata.get_ddl('PROCEDURE','SP1','USER1') from dual;
ERROR:
ORA-31603: object "SP1" of type PROCEDURE not found in schema "USER1"
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1


Solution:

Use uppercase, not lowercase.

AND

grant select_catalog_role to user1;

View original post

January 13, 2017 / doganay

PL/SQL PROFILER

--If needed, create the PL/SQL Profiler Tables under your application schema:
--@?/rdbms/admin/proftab
--If needed, install the DBMS_PROFILER API, connected as SYS:
--@?/rdbms/admin/profload
exec dbms_profiler.start_profiler ('Test of raise procedure by mdoganay');
--do your work here
exec dbms_profiler.stop_profiler();
@profiler


For profiler.sql read this MOS note:

Customer RecommendedScript to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data (Doc ID 243755.1)

January 13, 2017 / doganay

CTAS TEXT TRUNCATED

You can see full text sql using 10053 trace:

oradebug setospid <OSPID>
oradebug unlimit
oradebug Event 10053 trace name context forever, level 12
oradebug tracefile_name
--wait some time
oradebug Event 10046 trace name context off
January 13, 2017 / doganay

EXCEPTION USAGE IN LOOPS

BEGIN
    FOR i IN (...)
    LOOP
        --BEGIN needed
        BEGIN
            --Do your work here..
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line (i.col1);
                DBMS_OUTPUT.put_line ('SQLCODE: ' || SQLCODE);
                DBMS_OUTPUT.put_line ('SQLERRM: ' || SQLERRM);
        --END needed
        END;
    END LOOP;
END;
/
January 13, 2017 / doganay

DBMS_LOCK.SLEEP ALNERNATIVES


1) JAVA(milisecond):

SQL> create or replace procedure sleep(x_ms in number) as language java
2 name 'java.lang.Thread.sleep(int)';
3 /

Procedure created.

SQL> set timing on
SQL> exec sleep(10000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.02


2) USERLOCK

@?/rdbms/admin/userlock
--use 1 for 10 ms, 100 for 1 sec
exec user_lock.sleep(100);
January 13, 2017 / doganay

SHOW VIEW NAMES IN SQL PLANS

alter session set "_simple_view_merging"=false;
alter session set "_dump_qbc_tree"=1;

Many thanks to Tanel Poder 🙂

http://blog.tanelpoder.com/2007/09/16/how-to-resolve-sql-object-and-column-names-all-the-way-to-base-tables-and-columns-in-oracle/

January 13, 2017 / doganay

LOG MINER SCRIPT CREATOR


undefine V_START V_END
set pages 1000 lines 1000 feed off
accept V_START prompt "START TIME (dd.mm.yyyy hh24:mi:ss): "
accept V_END     prompt "END TIME (dd.mm.yyyy hh24:mi:ss): "

SELECT    'exec dbms_logmnr.add_logfile(LogFileName => '''
       || name
       || ''',Options => dbms_logmnr.ADDFILE);'
  FROM gv$archived_log
 WHERE     first_time >= TO_DATE ('&&V_START', 'dd.mm.yyyy hh24:mi:ss')
       AND first_time <= TO_DATE ('&&V_END', 'dd.mm.yyyy hh24:mi:ss') UNION ALL SELECT    'exec dbms_logmnr.add_logfile(LogFileName => '''
       || MIN (MEMBER)
       || ''',Options => dbms_logmnr.ADDFILE);'
  FROM gv$log a, gv$logfile b
 WHERE     a.inst_id = b.inst_id
       AND a.group# = b.group#
       AND a.first_time >= TO_DATE ('&&V_START', 'dd.mm.yyyy hh24:mi:ss')
       AND a.first_time <= TO_DATE ('&&V_END', 'dd.mm.yyyy hh24:mi:ss') GROUP BY a.group# UNION ALL SELECT 'exec dbms_logmnr.start_logmnr(options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);'
  FROM DUAL
UNION ALL
SELECT '--select * from gv$logmnr_contents;' FROM DUAL
UNION ALL
SELECT '--exec DBMS_LOGMNR.END_LOGMNR;' FROM DUAL;

prompt
set feed on
undefine V_START V_END
January 13, 2017 / doganay

SQL PATCH

If sql can not be edited (embedded in a tool etc.) and if you can use hint, you can use sql patch.
Sql patch applies hint to your sql.
But you must use only one hint. (After first select)
So, if you sql consists of multiple inner sqls, use @SEL$ syntax.
For example:

select /*+ index(@SEL$2 table2) */ * from table 1
where col1 in (select * from table2);

So, your hint is

index(@SEL$2 table2)

for this sql.

Easily you can use this anon.block to create your sql patch:

DECLARE
    v_sql_fulltext   CLOB;
    v_sql_id         VARCHAR2 (13)   := 'az6f9s2pkj7av';
    v_hint           VARCHAR2 (4096) := 'GATHER_PLAN_STATISTICS';
BEGIN
    SELECT sql_fulltext
      INTO v_sql_fulltext
      FROM gv$sql
     WHERE sql_id = v_sql_id AND ROWNUM = 1;

    sys.dbms_sqldiag_internal.i_create_patch (
        sql_text    => v_sql_fulltext,
        hint_text   => v_hint,
        name        => 'SQL_PATCH_' || v_sql_id);
END;
/

Of course you can give whatever name you want to your sql patch.
But our sql patch name is SQL_PATCH_az6f9s2pkj7av
and there is a reason for that:
After creating sql patch, to get the sql text you must know the sql_id.
Use this:

SELECT CAST (EXTRACTVALUE (VALUE (x), '/hint') AS VARCHAR2 (500))
           AS outline_hints
  FROM XMLTABLE (
           '/outline_data/hint'
           PASSING (SELECT xmltype (comp_data) xml
                      FROM sys.sqlobj$data
                     WHERE signature =
                               (SELECT signature
                                  FROM dba_sql_patches
                                 WHERE SUBSTR (name, -13) = 'SQL_PATCH_az6f9s2pkj7av'))) x;

You can alter it:

EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH('SQL_PATCH_az6f9s2pkj7av', 'STATUS', 'DISABLED');

And finally you can delete it:

DECLARE
  PATCHNOTFND EXCEPTION;
  PRAGMA EXCEPTION_INIT(PATCHNOTFND, -13833);
BEGIN
  DBMS_SQLDIAG.DROP_SQL_PATCH('SQL_PATCH_az6f9s2pkj7av');
EXCEPTION
  WHEN PATCHNOTFND THEN
    dbms_output.put_line('SQL Patch Not Found.');
END;
/