This example shows how to create a Solaris package - useful for packaging files and scripts for deployment with the custom module of JET.
This will use the Dtrace scripts installed in /opt/DTT as an example.
1. Create the prototype file:
# cd /opt/DTT
# find . -print | pkgproto > prototype
# echo "i pkginfo=./pkginfo" >> prototype
2. Create the pkginfo file:
# cat > pkginfo
NAME="Dtrace scripts"
DESC="Dtrace scripts used for debugging applications and kernel operations"
ARCH="sparc,x86"
VERSION="1.00"
CATEGORY="application"
BASEDIR="/opt/DTT"
CLASSES="none"
3. Create the package:
# pkgmk `pwd`
This creates the package in /var/spool/pkg
4. Transform the package:
# pkgtrans -s /var/spool/pkg /u01/software/dtrace/dttrace.pkg
We can then use the copy_custom_product utility to populate the jumpstart directory.
Friday, May 26, 2006
Thursday, May 25, 2006
Setting up Sun's JET for dhcp boot and install
This entry will describe setting up JET (using version 4.3.1) to enable sparc clients to boot using dhcp. Booting x86 clients will be tackled and posted in a later blog.
1: Download and install Jet.
The latest version of JET can be downloaded from:
http://www.sun.com/bigadmin/content/jet
and documentation/resources on:
http://jet.maui.co.uk
Install the jet toolkit into /opt/SUNWjet:
# pkgadd -d jet.pkg
This will create the directory structure in /opt/SUNWjet and also create a sample template in the Templates directory.
2: Setup Jumpstart.conf
Before loading the Solaris or Product media, we first need to set the jumpstart.conf file and create the necessary directories for the media and software we're going to install. Jumpstart.conf lives in /opt/SUNWjet/etc and sets the following variables:
JS_Default_Root_PW=boajrOmU7GFmY
JS_BUILD_DIR=/var/opt/sun/jet
JS_PKG_DIR=/u01/install/pkg
JS_PATCH_DIR=/u01/install/patch
JS_SOLARIS_DIR=/u01/install
JS_DHCP_VENDOR="SUNW.Ultra-5_10 SUNW.Ultra-30 SUNW.Sun-Fire-V440"
JS_CLIENT_MANAGEMENT="dhcp bootp"
#JS_CLIENT_MANAGEMENT="bootp"
In this particular setup, I'm going to install the Solaris 10 Sparc media into /u01/install directory, my packages into /u01/install/pkg and patches into /u01/install/patch. These directories need creating once jumpstart.conf has been updated:
# mkdir -p /u01/install/pkg
# mkdir -p /u01/install/patch
The other changes required are to setup the jumpstart server to accept DHCP boot requests from clients:
JS_CLIENT_MANAGEMENT="dhcp bootp"
and also the machine type which will be used to setup the dhcp client macro class. In this example, I'm booting a Sun Fire V440. Platform types can be found in /platform on any Solaris server:
JS_DHCP_VENDOR="SUNW.Ultra-5_10 SUNW.Ultra-30 SUNW.Sun-Fire-V440"
3: Use the Jet utilities to copy in the Solaris Media
Once setup, we can now copy in the Solaris media. In this example, I was using a copy of the Solaris 10 01/06 DVD. Use the copy_solaris_media in /opt/SUNWjet/bin to copy the media into the /u01/install directory. The subdirectory for the media is specified by passing the -d flag to the copy_solaris_media utility:
# ./copy_solaris_media -d 10-0106s /cdrom/sol_10_106_sparc/s0
Once this is complete, you can check whether the copy has been successful by running list_solaris_locations:
# ./list_solaris_locations
Version Location
------- --------
10 /u01/install/10-0106s
The version tag is used to identify which OS is installed on your client, defined by the base_config_ClientOS variable set in the client template, but more of that later.
4: Use the JET utilities to copy in the Solaris product media.
The JET package bundle comes with some base modules in addition to the base_config product (which installs the OS). These include:
custom - for customising client builds
jass - for hardening client builds
vts - virtual hardware tester
san - the Sun SAN packages
explo - for Sun Explorer
zones - for Solaris zones modules
flash - for building from flash archives
sds - for setting up SDS metadevices
In my custom client build, I will be installing vts and explo, so will use the JET utility, copy_product_media to copy in the vts and explo software:
# ./copy_product_media vts 6.1 /u01/software/vts sparc
Transferring sunwvtsts package instance
Transferring sunwvts package instance
Transferring sunwvtsmn package instance
Transferring sunwvtsr package instance
Packages copied.
# ./copy_product_media explo 5.4 /u01/software/explo sparc
Transferring sunwexplo package instance
Transferring sunwexplu package instance
Packges copied.
This will copy the product media into the:
${JS_PKG_DIR}/module/version/cpu
directory structure, which will be used by the post_install scripts as the source location of the product to be installed. If a version of the software is not supported, for example, Explorer 5.4 had not been added to the toolkit, the module's package.matrix file can be updated to reflect the new version.
5: Use the JET utilities to copy in custom product media.
For this client installation, I plan on installing a couple of Solaris freeware packages, lsof and sudo. These can be added to the custom product directory using the copy_custom_packages utility, an example of adding lsof is shown below.
# ./copy_custom_packages /u01/software/freeware/lsof-4.76-sol10-sparc-local sparc SMClsof
Transferring smclsof package instance
Packages copied
This package can then be installed on the client by entering the name of the package into one of the custom_package template variables.
6: Setup DHCP.
This jumpstart configuration will setup a local dhcp server on the jumpstart server. This is the easiest configuration and safe because the dhcp server will only respond to those clients which are configured for jumpstart.
The first task is to setup the local dhcp server, fortunately, this has been done for you in a script /opt/SUNWjet/Products/base_config/solaris/make_dhcp. The file first needs updating to include the network which you plan on jumpstarting on.
Set the network variables defined in the script and execute to create the basic dhcp server service with network macro.
NETWORK=192.168.16.0
NETMASK=255.255.240.0
ROUTER=192.168.16.1
# ./make_dhcp
Created DHCP configuration file.
Created dhcptab.
Added "Locale" macro to dhcptab.
Added server macro to dhcptab - s1prdmgt01.
DHCP server started.
Added network macro to dhcptab - 192.168.16.0.
Created network table.
The basic dhcp server can be checked by running:
# dhtadm -P
Name Type Value
==================================================
192.168.16.0 Macro :Subnet=255.255.240.0:Router=192.168.16.1:Broadcst=192.168.31.255:
s1prdmgt01 Macro :Include=Locale:Timeserv=192.168.17.72:LeaseTim=86400:LeaseNeg:
Locale Macro :UTCoffst=0:
and:
# svcs dhcp-server
STATE STIME FMRI
online 14:19:59 svc:/network/dhcp-server:default
7: Create the client template.
The first template I normally build just contains the base_config module (the default module) and installs the minimum Solaris metacluster. The initial requirement is just to get the boot net and install working, we can customize the build later. From my experience, it is much wiser to build up the Solaris image using each module, rather than attempting to do an initial build using the sample template which is created.
# ./make_template v440 base_config
Adding product configuration information for
+ base_config
Updating base_config template specifics
Client template created in /opt/SUNWjet/Templates
Filling in the template is self explanatory, I'll concentrate on the most important variables:
base_config_client_allocation="bootp dhcp grub"
defines how the client is going to boot on the network. We're going to configure our client to boot dhcp, so set this appropriately.
base_config_profile_usedisk=rootdisk.
defines which disk is used to install the image onto. The default is rootdisk. which normally detects the default disk, if it doesn't it can be defined here.
8: Make the client template.
The next step is to populate the client configuration directory using the make_client utility in /opt/SUNWjet/bin: The -f flag can be used to force an overwrite of a previous configuration. However, this will not remove the dhcp client configuration. To achieve this, use the remove_client utility.
At this stage, we're also populating the dhcp tables with our client information and vendor type which will be booting on the network using dhcp.
# remove_client v440
Gathering network information..
Client: 192.168.17.99 (192.168.16.0/255.255.240.0)
Server: 192.168.17.72 (192.168.16.0/255.255.240.0, SunOS)
Solaris: client_deallocate_dhcp.SunOS
# make_client -f v440
Gathering network information..
Client: 192.168.17.99 (192.168.16.0/255.255.240.0)
Server: 192.168.17.72 (192.168.16.0/255.255.240.0, SunOS)
Solaris: client_prevalidate
Clean up /etc/ethers
Solaris: client_build
Creating sysidcfg
WARNING: no base_config_sysidcfg_timeserver specified using JumpStart server
Creating profile
Adding base_config specifics to client configuration
Solaris: Configuring JumpStart boot for v440
Starting SMF services for JumpStart
Solaris: Configure DHCP build
Adding install client
Supporting VENDOR=SUNW.Ultra-5_10 SUNW.Ultra-30 SUNW.Sun-Fire-V440
Configuring v440 macro
Using local dhcp server
DHCP configuration complete
Running '/opt/SUNWjet/bin/check_client v440'
Client: 192.168.17.99 (192.168.16.0/255.255.240.0)
Server: 192.168.17.72 (192.168.16.0/255.255.240.0, SunOS)
Checking product base_config/solaris
--------------------------------------------------------------
Check of client v440
-> Passed....
8: Boot the client.
From the okay prompt, boot the client:
{0} ok boot net:dhcp - install
SC Alert: Host System has Reset
Probing system devices
Probing memory
ChassisSerialNumber 0546AL653A
Probing I/O buses
Sun Fire V440, No Keyboard
Copyright 1998-2004 Sun Microsystems, Inc. All rights reserved.
OpenBoot 4.16.4, 4096 MB memory installed, Serial #65405365.
Ethernet address 0:3:ba:e6:1:b5, Host ID: 83e601b5.
Rebooting with command: boot net:dhcp - install
Boot device: /pci@1c,600000/network@2:dhcp File and args: - install
/pci@1c,600000/network@2: 1000 Mbps full duplex link up
4000 /pci@1c,600000/network@2: 1000 Mbps full duplex link up
boot: lookup /dhcp/OS_10/Solaris_10/Tools/Boot failed
SunOS Release 5.10 Version Generic_118822-25 64-bit
Copyright 1983-2005 Sun Microsystems, Inc. All rights reserved.
Use is subject to license terms.
The client *should* boot, mount the Solaris image and install.
Troubleshooting:
1. When performing a boot net:dhcp - install, this error is returned. The dhcp service is not running and needs to be restarted.
TFTP server's IP address not known!
Evaluating:
Boot load failed
{1} ok
restart the dhcp-service:
# svcadm restart dhcp-server
2. If the install goes interactive and prompts for confirmation of the time, it may be because the time service on the jumpstart server has been disabled. This assumes of course you're using the jumpstart server as the time source!
Enable the time source by:
# svcadm enable time:stream
and if using tcp_wrappers check that clients are able to connect to the service by updating /etc/hosts.allow
1: Download and install Jet.
The latest version of JET can be downloaded from:
http://www.sun.com/bigadmin/content/jet
and documentation/resources on:
http://jet.maui.co.uk
Install the jet toolkit into /opt/SUNWjet:
# pkgadd -d jet.pkg
This will create the directory structure in /opt/SUNWjet and also create a sample template in the Templates directory.
2: Setup Jumpstart.conf
Before loading the Solaris or Product media, we first need to set the jumpstart.conf file and create the necessary directories for the media and software we're going to install. Jumpstart.conf lives in /opt/SUNWjet/etc and sets the following variables:
JS_Default_Root_PW=boajrOmU7GFmY
JS_BUILD_DIR=/var/opt/sun/jet
JS_PKG_DIR=/u01/install/pkg
JS_PATCH_DIR=/u01/install/patch
JS_SOLARIS_DIR=/u01/install
JS_DHCP_VENDOR="SUNW.Ultra-5_10 SUNW.Ultra-30 SUNW.Sun-Fire-V440"
JS_CLIENT_MANAGEMENT="dhcp bootp"
#JS_CLIENT_MANAGEMENT="bootp"
In this particular setup, I'm going to install the Solaris 10 Sparc media into /u01/install directory, my packages into /u01/install/pkg and patches into /u01/install/patch. These directories need creating once jumpstart.conf has been updated:
# mkdir -p /u01/install/pkg
# mkdir -p /u01/install/patch
The other changes required are to setup the jumpstart server to accept DHCP boot requests from clients:
JS_CLIENT_MANAGEMENT="dhcp bootp"
and also the machine type which will be used to setup the dhcp client macro class. In this example, I'm booting a Sun Fire V440. Platform types can be found in /platform on any Solaris server:
JS_DHCP_VENDOR="SUNW.Ultra-5_10 SUNW.Ultra-30 SUNW.Sun-Fire-V440"
3: Use the Jet utilities to copy in the Solaris Media
Once setup, we can now copy in the Solaris media. In this example, I was using a copy of the Solaris 10 01/06 DVD. Use the copy_solaris_media in /opt/SUNWjet/bin to copy the media into the /u01/install directory. The subdirectory for the media is specified by passing the -d flag to the copy_solaris_media utility:
# ./copy_solaris_media -d 10-0106s /cdrom/sol_10_106_sparc/s0
Once this is complete, you can check whether the copy has been successful by running list_solaris_locations:
# ./list_solaris_locations
Version Location
------- --------
10 /u01/install/10-0106s
The version tag is used to identify which OS is installed on your client, defined by the base_config_ClientOS variable set in the client template, but more of that later.
4: Use the JET utilities to copy in the Solaris product media.
The JET package bundle comes with some base modules in addition to the base_config product (which installs the OS). These include:
custom - for customising client builds
jass - for hardening client builds
vts - virtual hardware tester
san - the Sun SAN packages
explo - for Sun Explorer
zones - for Solaris zones modules
flash - for building from flash archives
sds - for setting up SDS metadevices
In my custom client build, I will be installing vts and explo, so will use the JET utility, copy_product_media to copy in the vts and explo software:
# ./copy_product_media vts 6.1 /u01/software/vts sparc
Transferring sunwvtsts package instance
Transferring sunwvts package instance
Transferring sunwvtsmn package instance
Transferring sunwvtsr package instance
Packages copied.
# ./copy_product_media explo 5.4 /u01/software/explo sparc
Transferring sunwexplo package instance
Transferring sunwexplu package instance
Packges copied.
This will copy the product media into the:
${JS_PKG_DIR}/module/version/cpu
directory structure, which will be used by the post_install scripts as the source location of the product to be installed. If a version of the software is not supported, for example, Explorer 5.4 had not been added to the toolkit, the module's package.matrix file can be updated to reflect the new version.
5: Use the JET utilities to copy in custom product media.
For this client installation, I plan on installing a couple of Solaris freeware packages, lsof and sudo. These can be added to the custom product directory using the copy_custom_packages utility, an example of adding lsof is shown below.
# ./copy_custom_packages /u01/software/freeware/lsof-4.76-sol10-sparc-local sparc SMClsof
Transferring smclsof package instance
Packages copied
This package can then be installed on the client by entering the name of the package into one of the custom_package template variables.
6: Setup DHCP.
This jumpstart configuration will setup a local dhcp server on the jumpstart server. This is the easiest configuration and safe because the dhcp server will only respond to those clients which are configured for jumpstart.
The first task is to setup the local dhcp server, fortunately, this has been done for you in a script /opt/SUNWjet/Products/base_config/solaris/make_dhcp. The file first needs updating to include the network which you plan on jumpstarting on.
Set the network variables defined in the script and execute to create the basic dhcp server service with network macro.
NETWORK=192.168.16.0
NETMASK=255.255.240.0
ROUTER=192.168.16.1
# ./make_dhcp
Created DHCP configuration file.
Created dhcptab.
Added "Locale" macro to dhcptab.
Added server macro to dhcptab - s1prdmgt01.
DHCP server started.
Added network macro to dhcptab - 192.168.16.0.
Created network table.
The basic dhcp server can be checked by running:
# dhtadm -P
Name Type Value
==================================================
192.168.16.0 Macro :Subnet=255.255.240.0:Router=192.168.16.1:Broadcst=192.168.31.255:
s1prdmgt01 Macro :Include=Locale:Timeserv=192.168.17.72:LeaseTim=86400:LeaseNeg:
Locale Macro :UTCoffst=0:
and:
# svcs dhcp-server
STATE STIME FMRI
online 14:19:59 svc:/network/dhcp-server:default
7: Create the client template.
The first template I normally build just contains the base_config module (the default module) and installs the minimum Solaris metacluster. The initial requirement is just to get the boot net and install working, we can customize the build later. From my experience, it is much wiser to build up the Solaris image using each module, rather than attempting to do an initial build using the sample template which is created.
# ./make_template v440 base_config
Adding product configuration information for
+ base_config
Updating base_config template specifics
Client template created in /opt/SUNWjet/Templates
Filling in the template is self explanatory, I'll concentrate on the most important variables:
base_config_client_allocation="bootp dhcp grub"
defines how the client is going to boot on the network. We're going to configure our client to boot dhcp, so set this appropriately.
base_config_profile_usedisk=rootdisk.
defines which disk is used to install the image onto. The default is rootdisk. which normally detects the default disk, if it doesn't it can be defined here.
8: Make the client template.
The next step is to populate the client configuration directory using the make_client utility in /opt/SUNWjet/bin: The -f flag can be used to force an overwrite of a previous configuration. However, this will not remove the dhcp client configuration. To achieve this, use the remove_client utility.
At this stage, we're also populating the dhcp tables with our client information and vendor type which will be booting on the network using dhcp.
# remove_client v440
Gathering network information..
Client: 192.168.17.99 (192.168.16.0/255.255.240.0)
Server: 192.168.17.72 (192.168.16.0/255.255.240.0, SunOS)
Solaris: client_deallocate_dhcp.SunOS
# make_client -f v440
Gathering network information..
Client: 192.168.17.99 (192.168.16.0/255.255.240.0)
Server: 192.168.17.72 (192.168.16.0/255.255.240.0, SunOS)
Solaris: client_prevalidate
Clean up /etc/ethers
Solaris: client_build
Creating sysidcfg
WARNING: no base_config_sysidcfg_timeserver specified using JumpStart server
Creating profile
Adding base_config specifics to client configuration
Solaris: Configuring JumpStart boot for v440
Starting SMF services for JumpStart
Solaris: Configure DHCP build
Adding install client
Supporting VENDOR=SUNW.Ultra-5_10 SUNW.Ultra-30 SUNW.Sun-Fire-V440
Configuring v440 macro
Using local dhcp server
DHCP configuration complete
Running '/opt/SUNWjet/bin/check_client v440'
Client: 192.168.17.99 (192.168.16.0/255.255.240.0)
Server: 192.168.17.72 (192.168.16.0/255.255.240.0, SunOS)
Checking product base_config/solaris
--------------------------------------------------------------
Check of client v440
-> Passed....
8: Boot the client.
From the okay prompt, boot the client:
{0} ok boot net:dhcp - install
SC Alert: Host System has Reset
Probing system devices
Probing memory
ChassisSerialNumber 0546AL653A
Probing I/O buses
Sun Fire V440, No Keyboard
Copyright 1998-2004 Sun Microsystems, Inc. All rights reserved.
OpenBoot 4.16.4, 4096 MB memory installed, Serial #65405365.
Ethernet address 0:3:ba:e6:1:b5, Host ID: 83e601b5.
Rebooting with command: boot net:dhcp - install
Boot device: /pci@1c,600000/network@2:dhcp File and args: - install
/pci@1c,600000/network@2: 1000 Mbps full duplex link up
4000 /pci@1c,600000/network@2: 1000 Mbps full duplex link up
boot: lookup /dhcp/OS_10/Solaris_10/Tools/Boot failed
SunOS Release 5.10 Version Generic_118822-25 64-bit
Copyright 1983-2005 Sun Microsystems, Inc. All rights reserved.
Use is subject to license terms.
The client *should* boot, mount the Solaris image and install.
Troubleshooting:
1. When performing a boot net:dhcp - install, this error is returned. The dhcp service is not running and needs to be restarted.
TFTP server's IP address not known!
Evaluating:
Boot load failed
{1} ok
restart the dhcp-service:
# svcadm restart dhcp-server
2. If the install goes interactive and prompts for confirmation of the time, it may be because the time service on the jumpstart server has been disabled. This assumes of course you're using the jumpstart server as the time source!
Enable the time source by:
# svcadm enable time:stream
and if using tcp_wrappers check that clients are able to connect to the service by updating /etc/hosts.allow
Friday, May 12, 2006
Using Oracle DBMS Support Package
This is a quick guide on how to setup and use the Oracle RDBMS support package for tracing sessions, formatting the output and using the output to trace an Oracle performance issue.
First install the Oracle dbms_support package:
(s1prdmgt01)oracle:/u01/app/oracle/product/9.2.0/rdbms/admin
$ sqlplus '/ as sysdba' @dbmssupp.sql
SQL*Plus: Release 9.2.0.6.0 - Production on Fri May 12 14:38:53 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Package created.
Package body created.
SQL>
Then using the package, trace the interesting session, you can get the session from the v$session table.
SQL> SELECT sid,serial# FROM v$session
2 WHERE username = 'SHORSMAN';
SID SERIAL#
---------- ----------
10 11
SQL>
Start the trace:
SQL> EXEC DBMS_SUPPORT.START_TRACE_IN_SESSION(10,11,waits=>true, binds=>true)
PL/SQL procedure successfully completed.
SQL>
Once the user has recreated the performance query, stop the trace:
SQL> EXEC DBMS_SUPPORT.STOP_TRACE_IN_SESSION(10,11)
PL/SQL procedure successfully completed.
SQL>
There should be a new file in the user dump directory
SQL> show parameter user_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/admin/testdb/u
dump
SQL>
Run tkprof to create the formatted output from the trace file. Use the explain option to get the explain plan of the query that is being traced.
********************************************************************************
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
d_owner#, nvl(property,0),subname
from
dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+) order by order#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 1 8 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.00 1 8 0 2
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2 SORT ORDER BY
2 NESTED LOOPS OUTER
2 TABLE ACCESS BY INDEX ROWID DEPENDENCY$
2 INDEX RANGE SCAN I_DEPENDENCY1 (object id 127)
1 TABLE ACCESS BY INDEX ROWID OBJ$
1 INDEX UNIQUE SCAN I_OBJ1 (object id 36)
********************************************************************************
If there's access by full table scans then there's trouble ahead.....
Next would be to investigate the columns being searched on, using the dictionary tables dba_tab_columns and dba_ind_columns:
select t.column_name
from dba_tab_columns t, dba_ind_columns i
where t.table_name = 'S_EVT_ACT'
and t.column_name != i.column_name
and t.column_name like 'X_GMC%'
and i.column_name like 'X_GMC%'
group by t.column_name;
First install the Oracle dbms_support package:
(s1prdmgt01)oracle:/u01/app/oracle/product/9.2.0/rdbms/admin
$ sqlplus '/ as sysdba' @dbmssupp.sql
SQL*Plus: Release 9.2.0.6.0 - Production on Fri May 12 14:38:53 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Package created.
Package body created.
SQL>
Then using the package, trace the interesting session, you can get the session from the v$session table.
SQL> SELECT sid,serial# FROM v$session
2 WHERE username = 'SHORSMAN';
SID SERIAL#
---------- ----------
10 11
SQL>
Start the trace:
SQL> EXEC DBMS_SUPPORT.START_TRACE_IN_SESSION(10,11,waits=>true, binds=>true)
PL/SQL procedure successfully completed.
SQL>
Once the user has recreated the performance query, stop the trace:
SQL> EXEC DBMS_SUPPORT.STOP_TRACE_IN_SESSION(10,11)
PL/SQL procedure successfully completed.
SQL>
There should be a new file in the user dump directory
SQL> show parameter user_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/admin/testdb/u
dump
SQL>
Run tkprof to create the formatted output from the trace file. Use the explain option to get the explain plan of the query that is being traced.
********************************************************************************
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
d_owner#, nvl(property,0),subname
from
dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+) order by order#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 1 8 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.00 1 8 0 2
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2 SORT ORDER BY
2 NESTED LOOPS OUTER
2 TABLE ACCESS BY INDEX ROWID DEPENDENCY$
2 INDEX RANGE SCAN I_DEPENDENCY1 (object id 127)
1 TABLE ACCESS BY INDEX ROWID OBJ$
1 INDEX UNIQUE SCAN I_OBJ1 (object id 36)
********************************************************************************
If there's access by full table scans then there's trouble ahead.....
Next would be to investigate the columns being searched on, using the dictionary tables dba_tab_columns and dba_ind_columns:
select t.column_name
from dba_tab_columns t, dba_ind_columns i
where t.table_name = 'S_EVT_ACT'
and t.column_name != i.column_name
and t.column_name like 'X_GMC%'
and i.column_name like 'X_GMC%'
group by t.column_name;
Subscribe to:
Posts (Atom)