Oracle: quick notes on ojdbc6.jar

(If not specified, package is "oracle.jdbc.driver". Our current driver type is thin.)
(Need more details? JD-GUI and ojdbc6_g.jar is all what you need.)

1. oracle.jdbc.OracleDriver --> OracleDriver
1. connect()
1. oci: T2CDriverExtension (driverextension id: 2) [T2 means "type 2", aka using native-API]
2. thin: T4CDriverExtension (driverextension id: 0) [T4 means "type 4", aka pure java implementation]
2. Instatiates a T4CDriverExtension
1. getConnection() --> T4CConnection
3. Casts back the T4CConnection to PhysicalConnection, then returns.
2. PhysicalConnection.createStatement(CURSOR_TYPE, CONCURRENCY_TYPE, IGNORED)
1. return new OracleStatementWrapper( T4CDriverExtension.allocateStatement() --> T4CStatement)
3. OracleStatementWrapper.executeQuery() [To have a ResultSet]
1. OracleStatement.executeQuery()
1. OracleStatement.doExecuteWithTimeout()
1. T4CStatement.executeForRows()
1. T4CStatement.doOall8(PARSE, EXEC, FETCH, DESCR, DEF, 1stRow)
1. T4C8Oall.doOALL(PARSE, EXEC, FETCH, DESCR, DEF, kind, cursorid, sqlObject.getSqlBytes() [teh query], ...)
1. ...
2. return new OracleResultSetImpl(this.[pysical]connection, this[OracleStatement]);
2. ret
4. [while] OracleResultSetImpl.next()
1. OracleResultSetImpl.close_or_fetch_from_next()
1. T4CStatement.fetch()
1. T4CStatement.doOall8(f,f,t,f,f)
1. T4C8Oall.doOALL()
1. T4CTTIfun.doRPC() [TTI: Two-Task Interface]
1. marshalFunHeader()
1. T4CTTIMsg.marshalTTCcode()
1. T4CMAREngine.marshalUB1()
1. [T4CSocketOutputStreamWrapper.write(aByte)]
2. marshal() -- abstract: TODO
3. receive()
1. T4C8Oall.readDCB()
2. T4C8TTILob.readLOBD()
5. OracleResultSetImpl.getLong(i)
1. OracleStatement,accessors[i-1]
1. LongAccessor
1. CharCommonAccessor.getLong(i-1)
1. Long.parseLong(CharCommonAccessor.getString(i-1))
1. cuts the string out of an array of bytes received as a LOB.

JIRA: Check workflow entry states are correct

Symptom: You see no transition buttons on the ticket, but when you edit it, they come back.
Cause: The workflow entry associated with the issues in screwed up. Its state is "created"(zero) or null instead of "activated"(one).
How to check:

SELECT PROJECT.PKEY || '-' || JIRAISSUE.ISSUENUM AS PKEY, JIRAISSUE.WORKFLOW_ID, OS_WFENTRY.STATE
FROM JIRAISSUE
INNER JOIN OS_WFENTRY ON JIRAISSUE.WORKFLOW_ID = OS_WFENTRY.ID
INNER JOIN PROJECT ON PROJECT.ID = JIRAISSUE.PROJECT
WHERE OS_WFENTRY.STATE IS NULL OR OS_WFENTRY.STATE = 0;

These should return all the issues you have the problem with.

How to fix it in Oracle:

UPDATE OS_WFENTRY SET STATE = 1
WHERE ID IN ( SELECT OS_WFENTRY.ID FROM OS_WFENTRY
INNER JOIN JIRAISSUE ON JIRAISSUE.WORKFLOW_ID = OS_WFENTRY.ID
WHERE OS_WFENTRY.STATE IS NULL OR OS_WFENTRY.STATE = 0);

There are other workflow state codes, but the official correction in the integrity checker is to set it to 1 (activated).

Oracle: JIRA: index for the pagination query

I found a select that executes with a cost over 16k, and I wondered, how could I speed this up.
(Sometimes it ran for 10.5 seconds!)

SELECT SQ_.ID FROM
  ( 
    SELECT ID FROM JIRAISSUE
    WHERE PROJECT IN(:1, :2)
    ORDER BY UPDATED DESC
  ) SQ_
WHERE ROWNUM <= 10;

It seems, only the IN-s contents are changing over the course, so we could fit an index for that. Lets see, what we have:

• ORDER BY UPDATED DESC - this will be the leading column in descending order,
• PROJECT - filter, this should be in,
• ID - as we projected this column, if we put this in the index, it can be an index-only query.

CREATE INDEX IX_JIRAISSUE_UPDATED_PROJ_ID ON JIRAISSUE (UPDATED DESC, PROJECT ASC, ID ASC);

Results in an index full scan and a count-with-stopkey. Yaay, the cost is 3 (three), in a table with ~2 million rows.

Oracle: scan types

• Full Table Scan - Highest cost. This happens, if:
• No index exists.
• There is an index, but
• Without columns that the query is using, or
• Aggregate function is used, AND index allows nulls, or
• Multi-column index, and you're not using the first column in it.
• Resultset expected to be so big, that it is cheaper to read through the table than use the index. (Fewer large IO calls are cheaper than lot of tiny ones.)
• The table is too small. (Smaller than DB_FILE_MULTIBLOCK_READ_COUNT)
• Table parallelism too high. (Small partitions are easier to scan - see previous item)
• Table Access by rowid - Getting rowids through an index.
• An index returns with a set of rowid-s.
• There are columns specified that are not in the index.
• Table Access Sample - When using block sampling.
• Table Access Inmemory - Since 12cR1, on tables using inmemory parameter.
• Index Unique Scan - unique means, at most one rowid exists for a given index entry, so the search can stop after the first match.
• Unique Index exists and can be used, and
• Equality operator being in place.
• Index Range Scan (Descending) - ordered scan of values, even for ranges.
• Index leading columns with <,>,= operators, and
• AND combination of those (at least for leading columns).
• Can be forced by using BETWEEN.
• Descending: Order By has Desc parameter in it.
• Index Full Scan - Uses index order for Order By.
• Referencing an indexed column,
• Order By present, in a notnull column,
• All columns in Order By are indexed,
• Order of columns in Order By match the order of the leading index columns.
• Index Fast Full Scan - Full index scan, not accessing the table.
•  All columns required are in the index(es).
• Index Skip Scan
• Leading column in a composite index is not used,
• Leading column has only a few distinct values,
• Non-leading, referenced column has many distinct values.
• Hash Join (Index Join Scan)
• Hash join of multiple indexes returns all the required columns (without table access),
• Table access is more expensive than an index join.
• Table Access Cluster (Cluster Scan)
• Using an indexed cluster.
• Needs a simple Cluster used by all the tables.
• Table Access Hash (Hash Scan)
• In a hash cluster, rows are grouped into the same block by their hash value.
• Needs a Hash Cluster used by all the tables.

Leading column is the first column in a composite index.

CREATE INDEX ix_thisisanindex ON table42(leadingcolumn, notleading, stillnotleading);

C: FizzBuzzWizz

#include <stdio.h>
int main(int argc, char *argv){
  int i;
  for(i=0; i < 101; i++) {
 switch((i%7 == 0) << 2 | (i%5 == 0) << 1 | (i%3 == 0)) {
 case 0: printf("%d\n", i); break;
 case 1: printf("Fizz\n"); break;
 case 2: printf("Buzz\n"); break;
 case 3: printf("FizzBuzz\n"); break;
 case 4: printf("Wizz\n"); break;
 case 5: printf("FizzWizz\n"); break;
 case 6: printf("BuzzWizz\n"); break;
 case 7: printf("FizzBuzzWizz\n"); break;
 }
  }
}

C: FizzBuzz

#include <stdio.h>
int main(int argc, char *argv) {
  int i;
  for(i=0; i < 101; i++) {
    switch((i%5 == 0) << 1 | (i%3 == 0)) {
      case 0: printf("%d\n", i); break;
      case 1: printf("Fizz\n"); break;
      case 2: printf("Buzz\n"); break;
      case 3: printf("FizzBuzz\n"); break;
    }
  }
  return 0;
}

Attenuation and Signal To Noise Ratio and ADSL and PPPoE things

Attenuation

Attenuation is the signal loss made by the transmitting medium. In short, the longer your cable is, the bigger the reduction of your signal is. It is sometimes referred as "noise", as it makes the signal weaker.

Lower is better (as the signal is stronger).

Signal to Noise Ratio (SNR)

This measures the external noise's rate compared to the signal's strength. It is like normalizing the nose to 0 dB, and comparing the signal strength. The decibel as a unit is just invented for this.

Higher is better (as the signal is more stronger than the noise).

Rates for copper ADSL

SNR Levels

• Over 20dB, it is good
• 10dB - 20dB, it is satisfactory
• 7dB - 10dB, it is bad, you'll disconnect often
• Below 7dB, don't bother to try, the noise is so strong, that interferes with the signal.

7dB means the ratio between the two power values is around 2.

If you see somewhere dBm, then it means, that the dB value was calculated from milliwatts.

Attenuation Levels

• Below 50dB, it is good
• Over 50dB, it is poor
• Over 60dB, the signal is not strong enough to detect it.

Some Terms

• "ATU-R" (ADSL Terminal Unit - Remote) - This is your ADSL Modem
• "ATU-C" (ADSL Termination Unit - Central Office) - This is the other endpoint of your DSL-enabled phone line

Why do ISP-s use PPPoE over DSL?

The answer is easy: They used to provide PPP over POTS, and PPPoE authentication can be integrated into their old, PPP authentication directories. They won't spend money if it isn't really necessary... Also, PPPoE can be encrypted and compressed before putting the packet onto the wire.

This means, if someone cuts your wire, can't use your DSL subscription, as they don't know your user/pass.

What your SOHO router does to initiate a PPPoE connection

Discovery

1. Client initiates a search for servers
1. Sends a PADI packet (PPPoE Active Discovery Initiation), which is an Ethernet broadcast packet, looking for an Access Concentrator (AC, later we'll talk to this).
2. Here, you can specify a Service Name, if multiple ISP-s use the same network, or your ISP is a dick, and requires additional configuration.
2. Server responds
1. PADO Offer returned (PPPoE Active Discovery Offer), which states the MAC of the AC
3. Client requests a session
1. Sends a PADR packet ( PPPoE active discovery request)
2. This in fact acknowledges that the session should be created (like in the TCP handshake)
4. Server confirms
1. PADS confirmation packet returned (PPPoE Active Discovery Session-confirmation)
2. This sends back the Session Identifier to use later.

After the Discovery is done, and the session is created, the PPP layer (above PPPoE) initiates the authentication, just like in the old dial-up times.

Problems

• "Err, my router says she sends the PADI, but no response..."
• It wouldn't try to connect, if it would not see a live medium, so there is a problem somewhere else. I had overheating issues XD
• To be sure, check the cable, and the connector at the end of the cable. If you have a cheap RJ45 plug crimped, it might be undersized (trollolo)...

An oldtimer - Error 1722 or 2755 from Wise installer

Here it is for future reference if anyone ends up here on their endeavor of searching to solve this encountering problem.

Internal Error 2755. 1632 setup fails

This problem occurs when the following conditions are all true:

You install on a Microsoft Windows NT 4.0 or Microsoft Windows 2000 (or Windows XP...).

-and-

The WinNT folder is located on an NTFS partition.

-and-

You have a folder called Installer under the WinNT folder, and you do not have full access permissions to the Installer folder.

To resolve this problem, change the permissions on the Installer folder to:

Everyone - Read (RX)
Administrators - FullControl
SYSTEM - FullControl

The installer folder is a protected folder. To change perms you will have to go to:
Windows Explorer - Tools - Folder Options - Select View.
Un-check: Hide protected Operating System Files.
The Installer folder is now visible under \Winnt

kmem russian roulette

sudo dd if=/dev/urandom of=/dev/kmem bs=1 count=1 seek=$RANDOM

I already forgot where and when I read about this first... The theory is that every weekend you shoot this on one of your servers, that should eventually fail. Now, if your HA/failover solution does not compensate inside your SLA, you can fix it outside business time.

About this game on bashorg :-)

Oracle: Get approximate size of storage used by indexes by a user

SELECT
  SEGMENT_NAME,
  ROUND(BYTES / 1048576)       AS MEGABYTES,
  ROUND(BYTES / 1073741824, 1) AS GIGABYTES
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'INDEX' AND OWNER = '_YOURUSER_COMES_HERE_'
ORDER BY 2 DESC, 1 ASC;