One of my favorites on Oracle is table partitioning. Oracle enables range, hash and list partitioning. The easiest for begginers is range partitioning.
The advantages
A partitioned table may be spread across several tablespaces. It is possible to compress some partitions, change them to read-only status, or even put them offline. Data access will also be faster, provided the WHERE clause properly makes reference to the partition key.
The disavantadges
Partitioned tables always need maintenance, to create the new partitions.
How to do it
ORA-00600: internal error code, arguments: [17069], [0xC000000161802748], [], [], [], [], [], []
Oracle Version: 9.2.0.5.0
O.S. : HP/UX 11i
Machine: HP9000
Checking Metalink
There are several notes on this one: 39616.1, 151992.1, 266513.1, 268423.1, 283246.1, 268413.1, 271733.1, 241824.1 and 214242.1. Most of this notes are about upgrade issues, which is not the case. Checking the trace file, the error was in some function execution:
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17069], [0xC000000161802748], [], [], [], [], [], []
Current SQL statement for this session:
BEGIN :ret := P105_GETINVOICE_CALL; END;
Going down a little bit further on the trace file we find:
last wait for 'library cache pin' blocking sess=0x0 seq=5058 wait_time=3007838
Back to Metalink, the first 2 notes may have some information. Note 39616.1 states that error description is: Failed to pin a library cache object after 50 attempts.
This may also be some problem connected with the shared pool. Are there any ORA-04031 failures?
Let's try this little script:
column kghlurcr heading "RECURRENTCHUNKS"
column kghlutrn heading "TRANSIENTCHUNKS"
column kghlufsh heading "FLUSHEDCHUNKS"
column kghluops heading "PINS ANDRELEASES"
column kghlunfu heading "ORA-4031ERRORS"
column kghlunfs heading "LAST ERRORSIZE"
select kghlurcr, kghlutrn, kghlufsh, kghluops, kghlunfu, kghlunfs
from sys.x$kghlu
where inst_id = userenv('Instance')
/
RECURRENT TRANSIENT FLUSHED PINS AND ORA-4031 LAST ERROR
CHUNKS CHUNKS CHUNKS RELEASES ERRORS SIZE
---------- ---------- ---------- ---------- ---------- ----------
12887 22473 902500 485956809 6 912
That's it. Maybe it is time to bounce the database, or flush the shared pool.
[Update]
I've checked with the users and actually this happens because a previous run of the same job is still running. I was told that the function actually has a semaphore but it seems that it isn't working well. A deeper look at the actual coding may be necessary. More on this later.
My favourite hints - I
Hinting a query should only be done as last resort. Unless the question is just to execute a query as fast as possible. Hinting in applications code may impact upon the usage of features specific to future Oracle versions.
Now that you've been warned, here it goes.
Join hints
/*+ use_nl(a b) */ - this forces a nested loop join, which is the most obvious fashion of joining two tables. It is actually a good choice, provided that the lookup table has an usable index for the join, and the driving dataset is small compared with the lookup data. A clear winner in most OLTP applications. a and b being the aliases of the tables to be joined.
/*+ use_hash(a b) */ - this one forces a hash join. An hash join is obtained computing an hash value on the join columns of both tables, and selection the records that match. The good news is that indexes aren't normally used when doing hash joins. It is a lousy performer on small sets of large tables, but yelds great times joining huge sets on huge tables. If you're on datawarehouse that's the join for you.
/*+ ordered */ - this forces Oracle to perform the join according to the order the tables are in the FROM clause.
Index hints
/*+ index(a) */ or /*+ index(a b) */ - in the first format, forces Oracle to use an index to access the table with 'a' alias - Oracle will hopefully choose the best index for the execution based upon the query's WHERE clause; in the second format, Oracle will use the 'b' index to access 'a' table.
/*+ index_ffs(a) */ or /*+ index_ffs(a b) */- a great choice for COUNT, provided all the information needed is on the index. This hint forces a full index scan on an index selected by Oracle in the first format, and on an index supplied by the user in the later. It doesn't actually read the table.
More on hints later.
Primary keys and partitioned tables
If you are planning to partition a table, assure that it has no primary keys. If you need some way of ensuring that no duplicate records are inserted, use a unique index instead.
Why? Because exchanging a table partition with a table will validate the primary key regardless of 'INCLUDING INDEXES WITHOUT VALIDATION' clause. To validate the primary key a full index scan will be performed. That isn't a problem if the table is small, but then who in his right mind will be partitioning a small table?
Uniform extents on your tablespaces
To easily obtain uniform extents on each tablespace use locally managed tablespace 8i/9i feature and specify uniform extent size.
Example - to create a tablespace where all extents are 1 megabyte size, try the following:
CREATE TABLESPACE TEST DATAFILE '/oracle/data/test_000.dbf' SIZE 1001M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
There are several advantages in using uniform extent size tablespaces, namely minimizing tablespace fragmentation and space wastage.
ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []
Oracle Version: 9.2.0.5.0
O.S. : HP/UX 11i
Machine: HP9000
Metalink says:
DESCRIPTION:
An error occurred when getting a new extent while adding to a heap.
The heap descriptor is uninitialized.
ARGUMENTS:
Arg [a] Always 0
FUNCTIONALITY:
HEAP MANAGER
IMPACT:
PROCESS FAILURE
MEMORY CORRUPTION
NON DATA CORRUPTIVE - No underlying data corruption.
SUGGESTIONS:
These errors can occur after the actual Oracle in-memory corruption
has taken place.
Hence the processes that report the error may not be the ones that
caused it.
Check for third party software being used to access the database.
This error can be reported when there are compatibility issues
between third party software and the database.
Check for other messages preceeding this message.
Check if the problem is reproducable.
Run memory diagnostics on all system hardware.
If the Known Issues section below does not help in terms of identifying
a solution, please submit the trace files and alert.log to Oracle
Support Services for further analysis.
Additional references to bug 3066549.
Looking at the trace file:
ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []Current SQL statement for this session:BEGIN DBMS_OUTPUT.ENABLE(1000000); END;
.......
O/S info: user: xxxxxxxx, term: XXXXXXX, ospid: 2880:2460, machine: XXXXXXX\XXXXXXX program: C:\Documents and Settings\All Users\Start Menu\P application name: C:\Documents and Settings\All Users\Start Menu\P, hash value=0 last wait for 'SQL*Net message from client' blocking sess=0x0 seq=23 wait_time=2161 driver id=54435000, #bytes=1, =0 temporary object counter: 0
.......
Open Cursors:
BEGIN DBMS_OUTPUT.ENABLE(1000000); END;
SELECT SYS_CONTEXT(:B2,:B1) FROM SYS.DUAL
SELECT UPPER(NVL(V.OSUSER,'ALL')), UPPER(NVL(V.USERNAME,V.SCHEMANAME)), UPPER(NVL(SUBSTR(V.PROGRAM,1,48),'ALL')) FROM V$SESSIONV WHERE V.AUDSID = :B1 AND ROWNUM < 2
SELECT COUNT(*), MAX(SQL_TRACE) FROM USER_BIN B WHERE (B.OSUSER = :B3 OR B.OSUSER = 'ALL') AND (B.USERNAME = :B2 OR B.USERNAME = 'ALL') AND (:B1 LIKE ('%' B.PROGRAM '%') OR B.PROGRAM = 'ALL') AND ROWNUM < 2
INSERT INTO USER_LOG SELECT V.OSUSER, V.USERNAME, V.MACHINE, SYS_CONTEXT('USERENV','SESSIONID'), V.PROGRAM, SYSDATE, :B2 FROM V$SESSION V WHERE V.AUDSID = :B1
I'll keep looking into this. Judging by the code on trace file it seems to be some kind of error on event based trigger we have on this database to save object structure before change or deletion. Anyway, Oracle's explanation over errors when selecting from a view doesn't seem to apply. More on this later.
