Oracle

Flushing caches for testing:

  • ALTER SYSTEM FLUSH BUFFER_CACHE;
  • alter system flush shared_pool;
Finding unusable, invalid, and disabled indexes:

  • select owner, index_name from dba_indexes where status='UNUSABLE';
  • select index_name, status from dba_indexes where status = 'DISABLED';
  • select owner,table_name,index_name,last_analyzed,status from dba_indexes where status != 'VALID';
Rebuild index:

  • alter index XXX.XXX rebuild;

  BEGIN

    FOR i IN

      (SELECT index_name 

       FROM dba_indexes 

       WHERE status != 'VALID' 

                       AND table_name='')

    LOOP

      EXECUTE immediate 'alter index ' || i.index_name || 'rebuild';

    END LOOP;

  END;

  /


Compute schema statistics:

Auto is superior to estimate
Use "cascade => TRUE" to also update all index stats on the table.
  • EXEC DBMS_STATS.gather_table_stats('schemaName', 'tableName', cascade => TRUE, estimate_percent => dbms_stats.auto_sample_size);
  • EXEC DBMS_UTILITY.ANALYZE_SCHEMA('schemaName','ESTIMATE', estimate_percent => dbms_stats.auto_sample_size);
  • exec DBMS_UTILITY.ANALYZE_SCHEMA('schemaName','ESTIMATE', estimate_percent => 10);
  • exec DBMS_UTILITY.ANALYZE_SCHEMA('schemaName','COMPUTE');
Hints to pin/unpin data in buffer cache:

  • alter table XXX storage (buffer_pool keep);
  • alter index XXX storage (buffer_pool keep);
  • alter table XXX storage (buffer_pool default);
  • alter index XXX storage (buffer_pool default);
Recently executed queries:
  • SELECT a.USERNAME, b.sql_text, a.STATUS FROM V$SESSION a INNER JOIN V$SQLAREA b ON a.SQL_ID = b.SQL_ID;
  • select sql_text from v$sqlarea where sql_text like '%someText%' order by last_active_time desc;
use gv$session on multi-node RAC

SQL datapump importing/exporting
Oracle 11g R2 (11.2) sequence numbers skip 

 Note on Using Sequences with Deferred Segments If you attempt to insert a sequence value into a table that uses deferred segment creation, the first value that the sequence returns will be skipped.

 The solution is to run “alter system set deferred_segment_creation=false;” as system user. This setting is a one-time thing for a database install.

Out of disk space:

While running high volume and performance tests, Oracle keeps consuming all available disk space. Discovered it was the undo and temp tablespaces. The following statements recover the space.

ALTER DATABASE DATAFILE '/oracle_ee/app/oradata/orcl/undotbs01.dbf' RESIZE 200m;

ALTER TABLESPACE temp SHRINK SPACE KEEP 256M;

Moving temp and undo tablespaces:

select * from dba_tablespaces;
select * from dba_data_files;

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/media/oradata/temp2_01.dbf'
SIZE 100M REUSE AUTOEXTEND ON MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

create undo tablespace undotbs2 datafile '/media/oradata/undo02.dbf' size 2000m AUTOEXTEND ON MAXSIZE unlimited EXTENT MANAGEMENT LOCAL;

alter system set undo_tablespace= undotbs2 ;

 Objects in a tablespace

SELECT owner, segment_name, segment_type
FROM   dba_segments
WHERE  lower(tablespace_name) like lower('%&tsName%')
ORDER BY owner, segment_name;


 User's tablespaces

SELECT tablespace_name, ceil(sum(bytes) / 1024 / 1024) "MB"
FROM dba_extents
WHERE owner like '&userName'
GROUP BY tablespace_name
ORDER BY tablespace_name;


 Dropping objects:

drop tablespace undotbs1 including contents and datafiles;

Faster count avoiding full index/table scan with sampling:

 1% sample
SELECT COUNT(*) * 100 FROM SAMPLE (1);

 Dynamic SQL:
DECLARE
  sqlRec  VARCHAR2(2000);
  CURSOR sqlCur IS select 'drop index ' || index_name from all_indexes where table_name='TABLE_NAME' and index_name not like '%PK%';
BEGIN
  OPEN sqlCur;
  LOOP
    FETCH sqlCur INTO sqlRec;
    EXIT WHEN sqlCur%NOTFOUND;
    EXECUTE IMMEDIATE sqlRec;
  END LOOP;
  CLOSE sqlCur;
END;

 Drop indexes on table:
  CURSOR sqlCur IS select 'drop index ' || index_name from all_indexes where table_name='TABLE_NAME' and index_name not like '%PK%';

Analyze indexes sample 2%:



  CURSOR sqlCur IS select 'analyze index ' || index_name || ' estimate statistics sample 2 percent' from all_indexes where table_name='TABLE_NAME';


Database free space by tablespace:



SELECT tablespace_name,
  file_id,
  file_name,
  dfsizeMB,
  hwmMB,
  dffreeMB,
  TRUNC((dffreeMB/dfsizeMB)*100,2) "% Free",
  TRUNC(dfsizeMB -hwmMB,2) "Resizeble"
FROM
  (SELECT df.tablespace_name tablespace_name,
    df.file_id file_id,
    df.file_name file_name,
    df.bytes     /1024/1024 dfsizeMB,
    TRUNC((ex.hwm*(ts.block_size))/1024/1024,2) hwmMB,
    dffreeMB
  FROM dba_data_files df,
    dba_tablespaces ts,
    (SELECT file_id,
      SUM(bytes/1024/1024) dffreeMB
    FROM dba_free_space
    GROUP BY file_id
    ) free,
    ( SELECT file_id, MAX(block_id+blocks) hwm FROM dba_extents GROUP BY file_id
    ) ex
  WHERE df.file_id       = ex.file_id
  AND df.tablespace_name = ts.tablespace_name
  AND df.file_id         = free.file_id (+)
  ORDER BY df.tablespace_name,
    df.file_id
  );

FKs without indexes:
Cause table locks





SELECT a.session_id,
  a.oracle_username,
  a.os_user_name,
  b.owner "OBJECT OWNER",
  b.object_name,
  b.object_type,
  a.locked_mode
FROM
  (SELECT object_id,
    SESSION_ID,
    ORACLE_USERNAME,
    OS_USER_NAME,
    LOCKED_MODE
  FROM v$locked_object
  ) a,
  (SELECT object_id, owner, object_name,object_type FROM dba_objects
  ) b
WHERE a.object_id=b.object_id;

 Script parameters:

&1 - used once and cleared
&&1 - used repeatedly
define foo=bar - define and set variable
accept - supports customized prompts, defaults, and validation

 Execute plsql from Java and get resultSet

 PKs can be non-unique and can be non-validated for legacy constraint violations

 Recent queries:

 SELECT
   last_active_time,
   sql_text 
FROM v$sql 
WHERE 
  sql_text LIKE '%someSQL%' AND NOT sql_text LIKE '%v$sql%' 
ORDER BY last_active_time DESC;

 Find rows block:

 SELECT dbms_rowid.rowid_block_number(rowid) FROM ? WHERE id=?

 If the pctfree and/or initrans for the block is very low and transactions use up all block level ITL slots, other transactions will queue.

 Does user have DBA privileges:
SELECT DECODE(COUNT(1),0,'No',1,'Yes') is_dba 
FROM user_role_privs 
WHERE granted_role = 'DBA';

 Get object name from object id:
select object_name from user_objects where object_id = 172844;

Comments

Post a Comment

Popular posts from this blog

Sites, Newsletters, and Blogs

BLOGS Java.net Weblogs Martin Fowler's blog Paul Graham Ruminations of a Programmer Form Follows Function Newsletters Core Java Tech Tips Enterprise Java Tech Tips JavaSpecialists.EU TheServerSide.com Sites Java.net Today Hot Java Sites CSS Zen Garden Evernote Hacker News Pragmatic Programmer
Read more

Oracle JDBC ReadTimeout QueryTimeout

If a query exceeds the oracle.jdbc.ReadTimeout without receiving any data, an exception is thrown and the connection is terminated by the Oracle driver on the client. Unfortunately the session will still be queued on the database and continue to wait for locks, hold any current locks, and complete any DML/PL*SQL procedures that are pending on the server-side of the orphaned connection. If an application, on a another connection, due to ReadTimeout exception, retries DML/PL*SQL  which requires locks, those queries will queue behind the initial DML/PL*SQL. This will increase the workload exacerbating the situation. On applications with retries, this can be observed by querying the v$session table  or gv$session on RAC and noting new sessions started periodically based on the ReadTimeout interval. The sessions may often have the same SQL_ID and/or SQL_HASH_VALUE. If stmt.setQueryTimeout(Seconds) is issued and the statement exceeds the timeout, it will atte...
Read more

Locks held on Oracle for hours after sessions abnormally terminated by node failure

If a session holds db locks and is abnormally terminated (no fin/ack), the locks will persist until the db session is closed, typically around 2 hours and 12 minutes with default network tcp_keepalive settings. Abnormally terminated does not include CTRL C or kill -9. The ojdbc6 driver apparently has a shutdown hook thread that closes the connections in graceful shutdowns. The OS apparently closes the connections when processes are killed. Abnormally terminated might include power failure, firewall failures, Out Of Memory, OS/kernel crash, network connection failure, JBoss or other server node failure, etc. This was easily reproduced by creating a  process that connected to the db and updated a record but did not commit. While the first process was waiting, a second process with a contending update was started which blocked on the first update. The first clients network cable was then disconnected. The second clients transaction waited for approximately 2 hours until the databa...
Read more