Get object id from rowid

 

Get object id from rowid:

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

Some time we have rowid casuing issue, but we dont know for which object you can find object id from the below query.

Select DBMS_ROWID.rowid_object('give your row id here') from dual;

Get init trans value of Object

 Get init trans value of Object:

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

select table_name, INI_TRANS  from dba_tables  where table_name = 'TABLE_NAME';

 SELECT table_name, index_name, status, ini_trans   FROM DBA_INDEXES

  

INITTRANS value set double for index than object, like below ex:

ALTER TABLE TABLE_NAME MOVE INITRANS 10;

ALTER INDEX TABLE_NAME_PK REBUILD INITRANS 20;

ALTER INDEX TABLE_NAME_I1 REBUILD INITRANS 20;

Get DDL from database

 Get DDL from database:

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

SQL>set lines 120

SQL>set pages 99999

SQL>set long 1000000

SQL> SELECT DBMS_METADATA.GET_DDL('<object_type','<object_name>','<object owner>') FROM DUAL;

select DBMS_METADATA.GET_DDL(object_type, object_name) from user_objects where object_type = 'TABLE' and object_name = 'MY_TABLE'

Blocking session detail

Try below 2 querys for blocking session detail:

For which SQL is currently waiting on:

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

select

   sid,

   sql_text 

from

   v$session s,

   v$sql q 

where

   sid in

   (select

      sid

   from

      v$session

  where

      state in ('WAITING')

  and

      wait_class != 'Idle'

  and 

      event='enq: TX - row lock contention'

  and 

     (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

The blocking session is:

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

select

   blocking_session,

   sid,    serial#,

   wait_class,

   seconds_in_wait 

from

   v$session 

where

   blocking_session is not NULL

order by

   blocking_session;

Query for checking archive log at RDS

 

 Query for checking archive log at RDS.

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

select * from table (rdsadmin.rds_file_util.listdir(p_directory => 'ARCHIVELOG_DIR')) order by filename;

select min(filename) from table (rdsadmin.rds_file_util.listdir(p_directory => 'ARCHIVELOG_DIR')) where filename like 'archive_name%';

Creating SQL Tuning Task with sql_id

Creating SQL Tuning Task with sql_id
=============================

First get sql_id of sql those who try to create task

Hope as dba you all know how get sql_id. and here i take below sql-id to create task

sql_id= 2w3y1nfj1uanf

Note: remember in below red color sql_id change as per your id.

Step1: - Create task: 


SET serveroutput ON

DECLARE
  v_tune_taskid  VARCHAR2(100);
BEGIN
  v_tune_taskid := dbms_sqltune.create_tuning_task (
                          sql_id      => '2w3y1nfj1uanf',
                          scope       => dbms_sqltune.scope_comprehensive,
                          time_limit  => 30,
                          task_name   => 'sql_tuning_task_2w3y1nfj1uanf',
                          description => 'Tuning task sql_id 2w3y1nfj1uanf');
  dbms_output.put_line('taskid = ' || v_tune_taskid);
END;
/


PL/SQL procedure successfully completed.

successful mean task created

Step2: - verify task as you created.


SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_2w3y1nfj1uanf';


Step3:  Execute SQL Tuning task to get recommendation. 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_2w3y1nfj1uanf');

Step4:  once complete run verify status of job. 

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_2w3y1nfj1uanf';


Step5:  Run report to get info. 

SET LINES 150
SET pages 50000
SET long 5000000
SET longc 5000000


SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_2w3y1nfj1uanf') AS recommendations FROM dual;

====================================================================
make below step recomdation task need to run or need if run recommendation
example jobs looks execute dbms_sqltune.accept_sql_profile
=====================================================================
Step6:  drop task once finish your recomdation run . 

BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_2w3y1nfj1uanf');
END;

/

SQL Tuning Task Creation ORA-13780: SQL statement does not exist.

SQL Tuning Task Creation ORA-13780: SQL statement does not exist.
=================================================


example:-

declare
 l_sql_tune_task_id  varchar2(100);
begin
 l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
        sql_id      => 'your sql_id',
        scope       => dbms_sqltune.scope_comprehensive,
        time_limit  => 10800,
        task_name   => 'tuning task name',
        description => 'tuning task description');
 dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
end;
/

declare
*
ERROR at line 1:
ORA-13780: SQL statement does not exist.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 125
ORA-06512: at "SYS.DBMS_SQLTUNE", line 655

solution :----

 SELECT SNAP_ID FROM DBA_HIST_SQLSTAT
WHERE SQL_ID='sql_id'
ORDER BY SNAP_ID;


===========now create task============

declare
 l_sql_tune_task_id varchar2(100);
begin
 l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
  begin_snap => 12345, 
  end_snap => 12350,
  sql_id => 'your sql_id',
  scope => dbms_sqltune.scope_comprehensive,
  time_limit => 10800,
  task_name => 'tuning task_name',
  description => 'tuning task description');
 dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
end;
/






===============hope it helps you================