Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 4, 2007

Logical I/O(consistent get) and Arraysize relation with SQL*PLUS

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 7:18 pm

“Set Arraysize” sets the number of rows that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000. The effectiveness of setting ARRAYSIZE depends on how well Oracle Database fills network packets and your network latency and throughput. Here is a small test case;

conn hr/hr

drop table test_arraysize purge ;
create table test_arraysize nologging as select * from all_source ;
set serveroutput on
exec show_space(upper(‘test_arraysize’));

Unformatted Blocks ………………… 0
FS1 Blocks (0-25) ………………… 0
FS2 Blocks (25-50) ………………… 0
FS3 Blocks (50-75) ………………… 0
FS4 Blocks (75-100)………………… 0
Full Blocks ………………… 9,976
Total Blocks………………………. 10,240
Total Bytes……………………….. 83,886,080
Total MBytes………………………. 80
Unused Blocks……………………… 121
Unused Bytes………………………. 991,232
Last Used Ext FileId……………….. 4
Last Used Ext BlockId………………. 11,529
Last Used Block……………………. 903

PL/SQL procedure successfully completed.

set timing on
set autotrace traceonly statistics
show arraysize

arraysize 15 <– default value

select text from test_arraysize where owner = ‘SYS’ ;

set arraysize 5
select text from test_arraysize where owner = ‘SYS’ ;

set arraysize 50
select text from test_arraysize where owner = ‘SYS’ ;

set arraysize 100
select text from test_arraysize where owner = ‘SYS’ ;

Here are the information given;

1- “select text from test_arraysize where owner = ‘SYS’” query returns 93963 rows for my case,
2- test_arraysize table Full Blocks 9976 and Unused Blocks 121, returned from show_space procedure
3- Formula is : Consistent Gets ~= (number of rows / arraysize) + (Full Blocks – Unused Blocks)

Lets test the formula from these information and above test scripts results;

** arraysize 15(default)
16155 consistent gets
Elapsed: 00:00:04.51

93963 rows / 15 arraysize = 6264,2
9976 Full Blocks – 121 Unused Blocks = 9855
6264,2 + 9855 = 16119,2

** arraysize 5
28233 consistent gets
Elapsed: 00:00:05.34

93963 rows / 5 arraysize = 18792,6
9976 Full Blocks – 121 Unused Blocks = 9855
18792,6 + 9855 = 28647,6

** arraysize 50
11819 consistent gets
Elapsed: 00:00:03.71

93963 rows / 50 arraysize = 1879,26
9976 Full Blocks – 121 Unused Blocks = 9855
1879,26 + 9855 = 11734,26

** arraysize 100
10904 consistent gets
Elapsed: 00:00:03.84

93963 rows / 100 arraysize = 939,63
9976 Full Blocks – 121 Unused Blocks = 9855
939,63 + 9855 = 10794,63

Responce time changes with the arraysize since logical I/O changes. Ok what about increasing arraysize to 500, what will be the impact;

set arraysize 15
select count(*) from test_arraysize where owner = ‘SYS’ ;

Elapsed: 00:00:02.82

set arraysize 500
select count(*) from test_arraysize where owner = ‘SYS’ ;

Elapsed: 00:00:02.96

Responce time got worser, overlarge sizes can easily take more SQL*Plus memory which may decrease overall performance. So optimum arraysize for your query is a test subject, no silver bullet again :)

Also in recent versions of SQL*Plus and Oracle Database, ARRAYSIZE may have little effect.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.2.0

Refences Used : “consistent gets — Very puzzling” askTom thread
show_space procedure on asktom

New Dictionary Enhancements with 10g – Part 3

Filed under: Oracle 10g New Features — H.Tonguç Yılmaz @ 2:28 pm

In Part 1 I mentioned three dictionary enhancements;

1- OS Statistics and Finding System CPU Utilization with 10g
2- BLOCKING_SESSION_STATUS and BLOCKING_SESSION columns on V$SESSION
3- Identify Tracing Enabled Sessions

And in In Part 2 I mentioned four more dictionary enhancements;

4- Tuning and monitoring enhancements; latch free and enqueue examples
5- What are the valid values for Oracle parameters?
6- Oracle Active Session History(ASH) samples
7- New views for usage pattern of the database

Now I will continue with some DBA stuff :)

8- Rman Views V$RMAN_BACKUP_JOB_DETAILS, V$RMAN_BACKUP_TYPE, V$RMAN_OUTPUT


col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,
to_char(END_TIME,’mm/dd/yy hh24:mi’)   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key ;

SESSION_KEY    INPUT_TYPE      STATUS    START_TIME     END_TIME          HRS
———–    ————-   ——–  ————– ————-     ——-
1    DATAFILE FULL   COMPLETED 03/25/05 00:48 03/25/05 00:48    .00
4    DB FULL         COMPLETED 03/27/05 02:09 03/27/05 02:11    .04
7    DB FULL         FAILED    03/27/05 02:18 03/27/05 02:24    .10

col ins format a10
col outs format a10
select SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY ins,
OUTPUT_BYTES_PER_SEC_DISPLAY outs,
TIME_TAKEN_DISPLAY
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

SESSION_KEY OPT COMPRESSION_RATIO       INS        OUTS TIME_TAKEN
———– — —————– ———- ———- ———-
1 NO         2.23776224      3.33M      1.49M  00:00:06
4 NO         1.31065794      6.92M      5.28M  00:02:16
7 NO         1.32363058      3.68M      2.78M  00:06:00

select * from V$RMAN_BACKUP_TYPE;

WEIGHT INPUT_TYPE
———- ————-
1 BACKUPSET
2 SPFILE
3 CONTROLFILE
4 ARCHIVELOG
5 DATAFILE INCR
6 DATAFILE FULL
7 DB INCR
8 RECVR AREA
9 DB FULL

select output
from v$rman_output
where session_key = 4
order by recid;

OUTPUT
———————————————————————-
connected to target database: TEST (DBID=1849323268)
Starting backup at 27-MAR-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=201 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/TEST/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/TEST/sysaux01.dbf
...

9- Using V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE

The V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE views can help you determine whether you have allocated enough space for your flash recovery area.


SELECT * FROM V$RECOVERY_FILE_DEST;

NAME            SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------  ----------- ---------- ----------------- ---------------
/mydisk/rcva     5368709120 109240320             256000              28

select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                   ,98                         0               2
ARCHIVELOG                    0                         0               0
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0

6 rows selected.

10- Tablespace Usage Statistics in dba_tablespace_usage_metrics


select * from dba_tablespace_usage_metrics;

TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
SYSAUX                              60672         4194302   1,44653389
SYSTEM                              53872           76800   70,1458333
TEMP                                    0         4194302            0
UNDO                                  160           64000          ,25
USERS                               16456          655360   2,51098633

5 rows selected.

Also for the history information DBA_HIST_TBSPC_SPACE_USAGE view can be used.

11- DB Startup time log


col instance_name format a15
col startup_time format a25
SELECT instance_name,startup_time
FROM dba_hist_database_instance;

INSTANCE_NAME   STARTUP_TIME
--------------- -------------------------
xe              21/11/2006 11:18:58,000
xe              28/11/2006 08:00:29,000
xe              27/11/2006 15:34:06,000
xe              30/11/2006 08:14:06,000
xe              05/12/2006 08:53:35,000
xe              06/12/2006 16:00:00,000
xe              07/12/2006 09:21:17,000
...

12- Redo log file size advice

The size of the redo log files can influence performance, because the behavior of the database writer and archiver processes depend on the redo log sizes. Generally, larger redo log files provide better performance. Undersized log files increase checkpoint activity and reduce performance. V$INSTANCE_RECOVERY monitors the mechanisms available to users to limit recovery I/O.
OPTIMAL_LOGFILE_SIZE column shows redo log file size (in megabytes) that is considered optimal based on the current setting of FAST_START_MTTR_TARGET. It is recommended that the user configure all online redo logs to be at least this value.


select optimal_logfile_size from v$instance_recovery;

OPTIMAL_LOGFILE_SIZE
--------------------
43 -- size in MB

13- Supported Platforms Dictionary Views

V$TRANSPORTABLE_PLATFORM displays all platforms that support cross-platform tablespace transport. Specifically, it lists all platforms supported by the RMAN CONVERT TABLESPACE command, along with the endianness of each platform.

V$DB_TRANSPORTABLE_PLATFORM displays all platforms to which the database can be transported using the RMAN CONVERT DATABASE command. The transportable database feature only supports transports of the same endian platform. Therefore, V$DB_TRANSPORTABLE_PLATFORM displays fewer rows than V$TRANSPORTABLE_PLATFORM.


col PLATFORM_NAME format a35
col ENDIAN_FORMAT format a15
select PLATFORM_NAME , ENDIAN_FORMAT from v$transportable_platform ;

PLATFORM_NAME                       ENDIAN_FORMAT
----------------------------------- ---------------
Solaris[tm] OE (32-bit)             Big
Solaris[tm] OE (64-bit)             Big
Microsoft Windows IA (32-bit)       Little
Linux IA (32-bit)                   Little
AIX-Based Systems (64-bit)          Big
HP-UX (64-bit)                      Big
HP Tru64 UNIX                       Little
HP-UX IA (64-bit)                   Big
Linux IA (64-bit)                   Little
HP Open VMS                         Little
Microsoft Windows IA (64-bit)       Little
IBM zSeries Based Linux             Big
Linux 64-bit for AMD                Little
Apple Mac OS                        Big
Microsoft Windows 64-bit for AMD    Little
Solaris Operating System (x86)      Little
IBM Power Based Linux               Big

17 rows selected.

col PLATFORM_NAME format a35
col ENDIAN_FORMAT format a15
select PLATFORM_NAME , ENDIAN_FORMAT from V$DB_TRANSPORTABLE_PLATFORM ;

PLATFORM_NAME                       ENDIAN_FORMAT
----------------------------------- -------------
Microsoft Windows IA (32-bit)       Little
Linux IA (32-bit)                   Little
HP Tru64 UNIX                       Little
Linux IA (64-bit)                   Little
HP Open VMS                         Little
Microsoft Windows IA (64-bit)       Little
Linux 64-bit for AMD                Little
Microsoft Windows 64-bit for AMD    Little
Solaris Operating System (x86)      Little

9 rows selected.

The v$database data dictionary view also adds two columns, platform ID and platform name:


select name, platform_name from   v$database;

NAME      PLATFORM_NAME
--------- ------------------------------
XE        Microsoft Windows IA (32-bit)

1 row selected.

To transport a tablespace from one platform to another, datafiles on different platforms must be in the same endian format (byte ordering).

To use RMAN TRANSPORT TABLESPACE, start the RMAN client, connecting to the source database and, if used, the recovery catalog. Then enter the TRANSPORT TABLESPACE command, specifying the required arguments. For example, to transport the tablespaces tbs_2 and tbs_3, use the TRANSPORT TABLESPACE command as follows:


transport tablespace tbs_2, tbs_3
tablespace destination '/disk1/transportdest'
auxiliary destination '/disk1/auxdest'
;

If the query returns a row, the platform supports cross-platform tablespace transport.


SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

-- The following is the query result from the source platform

PLATFORM_NAME             ENDIAN_FORMAT
------------------------- --------------
Solaris[tm] OE (32-bit)   Big

-- The following is the result from the target platform

PLATFORM_NAME             ENDIAN_FORMAT
------------------------- --------------
Microsoft Windows NT      Little

You can see that the endian formats are different and thus a conversion is necessary for transporting the tablespace set.

14- List of all oracle background processes


SELECT NAME, description FROM v$bgprocess ;

NAME  DESCRIPTION
----- -------------------------------------------
PMON  process cleanup
DIAG  diagnosibility process
FMON  File Mapping Monitor Process
PSP0  process spawner 0
LMON  global enqueue service monitor
LMD0  global enqueue service daemon 0
LMS0  global cache service process 0
LMS1  global cache service process 1
LMS2  global cache service process 2
LMS3  global cache service process 3
LMS4  global cache service process 4
LMS5  global cache service process 5
LMS6  global cache service process 6
LMS7  global cache service process 7
LMS8  global cache service process 8
LMS9  global cache service process 9
LMSa  global cache service process 10
LMSb  global cache service process 11
LMSc  global cache service process 12
LMSd  global cache service process 13
LMSe  global cache service process 14
..

15- Naming Restore Points

In Oracle 10.2 and above restore points can be named, previously required SCN or timestamp. Feature requires Archiving and Flashback

To enable flashback set the following parameters:


ALTER SYSTEM SET db_recovery_file_dest_size = 1000M;
ALTER SYSTEM SET db_recovery_file_dest = ‘’;
ALTER SYSTEM SET db_flashback_retention_target = 1440;

-- Enable flashback mode
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

Restore points can be;
- Guaranteed restore points
- FLASHBACK must be enabled before creation
- Must be manually deleted
- Can restore beyond time specified by DB_FLASHBACK_RETENTION_TARGET parameter

Normal restore points
- Can be created before FLASHBACK is enabled
- Can be automatically deleted
- Cannot restore beyond time specified by DB_FLASHBACK_RETENTION_TARGET parameter


-- To create a restore point
CREATE RESTORE POINT restore_point1;
CREATE RESTORE POINT restore_point2 GUARANTEE FLASHBACK DATABASE;

-- To drop a restore point
DROP RESTORE POINT restore_point1;

-- To flashback database to a restore point
SHUTDOWN IMMEDIATE
STARTUP MOUNT
FLASHBACK DATABASE TO RESTORE POINT restore_point1;
ALTER DATABASE OPEN RESETLOGS;
Flashback table to restore point
FLASHBACK TABLE TO RESTORE POINT restore_point3;

New dynamic performance view; V$RESTORE_POINT

Name Type
SCN NUMBER
DATABASE_INCARNATION# NUMBER
GUARANTEE_FLASHBACK_DATABASE VARCHAR2(3)
STORAGE_SIZE NUMBER
TIME TIMESTAMP(9)
NAME VARCHAR2(128)

Additional Note 1 – OEM Warning

The Enterprise Manager for Oracle 10g comes with a number of packages which need an additional license if you want to use them. These packages are as follows:
- Database Diagnostics Pack
- Database Diagnostics Pack
- Configuration Management Pack

Additional Note 2 – Some usefull synonyms

CAT Synonym for USER_CATALOG
CLU Synonym for USER_CLUSTERS
COLS Synonym for USER_TAB_COLUMNS
DICT Synonym for DICTIONARY


column table_name format a25
column column_name format a25
column comments format a35
SELECT table_name, column_name, comments
FROM   dict_columns
WHERE  table_name like '%QUEUE%' and comments is not null
ORDER BY table_name, column_name, comments ;

TABLE_NAME                COLUMN_NAME               COMMENTS
------------------------- ------------------------- -----------------------------------
ALL_APPLY_ENQUEUE         DESTINATION_QUEUE_NAME    Name of the queue where events sati
sfying the rule will be enqueued
ALL_APPLY_ENQUEUE         RULE_NAME                 Name of the rule
ALL_APPLY_ENQUEUE         RULE_OWNER                Owner of the rule…
...

Additional Note 3 – {ORACLE_HOME}/rdbms/admin

catalog.sql Builds the data dictionary views
catblock.sql Creates views that dynamically display lock dependency graphs
caths.sql Installs packages for administering heterogeneous services
catio.sql Allows I/O to be traced on a table-by-table basis

Check this list; http://www.psoug.org/reference/files.html

Additional Note 4 – Data dictionary flash file :)
Data dictionary for Oracle 10g

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.2.0

Refences Used : Oracle® Database Reference 10g Release 2 (10.2)
Oracle® Database New Features Guide 10g Release 2 (10.2)
How Oracle 10g makes life easier in a day to day administration.
Oracle 10g Enterprise Manager Packages usage and licensing
New Cross-Platform Transportable Tablespaces
Julian Dyke Presentations

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 79 other followers