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, […]