Oracle Knowledge Table: July 2013

Thursday, July 18, 2013

DB12c: 171 parameters modifiable at PDB level

There are 171 parameters modifiable at PDB level.
In the list below I filtered one the log_archive_* ones as this would make a too long list.

SQL> select banner from v$version where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> select name, ispdb_modifiable
2 from v$parameter
3 where ispdb_modifiable='TRUE' and name not like 'log_archive%

NAME ISPDB
---------------------------------------- -----
sessions TRUE
timed_statistics TRUE
timed_os_statistics TRUE
resource_limit TRUE
nls_language TRUE
nls_territory TRUE
nls_sort TRUE
nls_date_language TRUE
nls_date_format TRUE
nls_currency TRUE
nls_numeric_characters TRUE
nls_iso_currency TRUE
nls_calendar TRUE
nls_time_format TRUE
nls_timestamp_format TRUE
nls_time_tz_format TRUE
nls_timestamp_tz_format TRUE
nls_dual_currency TRUE
nls_comp TRUE
nls_length_semantics TRUE
nls_nchar_conv_excp TRUE
resource_manager_plan TRUE
db_file_multiblock_read_count TRUE
db_create_file_dest TRUE
db_create_online_log_dest_1 TRUE
db_create_online_log_dest_2 TRUE
db_create_online_log_dest_3 TRUE
db_create_online_log_dest_4 TRUE
db_create_online_log_dest_5 TRUE
db_unrecoverable_scn_tracking TRUE
temp_undo_enabled TRUE
resumable_timeout TRUE
heat_map TRUE
recyclebin TRUE
db_index_compression_inheritance TRUE
db_securefile TRUE
create_stored_outlines TRUE
O7_DICTIONARY_ACCESSIBILITY TRUE
global_names TRUE
listener_networks TRUE
remote_dependencies_mode TRUE
smtp_out_server TRUE
plsql_v2_compatibility TRUE
plsql_warnings TRUE
plsql_code_type TRUE
plsql_debug TRUE
plsql_optimize_level TRUE
plsql_ccflags TRUE
plscope_settings TRUE
java_jit_enabled TRUE
cursor_sharing TRUE
result_cache_mode TRUE
parallel_instance_group TRUE
result_cache_remote_expiration TRUE
object_cache_optimal_size TRUE
object_cache_max_size_percent TRUE
commit_write TRUE
commit_wait TRUE
commit_logging TRUE
optimizer_features_enable TRUE
fixed_date TRUE
sort_area_size TRUE
sort_area_retained_size TRUE
cell_offload_processing TRUE
cell_offload_decryption TRUE
cell_offload_parameters TRUE
cell_offload_compaction TRUE
cell_offload_plan_display TRUE
open_cursors TRUE
sql_trace TRUE
optimizer_mode TRUE
star_transformation_enabled TRUE
parallel_degree_policy TRUE
parallel_io_cap_enabled TRUE
optimizer_index_cost_adj TRUE
optimizer_index_caching TRUE
query_rewrite_enabled TRUE
query_rewrite_integrity TRUE
workarea_size_policy TRUE
optimizer_dynamic_sampling TRUE
statistics_level TRUE
cursor_bind_capture_destination TRUE
skip_unusable_indexes TRUE
optimizer_secure_view_merging TRUE
ddl_lock_timeout TRUE
deferred_segment_creation TRUE
optimizer_use_pending_statistics TRUE
optimizer_capture_sql_plan_baselines TRUE
optimizer_use_sql_plan_baselines TRUE
parallel_min_time_threshold TRUE
parallel_degree_limit TRUE
parallel_force_local TRUE
optimizer_use_invisible_indexes TRUE
dst_upgrade_insert_conv TRUE
max_string_size TRUE
optimizer_adaptive_reporting_only TRUE
parallel_fault_tolerance_enabled TRUE
parallel_degree_level TRUE
optimizer_adaptive_features TRUE
enable_ddl_logging TRUE
xml_db_events TRUE
olap_page_pool_size TRUE
asm_diskstring TRUE
sqltune_category TRUE
spatial_vector_acceleration TRUE
max_dump_file_size TRUE
cell_offloadgroup_name TRUE
pdb_file_name_convert TRUE

Thursday, July 11, 2013

Oracle Database 12c Installation on Windows

Tuesday, July 9, 2013

Identify hot blocks

SQL> select banner from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create table horia.new_date as select * from all_objects;
SQL>

Run 3 sessions in parallel in order to touch the same block several times and introduce latch contention.

declare
n number;
begin
for i in 1..1000000 loop
select object_id
into n
from new_date
where 1=1 --object_type='TABLE'
and object_id =116 and rownum <2;
end loop;
end;

Identify a high or rapid increasing wait count on the CACHE BUFFERS CHAINS latch.

SQL> select CHILD# "cCHILD"
2 , ADDR "sADDR"
3 , GETS "sGETS"
4 , MISSES "sMISSES"
5 , SLEEPS "sSLEEPS"
6 from v$latch_children
7 where name = 'cache buffers chains'
8 and sleeps !=0
9 order by 5, 1, 2, 3;

cCHILD sADDR sGETS sMISSES sSLEEPS
---------- ---------------- ---------- ---------- ----------
3421 000007FF309E0200 25923210 974760 1
889 000007FF30FF4028 51849242 2044527 3

Run the above query a few times to establish the id(ADDR) that has the most
consistent amount of sleeps. Once the id(ADDR) with the highest sleep count is found
then this latch address can be used to get more details about the blocks
currently in the buffer cache protected by this latch.
The query below should be run just after determining the ADDR with
the highest sleep count.

SQL> column segment_name format a35
SQL> select /*+ RULE */
2 e.owner ||'.'|| e.segment_name segment_name,
3 e.extent_id extent#,
4 x.dbablk - e.block_id + 1 block#,
5 x.tch,
6 l.child#
7 from
8 sys.v$latch_children l,
9 sys.x$bh x,
10 sys.dba_extents e
11 where
12 x.hladdr = '&ADDR' and
13 e.file_id = x.file# and
14 x.hladdr = l.addr and
15 x.dbablk between e.block_id and e.block_id + e.blocks -1
16 and x.tch>10
17 order by x.tch desc ;
Enter value for addr: 000007FF30FF4028
old 12: x.hladdr = '&ADDR' and
new 12: x.hladdr = '000007FF30FF4028' and

SEGMENT_NAME EXTENT# BLOCK# TCH CHILD#
----------------------------------- ---------- ---------- ---------- ----------
HORIA.NEW_DATE 0 3 156 889

Depending on the TCH column (The number of times the block is hit by a SQL
statement), you can identify a hot block. The higher the value of the TCH column,
the more frequent the block is accessed by SQL statements.

Monday, July 8, 2013

How to check if you have empty or stale statistics?

I have created and one can use this simple script.

set serveroutput on
declare
obj_empty dbms_stats.objecttab;
obj_stale dbms_stats.objecttab;
begin

dbms_stats.gather_database_stats(OPTIONS=>'LIST EMPTY',OBJLIST=>obj_empty);
for i in 1 .. obj_empty.count
loop
dbms_output.put_line('Empty statistics--->' ||' Owner: '||obj_empty(i).ownname||'--'||'Object name: '||obj_empty(i).objName||'--'||'Object type: '||obj_empty(i).objType);
end loop;

dbms_stats.gather_database_stats(OPTIONS=>'LIST STALE',OBJLIST=>obj_stale);
for j in 1 .. obj_stale.count
loop
dbms_output.put_line('Stale statistics--->' ||' Owner: '||obj_stale(j).ownname||'--'||'Object name: '||obj_stale(j).objName||'--'||'Object type: '||obj_stale(j).objType);
end loop;
end;
/

Sample output below:

Empty statistics---> Owner: HORIA--Object name: CONTRACTS_SEC--Object type: TABLE
Empty statistics---> Owner: SH--Object name: SYS_IOT_TOP_76625--Object type: INDEX
Empty statistics---> Owner: SH--Object name: SYS_IOT_TOP_76627--Object type: INDEX
Stale statistics---> Owner: SYS--Object name: HISTGRM$--Object type: TABLE
Stale statistics---> Owner: SYS--Object name: HIST_HEAD$--Object type: TABLE
Stale statistics---> Owner: SYS--Object name: IND$--Object type: TABLE
Stale statistics---> Owner: SYS--Object name: MON_MODS$--Object type: TABLE

Tuesday, July 2, 2013

Oracle Database check what features are used

To check which features are used you shall check:
select name,version,detected_usages,currently_used,
to_char(first_usage_date, 'DD-MON-RRRR') as "FIRST USAGE",
to_char(last_usage_date, 'DD-MON-RRRR') as "LAST USAGE"
from dba_feature_usage_statistics
order by currently_used desc, name

SQL> select name,version,detected_usages,currently_used,
2 to_char(first_usage_date, 'DD-MON-RRRR') as "FIRST USAGE",
3 to_char(last_usage_date, 'DD-MON-RRRR') as "LAST USAGE"
4 from dba_feature_usage_statistics
5 order by currently_used desc, name;

NAME VERSION DETECTED_USAGES CURRE FIRST USAGE LAST USAGE
---------------------------------------------------------------- ----------------- --------------- ----- -------------------- ---------------
Automatic Maintenance - Optimizer Statistics Gathering 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
Automatic Maintenance - SQL Tuning Advisor 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
Automatic Maintenance - Space Advisor 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
Automatic SGA Tuning 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
Automatic SQL Execution Memory 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
Automatic Segment Space Management (system) 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
Automatic Undo Management 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
Character Set 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
Deferred Segment Creation 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
Locally Managed Tablespaces (system) 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
Locally Managed Tablespaces (user) 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
Object 11.2.0.3.0 2 TRUE 20-JUN-2013 28-JUN-2013
Oracle Java Virtual Machine (system) 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
Partitioning (system) 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
Recovery Area 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
SecureFiles (system) 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
SecureFiles (user) 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
Server Parameter File 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
Virtual Private Database (VPD) 11.2.0.3.0 6 TRUE 09-MAY-2013 28-JUN-2013
ADDM 11.2.0.3.0 0 FALSE
ASO native encryption and checksumming 11.2.0.3.0 0 FALSE
AWR Baseline 11.2.0.3.0 0 FALSE
AWR Baseline Template 11.2.0.3.0 0 FALSE
AWR Report 11.2.0.3.0 0 FALSE
Active Data Guard - Real-Time Query on Physical Standby 11.2.0.3.0 0 FALSE
Advanced Replication 11.2.0.3.0 0 FALSE
Application Express 11.2.0.3.0 0 FALSE
Audit Options 11.2.0.3.0 0 FALSE
Automatic Memory Tuning 11.2.0.3.0 0 FALSE
Automatic SQL Tuning Advisor 11.2.0.3.0 4 FALSE 20-MAY-2013 20-JUN-2013
Automatic Segment Space Management (user) 11.2.0.3.0 0 FALSE
Automatic Storage Management 11.2.0.3.0 0 FALSE
Automatic Workload Repository 11.2.0.3.0 0 FALSE

To check which options are installed you shall check:
SELECT comp_name, version, status FROM dba_registry;

SQL> SELECT comp_name, version, status FROM dba_registry;

COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ --------------
OWB 11.2.0.3.0 VALID
Oracle Application Express 3.2.1.00.12 VALID
Oracle Enterprise Manager 11.2.0.3.0 VALID
OLAP Catalog 11.2.0.3.0 VALID
Spatial 11.2.0.3.0 VALID
Oracle Multimedia 11.2.0.3.0 VALID
Oracle XML Database 11.2.0.3.0 VALID
Oracle Text 11.2.0.3.0 VALID
Oracle Expression Filter 11.2.0.3.0 VALID
Oracle Rules Manager 11.2.0.3.0 VALID
Oracle Workspace Manager 11.2.0.3.0 VALID
Oracle Database Catalog Views 11.2.0.3.0 VALID
Oracle Database Packages and Types 11.2.0.3.0 VALID
JServer JAVA Virtual Machine 11.2.0.3.0 VALID
Oracle XDK 11.2.0.3.0 VALID
Oracle Database Java Packages 11.2.0.3.0 VALID
OLAP Analytic Workspace 11.2.0.3.0 VALID
Oracle OLAP API 11.2.0.3.0 VALID