SQL Tips

g-Solutions

SQL Tips

Oracle Information URLs

Useful Oracle SQL

 Monitoring SQL execute > 30 minutes

-- Oracle SQLs executing greater than 30 minutes
 SELECT  distinct s.SID,s.SERIAL#,s.USERNAME,
            s.SCHEMANAME,s.TERMINAL,s.LOGON_TIME, s.last_call_et AS last_call_et,
   sql_info.LAST_ACTIVE_TIME as LAST_ACTIVE_TIME,s.MACHINE as machine,s.STATUS, s.program, s.sql_id, 
            s.BLOCKING_SESSION, dbms_lob.substr(NVL(sql_info.SQL_FULLTEXT,chr(38)||'nbsp;'), 4000, 1)
            SQL_FULLTEXT, sql_info.cpu_time 
       FROM v$session s, V$sql sql_info
       where s.STATUS = 'ACTIVE'
        AND   RAWTOHEX(s.sql_address) <> '00'    
        AND   s.username is not null
        and   s.sql_address = sql_info.address  
        and   s.sql_hash_value = sql_info.hash_value                       
        and s.last_call_et >= 60*30 -- 30 minutes       
        order by s.USERNAME

 

Get Session information

 

SELECT distinct SID, SERIAL# serial, s_sql.SQL_TEXT, s_sql.SQL_ID

 FROM   v$session s, V$SQLTEXT s_sql

where USERNAME = 'HELN8'

and MODULE  = 'w3wp.exe'

and STATUS = 'ACTIVE'

and LOGON_TIME  > sysdate - 1/24

and s.SQL_ID = s_sql.SQL_ID

and upper(s_sql.SQL_TEXT) like 'select%'

 

Privileges

 

select * from session_privs -- database

select * from session_roles --  user roles

 

grant all on data_tests to app_db_mon;

 DB Roles

 

1. Create role sample_role;

2. Grant sample_role to ;

3. grant create job to sample_role;

grant execute on package_sample to sample_role;

grant select on table_sample to sample_role;

 

Using dms_advisor.sql 

DECLARE

  l_taskname     VARCHAR2(30)   := 'test_sql_access_task';

  l_task_desc    VARCHAR2(128)  := 'Test SQL Access Task';

  l_wkld_name    VARCHAR2(30)   := 'test_work_load';

  l_saved_rows   NUMBER         := 0;

  l_failed_rows  NUMBER         := 0;

  l_num_found    NUMBER;

BEGIN

  -- Create an SQL Access Advisor task.

  DBMS_ADVISOR.create_task (

    advisor_name => DBMS_ADVISOR.sqlaccess_advisor,

    task_name    => l_taskname,

    task_desc    => l_task_desc);

    

  -- Reset the task.

  DBMS_ADVISOR.reset_task(task_name => l_taskname);

 

  -- Create a workload.

  SELECT COUNT(*)

  INTO   l_num_found

  FROM   user_advisor_sqlw_sum

  WHERE  workload_name = l_wkld_name;

 

  IF l_num_found = 0 THEN

    DBMS_ADVISOR.create_sqlwkld(workload_name => l_wkld_name);

  END IF;

 

  -- Link the workload to the task.

  SELECT count(*)

  INTO   l_num_found

  FROM   user_advisor_sqla_wk_map

  WHERE  task_name     = l_taskname

  AND    workload_name = l_wkld_name;

  

  IF l_num_found = 0 THEN

    DBMS_ADVISOR.add_sqlwkld_ref(

      task_name     => l_taskname,

      workload_name => l_wkld_name);

  END IF;

  

  -- Set workload parameters.

  DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'ACTION_LIST', DBMS_ADVISOR.ADVISOR_UNUSED);

  DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'MODULE_LIST', DBMS_ADVISOR.ADVISOR_UNUSED);

  DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'SQL_LIMIT', DBMS_ADVISOR.ADVISOR_UNLIMITED);

  DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'ORDER_LIST', 'PRIORITY,OPTIMIZER_COST');

  DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'USERNAME_LIST', DBMS_ADVISOR.ADVISOR_UNUSED);

  DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'VALID_TABLE_LIST', DBMS_ADVISOR.ADVISOR_UNUSED);

 

  DBMS_ADVISOR.import_sqlwkld_sqlcache(l_wkld_name, 'REPLACE', 2, l_saved_rows, l_failed_rows);

 

  -- Set task parameters.

  DBMS_ADVISOR.set_task_parameter(l_taskname, '_MARK_IMPLEMENTATION', 'FALSE');

  DBMS_ADVISOR.set_task_parameter(l_taskname, 'EXECUTION_TYPE', 'INDEX_ONLY');

  DBMS_ADVISOR.set_task_parameter(l_taskname, 'MODE', 'COMPREHENSIVE');

  DBMS_ADVISOR.set_task_parameter(l_taskname, 'STORAGE_CHANGE', DBMS_ADVISOR.ADVISOR_UNLIMITED);

  DBMS_ADVISOR.set_task_parameter(l_taskname, 'DML_VOLATILITY', 'TRUE');

  DBMS_ADVISOR.set_task_parameter(l_taskname, 'ORDER_LIST', 'PRIORITY,OPTIMIZER_COST');

  DBMS_ADVISOR.set_task_parameter(l_taskname, 'WORKLOAD_SCOPE', 'PARTIAL');

  DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_INDEX_TABLESPACE', DBMS_ADVISOR.ADVISOR_UNUSED);

  DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_INDEX_OWNER', DBMS_ADVISOR.ADVISOR_UNUSED);

  DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_MVIEW_TABLESPACE', DBMS_ADVISOR.ADVISOR_UNUSED);

  DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_MVIEW_OWNER', DBMS_ADVISOR.ADVISOR_UNUSED);

 

  -- Execute the task.

  DBMS_ADVISOR.execute_task(task_name => 'SELECT action_key FROM HELN8_AUDIT.ENA_documents WHERE section_key = :sKey AND field_key = :fKey ORDER BY action_key');

END;

/

 

-- Display the resulting script.

SET LONG 100000

SET PAGESIZE 50000

SELECT DBMS_ADVISOR.get_task_script('test_sql_access_task') AS script

FROM   dual;

SET PAGESIZE 24

 

 

Oracle Quick Tune

quick_tune.sql

 

1) begin DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, 'NAME1','select user from dual'); end;

2) SELECT TO_CHAR(DBMS_ADVISOR.get_task_script('NAME1')) AS script FROM   dual;

Kill Oracle Sessions

ALTER SYSTEM KILL SESSION '1608,14';

Breaking a Job

 

 EXEC DBMS_JOB.BROKEN(job#,TRUE)

 

Manual Oracle Startup

 

SQLPLUS /nolog

CONNECT SYS/security AS SYSDBA

startup