Checking Oracle server performance during a scheduled job

Hi gmollineau,

Performance issues could be on the server, network, or client.  If I was having to run this down, I'd start by looking at a couple of key stats on the server side, and branching out from there.

Start by checking the explain plan(s).  Does anything suggest that the query/queries should run a long time?

If not, check the cache hit ratio.  Run the script below and spool the output to a host file, or insert the results into a new table.

select 1-(PHY.VALUE - LOB.VALUE - DIR.VALUE)/SES.VALUE "CACHE HIT RATIO"
FROM V$SYSSTAT SES, V$SYSSTAT LOB, V$SYSSTAT DIR, V$SYSSTAT PHY
WHERE SES.NAME ='session logical reads'
and DIR.NAME ='physical reads direct'
and LOB.NAME ='physical reads direct (lob)'
and PHY.NAME ='physical reads'
/ 
SELECT SUM(PINS-RELOADS)/SUM(PINS)*100 "Library Cache Hit Ratio"
FROM V$LIBRARYCACHE
/
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "MISSES", SUM(RELOADS)/SUM(PINS)
"RELOAD RATIO"
FROM V$LIBRARYCACHE
/

Select allOpen in new window

The output (if spooled) will look something like this:

CACHE HIT RATIO                                                                 
---------------                                                                 
     .871069536                                                                 

1 row selected.


Library Cache Hit Ratio                                                         
-----------------------                                                         
             99.9561845                                                         

1 row selected.


EXECUTIONS     MISSES RELOAD RATIO                                              
---------- ---------- ------------                                              
    278456        122    .00043813                                              

1 row selected.

Select allOpen in new window

If the cache hit ratio is under 90%, increase the DB_CACHE_SIZE.  (Be mindful of SGA_MAX_SIZE.)

The library cache hit ratio should be at least 99%.  If the reloads to pins ratio is more than 1%, increase the SHARE_POOL_SIZE.


If the numbers look good, you'll need to check other items.  (I suspect it's a handshake issue between the client and server, but it's easier to test and tune the server so I'd start there and perhaps rule that out.)

great thanks alot. I will try

hi,

is this running in powershell on the windows 2012 server?

No.  That's to run on the remote database.

ok, so I need to run this on the server that has the Oracle application,which is the remote database?

The Oracle database is the server side, the Oracle application is the client side.  You can run it from a client connection to the database, but the query will need to execute on the server.

performance ? > ITSS !
It's The Stupid Sql's !

Start a trace, and then tkprof the results
https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof

sort by buffer gets or elapsed time
and you'll know what query to tune

hi,

these are the results for the query.

any help would be appreciated.
thanks
oracleserver.docx

hi,

thanks. How can i test the server/server handshake? it is a SQL server to oracle server.

thanks

The handshake between SQL Server and Oracle should be relatively efficient.  Some older products (including old versions of Oracle Forms) transfer data in small segments and communicate back to the server with each block.  Newer products will just stream the data to the client and let the TCP/IP protocols deal with data management, making them much more efficient and faster.

Can you get to the query/queries being run?  An explain plan would be a good thing.

What the bollocks do you know with those numbers ?
They don't say anything

trace it and post the raw trace file or the tkproffed one

you can directly find out the network latency from a trace file