DBA Blogs

index issue with our partitioned table ora14196 and question if plan OK?

Tom Kyte - Wed, 2016-12-07 17:46
Hello Tom I have an index issue with our partitioned table this table cw_tb_zvlist11 has about 500 mio rows inserted and the insert process is still active I see that unfortunality we created the primary index as a nonunique one. Now I tr...
Categories: DBA Blogs

adding column with default value 0

Tom Kyte - Wed, 2016-12-07 17:46
Hi Tom, I have read lot of posts which you have discussed on the subject of adding column with some default value. Thanks a lot for such a great service you render to oracle community. Unfortunately one of my friend informed me some bugs are as...
Categories: DBA Blogs

Is there another option for Next Value For

Tom Kyte - Wed, 2016-12-07 17:46
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 64-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production How would I go abo...
Categories: DBA Blogs

12.2 New Features -- 5 : Memory Parameters for Pluggable Database

Hemant K Chitale - Tue, 2016-12-06 08:07
12.2 allows Instance Memory parameters to be configured at the PDB level.

[oracle@HKCORCL ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 6 13:56:28 2016

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 2544M
sga_min_size big integer 0
sga_target big integer 2544M
unified_audit_sga_queue_size integer 1048576
SQL> show parameter db_cach

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 0
SQL>


Those are parameters set at the CDB level. Let's see the PDB.

SQL> alter session set container = PDB1;

Session altered.

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 2544M
sga_min_size big integer 0
sga_target big integer 0
unified_audit_sga_queue_size integer 1048576
SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 0
SQL> alter system set db_cache_size=400M;

System altered.

SQL>
SQL> alter system set sga_target=512M;
alter system set sga_target=512M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-56750: invalid value 536870912 for parameter sga_target; must be larger
than 200% of parameter db_cache_size


SQL> alter system set sga_target=810M;

System altered.

SQL> alter system set shared_pool_size=256M;

System altered.

SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 400M
SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 810M
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 26004684
shared_pool_size big integer 256M
SQL>
SQL> alter system set pga_aggregate_target=128M;

System altered.

SQL>


Returning to the CDB ...

SQL> connect / as sysdba
Connected.
SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 0
SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 2544M
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 26004684
shared_pool_size big integer 0
SQL> show parameter pga_aggergate_target
SQL> show parameter pga_aggregate_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 1775294400
SQL>


Thus, multiple PDBs can have their own private target and limits (even an SGA_MIN_SIZE) all shared within the single instance that they co-exist in.
Note : The requirement is that MEMORY_TARGET is to be not set.
.
.
.

Categories: DBA Blogs

Flashback Archive Internal History table is not compressed -- Kindly suggest

Tom Kyte - Tue, 2016-12-06 05:06
Hi Tom, Once flashback archive is enabled for a table, a corresponding history table will be created by oracle internally. It is automatically partitioned and compressed as well. But when I have enabled FBA for a table, the history table is partit...
Categories: DBA Blogs

Column with default value

Tom Kyte - Tue, 2016-12-06 05:06
I have a table in which one of the column is as below create_date TIMESTAMP(6) default SYSDATE, When the record is inserted from the GUI(URL) , it doesn't insert any value for create_Date .. But when inserted from other than URL i...
Categories: DBA Blogs

Redo Log 4K Blocksize

Tom Kyte - Tue, 2016-12-06 05:06
Good Evening, In 11g, I've read about the possibility of setting redo logs to have a blocksize of 4k. Supposedly, the blocksize is automatically set based on the block sector of the disk. Supposedly, high capacity disks have block sectors of 4k....
Categories: DBA Blogs

SQL profile is usable?

Tom Kyte - Tue, 2016-12-06 05:06
Hi,guy! I have some question about the SQL PROFILE, when use SQL PROFILE to bind the SQL,it performanced good,but as time goes by,the data in the table will grow rapidly and the SQL which use SQL PROFILE performanced bad. So what's the meaning of th...
Categories: DBA Blogs

db2 Query in Unix: load from /dev/null of del replace into Schema.Tablename nonrecoverable;

Tom Kyte - Tue, 2016-12-06 05:06
Hi Tom, In Unix db2 I am using the below query to clear the table. db2 Query in Unix: load from /dev/null of del replace into Schema.Tablename nonrecoverable; What would be the best approach to do the same thing in Oracle. I don't thin...
Categories: DBA Blogs

How mandatory is to use DBMS_AUDIT_MGMT

Tom Kyte - Tue, 2016-12-06 05:06
Hello everyone, One question please, how mandatory is to use the package DBMS_AUDIT_MGMT for Oracle. a)There is a NOT ADVISABLE suggestion from Oracle to work in aud$ table directly, to force to use ALWAYS the package. b) is simple we ADVISE ...
Categories: DBA Blogs

Latch Free

Tom Kyte - Tue, 2016-12-06 05:06
Hi Connor, I have no intention of complaining. But all over the web I find lot of discussion about latch , latch spin, and latch sleep. And the description goes like below. 1] Try to acquire a latch 2] Failed ! Try again after sometime 3] Ret...
Categories: DBA Blogs

How to reduce Buffer Busy Waits with Hash Partitioned Tables in #Oracle

The Oracle Instructor - Tue, 2016-12-06 04:57

fight_contention_2

Large OLTP sites may suffer from Buffer Busy Waits. Hash Partitioning is one way to reduce it on both, Indexes and Tables. My last post demonstrated that for Indexes, now let’s see how it looks like with Tables. Initially there is a normal table that is not yet hash partitioned. If many sessions do insert now simultaneously, the problem shows:

Contention with a heap table

Contention with a heap table

The last extent becomes a hot spot; all inserts go there and only a limited number of blocks is available. Therefore we will see Buffer Busy Waits. The playground:

SQL> create table t (id number, sometext varchar2(50));

Table created.

create sequence id_seq;

Sequence created.

create or replace procedure manyinserts as
begin
 for i in 1..10000 loop
  insert into t values (id_seq.nextval, 'DOES THIS CAUSE BUFFER BUSY WAITS?');
 end loop;
 commit;
end;
/

Procedure created.

create or replace procedure manysessions as
v_jobno number:=0;
begin
FOR i in 1..100 LOOP
 dbms_job.submit(v_jobno,'manyinserts;', sysdate);
END LOOP;
commit;
end;
/

Procedure created.

The procedure manysessions is the way how I simulate OLTP end user activity on my demo system. Calling it leads to 100 job sessions. Each does 10.000 inserts:

SQL> exec manysessions

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

  COUNT(*)
----------
   1000000

SQL> select object_name,subobject_name,value from v$segment_statistics 
     where owner='ADAM' 
     and statistic_name='buffer busy waits'
     and object_name = 'T';

OBJECT_NAM SUBOBJECT_	   VALUE
---------- ---------- ----------
T			    2985

So we got thousands of Buffer Busy Waits that way. Now the remedy:

SQL> drop table t purge;

Table dropped.

SQL> create table t (id number, sometext varchar2(50))
     partition by hash (id) partitions 32;

Table created.

 
SQL> alter procedure manyinserts compile;

Procedure altered.

SQL> alter procedure manysessions compile;

Procedure altered.

SQL> exec manysessions 

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

  COUNT(*)
----------
   1000000

SQL> select object_name,subobject_name,value from v$segment_statistics 
     where owner='ADAM' 
     and statistic_name='buffer busy waits'
     and object_name = 'T';  

OBJECT_NAM SUBOBJECT_	   VALUE
---------- ---------- ----------
T	   SYS_P249	       0
T	   SYS_P250	       1
T	   SYS_P251	       0
T	   SYS_P252	       0
T	   SYS_P253	       0
T	   SYS_P254	       0
T	   SYS_P255	       0
T	   SYS_P256	       1
T	   SYS_P257	       0
T	   SYS_P258	       0
T	   SYS_P259	       1
T	   SYS_P260	       0
T	   SYS_P261	       0
T	   SYS_P262	       0
T	   SYS_P263	       0
T	   SYS_P264	       1
T	   SYS_P265	       1
T	   SYS_P266	       0
T	   SYS_P267	       0
T	   SYS_P268	       0
T	   SYS_P269	       0
T	   SYS_P270	       0
T	   SYS_P271	       1
T	   SYS_P272	       0
T	   SYS_P273	       0
T	   SYS_P274	       0
T	   SYS_P275	       1
T	   SYS_P276	       0
T	   SYS_P277	       0
T	   SYS_P278	       0
T	   SYS_P279	       2
T	   SYS_P280	       0

32 rows selected.

SQL> select sum(value) from v$segment_statistics 
     where owner='ADAM' 
     and statistic_name='buffer busy waits'
     and object_name = 'T';

SUM(VALUE)
----------
	 9

SQL> select 2985-9 as waits_gone from dual;

WAITS_GONE
----------
      2976

The hot spot is gone:

hash_part_table

This emphasizes again that Partitioning is not only for the Data Warehouse. Hash Partitioning in particular can be used to fight contention in OLTP environments.


Tagged: partitioning, Performance Tuning
Categories: DBA Blogs

12.2 Index Advanced Compression “High” – Part I (High Hopes)

Richard Foote - Mon, 2016-12-05 23:52
Oracle first introduced Advanced Compression for Indexes in 12.1 as I’ve discussed here a number of times. With Oracle Database 12c Release 2, you can now use Index Advanced Compression “High” to further (and potentially dramatically) improve the index compression ratio.  Instead of simply de-duplicating the index entries within an index leaf block, High Index […]
Categories: DBA Blogs

About Joins

Tom Kyte - Mon, 2016-12-05 10:46
Hello I am facing problem with alias name in the joins. Pleae help me out. Below is the example select y.employee_id from ((select employee_id,department_id,manager_id from employees)a join (select department_id,manager_id from departme...
Categories: DBA Blogs

Dynamic Cursors

Tom Kyte - Mon, 2016-12-05 10:46
i have to write a procedure where the procedure takes a parameter say employee_status(Retired/Not-Retired) based on the parameter i've to use a cursor select employees from mytable who are retired or not retired and loop over all the employees (Ret...
Categories: DBA Blogs

Import Data from Excel to DB Table

Tom Kyte - Mon, 2016-12-05 10:46
Hi I need all-time executable SP that can import data from an Excel and do some DML operation on live tables. First of all, is that possible? IF so 1. what are the many ways that we can do that? 2. How do we do that using a Stored Procedu...
Categories: DBA Blogs

Oracle 11g Database , and Weblogic Server 11g Forms , Reports and facing dead slow

Tom Kyte - Sun, 2016-12-04 16:26
i am using Oracle 11g Database , and Weblogic Server 11g Forms , Reports and facing dead slow issue at developers level as well as user level. i test 6i forms on same database same tables its running ok please any one help me what could be the ...
Categories: DBA Blogs

string patterns

Tom Kyte - Sun, 2016-12-04 16:26
I need to write a query which will look for the data in a particular column for the strings of patterns nnnnnn or Annnnn where n stands for number and A stands for capital letter. Any strings which are in a different pattern other than these two shou...
Categories: DBA Blogs

LOG(2,4/8/16 Issue

Tom Kyte - Sun, 2016-12-04 16:26
Hi Chris&Connor..Good Morning! You both are doing good job. My question is: The minimum height of a Red-Black tree H = FLOOR(LOG(2,N)), where N is nodes. Ideally, when N=4 then H=2 and when N=8 then H=3 and so on as per the formula. But, the LOG(...
Categories: DBA Blogs

Tom - what is going to happen to this website?

Tom Kyte - Sun, 2016-12-04 16:26
I wish you much happiness in your upcoming retirement. What will happen to this website? Could one buy an archived copy? It has been such an amazing resource through my career. Even if you are going to hang it up, I'm sure it is still very usefu...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs