Oracle Point, Oracle Life.

You may ever tried Oracle SQL Developer on lower version than v1.2. And you may agree that that’s a real tool for Oracle developers.

With Oracle SQL Developer, you can:

1. view database objects strcuture
2. run sql statement
3. write PL/SQL program units
and so on.

With Latest verison of Oracle SQL Developer (V1.2), you now can run reports in Oracle SQL Developer, like:
1. list invalid objects not only for specific schema but for instance level
2. list object count by type
3. view object distribution by plotting
4. search source code from PL/SQL program units

The above features are helpful for developers while they need to gather report on specific topics. Besides these, new version of Oracle SQL Developer can accommodate user-defined report. For instance, you may can create new report to present system level statistics such as the script listed at the bottom.

Yes, go ahead. And you can easily check the results by using graphical tool. The most convenient part is that you can easily switch from different connections. That means, you can view multi-databases’ statistics by executing one user-defined report. Besides of this, there are other avaialble enhancement on Oracle SQL Developer to offer some DBA features since Oracle SQL Developer v1.1. such as:

1. list database parameters
2. show info. about locks
3. present sessions (active, inactive, sessions by username, system sessions, &etc)
4. show storage information about datafilles, tablespaces and so on
5. present top SQL (by buffer gets, by CPU, and more)
6. measure users by profile, by default tablespace.

Actually, you can appy the plug-in esdev to lower version than v1.1. And, it seems Oracle SQL Developer v1.1 and v1.2 integrate esdev and perform some DBA jobs. With esdev, Oracle SQL Developer may has some DBA features,

Now, Oracle SQL Developer is a much enhanced FREE Tool, not only for developers, but for DBAs. And, it’s FREE.

Please see Oracle SQL Developer at http://www.oracle.com/technology/software/products/sql/index.html

Appendix (Oracle Database Overall Checking for Daily Use)

set pagesize 1000
set linesize 1000

spool sys_run.txt

– response_time_overall.sql
– edited by R. Wang
– To get a report on CPU usage, disk I/O, resource waits, and routine latencies
– Run with sysdba role
– 03/15/06

select
substr(n_major, 3) major,
substr(n_minor, 3) minor,
wait_event,
round(time/100) seconds,
substr(to_char(100 * ratio_to_report(time) over (), ‘99.00′), 2) || ‘%’ pct
from
(
select /*+ ordered use_hash(b) */
‘1 CPU time’ n_major,
decode(t.ksusdnam,
‘redo size’, ‘2 reloads’,
‘parse time cpu’, ‘1 parsing’,
‘3 execution’
) n_minor,
‘n/a’ wait_event,
decode(t.ksusdnam,
‘redo size’, nvl(r.time, 0),
‘parse time cpu’, t.ksusgstv - nvl(b.time, 0),
t.ksusgstv - nvl(b.time, 0) - nvl(r.time, 0)
) time
from
sys.x$ksusgsta t,
(
select /*+ ordered use_nl(s) */– star query: few rows from d and b
s.ksusestn,– statistic#
sum(s.ksusestv) time– time used by backgrounds
from
sys.x$ksusd d,– statname
sys.x$ksuse b,– session
sys.x$ksbdp p,– background process
sys.x$ksusesta s– sesstat
where
d.ksusdnam in (
‘parse time cpu’,
‘CPU used when call started’) and
b.ksspaown = p.ksbdppro and
s.ksusestn = d.indx and
s.indx = b.indx
group by
s.ksusestn
) b,
(
select /*+ no_merge */
ksusgstv *– parse cpu time *
kglstrld /– SQL AREA reloads /
greatest(1, kglstget - kglstght)– SQL AREA misses
time
from
sys.x$kglst k,
sys.x$ksusgsta g
where
k.indx = 0 and
g.ksusdnam = ‘parse time cpu’
) r
where
t.ksusdnam in (
‘redo size’,– arbitrary: to get a row to replace
‘parse time cpu’,– with the ‘reload cpu time’
‘CPU used when call started’) and
b.ksusestn (+) = t.indx
union all
select
decode(n_minor,
‘1 normal I/O’,'2 disk I/O’,
‘2 full scans’,'2 disk I/O’,
‘3 direct I/O’,'2 disk I/O’,
‘4 BFILE reads’,'2 disk I/O’,
‘5 other I/O’,'2 disk I/O’,
‘1 DBWn writes’,'3 waits’,
‘2 LGWR writes’,'3 waits’,
‘3 ARCn writes’,'3 waits’,
‘4 enqueue locks’,'3 waits’,
‘5 PCM locks’,'3 waits’,
‘6 other locks’,'3 waits’,
‘1 commits’,'4 latency’,
‘2 network’,'4 latency’,
‘3 file ops’,'4 latency’,
‘4 process ctl’,'4 latency’,
‘5 global locks’,'4 latency’,
‘6 misc’,'4 latency’
) n_major,
n_minor,
wait_event,
time
from
(
select /*+ ordered use_hash(b) use_nl(d) */
decode(
d.kslednam,
– disk I/O
‘db file sequential read’,'1 normal I/O’,
‘db file scattered read’,'2 full scans’,
‘BFILE read’,'4 BFILE reads’,
‘KOLF: Register LFI read’,'4 BFILE reads’,
‘log file sequential read’,'5 other I/O’,
‘log file single write’,'5 other I/O’,
– resource waits
‘checkpoint completed’,'1 DBWn writes’,
‘free buffer waits’,'1 DBWn writes’,
‘write complete waits’,'1 DBWn writes’,
‘local write wait’,'1 DBWn writes’,
‘log file switch (checkpoint incomplete)’,'1 DBWn writes’,
‘rdbms ipc reply’,'1 DBWn writes’,
‘log file switch (archiving needed)’,'3 ARCn writes’,
‘enqueue’,'4 enqueue locks’,
‘buffer busy due to global cache’,'5 PCM locks’,
‘global cache cr request’,'5 PCM locks’,
‘global cache lock cleanup’,'5 PCM locks’,
‘global cache lock null to s’,'5 PCM locks’,
‘global cache lock null to x’,'5 PCM locks’,
‘global cache lock s to x’,'5 PCM locks’,
‘lock element cleanup’,'5 PCM locks’,
‘checkpoint range buffer not saved’,'6 other locks’,
‘dupl. cluster key’,'6 other locks’,
‘PX Deq Credit: free buffer’,'6 other locks’,
‘PX Deq Credit: need buffer’,'6 other locks’,
‘PX Deq Credit: send blkd’,'6 other locks’,
‘PX qref latch’,'6 other locks’,
‘Wait for credit - free buffer’,'6 other locks’,
‘Wait for credit - need buffer to send’,'6 other locks’,
‘Wait for credit - send blocked’,'6 other locks’,
‘global cache freelist wait’,'6 other locks’,
‘global cache lock busy’,'6 other locks’,
‘index block split’,'6 other locks’,
‘lock element waits’,'6 other locks’,
‘parallel query qref latch’,'6 other locks’,
‘pipe put’,'6 other locks’,
‘rdbms ipc message block’,'6 other locks’,
‘row cache lock’,'6 other locks’,
’sort segment request’,'6 other locks’,
‘transaction’,'6 other locks’,
‘unbound tx’,'6 other locks’,
– routine waits
‘log file sync’,'1 commits’,
‘name-service call wait’,'2 network’,
‘Test if message present’,'4 process ctl’,
‘process startup’,'4 process ctl’,
‘read SCN lock’,'5 global locks’,
decode(substr(d.kslednam, 1, instr(d.kslednam, ‘ ‘)),
– disk I/O
‘direct ‘,’3 direct I/O’,
‘control ‘,’5 other I/O’,
‘db ‘,’5 other I/O’,
– resource waits
‘log ‘,’2 LGWR writes’,
‘buffer ‘,’6 other locks’,
‘free ‘,’6 other locks’,
‘latch ‘,’6 other locks’,
‘library ‘,’6 other locks’,
‘undo ‘,’6 other locks’,
– routine waits
‘SQL*Net ‘,’2 network’,
‘BFILE ‘,’3 file ops’,
‘KOLF: ‘,’3 file ops’,
‘file ‘,’3 file ops’,
‘KXFQ: ‘,’4 process ctl’,
‘KXFX: ‘,’4 process ctl’,
‘PX ‘,’4 process ctl’,
‘Wait ‘,’4 process ctl’,
‘inactive ‘,’4 process ctl’,
‘multiple ‘,’4 process ctl’,
‘parallel ‘,’4 process ctl’,
‘DFS ‘,’5 global locks’,
‘batched ‘,’5 global locks’,
‘on-going ‘,’5 global locks’,
‘global ‘,’5 global locks’,
‘wait ‘,’5 global locks’,
‘writes ‘,’5 global locks’,
‘6 misc’
)
) n_minor,
d.kslednam wait_event,– event name
i.kslestim - nvl(b.time, 0) time– non-background time
from
sys.x$kslei i,– system events
(
select /*+ ordered use_hash(e) */– no fixed index on e
e.kslesenm,– event number
sum(e.kslestim) time– time waited by backgrounds
from
sys.x$ksuse s,– sessions
sys.x$ksbdp b,– backgrounds
sys.x$ksles e– session events
where
s.ksspaown = b.ksbdppro and– background session
e.kslessid = s.indx
group by
e.kslesenm
having
sum(e.kslestim) > 0
) b,
sys.x$ksled d
where
i.kslestim > 0 and
b.kslesenm (+) = i.indx and
nvl(b.time, 0) < i.kslestim and
d.indx = i.indx and
d.kslednam not in (
'Null event',
'KXFQ: Dequeue Range Keys - Slave',
'KXFQ: Dequeuing samples',
'KXFQ: kxfqdeq - dequeue from specific qref',
'KXFQ: kxfqdeq - normal deqeue',
'KXFX: Execution Message Dequeue - Slave',
'KXFX: Parse Reply Dequeue - Query Coord',
'KXFX: Reply Message Dequeue - Query Coord',
'PAR RECOV : Dequeue msg - Slave',
'PAR RECOV : Wait for reply - Query Coord',
'Parallel Query Idle Wait - Slaves',
'PL/SQL lock timer',
'PX Deq: Execute Reply',
'PX Deq: Execution Msg',
'PX Deq: Index Merge Execute',
'PX Deq: Index Merge Reply',
'PX Deq: Par Recov Change Vector',
'PX Deq: Par Recov Execute',
'PX Deq: Par Recov Reply',
'PX Deq: Parse Reply',
'PX Deq: Table Q Get Keys',
'PX Deq: Table Q Normal',
'PX Deq: Table Q Sample',
'PX Deq: Table Q qref',
'PX Deq: Txn Recovery Reply',
'PX Deq: Txn Recovery Start',
'PX Deque wait',
'PX Idle Wait',
'Replication Dequeue',
'Replication Dequeue ',
'SQL*Net message from client',
'SQL*Net message from dblink',
'debugger command',
'dispatcher timer',
'parallel query dequeue wait',
'pipe get',
'queue messages',
'rdbms ipc message',
'secondary event',
'single-task message',
'slave wait',
'virtual circuit status'
) and
d.kslednam not like 'resmgr:%'
)
)
order by
n_major,
n_minor,
time desc
/

spool out

exit

Popularity: 6% [?]

Popularity: 2% [?]