Hi
I'd certainly look at the sql being run, examine the explain plan and in
particular SQL_TRACE, TIMED_STATISTICS, and TKPROF, these will really
highlight issues.
see following for autotrace which can generate explain plan etc.
http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/autotrac.htm
then the following can really help
SQL>alter session set sql_trace=true;
run sql
SQL>alter session set sql_trace=false ( this si very important as it
closes the trace session )
SQL>show parameters show parameters user_dump_dest
.... location of output from sql trace
go to user dump dest
you wills ee somethign like
${ORACLE_SID}_ora_6919.trc
tkprof ${ORACLE_SID}_ora_6919.trc 6919.trc explain=scott/tiger sys=no
ie explain=schema owner and passwrd, if unsure just run
tkprof ${ORACLE_SID}_ora_6919.trc 6919.trc
this can provide some very informative info, ie unseen ora errors from
user functions and so on.
read the following to get an idea of how to get at the problematic SQL
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_1016.htm#i26072
I had an interesting issue the other day, where a tablespace was nearing
100% full on a test DB that isn't properly monitored, and queries stated
to run really really slow.
Enda
On 09/12/2010 20:22, Jabbar wrote:
Hello Tony,
If the hardware hasn't changed I'd look at the workload on the database
server. If the customer is taking regular statspack snapshots they might
be able to see whats causing the extra activity. They can use AWR or the
diagnostic pack, if they are licensed, to see the offending SQL or
PL/SQL or any hot objects.
However if you want to tune at the ZFS level then the following has some
advice for ZFS and databases
http://www.solarisinternals.com/wiki/index.php/ZFS_for_Databases.
On 9 December 2010 15:48, Tony Marshall <tony.marsh...@oracle.com
<mailto:tony.marsh...@oracle.com>> wrote:
Hi All,
Is there a way to tune the zfs prefetch on a per pool basis? I have
a customer that is seeing slow performance on a pool the contains
multiple tablespaces from an Oracle database, looking at the LUNs
associated to that pool they are constantly at 80% - 100% busy.
Looking at the output from arcstat for the miss % on data, prefetch
and metadata we are getting around 5 - 10 % on data, 50 - 70 % on
prefetch and 0% on metadata. I am thinking that the majority of the
prefetch misses are due to the tablespace data files.
The configuration of the system is as follows
Sun Fire X4600 M2 8 x 2.3 GHz Quad Core Processor, 256GB Memory
Solaris 10 Update 7
ZFS Arc cache max set to 85GB
4 Zpools configured from a 6540 Storage array
* apps - single LUN (raid 5) recordsize set to 128k, from the
array, pool contains binaries and application files
* backup - 8 LUNs (varying sizes all from a 6180 array with SATA
disks) used for storing oracle dumps
* data - 5 LUNs (Raid 10 6 physical drives) recordsize set to
8k, used for Oracle data files
* logs - single LUN (raid 10 from 6 physical drives) recordsize
set to 128k, used for Oracle redo log files, temp db, undo db
and control files.
18 Solaris 10 zones, of which 12 of these are oracle zones sharing
the data and logs pools.
I think that the prefetch will be useful on the apps and backup
pools, however I think that on the data and logs pools this could be
causing issues with the amount of IO that is being caused by the
prefetch and the amount that it is missing in the arcstats could be
the reason why the devices are at 100% busy. Is there a way to turn
the prefetch off for just a single pool? Also is this something that
can be done online or will it require a reboot to put into effect.
Thanks in advance for your assistance in this matter.
Regards
Tony
--
Oracle <http://www.oracle.com>
Tony Marshall | Technical Architect
Phone: +44 118 924 9516 <tel:+44%20118%20924%209516> | | | Mobile:
+44 7765 898570 <tel:+44%207765%20898570>
Oracle Remote Operations Management
United Kingdom
ORACLE Corporation UK Ltd is a company incorporated in England &
Wales | Company Reg. No. 1782505 | Reg. office: Oracle Parkway,
Thames Valley Park, Reading RG6 1RA
Green Oracle <http://www.oracle.com/commitment> Oracle is committed
to developing practices and products that help protect the environment
_______________________________________________
zfs-discuss mailing list
zfs-discuss@opensolaris.org <mailto:zfs-discuss@opensolaris.org>
http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
--
Thanks
A Jabbar Azam
_______________________________________________
zfs-discuss mailing list
zfs-discuss@opensolaris.org
http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
_______________________________________________
zfs-discuss mailing list
zfs-discuss@opensolaris.org
http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
