Home / user-12854

b.roth's questions

Martin Hope
b.roth
Asked: 2009-11-13 10:07:08 +0800 CST
  • 2

My application has a SQL that takes ~ 30 min to run in the Oracle server in production. It takes about the same in a test Oracle server.

For some reason, in another Oracle server, it runs much faster: only 5 min or so!

These timings are really for the SQL only (no application processing overhead). I got them from Oracle Enterprise Manager. Moreover, they are consistent, i.e. if you rerun the SQL again, you get about the same timings.

Hardware, Oracle version (10g), Data are the same in these three servers. Even the SQL execution plans are exactly the same.

What makes the SQL run so much faster in that test environment?

The SQL is a MERGE:

MERGE /*+ USE_NL(DORMANT_POINTS) */
INTO MKT_CURVE_POINT DORMANT_POINTS
USING (SELECT
// big select   
) ACTIVE_POINTS
    ON (
// ..
)
WHEN MATCHED THEN
UPDATE
SET DORMANT_POINTS.ACTIVE_PARENT_PRICE = ACTIVE_POINTS.ACTIVE_PARENT_PRICE
WHERE DORMANT_POINTS.ACTIVE_PARENT_PRICE <>
ACTIVE_POINTS.ACTIVE_PARENT_PRICE;

I suspect that this is something about caching. I noticed a high number of buffer gets as compared with physical reads in the server that runs the SQL fast. That ratio is lower in the server that runs it slowly.

What can explain this huge performance difference?

performance
Martin Hope
b.roth
Asked: 2009-11-11 02:56:51 +0800 CST
  • 2

In Oracle Enterprise Manager, in "Performance > Top Activity", you can click a SQL that is being executed and then go to the Plan tab to see SQL execution plan.

For the execution plan, the Enterprise Manager shows a table with the execution plan steps. That table has the following columns:

Operation   Object  Object Type Order   Rows    Size (KB)   Cost    Time (sec)  CPU Cost    I/O Cost

I'd like to understand the meaning of each one of these columns, but I did not find any documentation on this.

Can you recommend me any document or explain the meaning of the columns?

oracle
Martin Hope
b.roth
Asked: 2009-07-15 08:24:59 +0800 CST
  • 2

The tablespace in Oracle 10g is almost 100% used.

Size (MB) = 571,768.0 Used (MB) = 571,534.0

I just deleted (and committed) thousands of records in a table that belongs to a schema associated with that tablespace. Surprisingly, no space was freed up according to the Tablespaces page on Enterprise Manager.

Question: is there anything that I need to do to force Oracle to release the space corresponding to the deleted records?

oracle oracle10g