set pages 5000 lines 180
set heading on
set feedback off
set serveroutput on

col LABEL for a28
col DISKGROUP for a9
col STATUS for a9
col TOTAL for a11
col MOUNT_STATE for a11

exec dbms_output.put_line('---');
exec dbms_output.put_line('--- DISK OVERVIEW');
exec dbms_output.put_line('---');

SELECT
        D.LABEL||' ('||D.NAME||')' LABEL,
        (SELECT
          NAME
         FROM
          V$ASM_DISKGROUP
         WHERE
          GROUP_NUMBER=D.GROUP_NUMBER)
        DISKGROUP,
        D.STATE STATUS,
        lpad(D.TOTAL_MB/1024,6)||' GB' TOTAL,
        D.MOUNT_STATUS MOUNT_STATE
FROM
        V$ASM_DISK D
ORDER BY 2;

exec dbms_output.put_line('---');
set feedback on

The output looks as follow:

---
--- DISK OVERVIEW
---

LABEL                        DISKGROUP STATUS    TOTAL       MOUNT_STATE
---------------------------- --------- --------- ----------- -----------
ORAASM1 (ORAASM1)            DG1       NORMAL    99.984 GB   CACHED
ORAASM3 (ORAASM3)            DG1       NORMAL    99.984 GB   CACHED
ORAASM5 (ORAASM5)            DG1       NORMAL    99.984 GB   CACHED
ORAASM4 (ORAASM4)            DG1       NORMAL    99.984 GB   CACHED
ORAASM2 (ORAASM2)            FRA1      NORMAL    99.984 GB   CACHED
ORAASM6 ()                             NORMAL         0 GB   CLOSED
---

I will explain a simple solution. But first lets have a look to our problem.
Table size: 2G
Row count: 33′000
Query response time: ~ 25s

mysql> desc images;
+-------------+-----------------------------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------+------+-----+---------+----------------+
| imageid | int(11) | NO | PRI | NULL | auto_increment |
| filename | varchar(200) | YES | MUL | NULL | |
| description | varchar(200) | YES | | NULL | |
| data | longblob | YES | | NULL | |
| type | enum('coversmall','cover','coverback','screenshot','specialcover','banner') | YES | MUL | NULL | |
+-------------+-----------------------------------------------------------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


The index can not be used


mysql> explain select imageid,filename,description,type from images where filename like '%call%' order by filename;
+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | images | ALL | NULL | NULL | NULL | NULL | 371398 | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)


Index can be use if the expression does not start with a wild card


mysql> explain select imageid,filename,description,type from images where filename like 'call%' order by filename;
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+
| 1 | SIMPLE | images | range | idx_images_filename | idx_images_filename | 603 | NULL | 66 | Using where |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select imageid,filename,description,type from images where filename like 'call%abc' order by filename;
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+
| 1 | SIMPLE | images | range | idx_images_filename | idx_images_filename | 603 | NULL | 66 | Using where |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+


Since we are searching for the filename, we a new column for the filename. Add a character in front of the string and index the new column.


mysql> alter table images add vfilename varchar(201);

Now we change our application so it add always the same character “a” in the pattern.
So we can use the index.


mysql> explain select imageid,filename,description,type from images where vfilename like 'a%abc%' order by filename;
+----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | images | range | idx_images_vfilename | idx_images_vfilename | 603 | NULL | 75840 | Using where; Using filesort |
+----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)


The perfomance benefit is, that we can do now a index scan (full/range scan) which read much less data than a full table scan.
We reduced i/o, saved our cache and speed up the query by magnitudes!

This is a real ask the real tom solution!

Usually you get following Error message.

SQL> startup
SQL> ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory


The problem it ulimit -l
You have to set it in /etc/security/limits.conf and relogon to the system and restart database.

cat /etc/security/limits.conf
...
oracle soft memlock unlimited
oracle hard memlock unlimited



alter system set lock_sga=true scope=spfile;
shutdown immediate
startup


And you SGA stays in physical memory.

If you SGA gets pages out you will encounter following waits.
Wait Event: latch: shared pool

1. Enable Constraint with novalidate option
2. Set parallel degree on the table or on session with force ddl parallel
3. Enable nable the constraints with validation

Example:

ALTER TABLE T_1 ADD CONSTRAINT NC_1 not null (colname) disabled;
ALTER TABLE T_1 ENABLE NOVALIDATE CONSTRAINT NC_1;
ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
ALTER TABLE T_1 PARALLEL 8;
ALTER TABLE T_1 ENABLE VALIDATE CONSTRAINT NC_1;
ALTER SESSION DISABLE PARALLEL DDL;



SQL> startup nomount
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device


There are 64G physical memoray avaliable.

shmmax is set to 52G

But still its not possibel to start the Oracle instance with more than 30G SGA.

Solution:

kernel.shmall is set to small.


cat /proc/sys/kernel/shmall
8248733
getconf PAGE_SIZE
4096


This means that only 8248733 memory pages can be allocate.

4096 x 8248733 = 32G



To allocate 50G we need to set shmall to at least 13107200


ORA-56708: Could not find any datafiles with asynchronous i/o capability
ORA-06512: at "SYS.DBMS_RMIN", line 456
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1285
ORA-06512: at line 6


The ASM files show ASYNC_ON

SELECT name, asynch_io
FROM v$datafile f,v$iostat_file i
WHERE f.file# = i.file_no
AND filetype_name = 'Data File'
/
+DG1/xxxx/datafile/system.260.721649043
ASYNC_ON
....


did not help as well

alter system set filesystemio_options=setall scope=spfile;
shutdown immediate
startup


I guess that the problem is that moste datafiles (smallfiles) are full (max. number of blocks). So the io calibrate tool can not write to this files.

System aio slots:

To find out the maximum avalable asynch I/O slots:
$ cat /proc/sys/fs/aio-max-nr
65536

To find out how many are being used:
$ cat /proc/sys/fs/aio-nr

increase fs.aio-max-nr if nessesary (or restart some db's)
65536

It can be usefull to compre the application perfomance on batch processing system and analyze commit related waits.

@snapcommit

Example
@snapcommit 1 20

snapcommit.sql

set tab off verify off linesize 299
set termout on serverout on size 1000000 format wrapped

dbms_lock.sleep(&snap_interval);
end loop;
cmt_diff := cmt_curr - cmt_start;
dbms_output.put_line('total commits: '||cmt_diff );
end;
/


Solution:
use tha statice converter function of the anydata object type

Example

select SQL_ID,NAME,DATATYPE_STRING,VALUE_STRING,anydata.AccessTimestamp(VALUE_ANYDATA) from DBA_HIST_SQLBIND where sql_id='f17fd6xuvxc7x' and name=':1'


The user claims that a oracle web application is slow. The web application is unsing in database java.

Test 1: Determine the slow Part

The webapplication can be run in sqlplus with following pl/sql

set serveroutput on
set timing on
declare
name owa.vc_arr;
val owa.vc_arr;
res owa.vc_arr;
simple_list__ owa_util.vc_arr;
complex_list__ owa_util.vc_arr;
c clob;
begin
name(1) := '1';
val(1) := '1';
OWA.init_cgi_env (0, name, val);
HTP.init;
-- if ((owa_match.match_pattern('jdb', simple_list__, complex_list__, true))) then
jdb(1, name, val, res);
-- end if;
wpg_docload.get_download_file(:doc_info);
OWA_UTIL.showpage;
end;
/

The result showed that the application is using 4 frames. This means that 1 web request is followed by 4 subsequend requests.
We see as well that the owa functions are not the problem.

Test 2: Trace the application
To just trace the relevant part, I traced by server and modul name


select username,client_identifier,process,module from v$session where module like 'httpd%';

cd udump
tkprof


The trace showed one query taken about 10 to 20 seconds per executionn

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 192 (recursive depth: 1)


Now its a simple sql tuning task to solve the problem

All query include the primary key, no range, no full scan’s.

So I told him it doen’t make sense to partition this table for perfomance reasons.
To profe my statement I did a small Test case.


create table part_range_iot (c1 number, c2 varchar2(20), c3 char(1),
constraint part_range_iot_pk primary key (c1))
partition by HASH (c1)
PARTITIONS 100
/
begin
for i in 1..999
loop
insert into part_range_iot values (i,'abcd','y');
end loop;
commit;
end;
/

select * from part_range_iot where c1 = 99;

The output from the query was

Execution Plan
----------------------------------------------------------
Plan hash value: 3787470184

Statistics
----------------------------------------------------------
104 recursive calls
0 db block gets
14 consistent gets
0 physical reads

So 14 consitent gets were used

The same case without partitioning

create table part_range_iot (c1 number, c2 varchar2(20), c3 char(1),
constraint part_range_iot_pk primary key (c1))
/

select * from part_range_iot where c1 = 99;


The results on the IOT table without partitions

Execution Plan
----------------------------------------------------------
Plan hash value: 2398071293

Statistics
----------------------------------------------------------
46 recursive calls
0 db block gets
8 consistent gets
0 physical reads

Just 8 consitent reads.

Conclusion:
Partition an IOT on its primary key has just a negative perfomance impact.
The difference is here
TABLE ACCESS BY INDEX ROWID and TABLE ACCESS BY GLOBAL INDEX ROWID.
On the the non partitioned table we have to do just a unique index scan. On the partitioned table we do the same, but we have to find the right partition first.