Monitor Dynamic SGA components

Use this SQL script when logged in as SYSDBA to see what happens to the SGA components over time, how they grow and shrink

I found it very useful monitoring the SHARED_POOL usage over time.

SELECT COMPONENT ' - ' OPER_TYPE COMPONENT_STATUS,INITIAL_SIZE,TARGET_SIZE,FINAL_SIZE,STATUS,TO_CHAR(START_TIME, ‘dd-mon-yyyy hh24:mi’) START_TIME,END_TIMEFROM V$SGA_RESIZE_OPSORDER BY END_TIME;

SPFILE or PFILE startup

I have found this very useful SQL to identify what type of initialization parameter file an oracle instance has started with.



Found this on Thomas Kyte’s site at http://asktom.oracle.com most useful information as ever!

Run the following as SYSDBA.

SELECT DECODE(COUNT(*), 1, 'spfile', 'pfile')
FROM V$SPPARAMETER
WHERE ROWNUM = 1
AND ISSPECIFIED = ‘TRUE’

Install Oracle 10g R2 on Ubuntu (Breezy Badger and Dapper Drake)

I have found http://www.dizwel.com website very helpful when I was trying to install Oracle 10g R2 on Ubuntu.

The instructions are clear end well tested.I have used it couple of time to install Oracle on Ubuntu.

I strongly recommend it.Step by step installation guide of Oracle 10g R2 on Ubuntu (Breezy Badger and Dapper Drake)

Great website :http://www.dizwell.com/prod/node/52?page=0%2C0

Search anywhere in a string with INSTR function

Type your summary here


Suppose you have a table X with the following data and you want to search for a particular string in its columns.

SQL>SELECT * FROM X;registration_origin

-------------------
aaa.bbbbb
ccccccc.dd
f.eeeeeeee

You can change this data using

SQL> SELECT REPLACE(SUBSTR(REGISTRATION_ORIGIN, 0, INSTR(REGISTRATION_ORIGIN, '.')), '.', ' ') FROM X
/

to this

registration_origin
-------------------
aaa
ccccccc
f

Shared_pool latching

Trying to get answer for this from Gurus like Thomas Kyte and Jonathan Lewis.
SHARED POOL LATCHING in LIBRARY CACHE. Why is it bad?


Shared pool is where the SQL code and the data dictionary lives. Isn’t this structure ‘mostly’ READ_ONLY? Why latch/lock? Why do you care to latch? What will be corrupted in the library cache? Why does Oracle have to “protect” it, as you say above?

To stop latching/parsing and the bad scalability which comes with it, Tom Kyte says we must use bind variables, yes I agree, but why latch the shared pool? What will that other “nasty” user do to your SQL statement in the shared pool apart from sharing the SQL with you.

Shared Pool is not like the Data Buffer where data lives . In the Data Buffer I understand the reason latching/locking (shared locks, exclusive locks during updates, selects) is bad, and why it inhibits concurrency, ‘Consistent Gets’ ACID theory of transactions, locks, transactions trying to concurrently modify same data, phantom reads and dirty reads etc, they all come to mind, fine.
But why in SHARED POOL, it is just SQL text, what is going to be corrupt? Why does it have to be concurrent and use latches (serialization devices) ?

Wiating Response from :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::
P11_QUESTION_ID:1513005503967#58524800346079273

http://jonathanlewis.wordpress.com/2007/01/05/bind-variables/