Resize Online SGA_TARGET

From latest versions  you can configure Oracle memory in different ways. I have to say that in very large environments and versions 10 and 11 the dynamic configuration can cause problems, so we must always look for hours with low activity or workload, or apply for a window of intervention to such operations. In small and medium systems this should not be a problem.

One time, attempting to increase SGA_TARGET from 8GB up to 10GB (SGA_MAX_SIZE = 10GB), by executing

alter system set sga_target = 6000M scope=both sid=’*’

After two hours, there is no “system altered returned” and MMAN process is busy 100% on cpu.

 

Example

RESIZE_SGA

 

11520 is in this case multiple of the granule, if you configure a value not multiple of granule Oracle rounds it up. You can see the size of granule in the V$SGAINFO.

HTH – Antonio NAVARRO

Advertisements

Index Usage

Oracle provides index monitoring to see if an index is used or not, since you can set on if use is made of index or if not, by the time you want, turn this sometimes is difficult, especially in critical environments or large database and that may cause locks in the system (when you set). An alternative is to look for in memory, plans are in memory if they being used or are relatively recent. We have the v$SQL_PLAN, look for the name the index of interest. of course, this query does not guarantee that is not in use if the query returns no rows, but time could be a solution.

Example

 

SELECT sql_id FROM v$sql_plan WHERE object_name =’TAB1_PK’;

 

We can see also the executions per statement.

 

SELECT sql_id, executions FROM v$sql WHERE sql_id IN
(SELECT sql_id FROM v$sql_plan WHERE object_name =’TAB1_PK’);

 

HTH – Antonio NAVARRO

Using DBMS_METADATA To Get DDL

An easy, fast and simple way to get DDL (Data Definition Language) of objects within the database is using the package DBMS_METADATA. This package will return us a script with the source code. Below we list some of the types you can get.

 

  • TABLE
  • VIEW
  • INDEX
  • PACKAGE
  • PACKAGE_BODY
  • PROCEDURE
  • FUNCTION
  • CLUSTER
  • CONTEXT
  • DB_LINK
  • JAVA_SOURCE
  • LIBRARY
  • MATERIALIZED_VIEW
  • MATERIALIZED_VIEW_LOG
  • OPERATOR
  • SEQUENCE
  • SYNONYM
  • TABLESPACE
  • TRIGGER
  • TYPE
  • TYPE_BODY

 

Usage;

SELECT DBMS_METADATA.GET_DDL(‘OBJECT_TYPE’,’OBJECT_NAME’,’SCHEMA_OWNER’)
FROM DUAL;

 

By way of example

SELECT DBMS_METADATA.GET_DDL(‘VIEW’,’DBA_ROLE_PRIVS’, ‘SYS’)
FROM DUAL;

 

There are some exceptions such as in the case of an object of type tablespace where you do not need to specify schema owner.

SELECT DBMS_METADATA.GET_DDL(‘TABLESPACE’,’SYSTEM’)
FROM DUAL;

 

 

HTH – Antonio NAVARRO