http://coskan.wordpress.com/2007/09/14/what-i-learned-about-shared-pool-management/

 

What I learned about shared pool management

Filed under: Performance — coskan @ 11:00 pm
 
22
 
1

 

 
Rate This

 

Update on 07/06/11: Please also watch this video tutorial from Tanel Poder after you read this article

Nowadays, I am working on performance tuning guide of 10gR2 and I found it is worthy to write about shared pool management after reading the metalink note Note:396940.1 Troubleshooting and Diagnosing ORA-4031 Error.

Note: This blog entry is my summary of the references at the bottom and to be honest I wrote this long entry for myself for not to get lost between those references again 🙂

Shared pool is used to cache different types of data such as textual and executable forms of PL/SQL blocks and SQL statements, dictionary cache data, and other data. If you use shared pool effectively you can reduce resource consumption in at least four ways

  1. Parse overhead is avoided if the SQL statement is already in the shared pool. This saves CPU resources on the host and elapsed time for the end user.
  2. Latching resource usage is significantly reduced, which results in greater scalability.
  3. Shared pool memory requirements are reduced, because all applications use the same pool of SQL statements and dictionary resources.
  4. I/O resources are saved, because dictionary elements that are in the shared pool do not require disk access.

Main components of shared pool are library cache (executable forms of SQL cursors, PL/SQL programs, and Java classes.) and the dictionary cache (usernames, segment information, profile data, tablespace information, and sequence numbers. ).The library cache stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code. The dictionary cache stores data referenced from the data dictionary. This caches are managed by LRU algorithm to “age out” memory structures that have not been reused over time. Allocation of memory from the shared pool is performed in chunks. This allows large objects (over 5k) to be loaded into the cache without requiring a single contiguous area, hence reducing the possibility of running out of enough contiguous memory due to fragmentation. Starting with 9i The Shared Pool divide its shared memory areas into subpools. Each subpool will have Free List Buckets (containing pointers to memory chunks within the subpool ) and , memory structure entries, and LRU list. This architecture is designed to to increase the throughput of shared pool in that now each subpool is protected by a Pool child latch. This means there is no longer contention in the Shared Pool for a single latch as in earlier versions.

Infrequently, Java, PL/SQL, or SQL cursors may make allocations out of the shared pool that are larger than 5k then Oracle must search for and free enough memory to satisfy this request. This operation could conceivably hold the latch resource for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation. To allow these allocations to occur most efficiently, Oracle segregates a small amount of the shared pool. This memory is used if the shared pool does not have enough space. The segregated area of the shared pool is called the reserved pool which is also divided into subpools. Smaller objects will not fragment the reserved list, helping to ensure the reserved list will have large contiguous chunks of memory. Once the memory allocated from the reserved list is freed, it returns to the reserved list.

By using automatic shared memory management (ASMM) option available with 10G, which is activated by setting SGA_TARGET parameter with a value greater than 0, shared pool size is started to be managed by Oracle, under the limits of SGA_TARGET and other SGA components.

After these explanations lets start to explain how to manage shared pool with ASMM.

1- Using Shared Pool Effectively

avoid hard parsing by

  • using bind variables instead of literal values in your queries The script below can be used to find sqls which use literals
    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT substr(sql_text,1,40) "SQL",
     count(*) ,
     sum(executions) "TotExecs"
     FROM v$sqlarea
     WHERE executions < 5
     GROUP BY substr(sql_text,1,40)
     HAVING count(*) > 30
     ORDER BY 2
     ;
  • Standardizing naming conventions for bind variables and spacing conventions for SQL statements and PL/SQL blocks.
  • Because they are stored, Consider using stored procedures whenever possible
  • Avoiding users from change the optimization approach and goal for their individual sessions.
  • Reducing the number of entries in the dictionary cache by explicitly qualifying the segment owner, rather than using public synonyms or try to connect to the database through a single user ID, rather than individual user IDs because Reducing the number of distinct userIDs also reduces the load on the dictionary cache.SELECT employee_id FROM hr.employees WHERE department_id = :dept_id;
  • Using PL/SQL packages when your system has thousands of users, each with individual user sign-on and public synonyms because a package is executed as the owner, rather than the caller, which reduces the dictionary cache load considerably.
  • Avoid performing DDLs in peak hours because ddl operations invalidate the dependent SQLs and cause hard parsing when the statement called again.
  • Cache the sequence numbers by using CACHE keyword of CREATE/ALTER SEQUENCE clause to reduce the frequency of dictionary cache locks,
  • Try to avoid closing of rapidly executed cursors
  • Check for hash values which maps different literals. The query below should return no rows otherwise there is possibility for a bug
    1
    2
    3
    4
    5
    SELECT hash_value, count(*)
    FROM v$sqlarea
    GROUP BY hash_value
    HAVING count(*) > 5
    ;

3- Identify which sqls are using lots of shared memory

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT substr(sql_text,1,20) "Stmt", count(*),
 sum(sharable_mem) "Mem",
 sum(users_opening) "Open",
 sum(executions) "Exec"
 FROM v$sql
 GROUP BY substr(sql_text,1,20)
 HAVING sum(sharable_mem) > 1426063 -–%10 of Shared Pool Size
 ;
 
---Dynamic version
 SELECT substr(sql_text,1,20) "Stmt", count(*),
  sum(sharable_mem)/1024/1024 "Mem",
  sum(users_opening) "Open",
  sum(executions) "Exec"
  FROM v$sql
  GROUP BY substr(sql_text,1,20)
  HAVING sum(sharable_mem) > (select current_size*0.1 from v$sga_dynamic_components where component='shared pool');--10 percent
Stmt COUNT(*) Mem Open Exec
/* OracleOEM */ SEL 18 1445971 2 54

This should show if there are similar literal statements, or multiple versions of a statements which account for a large portion of the memory in the shared pool.

4- Identify which allocations causing shared pool to be aged out

1
2
3
4
spool ageout.txt
SELECT *FROM x$ksmlru
WHERE ksmlrnum>0;
spool off

This select returns no more than 10 rows and then erases the contents of the X$KSMLRU table so be sure to SPOOL the output. The X$KSMLRU table shows which memory allocations have caused the MOST memory chunks to be thrown out of the shared pool since it was last queried. This is sometimes useful to help identify sessions or statements which are continually causing space to be requested.

5- Why are there multiple child cursors.

V$SQL_SHARED_CURSOR explains why a particular child cursor is not shared with existing child cursors. Each column identifies a specific reason why the cursor cannot be shared.

1
2
3
4
5
SELECT SA.SQL_TEXT,SA.VERSION_COUNT,SS.*
FROM V$SQLAREA SA,V$SQL_SHARED_CURSOR SS
WHERE SA.ADDRESS=SS.ADDRESS
AND SA.VERSION_COUNT > 5
ORDER BY SA.VERSION_COUNT

6- Monitor Shared Pool sizing operations

You can see the shrinking and growing operations from V$SGA_RESIZE_OPS dynamic view and you can guess why there is need for this operations by focusing the sql at the sizing operation times.

1
2
3
4
5
6
7
8
column initial_size format 999999999999999
column target_size format 999999999999999
column final_size format 999999999999999
 
select to_char(end_time, 'dd-Mon-yyyy hh24:mi') end, oper_type, initial_size,
 target_size, final_size from V$SGA_RESIZE_OPS
 where component='shared pool'
 order by end;
END OPER_TYPE INITIAL_SIZE TARGET_SIZE FINAL_SIZE
12-Sep-2007 19:05 STATIC 0 134217728 134217728
12-Sep-2007 22:01 SHRINK 134217728 130023424 130023424
13-Sep-2007 11:35 SHRINK 130023424 125829120 125829120
13-Sep-2007 11:36 SHRINK 125829120 121634816 121634816
13-Sep-2007 22:08 GROW 121634816 125829120 125829120
13-Sep-2007 22:09 GROW 125829120 130023424 130023424
13-Sep-2007 22:10 GROW 130023424 134217728 134217728
13-Sep-2007 22:12 GROW 134217728 138412032 138412032
14-Sep-2007 09:49 GROW 138412032 142606336 142606336
14-Sep-2007 16:13 GROW 142606336 146800640 146800640

7- Minimum Size of Shared Pool

Current size of the shared pool;

1
2
column bytes format 999999999999999
select bytes,bytes/1024/1024/1024 size_gb from v$sgainfo where name='Shared Pool Size';

You can use the sizing advices from the view v$shared_pool_advice. This view displays information about estimated parse time in the shared pool for different pool sizes and the sizes range from %10 to %200 of current shared pool size. This can give you idea for sizing SGA and obliquely shared pool by the help of ASMM.

select * from V$SHARED_POOL_ADVICE;

Suggested minimum shared pool size;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
column cr_shared_pool_size format 99999999
column sum_obj_size format 99999999
column sum_sql_size format 99999999
column sum_user_size format 99999999
column min_shared_pool format 9999999
 
 
select cr_shared_pool_size,
sum_obj_size, sum_sql_size,
sum_user_size,
(sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool
from (select sum(sharable_mem) sum_obj_size
from v$db_object_cache where type<> 'CURSOR'),
(select sum(sharable_mem) sum_sql_size from v$sqlarea),
(select sum(250*users_opening) sum_user_size from v$sqlarea),
(select to_Number(b.ksppstvl) cr_shared_pool_size
 from x$ksppi a, x$ksppcv b, x$ksppsv c
 where a.indx = b.indx and a.indx = c.indx
 and a.ksppinm ='__shared_pool_size' );
CR_SHARED_POOL_SIZE SUM_OBJ_SIZE SUM_SQL_SIZE SUM_USER_SIZE MIN_SHARED_POOL
146800640 9520659 25660770 11750 45751132,7

You should set the suggested minimum shared pool size to avoid shrinking operation of ASMM

SQL>alter system set shared_pool_size=73M;
System altered.

8- How much free memory in SGA is available for shared pool and how to interpret the free memory

First of all find the free memory in shared pool. If you have free memory you should relax but if you don’t have go to the step below

1
2
3
SELECT * FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool';

The X$KSMSP view shows the breakdown of memory in the SGA. You can run this query to build trend information on memory usage in the SGA. Remember, the ‘free’ class in this query is not specific to the Shared Pool, but is across the SGA.Dont use the script below when db is under load. Check out Jonathan Lewis’s experiences on this view from here

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
FROM X$KSMSP GROUP BY KSMCHCLS;
 
 
CLASS    NUM        SIZ        AVG SIZE
-------- ---------- ---------- ------------
R-free           12    8059200      655.86k <<= Reserved List
R-freea          24        960         .04k <<= Reserved List
free            331  151736448      447.67k <<= Free Memory
freeabl        4768    7514504        1.54k <<= Memory for user/system
                                                    processing
perm              2   30765848   15,022.39k <<= Memory allocated to the
                                                  system
recr           3577    3248864         .89k <<= Memory for user/system
                                                  processing
Watch for trends using these guidelines:

Watch for trends using these guidelines:

a) if ‘free’ memory is low (less than 5mb or so) you may need to increase the shared_pool_size and shared_pool_reserved_size. You should expect ‘free’ memory to increase and decrease over time. Seeing trends where ‘free’ memory decreases consistently is not necessarily a problem, but seeing consistent spikes up and down could be a problem.
b) if ‘freeable’ or ‘perm’ memory continually grows then it is possible you are seeing a memory bug.
c) if ‘freeabl’ and ‘recr’ memory classes are always huge, this indicates that you have a lot of cursor info stored that is not releasing.
d) if ‘free’ memory is huge but you are still getting 4031 errors, the problem is likely reloads and invalids in the library cache causing fragmentation.
!!!!!!!!!! Note says that this query can hang database on HP platforms

To see the free memory chunks detailed use the script below

1
2
3
4
5
6
7
8
9
10
11
12
select KSMCHIDX "SubPool", 'sga heap('||KSMCHIDX||',0)'sga_heap,ksmchcom ChunkComment,
decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',
4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,
'8-9k', 9,'9-10k','> 10K') "size",
count(*),ksmchcls Status, sum(ksmchsiz) Bytes
from x$ksmsp
where KSMCHCOM = 'free memory'
group by ksmchidx, ksmchcls,
'sga heap('||KSMCHIDX||',0)',ksmchcom, ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K',
 1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,
 '6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K')
 order by 1;
SubPool SGA_HEAP CHUNKCOMMENT size COUNT(*) STATUS BYTES
1 sga heap(1,0) free memory > 10K 34 R-free 7238192
1 sga heap(1,0) free memory 3-4K 2 free 6284
1 sga heap(1,0) free memory > 10K 241 free 35707400
1 sga heap(1,0) free memory 8-9k 1 free 7712
1 sga heap(1,0) free memory 2-3K 4 free 6752
1 sga heap(1,0) free memory 0-1K 2090 free 133288
1 sga heap(1,0) free memory 9-10k 21 free 188676
1 sga heap(1,0) free memory 1-2K 30 free 25868

If you see lack of large chunks it is possible that you can face with ORA-04031 in near future.
9- Is library_cache or dictionary_cache utilization satisfactory ?

The statistics below is based since the start of the instance. You should take interval statistics to interpret these values for performance issues .

  • Library Cache Stats
1
2
3
4
5
6
7
8
9
SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS
FROM V$LIBRARYCACHE
ORDER BY NAMESPACE;
 
SQL> SELECT SUM(PINHITS)/SUM(PINS) FROM V$LIBRARYCACHE;
 
SUM(PINHITS)/SUM(PINS)
----------------------
            .964764429
NAMESPACE PINS PINHITS RELOADS INVALIDATIONS
BODY 72782 72582 49 0
CLUSTER 1175 1161 3 0
INDEX 2800 2023 42 0
JAVA DATA 0 0 0 0
JAVA RESOURCE 0 0 0 0
JAVA SOURCE 0 0 0 0
OBJECT 0 0 0 0
PIPE 0 0 0 0
SQL AREA 563349 541678 2069 342
TABLE/PROCEDURE 175850 165318 2005 0
TRIGGER 6923 6802 34 0

High invalidations indicates that there is parsing problem with the namespace and high reloads indicates that there is a sizing problem which causes aging out.

  • Library cache hit ratio;
1
2
3
SQL>SELECT SUM(PINHITS)/SUM(PINS) FROM V$LIBRARYCACHE;
SUM(PINHITS)/SUM(PINS)----------------------
95558088

low hit ratio is an indication of a sizing or caching problem

  • Dictionary cache stats
1
2
3
4
5
6
7
8
SELECT PARAMETER
, SUM(GETS)
, SUM(GETMISSES)
, 100*SUM(GETS – GETMISSES) / SUM(GETS) PCT_SUCC_GETS
, SUM(MODIFICATIONS) UPDATES
FROM V$ROWCACHE
WHERE GETS > 0
GROUP BY PARAMETER;
PARAMETER SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS UPDATES
dc_constraints 99 35 64,6464646 99
dc_tablespaces 90104 14 99,9844624 0
dc_tablespace_quotas 13 3 76,9230769 0
dc_awr_control 1351 2 99,8519615 121
dc_object_grants 867 174 79,9307958 0
dc_histogram_data 52053 6181 88,1255643 3047
dc_rollback_segments 55098 92 99,8330248 263
dc_sequences 100 27 73 100
dc_usernames 6632 33 99,5024125 0
dc_segments 23404 2466 89,4633396 331
dc_objects 37434 3776 89,9129134 358
dc_histogram_defs 65987 16796 74,5465016 3280
dc_table_scns 8 8 0 0
dc_users 171638 105 99,9388247 0
outstanding_alerts 1674 58 96,5352449 66
dc_files 80 10 87,5 0
dc_object_ids 134005 2646 98,0254468 123
dc_global_oids 52337 185 99,6465216 0
dc_profiles 1962 4 99,7961264 0

High updates with low pct_succ_gets can be a clue of performance problems when accessing that dictionary object. For frequently accessed dictionary caches, the ratio of total GETMISSES to total GETS should be less than 10% or 15%, depending on the application. If this ratio is higher and every previous control is OK then you should consider to increase the shared pool size

  • Dictionary cache hit ratio;
1
2
3
4
5
6
7
SQL>SELECT (SUM(GETS – GETMISSES – FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
 
ROW CACHE
 
--------------------,
 
9516921886454345524

Low hit ratio is an indication of a sizing problem.

10- Are there any objects candidate for library cache pinning ?

Having objects pinned will reduce fragmentation and changes of encountering the ORA-04031 error. Objects causing a large number of other objects been flushed out from the shared pool are candidates to be pinned into the shared pool using dbms_shared_pool.keep procedure. You can check the x$ksmlru fixed table to see the candidates. This table keeps track of the objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm. Because this is a fixed table, once you query the table, Oracle will automatically reset the table so first insert the contents to temporary table like below,

1
2
3
CREATE TABLE LRU_TMP AS SELECT * FROM X$KSMLRU;
 
INSERT INTO LRU_TMP SELECT * FROM X$KSMLRU;

Use the LRU_TMP table for analysis. You can use a query below to see more information on candidate code in the library cache.

1
2
3
4
5
SELECT USERNAME, KSMLRCOM, KSMLRHON, KSMLRNUM, KSMLRSIZ, SQL_TEXT
FROM V$SQLAREA A, LRU_TMP K, V$SESSION S
WHERE KSMLRSIZ > 3000
AND A.ADDRESS=S.SQL_ADDRESS AND A.HASH_VALUE = S.SQL_HASH_VALUE
AND SADDR=KSMLRSES;

You can see the candidates to pin from the query below

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
COL STORED_OBJECT FORMAT A40;
COL SQ_EXECUTIONS FORMAT 999,999;
SELECT /*+ ORDERED USE_HASH(D) USE_HASH(C) */
O.KGLNAOWN||'.'||O.KGLNAOBJ STORED_OBJECT,
SUM(C.KGLHDEXC) SQL_EXECUTIONS
FROM SYS.X$KGLOB O, SYS.X$KGLRD D, SYS.X$KGLCURSOR C
WHERE
O.INST_ID = USERENV('INSTANCE') AND
D.INST_ID = USERENV('INSTANCE') AND
C.INST_ID = USERENV('INSTANCE') AND
O.KGLOBTYP IN (7, 8, 9, 11, 12) AND
D.KGLHDCDR = O.KGLHDADR AND
C.KGLHDPAR = D.KGLRDHDL
GROUP BY O.KGLNAOWN, O.KGLNAOBJ
HAVING SUM(C.KGLHDEXC) > 0
ORDER BY 2 DESC;

You should pin objects you find immediatelly after the each restart of instance. You can pin the object by DBMS_SHARED_POOL package like below

EXECUTE DBMS_SHARED_POOL.KEEP(OWNER.TRIGGER, ‘R’)

11- Is my Reserved Area sized properly?

An ORA-04031 error referencing a large failed requests indicates the Reserved Area is too fragmented.

1
2
3
4
5
6
7
8
9
col free_space for 999,999,999,999 head "TOTAL FREE"
col avg_free_size for 999,999,999,999 head "AVERAGE|CHUNK SIZE"
col free_count for 999,999,999,999 head "COUNT"
col request_misses for 999,999,999,999 head "REQUEST|MISSES"
col request_failures for 999,999,999,999 head "REQUEST|FAILURES"
col max_free_size for 999,999,999,999 head "LARGEST CHUNK"
 
select free_space, avg_free_size, free_count, max_free_size, request_misses, request_failures
from v$shared_pool_reserved;
TOTAL FREE AVERAGE
CHUNK SIZE
COUNT LARGEST CHUNK REQUEST
MISSES
REQUEST
FAILURES
7,238,192 212,888 34 212,888 0 0

The reserved pool is small when:

REQUEST_FAILURES > 0 (and increasing)

The DBA should Increase shared_pool_reserved_size and shared_pool_size together.

It is possible that too much memory has been allocated to the reserved list.
If:

REQUEST_MISS = 0 or not increasing
FREE_MEMORY = > 50% of shared_pool_reserved_size minimum

The DBA should Decrease shared_pool_reserved_size

You should also use hidden and unsupported parameter “_shared_pool_reserved_pct” to control reserved pool. This parameter controls the allocated percentage of shared pool for reserved pool. By default it is %5 of the shared pool and if you use ASMM for memory management you can set this value higher like 10 to allocate reserved pool dynamically. When you set the parameter you will see the shared_pool_reserved_size parameter will be adjusted to the new setting.

The parameter can not be modified when instance is started. You can use the query below to see the current value

1
2
3
4
5
6
select a.ksppinm "Parameter",
 b.ksppstvl "Session Value",
 c.ksppstvl "Instance Value"
 from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
 where a.indx = b.indx and a.indx = c.indx
 and a.ksppinm = '_shared_pool_reserved_pct';
Parameter Session Value Instance Value
_shared_pool_reserved_pct 10 10

12-Is there any fragmentation in shared pool?

The primary problem that occurs is that free memory in the shared pool becomes fragmented into small pieces over time. Any attempt to allocate a large piece of memory in the shared pool will cause large amount of objects in the library cache to be flushed out and may result in an ORA-04031 out of shared memory error. But how to understand the fragmentation ?

  • Occurrence of ORA-04031 error. Before this error signalled, memory is freed from unnecessary objects and merged. This error only occurs when there is still not a large enough contiguous piece of free memory after this cleaning process. There may be very large amounts of total free memory in the shared pool, but just not enough contiguous memory.
  • Using X$KSMLRU internal fixed table. We told about this view before about its usage for tracking age out operations, it also can be used to identify what is causing the large allocations. KSMLRSIZ column of this table shows the amount of contiguous memory being allocated. Values over around 5K start to be a problem, values over 10K are a serious problem, and values over 20K are very serious problems. Anything less then 5K should not be a problem. Again be careful to save spool the result when you query this table
    1
    2
    3
    spool ageout.txt
    select * from x$ksmlru where ksmlrsiz > 5000;
    spool off

After finding the result you should do the followings to correct fragmentation

  • Keep object by pinning them as we discussed above
  • Use bind variables as we discussed before
  • Eliminate large anonymous PL/SQL block. Large anonymous PL/SQL blocks should be turned into small anonymous PL/SQL blocks that call packaged functions. The packages should be ‘kept’ in memory. To view candidates

    select sql_text from v$sqlarea
    where command_type=47 — command type for anonymous block
    and length(sql_text) > 500;

Fallacies about solving shared pool fragmentation

  • Free memory in shared pool prevents fragmentation. This is not true because Free memory is more properly thought of as ‘wasted memory’. You would rather see this value be low than very high. In fact, a high value of free memory is sometimes a symptom that a lot of objects have been aged out of the shared pool and therefore the system is experiencing fragmentation problems.
  • Flushing shared pool frequently solves fragmentation and improves performance. This is also incorrect because Executing this statement causes a big spike in performance and does nothing to improve fragmentation. You lost your cached cursors when you flush and they will hard parsed next time with high CPU consumption.

13- Using related database parameters

  • CURSOR_SHARING: Setting this parameter to smilar can solve your hard parse problems caused by using literals but can have side effects mostly on DSS environments and systems which uses stored outlines.
  • CURSOR_SPACE_FOR_TIME: This parameter specifies whether a cursor can be deallocated from the library cache to make room for a new SQL statement. CURSOR_SPACE_FOR_TIME has the following values meanings:
    • If CURSOR_SPACE_FOR_TIME is set to false (the default), then a cursor can be deallocated from the library cache regardless of whether application cursors associated with its SQL statement are open. In this case, Oracle must verify that the cursor containing the SQL statement is in the library cache.
    • If CURSOR_SPACE_FOR_TIME is set to true, then a cursor can be deallocated only when all application cursors associated with its statement are closed. In this case, Oracle need not verify that a cursor is in the cache, because it cannot be deallocated while an application cursor associated with it is open.

You must be sure that the shared pool is large enough for the work load otherwise performance will be badly affected and ORA-4031 eventually signalled.

  • OPEN_CURSORS: This parameter sets the upper bound for the number of cursor that a session can have open and if you size it correctly, cached cursors can be stay opened and won’t have to be closed to let new cursor open
  • PROCESSES / SESSIONS: You can review the high water mark for Sessions and Processes in the V$RESOURCE_LIMIT view. If the hard-coded values for these parameters are much higher than the high water mark information, consider decreasing the parameter settings to free up some memory in the Shared Pool for other uses.
  • SESSION_CACHED_CURSORS: When a cursor is closed, Oracle divorces all association between the session and the library cache state. If no other session has the same cursor opened, the library cache object and its heaps are unpinned and available for an LRU operation. The parameter SESSION_CACHED_CURSORS controls the number of cursors “soft” closed, much like the cached PL/SQL cursors. Oracle checks the library cache to determine whether more than three parse requests have been issued on a given statement. If so, then Oracle assumes that the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session then find the cursor in the session cursor cache.To determine whether the session cursor cache is sufficiently large for your instance, you can examine the session statistic session cursor cache hitsin the V$SYSSTAT view. This statistic counts the number of times a parse call found a cursor in the session cursor cache. If this statistic is a relatively low percentage of the total parse call count for the session, then consider settingSESSION_CACHED_CURSORS to a larger value. Steve Adams also wrote usefully queries to find the usage and the maximum cacheable cursors.session_cursor_cache.sql

This was a long article and if you see anything wrong or suspicious please feel free to comment for correction

Update 07/06/11 : Please also check out sgastatx.sql by Tanel poder

All of the queries are tested on Oracle 10.2.0.3 for Windows

Code Depot of The Queries (All scripts are taken from metalink notes and official documentation)
References :

Oracle® Database Performance Tuning Guide 10g Release 2 (10.2)

Metalink Notes

Note:396940.1 Troubleshooting and Diagnosing ORA-4031 Error

Note:146599.1 Diagnosing and Resolving Error ORA-04031

Note:61623.1 Resolving Shared Pool Fragmentation In Oracle7

Note:62143.1 Understanding and Tuning the Shared Pool

Note:1012047.6 How To Pin Objects in Your Shared Pool

Note:274496.1 ora-7445 and ora-4031 in 9.2.0.5 and 10g if SESSION_CACHED_CURSORS is used

http://www.ixora.com Oracle Advanced Performance Tuning Scripts

Mailing list threads from Oracle-l

http://www.freelists.org/archives/oracle-l/08-2007/msg00975.html

Update 09/12/2011
Some extra useful metalink note

Interpreting the automatically generated ORA-4031 diagnostic trace. [ID 809560.1]

 

Hi Coskan,
this is a very good post. I like it very much!

What I would like to add here is:
– there is additional shared memory needed in the SHARED POOL if you are using ASM storage. It can be calculated by using a formula and teh amount of shared memory needed is determined by the amount of ASM storage you use as well as the redundancy level of the ASM diskgroups. For more info you can refer to my post on this
=> http://sysdba.wordpress.com/2006/03/30/how-to-calculate-the-minimum-size-of-the-shared-pool-in-oracle-10g/

– in 11g we have a new feature called SQL-Relsut-Cache which can be used to cache results of queries as well as PL/SQL functions in order to share it amoun different sessions. This result cache utilizes SHARED POOL memory!
For more see my post
=> http://sysdba.wordpress.com/2007/09/11/my-top-10-oracle-11g-new-features-part-4-the-sql-query-result-cache/

– also I would like to point out that it is strongly recommended to configure a LARGE_POOL under certain conditions, such as parallel executions, use of shared servers and use of RMAN for tape backups (pls refer to my post about this
=> http://sysdba.wordpress.com/2007/05/08/how-to-calculate-the-minimum-size-of-large_pool_size-for-io-slaves-rman-and-memory-utilization/.
Otherwise you can also cause fragmentation in the SHARED POOL if no LARGE_POOL is available at appropriate size and configured for use.

Cheers,
Lutz
=;-)

Aside | Posted on by | Leave a comment

Avoiding ORA-04068 existing state of packages has been discarded

http://betteratoracle.com/posts/34-avoiding-ora-04068-existing-state-of-packages-has-been-discarded

Avoiding ORA-04068 existing state of packages has been discarded

Sometime back, I wrote about the new 11gR2 feature that allows PLSQL to be changed without an outage. In that series of articles I pointed to ‘fine grained dependency checking’ that was introduced in 11gR1 as an enabler for this feature citing that online changes in 10g were impossible. However, there was a subtle, but now pretty obvious point that I missed back then, and some online changes are in fact possible in 10g

Not All Invalidations Are Equal

In Oracle 10g and before, if a package referenced a table, and the table was modified (by adding a column for example), the package would immediately be marked as invalid. In 11g this invalidation can be avoided, but even if the package does become invalid, it is not as bad as it seems.

The next time the package is called, execution will be delayed for a short period while Oracle recompiles it, and if the compile is successful, it will be called as normal. Even if a connected session has a prepared call to that procedure open, it will handle this sort of invalidation seamlessly. For example:

create table t1 (c1 integer);

create or replace package pkg1
as
  procedure p1;
end;
/

create or replace package body pkg1
as
  procedure p1 
  is
  begin
    for row in (select * from t1) loop
      null;
    end loop;
  end;
end;
/

Next I create a simple JDBC program using JRuby. This program doesn’t do anything useful, but it illustrates the point. I first prepare and execute a call to my package, then I sleep. While the program is sleeping I add a column to T1 which will invalidate the package (because there is a select * in the procedure P1).

require 'java'
java_import 'oracle.jdbc.OracleDriver'
java_import 'java.sql.DriverManager'

oradriver = OracleDriver.new

DriverManager.registerDriver oradriver
@connection = DriverManager.get_connection
    "jdbc:oracle:thin:@localhost:1521/local11gr2.world",      
    'sodonnel', 'sodonnel'
@connection.auto_commit = false

call = @connection.prepare_call("begin pkg1.p1; end;")

call.execute
puts "called once"

sleep(15)

call.execute
puts "called twice"

While the JRuby program is sleeping, I executed the following code:

alter table t1 add (c2 integer);

select object_name, status 
from user_objects
where object_name like 'PKG1';

OBJECT_NAME         STATUS  
------------------- ------- 
PKG1                VALID   
PKG1                INVALID   

So the change to the table invalidated my package, but the JRuby program completed the second call to the package without an error, proving it is not an issue.

ORA-04068: existing state of packages has been discarded

In the above example, the invalidation of the package code is not a problem, as any application will recover seamlessly from it. However, a simple change to the package body can spoil the party:

create or replace package body pkg1
as
  g_var integer; -- <<- Global variable

  procedure p1 
  is
  begin
    for row in (select * from t1) loop
      null;
    end loop;
  end;
end;
/

By adding a global variable to the package, it now has internal state, and this is a problem. Even if this variable is a constant it will still cause problems on invalidations.

If you run the above example again, modifying the table while the JRuby code is sleeping, the second call will produce the following exception:

NativeException: java.sql.SQLException: ORA-04068: existing state of packages has been discarded

This is an artefact of how Oracle maintains the internal package state. A package with no global variable has no internal state, and when it is recompiled Oracle knows it does not have to throw an ORA-04068 if the package is accessed again in the same session.

However, a package with internal state (caused by having a package global variable) will lose that state when it is recompiled, causing the ORA-04068.

Avoiding ORA-04068

To avoid this error, all you have to do is avoid using global variables in your package. That can be easier said than done in some cases, and many well written applications will define a series of constants in the package unknowingly causing this problem.

A workaround is to define a ‘constants package’ that does nothing other that define the constants, and a separate package that accesses the tables and retrieves the constants from the constants package. In that way, the package with the internal state will not be invalidated by any changes to the underlying schema and active sessions will not see any errors.

In conclusion if you need to modify tables while the application is online, especially in 10g, give careful thought to how the code is organised to avoid unexpected ORA-04068 errors in the application.

Update – if you use stand alone functions or procedures in your application, you may want to read about how you can still get an ORA-04068, which was something I wasn’t expecting.

 

http://betteratoracle.com/posts/42-ora-04068-when-compiling-a-function

ORA-04068 when compiling a function

I thought I had figured out all the pitfalls of changing stored procedures and functions on-line, and then it happened again:

ORA-04068: existing state of packages has been discarded

This occurred when upgrading a single stored procedure – no packages, no complex interdependencies and on Oracle 11.2.0.3. The procedure I changed was not referenced by any other procedures, and it was being invoked from Java on a fairly busy system.

I am pretty sure this shouldn’t happen, so I decided to create a test case to try and prove it one way or another.

First of all I need a procedure to play with – pretty much the simplest procedure possible (p1.sql):

create or replace procedure p1
as
begin
  null;
end;
/

And I need a second procedure to change on-line (p1_1.sql):

create or replace procedure p1
as
begin
  null;
  null;
end;
/

Notice the second ‘NULL;’ call in the second procedure, which makes it different from the first version. Then I need some Java code to call this procedure in a loop – again I am going for the most simple Java code I can:

import java.sql.*;
import oracle.jdbc.*;

public class SimpleProc {

    private static Connection conn;

    public static void main(String[] args)
    throws ClassNotFoundException, SQLException, InterruptedException
    {
        connect();

        CallableStatement stmt = conn.prepareCall("begin p1(); end;");
        int count = 0;
        while (true) {
            stmt.execute();
            count ++;
            System.out.println ("Made procedure call number "+ count);
            Thread.sleep(1000);
        }
    }

    public static void connect()
    throws ClassNotFoundException, SQLException
    {
        DriverManager.registerDriver
            (new oracle.jdbc.driver.OracleDriver());

        String url = "jdbc:oracle:thin:@//localhost/local11gr2.world";
        //            jdbc:oracle:thin:@//host:port/service

        conn = DriverManager.getConnection(url,"hotplsql","hotplsql");
        conn.setAutoCommit(false);
    }

}

This code prepares a call to the procedure P1, and then executes it in a loop, printing out the execution count after each execution. Then it sleeps for a second before executing the procedure again, over and over until the process is killed. One thing to note is that the call to the procedure is only prepared one time, but the procedure is executed many times, which is a common pattern in a well written application. If I start the Java running, and open a SQLPLUS session, I find that I can change between the two version of my stored procedure without causing any errors in the Java code:

SQL> @p1

Procedure created.

SQL> @p1_1

Procedure created.

SQL> @p1

Procedure created.

SQL> @p1_1

Procedure created.

SQL> @p1

Procedure created.

So this looks good, it would seem I can recreate the procedure with no problems, which is what I thought when I suggested changing this procedure on our production system.

It turns out not to be that simple. If I make a small change to the Java code, and remove the Thread.sleep(1000) line, things change dramatically. As soon as I compile the new version of my procedure, I get this error:

Made procedure call number 21368
Made procedure call number 21369
Made procedure call number 21370
Made procedure call number 21371
Exception in thread "main" java.sql.SQLException: ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "HOTPLSQL.P1"
ORA-06508: PL/SQL: could not find program unit being called: "HOTPLSQL.P1"
ORA-06512: at line 1

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
        at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204)
        at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1007)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677)
        at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4694)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1086)
        at SimpleProc.main(SimpleProc.java:16)

Which is exactly what happened when I put this procedure into production. The error isn’t a total disaster, as if I had my call wrapped in a try-catch block, then on the next execution it will work correctly, but it still means some transactions will fail.

In my opinion there is something strange going on here – when there is a bit of a delay between executions Oracle can handle the procedure recompiling, but if the execution happens too quickly after the recompile, it fails. Tracing the session reveals some interesting information. With a delay in the Java code, the trace file looks like this:

    PARSING IN CURSOR #390309088 len=16 dep=0 uid=223 oct=47 lid=223 tim=264320251394 hv=3303240157 ad='7ff1fa66878' sqlid='36ytppb2f6wfx'
    begin p1(); end;
    END OF STMT
    EXEC #390309088:c=0,e=197,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=264320251389
    WAIT #390309088: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=264320251979

    *** 2013-01-10 10:21:00.532
    WAIT #390309088: nam='SQL*Net message from client' ela= 997998 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=264321250098
    EXEC #390309088:c=0,e=84,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=264321250370
    WAIT #390309088: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=264321250444

    *** 2013-01-10 10:21:01.532
    WAIT #390309088: nam='SQL*Net message from client' ela= 999933 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=264322250439
    EXEC #390309088:c=0,e=179,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=264322251647
    WAIT #390309088: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=264322251825

    <snip>

    *** 2013-01-10 10:21:11.531
    WAIT #390309088: nam='SQL*Net message from client' ela= 998423 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=264332250242
**> EXEC #390309088:c=0,e=3213,p=0,cr=0,cu=0,mis=1,r=1,dep=0,og=1,plh=0,tim=264332253903
    WAIT #390309088: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=264332254126

    *** 2013-01-10 10:21:12.531
    WAIT #390309088: nam='SQL*Net message from client' ela= 996845 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=264333251088
    EXEC #390309088:c=0,e=182,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=264333251703
    WAIT #390309088: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=264333251880

Notice that the cursor is parsed and then is executed many times with no further parsing (mis=0 in the EXEC lines). Then after I recompile the procedure, we get a EXEC line with mis=1 before it goes back to executing without any misses. So it appears that Oracle is smart enough to notice the procedure has changed, and then gracefully reparse and continue on without an error.

If I remove the sleep command from the Java code the trace file looks different:

    PARSING IN CURSOR #221881568 len=16 dep=0 uid=223 oct=47 lid=223 tim=264706629638 hv=3303240157 ad='7ff1fa66878' sqlid='36ytppb2f6wfx'
    begin p1(); end;
    END OF STMT
    EXEC #221881568:c=0,e=88,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=264706629636
    WAIT #221881568: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=264706629773

    <snip>

    EXEC #221881568:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=264712941846
    WAIT #221881568: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=264712941882
    WAIT #221881568: nam='SQL*Net message from client' ela= 149 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=264712942058
    EXEC #221881568:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=264712942135
    WAIT #221881568: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=264712942188
    WAIT #221881568: nam='SQL*Net message from client' ela= 1626 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=264712943853
**> WAIT #221881568: nam='library cache pin' ela= 10196 handle address=8792328936552 pin address=8792292571936 100*mode+namespace=842320396222466 obj#=-1 tim=264712954206
**> EXEC #221881568:c=0,e=10380,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=264712954356
**> ERROR #3:err=4068 tim=264712954394

In this trace file, we can see the process has waited on ‘library cache pin’, which didn’t happen before. This blocks the execution for a short time and is caused by my other session recompiling the stored procedure. You cannot execute a procedure while it is being compiled, and you cannot compile a procedure while it is being executed either, which is what this pin enforces. As soon as the recompile completes, the library cache pin is released and execution can continue. However this time, instead of the EXEC reporting a library cache miss, it attempts to execute the procedure and fails.

This seems like a bug – maybe the cache pin is released too soon, before all the old cursor resources are cleaned up, or maybe once a process gets as far as being blocked by a cache pin, it has passed the point where it can figure out it needs to reparse the cursor. Either way it is disappointing as it means you cannot safely recompile a procedure in a busy live system (unless of course you use Edition Based Redefinition) without risking some errors.

 

Posted in Uncategorized | Leave a comment

Blender or Juicer: Is it better to juice or to drink smoothies?

http://rawfoodswitch.com/raw-food-equipment-reviews/blender-juicer-juice-drink-smoothies/

Blender or Juicer: Is it better to juice or to drink smoothies?

Are you overwhelmed by the number of accessories and machines “Raw Food Gurus” tell you are needed to make eating a raw food diet successful? With the help of this guide you’ll be able to decide whether you should invest in a blender or a juicer, and figure out which one is more likely to give you the biggest nutritional bang for your buck.

Juicing vs. Blending

The Benefits of Blenders and Smoothies

1. Smoothies keep longer in the refrigerator than juice does.

Smoothies contain all of the fiber that the fruits and vegetables initially came with, albeit in a blended form. Because everything is still in the drink itself, you can actually store smoothies longer without losing too much nutritional value.

To preserve a smoothie you simply need to put it in a glass container like a mason jar filled to the brim. This keeps the air out, so that the smoothie will not oxidize. Seal the jar and drink it within 24 to 32 hours. You might need to shake it if the water separates.

2. The fiber of a green smoothie acts like chimney sweepers.

Smoothies have a cleansing effect on the bowel. Since smoothies still contain all of the fiber from the fruits and vegetables, you get a chimney sweeping effect. One of the reasons I’m so fond of smoothies is that they keep me regular, and cleanse my body on their way out.

3. Sugar is absorbed slower because of the built-in fiber.

Green smoothies allow you to have fruit without experiencing the aftermath of a sugar spike and crash. The fiber will keep the flow of sugar regular as your body digests it and it enters the bloodstream. Fruit smoothies will have the effect as eating whole fruits, so you may experience a bit of an up and down, but nothing compared to fruit juice. (By the way, store bought fruit juices are the worst, and I don’t recommend them at all.)

4. Commercial smoothies are “tainted”.

Most store-bought or juice-bar smoothies are less than ideal. These smoothies are made with frozen fruit, with store-bought sugary juices and liquors for added taste. Most store-bought smoothies are packed with sugar so that they taste extra yummy. These smoothies also tend to include dairy of a questionable quality and source.

Though commercial smoothies are tainted, commercial juices are even worse. Bottled juices are almost pure sugar with very little nutrition at all. They are also high in calories, and just plain bad for the health.

5. Blenders are easy to clean.

A blender is easily cleaned by running it under the tap. Or adding a bit of water and soap to the blender and pushing the clean cycle button for a few seconds. Because blenders are so easy to assemble and clean a lot of people gravitate towards blending instead of juicing.

6. Fruit smoothies are great tasting for the whole family.

Smoothies are great for making fruit-based drinks because it contains the whole fruit. They are also great for green smoothies because you can mix and match to please the taste buds of anyone in your household. A little extra fruit, or maybe a spoonful of cacao for an added twist.

7. With a blender you can make sauces, salsas, and dips.

Blenders are great for making sauces and dips. With a blender you can make all kinds of different things aside from smoothies, like salsas, guacamole, sauces, and soups. Though you can most certainly make soups with a juicer, blended soups tend to be thicker and creamier.

Get the Vitamix Blender

8. You can easily add supplements and superfoods into smoothies.

When you’re making a smoothie you can add a spoonful of hemp protein powder, spirulina, maca root powder, and so on. You wouldn’t necessarily add these supplements to a glass of juice, either because of the taste or the texture. That makes smoothies ideal for superfoods and giving yourself a nutritional edge.

My Recommended Blender of Choice

If you’ve got the means then I definitely recommend you go with a Vitamix blender. Otherwise, Amazon has a nice selection of mid-range blenders like the Oster Professional Series Blender.

The Benefits of Juicing and Freshly Squeezed Juice

Many long time raw foodists (ugh there we go with labels again!) state that their success is in part due to juicing.

1. Fresh juices are the shortcut to pure nutrition.

Freshly made juice gives you all the nutrients without any of the associated digestion. A freshly made juice bypasses your need to digest through the fibers before you get all the nutrients of the fruits and vegetables. This is ideal for people who have digestion problems and who need to heal themselves before they can absorb nutrients from whole foods. This ability to bypass your digestion and go straight into your nutritional system is important for giving your body a break and allowing it to heal itself fully.

2. Juice is ideal for cleansing and healing.

Many people have healed themselves and undergone complete transformations by following a supervised juice fast or juice feast. The idea is to drink only freshly made juicer for an extended period of time to allow your body to take a break and work on deeper issues that require healing. Remember to consider the benefits of organic produce when you’re juicing, especially if you aren’t peeling your produce.

3. Juice vegetables, but eat fruits.

Jay Kordich (The Juice Daddy) believes that you should juice vegetables and eat fruits. Vegetables are better juiced whereas fruits are better eaten whole. Fruits need all of the fiber in order to slow down the absorption of sugar in your blood stream. The exception is that of mixing apple juice with vegetables. Vegetables contain less sugar and therefore don’t need as much fiber to slow down the absorption. This goes back to the idea that store bought fruit juices are not a healthy drink.

4. Juice gives you an instant energy boost.

When you’re drinking freshly made juice, you’re getting all of the energy right away without waiting for your body to digest through it. Whenever I drink freshly squeezed vegetable juice I always feel energized and more alert.

5. Even without fiber juice is a great way to flush your system out.

Even without all of the fiber of smoothies, juices are a great way to give your body a chance to expel things via bowel movements. If you “chew your juice” as recommended, you give your body a signal that begins peristalsis and gets your bowels moving. That leads to a bowel movement and you are cleansing your body even without eating the fiber.

6. Cleaning a juicer can be quite a task.

I think it’s a tossup between making juice and making a smoothie in terms of time. For a smoothie you need to cut the fruits that you’re going blend, and for a juice you might need to cut apples or carrots if they don’t fit in the juicer.

A juicer definitely requires a little bit more time for cleanup. Although I’ve been able to time myself and make an entire batch of juice in less than 15 minutes, I think all of these things come with practice.

7. Juices don’t make you feel full.

After drinking juice you might be satisfied for 30 minutes to an hour. After drinking a smoothie, you might be satisfied for two to three hours. So I see juice more as a way to add nutrition into your day, and not as much as a meal like a smoothie might be.

8. Juice allows you to juice almost any produce.

You might never put a beet or a carrot in one of your smoothies, but you can definitely make juice out of these ingredients. Interestingly enough, you can disguise bad tasting vegetables with other better tasting vegetables and apples to make juices that contain a larger variety of ingredients. Not necessarily so with smoothies.

9. The debate over unlocked nutrition in juice and smoothies.

It’s still up for debate whether juice or smoothies unlock all the nutritional value in fruits and vegetables. Some juicer and blender manufacturers say that all of the nutrition is unlocked and is therefore superior to eating the fruits and vegetables themselves. This would potentially lead to increased absorption of nutrition. This is certainly the case for the elderly, or people who have a hard time chewing through foods properly.

While I cannot quote any scientific studies, I can definitely vouch with my experience that it is very easy to digest smoothies and juices.

My Recommended Juicer of Choice

Earlier this year I bought an Omega Masticating Juicer, and I’m so pleased with it! There are also some great mid-range juicers available like the Breville Juice Fountain.

Posted in Uncategorized | Leave a comment

Oracle generate list of IW week dates

http://stackoverflow.com/questions/10354600/oracle-generate-list-of-iw-week-dates

Oracle generate list of IW week dates

Goal: Output a list of IW standard week dates for a given time range based off of the current date.

Desired output: with Current date = 4/27/2012, list of past 7 IW week dates

enter image description here

 

 

Try this:

    SELECT TRUNC(SYSDATE - (LEVEL * 7), 'IW') TheDate
      FROM dual
   CONNECT BY LEVEL <= 7
Posted in Uncategorized | Leave a comment

Stripping (HTML) tags in XSLT

http://blog.joachim-selke.de/2011/01/stripping-html-tags-in-xslt/

Stripping (HTML) tags in XSLT

As there doesn’t seem to be any built-in function in XLST for stripping tags from strings (e.g., to remove all markup from a piece of HTML-formatted text), people came up with a recursive template-based solution, which has been posted several times on the web (e.g.,here). However, I found this approach hard to use when the string to be cleaned from all tags already is stored in a variable or is created by using a xsl:value-of statement. Therefore, I transformed the existing template-based solution into a function-based one, which is a bit shorter and easier to use. Here it is:

 

<xsl:function name="util:strip-tags">
  <xsl:param name="text"/>
  <xsl:choose>
    <xsl:when test="contains($text, '&lt;')">
      <xsl:value-of select="concat(substring-before($text, '&lt;'),
        util:strip-tags(substring-after($text, '&gt;')))"/>
    </xsl:when>
    <xsl:otherwise>
      <xsl:value-of select="$text"/>
    </xsl:otherwise>
  </xsl:choose>
</xsl:function>

 

Note: Don’t forget to declare a namespace for this function (called util in the above code).

UPDATE: From the comments I see that an example might be helpful here. Well, here it is:

example.xsl:

<xsl:stylesheet version="2.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:util="http://whatever">

<xsl:output method="text"/>

<xsl:function name="util:strip-tags">
  <xsl:param name="text"/>
  <xsl:choose>
    <xsl:when test="contains($text, '&lt;')">
      <xsl:value-of select="concat(substring-before($text, '&lt;'),
        util:strip-tags(substring-after($text, '&gt;')))"/>
    </xsl:when>
    <xsl:otherwise>
      <xsl:value-of select="$text"/>
    </xsl:otherwise>
  </xsl:choose>
</xsl:function>

<xsl:template match="/">
<xsl:value-of select="util:strip-tags(/content)"/>
</xsl:template>

</xsl:stylesheet>

 

input.xml:

<?xml version="1.0" encoding="UTF-8"?>
<content>
test <some><nice><tags>xyz</tags></nice></some> test
</content>

 

Now I use the SAXON XSLT processor to strip the tags (inside the content tag) from the input file. Note that you might need to change the path to the JAR file to make this example work for you:
java -jar /usr/share/java/saxon.jar input.xml example.xsl

The output:


test xyz test

 

This entry was posted in XML. Bookmark the permalink.

4 Responses to Stripping (HTML) tags in XSLT

  1. Raju says:

    Hi,

    Could you please provide the namespace information for the util which you have used above.

    Thanks,
    Raju

    • Here is an example of how to declare a namespace, define a function within it, and make some function calls:http://www.xml.com/pub/a/2003/09/03/trxml.html.

      Does this answer your question?

      • Raju says:

        Hi,

        I am getting the following error.

        “The following application error(s) occurred:
        Failed to render content because of an error java.lang.NoSuchMethodException: For extension function, could not find method org.apache.xml.utils.NodeVector.stripTags([ExpressionContext,] ). ”

        Thanks,
        Raju

        • I have updated my post. It now gives a complete example. Any other problem must be related to your specific XSLT processor. Please understand that I cannot help you with that.

Posted in Uncategorized | Leave a comment

How to Convert ISO Weeks to Dates in Oracle

http://blog.joachim-selke.de/2012/03/how-to-convert-iso-weeks-to-dates-in-oracle/

Converting a date to an ISO year-week string is easy in Oracle:

>SELECT TO_CHAR(DATE '2012-03-11', 'IYYY-IW') FROM dual;

2012-10

 

One should think that converting an ISO year-week string back to a date (denoting the first day in this week) should also be easy:

>SELECT TO_DATE('2012-10', 'IYYY-IW') FROM dual;

ORA-01820: format code cannot appear in date input format
01820. 00000 -  "format code cannot appear in date input format"
*Cause:    
*Action:

 

So, obviously this is not supported yet (speaking of version 11g R2).

Therefore, I decided to write a conversion function myself. Here it is:

CREATE OR REPLACE FUNCTION iso_week_to_date
  (iso_year IN INTEGER, -- full ISO year, e.g., 2012
   iso_week IN INTEGER) -- ISO week
  RETURN DATE
IS
  jan4_of_iso_year DATE;
  first_day_of_iso_year DATE;
  iso_date DATE;
  iso_date_iso_year INTEGER;
BEGIN
  -- Find the first day of iso_year
  -- (= the Monday of the week containing January 4th)
  jan4_of_iso_year := TO_DATE(iso_year || '-01-04', 'YYYY-MM-DD');
  first_day_of_iso_year := TRUNC(jan4_of_iso_year, 'IW');
  
  -- Add the ISO week (in days)
  iso_date := first_day_of_iso_year + 7 * (iso_week - 1);
  
  -- Check whether iso_week is a valid ISO week
  -- (= whether the Thursday of the week containing iso_date is contained in the year iso_year)
  iso_date_iso_year := TO_CHAR(iso_date, 'IYYY');
  IF iso_date_iso_year <> iso_year THEN
    RAISE VALUE_ERROR;
  END IF;
  
  RETURN iso_date;
END;

 

A quick test:

SELECT iso_week_to_date(2012, 10) FROM dual;

2012-03-05 00.00.00

 

Another one:

SELECT iso_week_to_date(2012, 1234) FROM dual;

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "JSELKE.ISO_WEEK_TO_DATE", line 23
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:

 

Perfect. :-)

Have fun!

For those prefering plain SQL:

SELECT TRUNC(TO_DATE(iso_year || '-01-04', 'YYYY-MM-DD'), 'IW') + 7 * (iso_week - 1) FROM dual;

--Example (iso_year = 2012, iso_week = 10):
SELECT TRUNC(TO_DATE(2012 || '-01-04', 'YYYY-MM-DD'), 'IW') + 7 * (10 - 1) FROM dual;

2012-03-05 00.00.00

 

UPDATE: Changed TRUNC(..., 'D') to TRUNC(..., 'IW') to remove dependency from the database parameter NLS_TERRITORY.

This entry was posted in Oracle. Bookmark the permalink.
 
Posted in Uncategorized | Leave a comment

http://www.askmaclean.com/archives/category/oracle/oracle-12c

Oracle Database 12c 常见问题FAQ

http://www.askmaclean.com/archives/category/oracle/oracle-12c

 

【Oracle Database 12c】12c 常见问题FAQ

 

 

问题 : 从哪里可以下载到Oracle Database 12c?

目前从OTN页面:http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html 可以下载到

 

问题 : 12c的补丁机制 patchset、security patch update spu和 patch set update PSU有改变吗?

从Oracle数据库版本12.1.0.1起,Oracle将只提供Patch Set Update (PSU)补丁来满足Critical Patch Update (CPU)对于安全性补丁程序的要求。SPU(Security Patch Update)补丁将不再提供

 

 

问题 :那些Solaris版本与Oracle Database 12c兼容?

Solaris Sparc
Solaris 10
Solaris 11

Solaris X86_64
Solaris 10
Solaris 11

 

问题 : 对于Oracle Database 12c而言那些平台目前或今后会出32bit的CLIENT客户端软件?

Linux x86
Solaris x86 and Solaris SPARC
Windows 7 & 8

 

 

问题 :在OOW 2013中宣传的”in-memory” database cache特性 何时可用?

在”in-memory” database cache特性将在Oracle Database 12c patchset 12.1.0.2中release ,12.1.0.2计划在2014年度放出

 

问题 : 与Oracle Database 12c支持较好的Linux版本是哪几个?

Oracle Linux 5
Oracle Linux 6
RedHat Linux 5
RedHat Linux 6
SLES 11

 

问题 : 如何配置数据库实例以便其能运行在12.1.0.1的Oracle Flex cluster leaf nodes上?

Oracle Database 12c中引入了新的集群拓扑,称之为Flex Cluster ,在Flex CLuster集群中节点被按照角色分为 HUB node 和 Leaf Node。 在版本12.1.0.1中,LEAF Node不能配置数据库, 数据库实例不支持运行在12.1.0.1的Leaf Node上。 askmaclean.com

 

 

问题 :  在新的多租户架构中,数据库(PDBS)以及其他options选项需要如何购买许可证license?

虽然多租户是一种新的逻辑架构, 但其许可证license仍采取和版本12.1之前一样的模式。  软件 包括 数据库和 其他选项的license基于CPU数目 计算。 对于 Named User  Plus NUP也是如此的。

 

因此一台 2个Intel 处理器总共8核的服务器,需要购买4个core( 8* 0.5    , 0.5是Intel 核心的license 因子)的  包括 数据库和 其他选项(例如分区)的license 。

举例来说, 例子1:

1个CDB 带了 3个PDB , PDB1 使用了partitioning 分区特性, PDB2只使用ASO特性,而PDB3 不使用任何options。 由此 基于core的数目,用户必须购买 database 、multitenant 多租户特性、ASO 特性 ,以及4个 core 的partitioning分区特性,所有上述的许可证license。 如果使用Named User Plus 命名用户方式计算, 则 最少需要购买 100个NUP(4*25),或者实际的用户数量, 哪个更高则采用哪一个。

综上所述,license的计算和实际某个PDB使用或者不使用某个options是没关系的, 只要你的这个CDB里有用任何option,就需要根据服务器核数来决定license 。 同时CDB里的任何一个PDB都可以使用已购买的option 。

 

例子2:

在与例子1中同样的硬件环境中,有3个CDB运行着,每一个CDB都包含多个PDB,而仅有CDB2的一个PDB1使用分区选项。 和之前的例子完全一样,还是要购买4核的 数据库,multitenant 多租户特性 和分区特性。

 

 问题 : 到目前为止(12.1.0.1) Oracle Database 12c是否支持Oracle Database Appliance (ODA)?

目前还不支持,  Oracle计划在12.1.0.2版本中支持Oracle Database Appliance (ODA)

 

问题 :Oracle Database 12.1是否支持Exadata 数据库一体机?

是的, Oracle Database 12.1支持Exadata 。 最低的支持Oracle 12.1的Exadata Storage Server Software 版本是11.2.3.2.1, 同时也可以在Exadata上同时运行 12.1 和 11.2的数据库。

 

所有Oracle 12.1的新特性,包括 多租户可拔插数据库都在  Exadata上支持。 但在12.1中 smart scan智能扫描不从Exadata Storage上过滤行,同时12.1的IORM plan可能不适用。 这些限制将在 Exadata storage server software version 12.1.1.1.0中被改善。 其他特性例如HCC, Smart Flash Cache, Smart Logging均支持。

 

问题 : 在多租户架构中的pluggable database 可拔插数据库是什么玩样?

 

pluggable database (PDBS)是Oracle Database Release 12.1中的新特性,  你可以在一个Oracle数据库中 存放很多个pluggable database。 pluggable database 向后兼容,可以像操作12.1之前的数据库那样操作pluggable database ,这里指绝大多数常规操作。

 

问题 :  哪样场景我应当考虑使用pluggable database PDB?

以下几个场景适合于使用pluggable database:

  1. 在产品系统中的某些应用实际仅使用十分少量的硬件资源。但是如果存在大量这样的应用,则还是需要构造大量的数据库实例并为这些小规模的数据库分配存储空间
  2. 对于那些并不十分复杂或重要,需要全职DBA花费大量时间管理的数据库
  3. 为了更好地利用硬件和DBA资源,用户有必要将大量的部门级应用整合到少数几个oracle RDBMS数据库中以便部署和管理

 

Pluggable Database 可拔插数据库允许DBA整合大量的小的部门级数据库到一个更庞大的数据库中。

 

问题: 为什么我启用了heat_map=on 但是 v$heat_map_segment没有数据?

检查下 enable_pluggable_database,如果=true 那说明是CDB,目前(12.1.0.1)多租户特性无法 与 heat map特性一起工作。如果启用了CDB就用不了heat map

 

 

 

问题 : PDB带来哪些好处?

 

在一个集中化的平台上操作多个数据库将有效降低成本:

  1. 更少的实例损耗
  2. 更低的存储成本

 

减少对DBA资源的使用,以及便于维护安全性:

  • 无需应用修改
  • 更快和简便的配置
  • 节省了打patch和升级的时间
  • 分离了以下责任:
    • 不同应用的管理员
    • 应用程序管理员和DBA
    • 应用用户
  • 提供isolation
  • 保证与非CDB 完整的向后兼容性
  • 完整的RAC操作使用
  • 与Oracle Enterprise Manager和Resource Manager整合在一起
  • 可以集中化管理多个数据库
    • 备份和灾难恢复
    • 补丁和升级

 

 

问题 : 如何从12.1之前的数据库迁移到 12c的多租户数据库?

 

计划A:

  • 先把12.1之前的数据库升级到12.1
  • 将数据库装换为CDB container database

 

计划B:

  • 创建一个12c的CDB,并建立一个空的PDB
  • 使用data pump/goldengate工具将数据导入到新的PDB中

 

 

问题  : 哪些 Oracle Database 特性还在12.1.0.1多租户环境中不被支持?

  • Continuous Query Notification
  • Flashback Data Archive
  • Heat Maps
  • Automatic Data Optimization

如果必须使用以上特性,则必须要创建non-cdb数据库

 

问题  : 如何知道我的数据库是否是多租户的CDB/PDB?

 

可以通过如下语句查询:

SQL> select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option ?" , OPEN_MODE, CON_ID from V$DATABASE;

NAME                        Multitenant Option ?                  OPEN_MODE              CON_ID
---------           ------------------------------           --------------------           ----------
CDB2              Multitenant Option enabled                      MOUNTED                       0

OR

SQL>  select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option ?" , OPEN_MODE, CON_ID from V$DATABASE;

NAME               Multitenant Option ?           OPEN_MODE                                    CON_ID
------------------ ------------------------------ ---------------------------------------- ----------
MACLEAN            Regular 12c Database:          MOUNTED                                           0

 

 

问题  : 如何知道一个容器数据库中有多少个pluggable database?

可以通过下面的查询得知:

 

 

 

SQL>  select CON_ID, NAME, OPEN_MODE from V$PDBS;

    CON_ID NAME                          OPEN_MODE
---------- ------------------------       ------------
         2 PDB$SEED                         READ ONLY
         3 PDB1                                 MOUNTED
         4 PDB2                                 MOUNTED
         5 PDB3                                 MOUNTED
         6 PDB4                                 MOUNTED
         7 PDB5                                 MOUNTED
         8 PDB6                                 MOUNTED
         9 PDB7                                 MOUNTED
 ...

 

 

问题  : 如何连接到一个PDB ,例如PDB6?

 

你可以从现有的链接切换到PDB6:

SQL> alter session set container = pdb6;

你也可以直接从SQLPLUS里直接登录PDB:

A)

使用easy connect 方式:

CONNECT username/password@host[:port][/service_name][:server][/instance_name]

例如:

$ sqlplus hpal/hpal@//hpal-node1:1521/pdb2
OR
$ sqlplus hpal/hpal@//localhost:1521/pdb2
OR
$ sqlplus hpal/hpal@//localhost/pdb2

SQL> show con_name

CON_NAME
——————————
PDB2

 

B)

通过Net Service name 连接

 

TNSNAMES.ora:

=======

LISTENER_CDB1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
CDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1)
)
)

PDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hpal-node1.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb2)
)
)
=======
使用sqlplus 登录

$ sqlplus hpal/hpal@pdb2

 

问题: 是否支持将 non-cdb非CDB转换为CDB?

不行, 无法直接转换。

对于12c之前的数据库 可以在升级到12c之后,然后作为PDB 插入plug到12c的CDB中。

或者采用data pump导入到12c的CDB中。  但是目前(12.1.0.1)不存在直接将non-CDB转换为CDB的方法。

For upgrade of 11g database to 12c, can we not convert non container database to CDB, during upgrade or post upgrade?

 

The only option I know is to move non-cdb as pdb.

that’s true – you can only create a fresh CDB in Oracle 12c. Earlier database versions need to be either upgraded first to 12c and can be plugged in as a PDB, or you can use Data Punp export/import or Full Transportable Export/Import for 11.2.0.3 databases.

 

问题  : 连接后如何切换到容器root数据库?

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;

 

问题  : 如何判断我连接的是CDB还是PDB?

可以通过 show con_name  或者 select sys_context ( ‘Userenv’, ‘Con_Name’) “Container DB” from dual;  查看

 

问题  : 如何启动一个 Pluggable Database?

启动当前已连接的PDB :

alter pluggable database open;

连接到root 后启动某一个PDB:

alter pluggable database pdb6 open;

 

 问题  : 如果shutdown 关闭一个Pluggable Database?

关闭当前已连接的PDB :

alter pluggable database close;

连接到root 后关闭某一个PDB:

alter pluggable database pdb6 close;

 

问题: 如何关闭和启动容器数据库 container database?

使用管理普通数据库的 shutdown /startup命令来关闭和启动 ROOT Database

当容器数据库被关闭,则所有的PDB均不可访问。

 

问题: 那些参数可以在PDB级别做修改?

select NAME, ISPDB_MODIFIABLE from V$PARAMETER;

==》主要是一些优化器参数、Pl/SQL、NLS参数可以被在pluggable databasei级别修改

 

问题: 在我的CDB中有那些用户是 common user?

select distinct USERNAME from CDB_USERS where common = ‘YES’;

 

问题: 如何创建一个 common user?

create user c##db_dba1 identified by manager1 container=all;

 

问题: 为什么我在12c中创建user失败 ORA-65049? 

在CDB 中创建common user,用户名必须以C##开头,例如C##MACLEAN

 

问题 : 我在CDB中创建common user能够不以c##为开头吗?

可以的,需要修改隐藏参数 _common_user_prefix   (默认为:C## Enforce restriction on a prefix of a Common User/Role/Profile name)

 

问题: 如何创建一个 local user?

create user pdb6_dba1 identified by manager1 container=current;

 

 问题: Container ID   CON_ID  0 和1 的区别是什么?

CON_ID =0  不指代任何的特定 容器,而指代整个CDB。  举例来说从V$DATABASE返回的一行结果 指代整个CDB 而非某一个容器, 因此CON_ID是0 。

CON_ID=0  代表整个CDB

1 代表root container
2 代表seed
3~254 指向某一个PDB

 

问题: 对于 PDB是否有独立的一套PMON、SMON后台进程?

没有, PDB和root共享同一套 后台进程

 

问题: 对于 PDB是否有独立的控制文件?

没有,  整个CDB 使用同一套redolog 和 controlfile

 

问题: 对于 PDB是否有独立的redo重做日志文件?

没有,  整个CDB 使用同一套redolog 和 controlfile

 

 

问题: 如何监控一个PDB对于SGA内存的使用?

PDB和root共享同一个SGA, 但仍可以通过如下脚本来计算每一个容器消耗的内存量:

SQL> alter session set container=CDB$ROOT;

SQL> select POOL, NAME, BYTES from V$SGASTAT where CON_ID = ‘&con_id’;

SQL> select CON_ID, POOL, sum(bytes) from  v$sgastat
group by CON_ID, POOL order by  CON_ID, POOL;

 

问题: 我可以限制某一个PDB对SGA内存的使用吗?

目前12.1.0.1中还不可以,可能作为今后 12.2的新特性加入该功能

 

问题: 如何监控一个PDB对PGA内存的使用?

select CON_ID, sum(PGA_USED_MEM), sum(PGA_ALLOC_MEM), sum(PGA_MAX_MEM)
from  v$process
group by CON_ID order by  CON_ID;

 

alter session set container =CDB$ROOT;
select NAME , value from  v$sysstat  where NAME like ‘workarea%’;

alter session set container = <targetPDB>;
select NAME , value from  v$sysstat  where NAME like ‘workarea%’;

 

问题: 每一个PDB是否独立配置一套Undo tablespace?

还是和普通数据库一样, 一个实例拥有一套undo tablespace, RAC每个实例一个undo tablespace

 

问题: 每一个PDB是否配置独立的SYSTEM表空间?

是的, root和每一个pdb都有独立的SYSTEM表空间

 

问题: 每一个PDB是否配置独立的SYSAUX表空间?

是的, root和每一个pdb都有独立的SYSAUX表空间

 

问题: 每一个PDB是否配置独立的Temporary tablespaces临时表空间?

整个CDB使用一个默认的临时表空间,但确实可以在独立的PDB中创建临时表空间。

 

问题: 我可以为PDB指定默认的表空间default tablespace吗?

可以

 

问题 : 对于root和PDB是否所有的物理数据文件都是独立的?

对于root、seed、和每一个pdb 都有独立的数据文件

在一个CDB中,绝大多数用户数据应当存放在PDB中。

 

问题 : PDB可以使用不同的字符集吗?

不能,整个CDB中的root、pdb使用同一套字符集

 

问题 : 在pluggable database环境下如何配置Oracle NET文件?

对整个CDB仍是使用同一套listener.ora, tnsnames.ora和 sqlnet.ora 网络配置文件

 

问题s : 我如何能够创建一个Container Database?

首先安装12c软件,之后启动DBCA 创建数据库为 容器数据库:

 

cdb1

 

 

 

问题s : 对于PDB有那些常见操作?

  • 创建PDB
  • 拔出PDB unplug pdb
  • 插入PDB plug pdb
  • drop pdb
  • 设置pdb的open_mode

 

问题s : 如何创建一个pluggable database?

create pluggable database x admin user a identified by p;

create pluggable database y admin user a identified by p file_name_convert = (‘pdbseed’, ‘y’);

问题s : 如何drop一个pluggable database?

drop pluggable database x  including datafiles;

问题s : 如何从现有的pdb 克隆一个新的pdb?

被克隆的PDB应当是read only状态

– Using Oracle-Managed Files

create pluggable database x2  from x;

 

问题s : 如何拔出一个pdb?

alter pluggable database x unplug into ‘/some_directory/x_description.xml’ ;

INTO后面指向 一个绝对目录,会写出描述PDB的XML

 

 

问题s : 如何增加或修改 user-managed service?

srvctl add service … –pdb <pdb_name>

 

问题s : 如何查看某个服务是否加入了某个pluggable database?

SQL> column NAME format a30

SQL> select PDB, INST_ID, NAME from gv$services order by 1;

PDB                                    INST_ID    NAME
——————————– ———- ——————————–
CDB$ROOT                                  1 cdb1XDB
CDB$ROOT                                  1 SYS$BACKGROUND
CDB$ROOT                                  1 SYS$USERS
CDB$ROOT                                  1 cdb1
PDB1                                           1 pdb1
PDB2                                           1 pdb2

 

 

问题: 我的pluggable database的alert log在哪里?

所有的PDB都只有一份alert log ,XML版和TEXT文本版的告警日志在12c中仍同时存在,具体可以参考 V$DIAG_INFO视图

 

问题: 我的pluggable database的trace file在哪里?

所有的PDB的trace文件一样存放在container database的DIAG TRACE目录下

具体可以参考V$DIAG_INFO视图

 

问题: 在Oracle标准版standard edition中多租户选项是否可用?

是的,但是在一个CDB里你只能建一个PDB

 

问题: 事务内否在PDB之间跨越?

不能,虽然可以在启动一个事务后 使用”alter session set container”在PDB之间切换,但仅有select 查询允许在第二PDB中操作。原事务被保留,用户仍可以切换到原来的PDB并commit或者rollback。

 

 

 问题: 在PDB中如果有一个用户定义的common user创建了对象,之后这个PDB 被拔出了,并插入到另一个CDB中,且该CDB中不存在该common user, 则如何?

 

如果用户将一个包含common user的PDB插入到一个CDB中,则将发生如下情况:

在PDB中的common user 账号丢失commonly赋予的权限,包括SET CONTAINER这个权限。

如果目标CDB中含有与 该common user账号一样名字的common user账号,则新的common user将被合并到目标库的common user。并采用目标CDB common user的密码。   否则在新的插入的PDB中的common user将被锁住locked。

在此情况下,用户可以选择:

保持该账号被锁,并使用其名下的对象

使用Oracle Data Pump将数据复制到别的schema 下,并删除源账号

关闭PDB,并连接到ROOT,并创建一个与被锁定的账号同名的 common user。当重新打开PDB时,Oracle自动会将必要的commonly 角色和权限赋予给锁定的用户。 之后用户可以解锁PDB中的账号,本地授予的权限和角色保持不变。

 

 

问题: 12c中的Flex ASM是什么东西?

Oracle Flex ASM是指在集群中以预定义的基数数量运行的ASM实例。默认的基数cardinality是3,用户可以通过srvctl modify asm来修改该基数。  换句话说,ASM不需要运行在集群中的每一个节点上。

在之前的版本中,ASM运行在集群的每一个节点上,ASM的客户程序例如database、ACSF、ADVM均只能访问本地主机上的ASM实例。

通过Oracle Flex ASM, 客户程序可以通过网络连接(例如ASM network)远程ASM实例。 如果运行着一个ASM的主机宕了,Oracle Clusterware会在别的服务器上启动一个ASM实例,以便维持基数。  如果一个12c的数据库使用某个特定的ASM实例,当该ASM实例由于服务器crash或者ASM实例奔溃而不可用,则Oracle 12c数据库实例将重新连接到现有运行的其他ASM实例中。 该特性统称Oracle Flex ASM。

 

 

问题: Oracle Flex Cluster和Oracle Flex ASM有什么区别?

Oracle Flex Cluster是将集群中的节点分为 Hub和Leaf 节点。  仅仅有Hub Node可以直接访问OCR和VoteDisk, Leaf node不能直接访问共享存储。

Flex Cluster需要Flex ASM,而Flex ASM不需要Flex Cluster。

在Flex Cluster节点中,Flex ASM运行在HUB node上并服务于Flex Cluster中的其他HUB node上的应用。

默认情况下不启用Flex Cluster,需要用户显式地自行启用。

 

 

问题: 什么是ASM Network和ASM listener?

在12c之前, Oracle 集群软件要求一个公用网络以便客户端访问 和 一个私有网络以便节点之间通信,并包括了ASM的节点之间通信。

在Flex ASM中,ASM network提供分离ASM内部网络流量的功能,以便其独享一个私有网络。OUI安装界面上会让DBA选择是否为ASM使用独占的网络。  ASM network是数据库与ASM以及ASM与ASM之间通信的通路,其主要流量应当是一些文件extent map类的元数据信息。基于用户的选择,ASM 私有网络可以为ASM通信而独占,也可以与CSS服务共享。

 

在Flex ASM运行的节点上将有本地ASM监听运行。 ASM的客户端透过3个ASM监听获得endpoint信息并通过ASM network连接到Flex ASM。ASM集群的负载应当是均衡的。

 

 

问题: 如何启用Flex ASM特性?

以以下2种途径启用Flex ASM

纯12c Flex ASM,该模式下Grid Infrastructure 和数据库均运行12c版本,这样database数据库将充分利用12c系特性

与12c之前的版本混合使用 ,该模式下为了支持12c之前的数据库,ASM需要运行每一个节点上。 只需要设置ASM cardinality为ALL即可保证ASM运行在集群中的每一个节点上。 这种方式的优势是如果12c的数据库实例丢失与ASM实例的连接,则数据库连接将切换到另一个服务器上的另一个ASM实例。

 

 

问题: 如何检查Oracle Flex ASM是否启用了?

ASMCMD> showclustermode
ASM cluster : Flex mode enabled

 

ASMCMD> showclusterstate
Normal

 

问题:如何修改集群中ASM实例启动的数量,基数cardinality?

[root@ol6r02 bin]# ./srvctl modify asm -count 1
[root@ol6r02 bin]# ./srvctl config asm
ASM home: /u01/app/12.1.0/grid
Password file: +DATA/orapwASM
ASM listener: LISTENER
ASM instance count: 1
Cluster ASM listener: ASMNET1LSNR_ASM

 

问题: 如何重定位数据库到其他ASM?

如果ASM实例奔溃,客户程序将自动重定位到可用ASM实例,并保证连接是负载均衡的。

也可以手动来重定位:

ALTER SYSTEM RELOCATE CLIENT

SQL> ALTER SYSTEM RELOCATE CLIENT ‘<instance_name>:<db_name>’;
— Query GV$ASM_CLIENT to determine instance_name and db_name.

 

 

问题: 如何检查ASM实例运行在哪几个节点上?

$ srvctl status asm -detail
ASM is running on ol6r01,ol6r02
ASM is enabled

 

 

问题:如何非Flex ASM切换到 Flex ASM

 

可以使用asmca图形界面 或者 静默模式

 

 

[oracle@ol6r01 ~]$ asmca -silent -convertToFlexASM -asmNetworks eth1/10.154.138.0 -asmListenerPort 1521

To complete ASM conversion, run the following script as privileged user in local node.

/u01/app/oracle/cfgtoollogs/asmca/scripts/converttoFlexASM.sh

CRS-2673: Attempting to stop 'ora.crsd' on 'ol6r01'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'ol6r01'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'ol6r01'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'ol6r01'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'ol6r01'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'ol6r01'
CRS-2673: Attempting to stop 'ora.cvu' on 'ol6r01'
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'ol6r01' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'ol6r01'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'ol6r01' succeeded
CRS-2673: Attempting to stop 'ora.ol6r01.vip' on 'ol6r01'
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'ol6r01' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'ol6r01'
CRS-2677: Stop of 'ora.cvu' on 'ol6r01' succeeded
CRS-2672: Attempting to start 'ora.cvu' on 'ol6r02'
CRS-2676: Start of 'ora.cvu' on 'ol6r02' succeeded
CRS-2677: Stop of 'ora.scan2.vip' on 'ol6r01' succeeded
CRS-2672: Attempting to start 'ora.scan2.vip' on 'ol6r02'
CRS-2677: Stop of 'ora.scan3.vip' on 'ol6r01' succeeded
CRS-2672: Attempting to start 'ora.scan3.vip' on 'ol6r02'
CRS-2676: Start of 'ora.scan2.vip' on 'ol6r02' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN2.lsnr' on 'ol6r02'
CRS-2677: Stop of 'ora.ol6r01.vip' on 'ol6r01' succeeded
CRS-2672: Attempting to start 'ora.ol6r01.vip' on 'ol6r02'
CRS-2676: Start of 'ora.scan3.vip' on 'ol6r02' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN3.lsnr' on 'ol6r02'
CRS-2676: Start of 'ora.ol6r01.vip' on 'ol6r02' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'ol6r02'
CRS-2676: Start of 'ora.storage' on 'ol6r02' succeeded
+ echo 'Cluster bounced in node ol6r02'

Cluster bounced in node ol6r02

ASMCMD> showclustermode
ASM cluster : Flex mode enabled
ASMCMD> showclusterstate
Normal
Posted in Uncategorized | Leave a comment