Archive

Archive for November, 2011

Big Tables for Testing

November 29, 2011 Leave a comment

An «ALL_OBJECTS» Table with 1’000’000 Rows

The following Code is from Tom Kyte (http://asktom.oracle.com)

— Create Table with same structure as ALL_TABLES from Oracle Dictionary
create table bigtab
as
select rownum id, a.*
from all_objects a
where 1=0;
alter table bigtab nologging;

— Fill 1’000’000 Rows into the Table
declare
l_cnt number;
l_rows number := 1000000;
begin
— Copy ALL_OBJECTS
insert /*+ append */
into bigtab
select rownum, a.*
from all_objects a;
l_cnt := sql%rowcount;
commit;

— Generate Rows
while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into bigtab
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from bigtab
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/

alter table bigtab add constraint
bigtab_pk primary key(id);

A Table with Random Data and same Size as ALL_OBJECTS

CREATE TABLE bigtab (
id NUMBER,
weight NUMBER,
adate DATE
);

INSERT INTO bigtab (id, weight, adate)
SELECT MOD(ROWNUM,1000),
DBMS_RANDOM.RANDOM,
SYSDATE-1000+DBMS_RANDOM.VALUE(0,1000)
FROM all_objects
/
51502 rows created.

A Table which can be used for Partition Tests

The ID of the table can be used for Range Partitioning

create table bigtab (
id number(12,6),
v1 varchar2(10),
padding varchar2(50)
)
nologging — just to save a bit of time
/

insert /*+ append ordered full(s1) use_nl(s2) */
into bigtab
select
3000 + trunc((rownum-1)/500,6),
to_char(rownum),
rpad(‘x’,50,’x’)
from
all_objects s1, — you’ll need the privilege
all_objects s2
where
rownum <= 1000000
/
commit;

ID V1 PADDING
———- ———- ————————————————–
3000 1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.002 2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.004 3 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.006 4 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.008 5 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.01 6 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.012 7 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.014 8 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.016 9 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

A Table with Date’s which can be used for Partition Tests

This code is from http://www.oracle-base.com

CREATE TABLE bigtab (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
);

DECLARE
l_lookup_id NUMBER(10);
l_create_date DATE;
BEGIN
FOR i IN 1 .. 1000000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;

INSERT INTO bigtab (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, ‘This is some data for ‘ || i);
END LOOP;
COMMIT;
END;
/

Verify:

SQL> select id,to_char(created_date,’DD.MM.YYYY’),
lookup_id, data
from bigtab where rownum < 10;

ID TO_CHAR(CR LOOKUP_ID DATA
———- ———- ———- —————————–
1 21.08.2007 3 This is some data for 1
2 21.08.2006 1 This is some data for 2
3 21.08.2005 2 This is some data for 3
4 21.08.2006 1 This is some data for 4
5 21.08.2007 3 This is some data for 5
6 21.08.2005 2 This is some data for 6
7 21.08.2007 3 This is some data for 7
8 21.08.2006 1 This is some data for 8
9 21.08.2005 2 This is some data for 9

Source:

Akadia

Advertisements

ORA-01127

November 21, 2011 Leave a comment

Error:

ORA-01127 database name ‘string‘ exceeds size limit of string characters

Reason:

The specified database name is too long.

Action:

Shorten the database name to eight characters or less.

Hit / Miss Ratios

November 21, 2011 Leave a comment

Draft Article *

Buffer Hit Ratio

Consistent Gets – The number of accesses made to the block buffer to retrieve data in a consistent mode.
DB Blk Gets – The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).
Physical Reads – The cumulative number of blocks read from disk.
Logical reads are the sum of consistent gets and db block gets.
The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.

Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

select sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
 sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
 sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
 round((sum(decode(name, 'consistent gets',value, 0)) +
 sum(decode(name, 'db block gets',value, 0)) -
 sum(decode(name, 'physical reads',value, 0))) /
 (sum(decode(name, 'consistent gets',value, 0)) +
 sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
from v$sysstat

Is the Oracle buffer hit ratio a useless metric for monitoring and tuning? – Bur

Data Dictionary Hit Ratio

Gets – Total number of requests for information on the data object.
Cache Misses – Number of data requests resulting in cache misses

Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

select sum(GETS),
 sum(GETMISSES),
 round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
from v$rowcache

SQL Cache Hit Ratio

Pins – The number of times a pin was requested for objects of this namespace.
Reloads – Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be > 85%

select sum(PINS) Pins,
 sum(RELOADS) Reloads,
 round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio
from v$librarycache

Library Cache Miss Ratio

Executions – The number of times a pin was requested for objects of this namespace.
Cache Misses – Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

select sum(PINS) Executions,
 sum(RELOADS) cache_misses,
 sum(RELOADS) / sum(PINS) miss_ratio
from v$librarycache

Source / Reference Links:

Quick RMAN Backup

November 20, 2011 Leave a comment

rman> backup database plus archivelog;

rman> backup current controlfile;

rman> restore database validate;

rman> delete noprompt obsolete;

rman> crosscheck backup;

Backup password file, tnsnames.ora & listener file.

DRG-10700

November 14, 2011 Leave a comment

Error:

Error DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER

Reason:

This error indicates that you do not have any preferences installed. When installing the Oracle® server software, the InterMedia should get installed automatically with the default preferences. These are dependent on the operating system language. You can install the French preference if the language of your Oracle server is French, or the English one if the language of your Oracle server is English.

Action:

Install the appropriate language-specific default preferences. There are scripts in …/ctx/admin/defaults which create language-specific default preferences. They are named in the form of drdefXX.sql, where XX is the language code (from the Server Reference Manual).

To install the US defaults, for instance:

sqlplus ctxsys/<password> @defaults/drdefus.sql

To install the French defaults, for instance:

sqlplus ctxsys/<password> @defaults/drdeffrc.sql

Grant ‘all privileges’ to ctxsys within Oracle.

After these steps, the InterMedia Context server should start.

Source

CakePHP Tutorial

November 10, 2011 Leave a comment

So far CodeIgniter was the only PHP development  frame work I had tasted. Now I’m moving onto CakePHP!

1. CakePHP from scratch – making a small application in cakephp

2. CakePHP from scratch – Data validation, routes and more

Categories: Uncategorized