Wednesday, October 26, 2011
Monday, October 24, 2011
db_block_checking overhead.
11g中的db_block_checking参数 | Oracle Clinic – 提供专业Oracle技术支持,性能调整及数据恢复服务
Furthermore, if there are few indexes, I believe more overhead needed.
I do have data loading performance issues in data warehouse, after enable
Furthermore, if there are few indexes, I believe more overhead needed.
I do have data loading performance issues in data warehouse, after enable
DB_ULTRA_SAFE = DATA_AND_INDEX
Sunday, October 02, 2011
Thursday, September 29, 2011
Cernatis Inc - Fix high "latch: cache buffers chains" waits
Cernatis Inc - Fix high "latch: cache buffers chains" waits
relevant links
http://blog.tanelpoder.com/2011/06/05/cache-buffers-chains-latch-contention-using-latchprofx-sql-example/
http://tech.e2sn.com/oracle/troubleshooting/latch-contention-troubleshooting
relevant links
http://blog.tanelpoder.com/2011/06/05/cache-buffers-chains-latch-contention-using-latchprofx-sql-example/
http://tech.e2sn.com/oracle/troubleshooting/latch-contention-troubleshooting
tnsping response time
Should I use TNSPING to test my oracle net performance ?
Typically when TNSPING times go up in Dedicated Server configuration, it is because the system is hitting a high level of load and the listener is having to wait for a process to fork and execute the oracle dedicated server.The TNSPING program sends a packet to the listener,which goes into it’s listening queue.If there were also connect requests in the queue, then the listener will handle each request (including the tns “ping”) in the order they were received per second. If those connect requests take time, then it will take time to process the ping.TNSPING should never be used to test network performance. TNSPING’s only function is to send a connect Packet (NSPTCN) to the listener, Listener replies with a refuse Packet (NSPTRF) and a round trip time is computed. A slow TNSPING time could be anything from poor DNS resolution to a slow network to a busy listener to a busy server.
If connections are going fine ,We should not be worried about the tnsping response time.
Tuesday, September 13, 2011
Friday, September 09, 2011
10g and 11g Statistics Tables Created by the DBMS_STATS Package
REM in 10g database
SYS> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname=>'PERFSTAT',stattab => 'T1_STATTAB');
SQL> DESC DBO.T1_STATTAB
Name Null? Type
----------------------------------------- -------- ----------------------------
STATID VARCHAR2(30)
TYPE CHAR(1)
VERSION NUMBER
FLAGS NUMBER
C1 VARCHAR2(30)
C2 VARCHAR2(30)
C3 VARCHAR2(30)
C4 VARCHAR2(30)
C5 VARCHAR2(30)
N1 NUMBER
N2 NUMBER
N3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBER
N7 NUMBER
N8 NUMBER
N9 NUMBER
N10 NUMBER
N11 NUMBER
N12 NUMBER
D1 DATE
R1 RAW(32)
R2 RAW(32)
CH1 VARCHAR2(1000)
REM in 11g
SYS> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for HPUX: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> DESC PERFSTAT.T1_STATTAB
Name Null? Type
----------------------------------------- -------- ----------------------------
STATID VARCHAR2(30)
TYPE CHAR(1)
VERSION NUMBER
FLAGS NUMBER
C1 VARCHAR2(30)
C2 VARCHAR2(30)
C3 VARCHAR2(30)
C4 VARCHAR2(30)
C5 VARCHAR2(30)
N1 NUMBER
N2 NUMBER
N3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBER
N7 NUMBER
N8 NUMBER
N9 NUMBER
N10 NUMBER
N11 NUMBER
N12 NUMBER
D1 DATE
R1 RAW(32)
R2 RAW(32)
CH1 VARCHAR2(1000)
CL1 CLOB
IN Step 33 of manual upgrade checklist to 11gR2.
Upgrade Statistics Tables Created by the DBMS_STATS Package
If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by executing the
following procedure:
EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('PERFSTAT','T1_STATTAB');
SYS> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname=>'PERFSTAT',stattab => 'T1_STATTAB');
SQL> DESC DBO.T1_STATTAB
Name Null? Type
----------------------------------------- -------- ----------------------------
STATID VARCHAR2(30)
TYPE CHAR(1)
VERSION NUMBER
FLAGS NUMBER
C1 VARCHAR2(30)
C2 VARCHAR2(30)
C3 VARCHAR2(30)
C4 VARCHAR2(30)
C5 VARCHAR2(30)
N1 NUMBER
N2 NUMBER
N3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBER
N7 NUMBER
N8 NUMBER
N9 NUMBER
N10 NUMBER
N11 NUMBER
N12 NUMBER
D1 DATE
R1 RAW(32)
R2 RAW(32)
CH1 VARCHAR2(1000)
REM in 11g
SYS> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for HPUX: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> DESC PERFSTAT.T1_STATTAB
Name Null? Type
----------------------------------------- -------- ----------------------------
STATID VARCHAR2(30)
TYPE CHAR(1)
VERSION NUMBER
FLAGS NUMBER
C1 VARCHAR2(30)
C2 VARCHAR2(30)
C3 VARCHAR2(30)
C4 VARCHAR2(30)
C5 VARCHAR2(30)
N1 NUMBER
N2 NUMBER
N3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBER
N7 NUMBER
N8 NUMBER
N9 NUMBER
N10 NUMBER
N11 NUMBER
N12 NUMBER
D1 DATE
R1 RAW(32)
R2 RAW(32)
CH1 VARCHAR2(1000)
CL1 CLOB
IN Step 33 of manual upgrade checklist to 11gR2.
Upgrade Statistics Tables Created by the DBMS_STATS Package
If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by executing the
following procedure:
EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('PERFSTAT','T1_STATTAB');
about "db file parallel read/write"
'db file parallel read' occurs during recovery. The datablocks that need to be changed are read from various datafiles and are placed in non-contiguous buffer blocks. The server process waits till all the blocks are read in to the buffer.
interesting chart here.
http://oracledba-vinod.blogspot.com/2009/08/db-file-parallel-readwrite.html
interesting chart here.
http://oracledba-vinod.blogspot.com/2009/08/db-file-parallel-readwrite.html
Friday, September 02, 2011
shrinking datafile
TIP #21: Shrink datafiles
Oracle Datafiles can be shrinked if chunk of free space exists at the end of datafiles.
This URL has some useful query for shrinking :
http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html
--- to shrink datafiles:
select 'alter database datafile ''' file_name ''' resize ' ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) 'm;' cmd from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil(blocks*&&blksize/1024/1024)- ceil((nvl(hwm,1)* &&blksize)/1024/1024 ) > 0;
$ cat resize_advisor.sql
set pages 1000
SELECT a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize-1 canshrinksize
FROM (SELECT file_id, file_name, round(bytes / 1024 / 1024) filesize FROM dba_data_files) a,
(SELECT file_id, round(SUM(dfs.bytes) / 1024 / 1024) freesize FROM dba_free_space dfs GROUP BY file_id) b,
--(SELECT file_id, round(MAX(block_id) * 8 / 1024) HWMsize FROM dba_extents GROUP BY file_id) c
(SELECT file_id, round(MAX(block_id + blocks) * (select value/1024 from v$parameter where name ='db_block_size') / 1024) HWMsize FROM dba_extents GROUP BY file_id) c
WHERE a.file_id = b.file_id
AND a.file_id = c.file_id
ORDER BY unsedsize_belowhwm DESC;
-- resize advisor for one tablespace
cat resize_advisor_tbs.sql
set pages 1000
SELECT a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize - 1 canshrinksize
FROM (SELECT file_id, file_name, round(bytes / 1024 / 1024) filesize FROM dba_data_files where tablespace_name='&tbs_name') a,
(SELECT file_id, round(SUM(dfs.bytes) / 1024 / 1024) freesize FROM dba_free_space dfs GROUP BY file_id) b,
(SELECT file_id, round(MAX(block_id + blocks) * (select value/1024 from v$parameter where name ='db_block_size') / 1024) HWMsize FROM dba_extents GROUP BY file_id) c
WHERE a.file_id = b.file_id
AND a.file_id = c.file_id
ORDER BY unsedsize_belowhwm DESC;
--- To find which objects have extents at the end of datafile.
Relocating these objects makes shrinking of relevant datafile possible.
select *
from (
select owner, segment_name,
segment_type, block_id
from dba_extents
where file_id =
( select file_id
from dba_data_files
where file_name = &FILE )
order by block_id desc
)
where rownum <= 5;
Oracle Datafiles can be shrinked if chunk of free space exists at the end of datafiles.
This URL has some useful query for shrinking :
http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html
--- to shrink datafiles:
select 'alter database datafile ''' file_name ''' resize ' ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) 'm;' cmd from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil(blocks*&&blksize/1024/1024)- ceil((nvl(hwm,1)* &&blksize)/1024/1024 ) > 0;
$ cat resize_advisor.sql
set pages 1000
SELECT a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize-1 canshrinksize
FROM (SELECT file_id, file_name, round(bytes / 1024 / 1024) filesize FROM dba_data_files) a,
(SELECT file_id, round(SUM(dfs.bytes) / 1024 / 1024) freesize FROM dba_free_space dfs GROUP BY file_id) b,
--(SELECT file_id, round(MAX(block_id) * 8 / 1024) HWMsize FROM dba_extents GROUP BY file_id) c
(SELECT file_id, round(MAX(block_id + blocks) * (select value/1024 from v$parameter where name ='db_block_size') / 1024) HWMsize FROM dba_extents GROUP BY file_id) c
WHERE a.file_id = b.file_id
AND a.file_id = c.file_id
ORDER BY unsedsize_belowhwm DESC;
-- resize advisor for one tablespace
cat resize_advisor_tbs.sql
set pages 1000
SELECT a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize - 1 canshrinksize
FROM (SELECT file_id, file_name, round(bytes / 1024 / 1024) filesize FROM dba_data_files where tablespace_name='&tbs_name') a,
(SELECT file_id, round(SUM(dfs.bytes) / 1024 / 1024) freesize FROM dba_free_space dfs GROUP BY file_id) b,
(SELECT file_id, round(MAX(block_id + blocks) * (select value/1024 from v$parameter where name ='db_block_size') / 1024) HWMsize FROM dba_extents GROUP BY file_id) c
WHERE a.file_id = b.file_id
AND a.file_id = c.file_id
ORDER BY unsedsize_belowhwm DESC;
--- To find which objects have extents at the end of datafile.
Relocating these objects makes shrinking of relevant datafile possible.
select *
from (
select owner, segment_name,
segment_type, block_id
from dba_extents
where file_id =
( select file_id
from dba_data_files
where file_name = &FILE )
order by block_id desc
)
where rownum <= 5;
Thursday, September 01, 2011
Friday, August 19, 2011
How to disable 10g recycle bin (to avoid ORA-01658)
Developer feedback that she is not able to create a table, regarless of 13GB free space in the tablespace.
The error message is :
ORA-01658: unable to create INITIAL extent for segment in tablespace.
Problem solved immediately after I purged recyclebin from my 2nd feeling.
Finally , I found :
in 10g , if you have database running with recyclebin=on for some period and you have objects create & drop in those tablespaces.
Due to some reason, e.g restore to another location with recyclebin = off, or you decided to turn it off. Howerver, the dropped objects when recyclebin was ON , will remain in the recyclebin even if we set the recyclebin parameter to OFF. Hence, the space shown in dba_free_space is not swapped out, cuased the error.
Below is my test.
SYS@ODSPRX2> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SYS@ODSPRX2> create tablespace tbs1 datafile '/software/oraods/labs/recyclebin/tbs1.dbf' size 5m
2 extent management local uniform size 512k
3 segment space management auto;
Tablespace created.
@> conn / as sysdba
Connected.
SYS@ODSPRX2> create user liqy identified by liqyliqy default tablespace tbs1;
User created.
SYS@ODSPRX2> grant dba to liqy;
Grant succeeded.
SYS@ODSPRX2> conn liqy/liqyliqy
Connected.
LIQY@ODSPRX2> create table t1 (f1 number) ;
Table created.
LIQY@ODSPRX2> create table t2 (f1 number) storage(initial 512k);
Table created.
LIQY@ODSPRX2> select segment_name, bytes from dba_segments where tablespace_name='TBS1';
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES
----------
T1
524288
T2
524288
LIQY@ODSPRX2> select bytes from user_free_space where tablespace_name='TBS1';
BYTES
----------
3670016
LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';
BYTES/512/1024
--------------
7
LIQY@ODSPRX2> alter table t2 allocate extent (size 3670016);
Table altered.
LIQY@ODSPRX2> select bytes from user_free_space where tablespace_name='TBS1';
no rows selected
LIQY@ODSPRX2> create table t3 (f1 number);
create table t3 (f1 number)
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TBS1
LIQY@ODSPRX2> select segment_name, bytes from dba_segments where tablespace_name='TBS1';
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES
----------
T1
524288
T2
4194304
LIQY@ODSPRX2> drop table t1;
Table dropped.
LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';
BYTES/512/1024
--------------
1
LIQY@ODSPRX2> create table t3 (f1 number);
Table created.
LIQY@ODSPRX2> drop table t3;
Table dropped.
LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';
BYTES/512/1024
--------------
1
LIQY@ODSPRX2> conn / as sysdba
Connected.
SYS@ODSPRX2> alter system set recyclebin=off;
System altered.
SYS@ODSPRX2> conn liqy/liqyliqy
Connected.
LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';
BYTES/512/1024
--------------
1
LIQY@ODSPRX2> create table t3 (f1 number);
create table t3 (f1 number)
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TBS1
LIQY@ODSPRX2> purge recyclebin;
Recyclebin purged.
LIQY@ODSPRX2> create table t3 (f1 number);
Table created.
LIQY@ODSPRX2> spool off
SYS@ODSPRX2> drop tablespace tbs1 including contents and datafiles;
Tablespace dropped.
SYS@ODSPRX2> spool off
The error message is :
ORA-01658: unable to create INITIAL extent for segment in tablespace.
Problem solved immediately after I purged recyclebin from my 2nd feeling.
Finally , I found :
in 10g , if you have database running with recyclebin=on for some period and you have objects create & drop in those tablespaces.
Due to some reason, e.g restore to another location with recyclebin = off, or you decided to turn it off. Howerver, the dropped objects when recyclebin was ON , will remain in the recyclebin even if we set the recyclebin parameter to OFF. Hence, the space shown in dba_free_space is not swapped out, cuased the error.
Below is my test.
SYS@ODSPRX2> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SYS@ODSPRX2> create tablespace tbs1 datafile '/software/oraods/labs/recyclebin/tbs1.dbf' size 5m
2 extent management local uniform size 512k
3 segment space management auto;
Tablespace created.
@> conn / as sysdba
Connected.
SYS@ODSPRX2> create user liqy identified by liqyliqy default tablespace tbs1;
User created.
SYS@ODSPRX2> grant dba to liqy;
Grant succeeded.
SYS@ODSPRX2> conn liqy/liqyliqy
Connected.
LIQY@ODSPRX2> create table t1 (f1 number) ;
Table created.
LIQY@ODSPRX2> create table t2 (f1 number) storage(initial 512k);
Table created.
LIQY@ODSPRX2> select segment_name, bytes from dba_segments where tablespace_name='TBS1';
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES
----------
T1
524288
T2
524288
LIQY@ODSPRX2> select bytes from user_free_space where tablespace_name='TBS1';
BYTES
----------
3670016
LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';
BYTES/512/1024
--------------
7
LIQY@ODSPRX2> alter table t2 allocate extent (size 3670016);
Table altered.
LIQY@ODSPRX2> select bytes from user_free_space where tablespace_name='TBS1';
no rows selected
LIQY@ODSPRX2> create table t3 (f1 number);
create table t3 (f1 number)
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TBS1
LIQY@ODSPRX2> select segment_name, bytes from dba_segments where tablespace_name='TBS1';
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES
----------
T1
524288
T2
4194304
LIQY@ODSPRX2> drop table t1;
Table dropped.
LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';
BYTES/512/1024
--------------
1
LIQY@ODSPRX2> create table t3 (f1 number);
Table created.
LIQY@ODSPRX2> drop table t3;
Table dropped.
LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';
BYTES/512/1024
--------------
1
LIQY@ODSPRX2> conn / as sysdba
Connected.
SYS@ODSPRX2> alter system set recyclebin=off;
System altered.
SYS@ODSPRX2> conn liqy/liqyliqy
Connected.
LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';
BYTES/512/1024
--------------
1
LIQY@ODSPRX2> create table t3 (f1 number);
create table t3 (f1 number)
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TBS1
LIQY@ODSPRX2> purge recyclebin;
Recyclebin purged.
LIQY@ODSPRX2> create table t3 (f1 number);
Table created.
LIQY@ODSPRX2> spool off
SYS@ODSPRX2> drop tablespace tbs1 including contents and datafiles;
Tablespace dropped.
SYS@ODSPRX2> spool off
But in 11.2.0.2, the dropped objects will be swapped out even with recyclebin=off.
BTW, in 11gr2 we can't use 'alter system' to change value of recyclebin, while in 10g we can change it on the fly. The oracle document is not correct.
Thursday, August 11, 2011
UTF8 and AL32UTF8
In short, I think we should use AL32UTF8 to keep characterset support latest Unicode standard.
Oracle 8i (or lower) RDBMS and client releases connect to an 9i (or up) AL32UTF8 system or when connecting using database links from an 9i (or up) AL32UTF8 database to an 8i (or lower) database.
UTF8 is Unicode revision 3.0 in 8.1.7 and up. AL32UTF8 is updated with newer Unicode versions in each major release.
If you need to understand "code point" , check code point and description in UTF-8 wiki
As far as these two character sets go in Oracle, the only difference between AL32UTF8 and UTF8 character sets is that AL32UTF8 stores characters beyond U+FFFF as four bytes (exactly as Unicode defines UTF-8). Oracle’s “UTF8” stores these characters as a sequence of two UTF-16 surrogate characters encoded using UTF-8 (or six bytes per character). Besides this storage difference, another difference is better support for supplementary characters in AL32UTF8 character set.
in
in
Difference between UTF8 and AL32UTF8
Caution:
AL32UTF8 is the Oracle Database character set that is appropriate for XMLType data. It is equivalent to the IANA registered standard UTF-8 encoding, which supports all valid XML characters. Using database character set UTF8 for XML data could cause a fatal error or affect security negatively.
References:
UTF-8 wiki
Problems connecting to AL32UTF8 databases from older versions (8i and lower) [ID 237593.1]
Unicode Character Sets In The Oracle Database [ID 260893.1]
Difference between UTF8 and AL32UTF8 http://oracleappstechnology.blogspot.com/2007/10/difference-between-utf8-and-al32utf8.html
Difference between UTF8 and AL32UTF8 character sets in Oracle
UTF8 to AL32UTF8
UTF8 to AL32UTF8
When PHP and Oracle assume the worst about each other
AL32UTF8/UTF8 (Unicode) Database Character Set Implications
AL32UTF8 / UTF8 (Unicode) Database Character Set Implications [ID 788156.1]
Friday, August 05, 2011
Hash Group by exceed temporary tablespace
Tuned a "HASH GROUP BY" after increase the pga_aggregate_target from 190MB to 2GB , since workload increased over the time.
Feeling the data volume in below plan :
- massive SUMs
- 52 milliows row,
- 5 month's data.
PERFSTAT@ABC>@disp_cursor_plan
Enter value for sqlid: 885gs8h6jjxzc
old 1: select * from table(dbms_xplan.display_cursor('&sqlid'))
new 1: select * from table(dbms_xplan.display_cursor('885gs8h6jjxzc'))
SQL_ID 885gs8h6jjxzc, child number 0
-------------------------------------
,sum(CASE period WHENesets
to_date('201107','YYYYMM') THEN (case hourofday when 0 then mou else 0 end) ELSE 0
,sum(case hourofday when 0 then mou else 0 end) as
,sum(CASE period WHEN to_date('201107','YYYYMM') THEN (case
hourofday when 0 then no_call else 0 end) ELSE 0 END) AS nocall1m_0001
,sum(case hourofday when 0 then no_call else 0 end) as nocall6m_0001
,sum(CASE period WHEN to_date('201107','YYYYMM') THEN (case hourofday when 1 then
,sum(case hourofday when 1 then102
,sum(CASE period WHEN2
to_date('201107','YYYYMM') THEN (case hourofday when 1 then no_call else 0 end)
,sum(case hourofday when 1 then no_call else 0
,sum(CASE period WHEN to_date('201107','YYYYMM') THEN
(case hourofday when 2 then mou else 0 end) ELSE 0 END) AS mou1m_0203
Plan hash value: 2842556147
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 263K(100)| |
| 1 | HASH GROUP BY | | 52M| 3929M| 263K (8)| 00:52:48 |
|* 2 | TABLE ACCESS FULL| DM_TRAN_LOCALOUTGOING | 52M| 3929M| 253K (4)| 00:50:43 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("CALL_TYP"='VOICE' AND "HOUROFDAY">=0 AND "HOUROFDAY"<=23 AND
"PERIOD"<=TO_DATE(' 2011-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"PERIOD">=TO_DATE(' 2011-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
Note
-----
- dynamic sampling used for this statement
37 rows selected.
Enter value for sqlid: 885gs8h6jjxzc
old 1: select * from table(dbms_xplan.display_cursor('&sqlid'))
new 1: select * from table(dbms_xplan.display_cursor('885gs8h6jjxzc'))
SQL_ID 885gs8h6jjxzc, child number 0
-------------------------------------
,sum(CASE period WHENesets
to_date('201107','YYYYMM') THEN (case hourofday when 0 then mou else 0 end) ELSE 0
,sum(case hourofday when 0 then mou else 0 end) as
,sum(CASE period WHEN to_date('201107','YYYYMM') THEN (case
hourofday when 0 then no_call else 0 end) ELSE 0 END) AS nocall1m_0001
,sum(case hourofday when 0 then no_call else 0 end) as nocall6m_0001
,sum(CASE period WHEN to_date('201107','YYYYMM') THEN (case hourofday when 1 then
,sum(case hourofday when 1 then102
,sum(CASE period WHEN2
to_date('201107','YYYYMM') THEN (case hourofday when 1 then no_call else 0 end)
,sum(case hourofday when 1 then no_call else 0
,sum(CASE period WHEN to_date('201107','YYYYMM') THEN
(case hourofday when 2 then mou else 0 end) ELSE 0 END) AS mou1m_0203
Plan hash value: 2842556147
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 263K(100)| |
| 1 | HASH GROUP BY | | 52M| 3929M| 263K (8)| 00:52:48 |
|* 2 | TABLE ACCESS FULL| DM_TRAN_LOCALOUTGOING | 52M| 3929M| 253K (4)| 00:50:43 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("CALL_TYP"='VOICE' AND "HOUROFDAY">=0 AND "HOUROFDAY"<=23 AND
"PERIOD"<=TO_DATE(' 2011-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"PERIOD">=TO_DATE(' 2011-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
Note
-----
- dynamic sampling used for this statement
37 rows selected.
Hash group (and hash joins, as well as other operations such as sorts etc.) can use either optimal (i.e. in-memory), one-pass or multi-pass methods. The last two methods use TEMP storage and is thus much slower.
By increasing the number of possible items you might have exceeded the number of items that will fit in memory reserved for this type of operations.
Try looking at v$sql_workarea_active whilst the query is running, to see if this is the case. Or look at v$sql_workarea for historical information. It will also give you an indication of how much memory and/or temp space is needed for the operation.
If turns out to be the actual problem - try increasing the pga_aggregate_target initialization parameter, if possible. The amount of memory available for optimal hash/sort operations is usually around a 5% fraction of the pga_aggregate_target.
Useful info from http://www.orafaq.com/node/1446
Hash Join
The hash join is used for high-volume equi-joins (joins with equals predicates). Oracle performs a single read of the smaller row source (call this T1) and builds a hash table in memory. The join key is used as the hash-key of the hash table. Then a single pass of the larger row source (call this T2) is performed, hashing the join key of each row to obtain an address in the hash table where it will find matching T1 rows.Provided T1 remains small enough to build the hash table in memory, T2 can be scaled up to any arbitrarily large volume without affecting throughput or exceeding temp space. If T1 cannot be hashed in memory, then a portion of the hash-table spills to disk. When the hash table is probed by T2, the rows with join keys that match those parts of the in-memory hash table are joined immediately; the rest are written to TEMP and joined in a second pass. The bigger T1 is, the smaller the proportion of the hash table that can fit in memory, and the larger the proportion of T2 that must be scanned twice. This slows the Hash Join down considerably and also makes the join non-scalable.
For large hash processes, where temp tablespace usage is unavoidable, temp tablespace groups offer significant improvement in I/O performance and more CPU utilization.
Indexed Nested Loops is used primarily in low volume joins; it is efficient over small volumes and versatile enough to be used in a variety of situations. Although it is fully scalable, Indexed Nested Loops is inefficient over large data volumes.
2.8 millions rows in driving table looks up a larger table still should be regarded as inefficient. I had once case which is unable to complete running for 12 hours. Solved by using hash join plus partition pruning.
If both row sources are small then they may both be sorted in memory, however large sorts will spill to disk making then non-scalable.
There is no way to make a Sort-Merge join scalable. The only other way to resolve a non-equijoin is to use Nested Loops, which is slower. As volumes increase, Sort-Merge will continue to out-perform Nested Loops, but will eventually run out of Temp space. The only solution is to extend TEMP, or convert the join to Nested Loops (and then wait).
also read
2.8 millions rows in driving table looks up a larger table still should be regarded as inefficient. I had once case which is unable to complete running for 12 hours. Solved by using hash join plus partition pruning.
Sort-Merge
A sort-merge join works by reading each row-source in the join separately; sorting both sets of results on the join column(s); then concurrently working through the two lists, joining the rows with matching keys. Sort-Merge is generally faster than Indexed Nested Loops but slower than Hash Join for equi-joins. It is used almost exclusively for non-equi joins (>, <, BETWEEN) and will occasionally be used when one of the row sources is pre-sorted (eg. a GROUP BY inline view)If both row sources are small then they may both be sorted in memory, however large sorts will spill to disk making then non-scalable.
There is no way to make a Sort-Merge join scalable. The only other way to resolve a non-equijoin is to use Nested Loops, which is slower. As volumes increase, Sort-Merge will continue to out-perform Nested Loops, but will eventually run out of Temp space. The only solution is to extend TEMP, or convert the join to Nested Loops (and then wait).
also read
http://www.dbspecialists.com/files/presentations/temp_space.html
http://stackoverflow.com/questions/154722/can-anyone-explain-how-the-oracle-hash-group-works
relatioship of physical reads and logical I/O
"A physical read of table or index data places the block into the buffer cache. Then we performan a logical I/O to retrieve the block. Hence most physical reads are immediately followed by a logical I/O!"
Generally, all physical I/Os result in logical I/Os
--from 《effective Oracle by Design》
Generally, all physical I/Os result in logical I/Os
--from 《effective Oracle by Design》
Friday, June 03, 2011
database capacity planning for running database
storage administrator may not know the annual growth in database, while the database may keep growing. As DBA we need to regularly , say yearly, review the growth of critical database, before it is too late to realize that we have no space to grow in SAN.
Below are few areas to drill down for careful review.
1. Identify main contributers (tablespaces) to the growth. To achieve this, ideally you have job to record the tablespace used, total size daily , or simply can based the data file creation timestamp in dba_data_files. Create a spreadshee to calculate space needed for coming two years, using dimension tablespace and mount point name.
2. Return space to disk by shrink down data files of over-allocated tablespace. Or even you can drop unused tablespace.
3. Check if any housekeeping job is not paused accidentally. If there is big table in the tablespace and keeps growing, check with application if housekeep can be taken.
4. Check tablespace with uniform extent size , especially for uniform size >= 10MB, make sure no space wasted due to forget to consider 8x8k or 4x32k header overhead for each data file.
Below are few areas to drill down for careful review.
1. Identify main contributers (tablespaces) to the growth. To achieve this, ideally you have job to record the tablespace used, total size daily , or simply can based the data file creation timestamp in dba_data_files. Create a spreadshee to calculate space needed for coming two years, using dimension tablespace and mount point name.
2. Return space to disk by shrink down data files of over-allocated tablespace. Or even you can drop unused tablespace.
3. Check if any housekeeping job is not paused accidentally. If there is big table in the tablespace and keeps growing, check with application if housekeep can be taken.
4. Check tablespace with uniform extent size , especially for uniform size >= 10MB, make sure no space wasted due to forget to consider 8x8k or 4x32k header overhead for each data file.
Thursday, June 02, 2011
fix the database date
SQL> select sysdate from dual;
SYSDATE
---------
03-JAN-11
SQL> !date
Tue May 31 09:33:05 SST 2011
SQL> alter system set fixed_date=none;
System altered.
SQL> select sysdate from dual;
SYSDATE
---------
31-MAY-11
SYSDATE
---------
03-JAN-11
SQL> !date
Tue May 31 09:33:05 SST 2011
SQL> alter system set fixed_date=none;
System altered.
SQL> select sysdate from dual;
SYSDATE
---------
31-MAY-11
Friday, May 27, 2011
datafile overhead of tablespace using uniform extent size
Note that there is datafile overhead for tablespaces with uniform size extent allocation .
i.e, eight 8k blocks used for 8k block_size tablespace and four 32k blocks for 32k block size tablespace.
eg. Add a 1000MB datafile to existing 8k block_size tablespace with 100MB uniform extent size tablespace in CUSTPA, the right size should be 1MB*1000+8k*8 =1000Mb+64Kb=1048641536 bytes.
-- If we specify right "size 1000m", there will be only 9 x 100Mb extents available, 1 extent with 100Mb is used for overhead (wasted).
-- If specify "size 1G" , it is 1024Mb , we waste (24Mb- 64kb). The datafile size should be (round to integer times of uniform extent size + datafile overhead). To make it easy, just plus 1MB for each data file in the tablespace.
Please take note when extend tablespaces.
SYS > show parameter block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 32768
SYS > create tablespace test datafile '/ods001/oradata/ODSDMS/test.dbf' size 100m
2 extent management local uniform size 1m;
Tablespace created.
SYS > select file#, bytes ,creation_time from v$datafile where file#=27;
FILE# BYTES CREATION_
---------- ---------- ---------
27 104857600 27-MAY-11
SYS > create user test identified by test;
User created.
SYS > grant dba to test;
Grant succeeded.
SYS > alter user test default tablespace test;
User altered.
SYS > connect test/test;
Connected.
TEST > desc dba_free_space
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME VARCHAR2(30)
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
TEST > set pages 1000
TEST > select * from dba_free_space where file_id=27;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 27 5 103809024 3168
27
TEST > select 103809024/1048576 from dual;
103809024/1048576
-----------------
99
TEST > prompt 1mb is "missing"
1mb is "missing"
TEST > prompt 1mb is the uniform size
1mb is the uniform size
TEST > desc dba_extents;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
EXTENT_ID NUMBER
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
TEST > select * from dba_extents where file_id=27;
no rows selected
TEST > prompt not allocated yet
not allocated yet
TEST > select 32*1024*8 overhead_bytes from dual;
OVERHEAD_BYTES
--------------
262144
TEST > select 104857600+262144 from dual;
104857600+262144
----------------
105119744
TEST > alter database datafile '/ods001/oradata/ODSDMS/test.dbf' resize 105119744 ;
Database altered.
TEST > select * from dba_free_space where file_id=27;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 27 5 104857600 3200
27
TEST > prompt 100MB available for allocation
100MB available for allocation
TEST > alter tablespace test add datafile '/ods001/oradata/ODSDMS/test2.dbf' size 10485760;
Tablespace altered.
TEST > select * from v$datafile where file#=28;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
CHECKPOINT_CHANGE# CHECKPOIN UNRECOVERABLE_CHANGE# UNRECOVER LAST_CHANGE#
------------------ --------- --------------------- --------- ------------
LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TI BYTES BLOCKS
--------- --------------- -------------- --------- ---------- ----------
CREATE_BYTES BLOCK_SIZE
------------ ----------
NAME
--------------------------------------------------------------------------------
PLUGGED_IN BLOCK1_OFFSET
---------- -------------
AUX_NAME
--------------------------------------------------------------------------------
FIRST_NONLOGGED_SCN FIRST_NON
------------------- ---------
28 93782698 27-MAY-11 8 28 ONLINE READ WRITE
93782699 27-MAY-11 0
0 0 10485760 320
10485760 32768
/ods001/oradata/ODSDMS/test2.dbf
0 32768
NONE
0
TEST > select * from dba_free_space where file_id=28;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 28 5 9437184 288
28
TEST > select 9437184/1048576 from dual;
9437184/1048576
---------------
9
TEST > select 32*1024*2+10485760 from dual;
32*1024*2+10485760
------------------
10551296
TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize 10551296;
Database altered.
TEST > select * from dba_free_space where file_id=28;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 28 5 9437184 288
28
-- -- 9 x 1MB extents
TEST > select 32*1024*3+10485760 from dual;
32*1024*3+10485760
------------------
10584064
TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize 10584064 ;
Database altered.
TEST > select * from dba_free_space where file_id=28;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 28 5 9437184 288
28
-- still 9 x 1MB extents
TEST > select 32*1024*4+10485760 "extra4blocks" from dual;
extra4blocks
------------
10616832
TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize 10616832 ;
Database altered.
TEST > select * from dba_free_space where file_id=28;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 28 5 10485760 320
-- 10 x 1MB extents now
i.e, eight 8k blocks used for 8k block_size tablespace and four 32k blocks for 32k block size tablespace.
eg. Add a 1000MB datafile to existing 8k block_size tablespace with 100MB uniform extent size tablespace in CUSTPA, the right size should be 1MB*1000+8k*8 =1000Mb+64Kb=1048641536 bytes.
-- If we specify right "size 1000m", there will be only 9 x 100Mb extents available, 1 extent with 100Mb is used for overhead (wasted).
-- If specify "size 1G" , it is 1024Mb , we waste (24Mb- 64kb). The datafile size should be (round to integer times of uniform extent size + datafile overhead). To make it easy, just plus 1MB for each data file in the tablespace.
Please take note when extend tablespaces.
SYS > show parameter block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 32768
SYS > create tablespace test datafile '/ods001/oradata/ODSDMS/test.dbf' size 100m
2 extent management local uniform size 1m;
Tablespace created.
SYS > select file#, bytes ,creation_time from v$datafile where file#=27;
FILE# BYTES CREATION_
---------- ---------- ---------
27 104857600 27-MAY-11
SYS > create user test identified by test;
User created.
SYS > grant dba to test;
Grant succeeded.
SYS > alter user test default tablespace test;
User altered.
SYS > connect test/test;
Connected.
TEST > desc dba_free_space
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME VARCHAR2(30)
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
TEST > set pages 1000
TEST > select * from dba_free_space where file_id=27;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 27 5 103809024 3168
27
TEST > select 103809024/1048576 from dual;
103809024/1048576
-----------------
99
TEST > prompt 1mb is "missing"
1mb is "missing"
TEST > prompt 1mb is the uniform size
1mb is the uniform size
TEST > desc dba_extents;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
EXTENT_ID NUMBER
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
TEST > select * from dba_extents where file_id=27;
no rows selected
TEST > prompt not allocated yet
not allocated yet
TEST > select 32*1024*8 overhead_bytes from dual;
OVERHEAD_BYTES
--------------
262144
TEST > select 104857600+262144 from dual;
104857600+262144
----------------
105119744
TEST > alter database datafile '/ods001/oradata/ODSDMS/test.dbf' resize 105119744 ;
Database altered.
TEST > select * from dba_free_space where file_id=27;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 27 5 104857600 3200
27
TEST > prompt 100MB available for allocation
100MB available for allocation
TEST > alter tablespace test add datafile '/ods001/oradata/ODSDMS/test2.dbf' size 10485760;
Tablespace altered.
TEST > select * from v$datafile where file#=28;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
CHECKPOINT_CHANGE# CHECKPOIN UNRECOVERABLE_CHANGE# UNRECOVER LAST_CHANGE#
------------------ --------- --------------------- --------- ------------
LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TI BYTES BLOCKS
--------- --------------- -------------- --------- ---------- ----------
CREATE_BYTES BLOCK_SIZE
------------ ----------
NAME
--------------------------------------------------------------------------------
PLUGGED_IN BLOCK1_OFFSET
---------- -------------
AUX_NAME
--------------------------------------------------------------------------------
FIRST_NONLOGGED_SCN FIRST_NON
------------------- ---------
28 93782698 27-MAY-11 8 28 ONLINE READ WRITE
93782699 27-MAY-11 0
0 0 10485760 320
10485760 32768
/ods001/oradata/ODSDMS/test2.dbf
0 32768
NONE
0
TEST > select * from dba_free_space where file_id=28;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 28 5 9437184 288
28
TEST > select 9437184/1048576 from dual;
9437184/1048576
---------------
9
TEST > select 32*1024*2+10485760 from dual;
32*1024*2+10485760
------------------
10551296
TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize 10551296;
Database altered.
TEST > select * from dba_free_space where file_id=28;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 28 5 9437184 288
28
-- -- 9 x 1MB extents
TEST > select 32*1024*3+10485760 from dual;
32*1024*3+10485760
------------------
10584064
TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize 10584064 ;
Database altered.
TEST > select * from dba_free_space where file_id=28;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 28 5 9437184 288
28
-- still 9 x 1MB extents
TEST > select 32*1024*4+10485760 "extra4blocks" from dual;
extra4blocks
------------
10616832
TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize 10616832 ;
Database altered.
TEST > select * from dba_free_space where file_id=28;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 28 5 10485760 320
-- 10 x 1MB extents now
Subscribe to:
Posts (Atom)
