Oracle DB administering

Oracle Linux Tests

Posted on

https://oss.oracle.com/projects/olt/dist/documentation/OLT_TestCoverage.pdf Oracle Linux Tests are designed to verify Linux kernel functionality and stability essential for the Oracle Database. The Oracle Linux Test (OLT) kit, which is distributed as an archive file, provides an automated framework, a set of tools and tests for virtualized and non­virtualized systems. This document is for QA teams who conduct the […]

Oracle DB administering

Exploring PGA usage

Posted on

Sorting out how Oracle RDBMS uses physical memory. We have: Oracle RDBMS 11g, Solaris 10 SPARC, total 64 GB physical memory available. select sum(pga_alloc_mem)/1024/1024/1024 from v$process 3 GB select sum(value)/1024/1024/1024 from v$sga 35,8 GB sga_max_size = 36 GB swap -s total: 41424832k bytes allocated + 926416k reserved = 42351248k used, 1573376k available # echo ‘::memstat’ […]

Oracle DB programming

Plan management

Posted on

Look for sql_id based on a fragment of sql text: select s.CHILD_NUMBER, s.PLAN_HASH_VALUE, s.IS_BIND_SENSITIVE, s.IS_BIND_AWARE, s.* from v$sql s where sql_text like ‘%O.ORDER_NO%’   Explain plan for sql_id: SELECT * FROM table ( DBMS_XPLAN.DISPLAY_CURSOR(sql_id => ‘gd92hchnmrbwc’, cursor_child_no => 0)); select * from TABLE(dbms_xplan.display_awr(‘5q5x7zcnt3c5d’)); Purge bad plan from SGA: select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID […]

Oracle DB administering

Temporary tablespace usage

Posted on

SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||’M’ AS temp_size, a.inst_id as Instance, a.sid||’,’||a.serial# AS sid_serial, NVL(a.username, ‘(oracle)’) AS username, a.program, a.status, a.sql_id FROM   gv$session a, gv$sort_usage b, gv$parameter p WHERE  p.name  = ‘db_block_size’ AND    a.saddr = b.session_addr AND    a.inst_id=b.inst_id AND    a.inst_id=p.inst_id ORDER BY b.tablespace, b.blocks select * from v$temporary_lobs order by cache_lobs desc SELECT A.inst_id, […]

Oracle DB administering

Purging old trace files

Posted on

http://www.pafumi.net/11g_Trace_Dump_Files_ADRCI.html   The ADR Command Interpreter (ADRCI) is a command-line tool that you use to manage Oracle Database diagnostic data. As you know the location of the alert log files and trace files has changed on 11g: The ADR can be managed via the 11g Enterprise Manager GUI (Database Control and not Grid Control) or […]

Oracle DB administering

Monitoring oracle RDBMS Standard Edition

Posted on

http://www.kylehailey.com/category/oracle/wait-events-oracle/ The wait events are rolled up in to groups called wait classes. For wait class we have the following views: V$SYSTEM_WAIT_CLASS – cumulative since start up V$WAITCLASSMETRIC – last 60 seconds deltas V$WAITCLASSMETRIC_HISTORY – 60 seconds deltas for last hour select sum(NVL(h.TIME_WAITED, 0) ) time_waited, n.WAIT_CLASS from v$waitclassmetric_history h, v$system_wait_class n where h.WAIT_CLASS# = n.WAIT_CLASS# group by […]

Oracle DB troubleshooting

Advisor failure troubleshooting

Posted on

Sympoms: auto space advisor job fails with the error: Errors in file /opt/oracle10/diag/rdbms/rdb3/rdb/trace/rdb_j007_4209.trc:ORA-12012: error on auto execute of job”SYS”.”ORA$AT_SA_SPC_SY_12310″ORA-20000: ORU-10027: buffer overflow, limit of 20000 byt Look at the auto tasks enabled: select * from dba_autotask_client; Disable an auto task: begin dbms_auto_task_admin.DISABLE(client_name => ‘auto space advisor’, operation => NULL, window_name => NULL); end; Find which […]

Oracle DB administering

SQL plan analysis

Posted on

Look at the execution plan for the last SQL statement in the session: SELECT * FROM table(dbms_xplan.display_cursor(null,null,’basic’)); Record and display run time statistics: SELECT /*+ gather_plan_statistics */ p.prod_name, SUM(s.quantity_sold) FROM sales s, products p WHERE s.prod_id =p.prod_id GROUP By p.prod_name ; Check if statistics for a table exists and is not stale: select column_name, num_distinct, […]