Oracle Logbook

March 28, 2011

What objects should go to recycle

Filed under: Uncategorized — Helio Dias @ 8:03 pm

Basically the objects with very low TCH
Col object_name format a30
Col object_type format a20
SELECT o.owner, object_name, object_type, COUNT(1) buffers
FROM SYS.x$bh, dba_objects o
WHERE (tch = 1 OR (tch = 0 AND lru_flag < 8))
AND obj = o. object_id
AND o.owner not in ('SYSTEM','SYS')
GROUP BY o.owner, object_name, object_type
ORDER BY buffers;
ALTER TABLE

STORAGE (BUFFER_POOL RECYCLE)

March 25, 2011

Bitmap or Btree

Filed under: Uncategorized — Helio Dias @ 3:35 pm

Today I faced with this question at: http://forums.oracle.com/forums/thread.jspa?messageID=9469239#9469239

========================================= The Question ================

I have a table having 5 million records.

If I want to build an index on one column,which index is better(bitmap or btree normal)?

The column had approx 6% different values for the total records in the table.

============================

My answer was:

With 5%, I’d go through btree,
bitmap have many detail that you should be aware.

March 22, 2011

Exists and IN which is better?

Filed under: Uncategorized — Helio Dias @ 5:02 pm

Today I saw this question at: http://forums.oracle.com/forums/thread.jspa?messageID=9459258&#9459258

My answer was:

There are two major difference between IN and EXISTS

The first difference is about what table will be the driving table

The second and most important difference is that EXISTS is a bit more flexible, because even when you overuse EXISTS, most of the time , Oracle is smart enough to change it to a IN, So if you don´t understand very well the concept about driving table and execution path, use EXISTS.

March 18, 2011

An Oracle user and scheme are the same?

Filed under: Uncategorized — Helio Dias @ 4:38 pm

Almost , but not exactly.

An Oracle user is the USERNAME, the SCHEMA is the set of objects associated with one OWNER

So when a USER become a SCHEMA? when he get the privileges to create objects;

Another thought, after login you are the user that you logon, but if you do an Alter session set current_schema=another_schema; 

Then you will be one user, but another schema.

Of course , to makes things easier to understand , you can think that they are the same.

One important thing, Inside Oracle dictionary tables just exists the USER

March 16, 2011

Using DBMS_JOB

Filed under: Uncategorized — Helio Dias @ 8:30 pm

If you what to run everyday at 1AM

BEGIN

DBMS_JOB.CHANGE(job=>3,what=>null,next_date=>TRUNC(SYSDATE+1)+1/24,interval=>’TRUNC(SYSDATE+1)+1/24′);

END;

/

Who to use EXISTS in an update

Filed under: Uncategorized — Helio Dias @ 6:16 pm

UPDATE table_1 t1

SET A.column_1 = (SELECT t2.column_2-t1.column_1 FROM table_2 t2

WHERE t1.id=t2.id)

WHERE EXISTS (SELECT  null FROM table_2 t2

WHERE t1.id=t2.id);

March 15, 2011

Searching for FULL table scan

Filed under: Uncategorized — Helio Dias @ 6:39 pm

Today I saw this question at OTN:

http://forums.oracle.com/forums/thread.jspa?messageID=9441473&#9441473

“Can someone provide me asql query which will show me the actual query which is going for FTS including how many time that FTS query executes so far.”

=========== My answer  =========

If your goal is to search possible queries with problems, you should try this:

select * from (
select sql_id,buffer_gets,executions,rows_processed from v$sqlarea order by 2 desc)
where rownu<30;

Then you should evaluate if make sense 10 rows query, executed just 5 times, consume 100,000 buffer_gets.
or
why some queries are being executed 1 billion times.

the botton line is , search weird things, and improve it.

You maybe even find out a huge consuming buffer_gets query that need a index,
Just keep in mind, FTS is not synonym of problem.
And even when it´s actually a problem, searching one by one , it´s not for sure the best aproach.

Avoiding logon trigger

Filed under: Uncategorized — Helio Dias @ 3:34 pm

Sometimes people can´t access the database any longer due to a system event logon trigger.

If this happen the solution is change the hidden parameter _system_trig_enabled to false.

March 11, 2011

Difference in gathering statistics

Filed under: Uncategorized — Helio Dias @ 8:29 pm

The major difference between DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC and DBMS_STATS.GATHER_DATABASE_STATS is that GATHER_DATABASE_STATS_JOB_PROC is smart enough to start the analyze with most in need objects.

Wait event : Read by other session

Filed under: Uncategorized — Helio Dias @ 4:02 pm

In case the contention is on indexes, I would recreate the primary key using reverse index.
Before do this , you should be aware the drawback regarding range scan.

This was my answer at
http://forums.oracle.com/forums/post!reply.jspa?messageID=9432150

Next Page »
March 2011
M T W T F S S
 123456
78910111213
14151617181920
21222324252627
28293031  

  • Recent Posts

  • Recent Comments

    wordman512 on Grouping with Rollup
    heliodias on AWR it´s not free and requires…
    Venkata on AWR it´s not free and requires…
    jsixface on Best way to delete millions ro…
    heliodias on What objects should go to…

  • _

  • Pages

  • Archives

  • Blog Stats

    • 129,788 hits

    • Blog at WordPress.com.