I modified the bind variable extraction script that I normally use to make it more helpful to me.
Here was my earlier post with the old script: blog post
Here is my updated script:
set termout on
set echo on
set linesize 32000
set pagesize 1000
set trimspool on
column NAME format a3
column VALUE_STRING format a17
select * from
I was testing SQL plan baselines on a base 18.104.22.168 release of Oracle on a 64 bit Linux virtual machine. I ran DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE to create a SQL plan baseline for a test query after running that query once to get its plan in the cursor cache. When I ran the test query after creating the SQL plan baseline and called dbms_xplan.display_cursor to see its new plan I got (more...)
I have a number 0f SQL Profiles on a database I’m working on and I wanted to know the sql_id of the SQL statements that they relate to. Here is what I came up with:
Here is the output on my system:
These are (more...)
Today I was trying to see if upgrading from 22.214.171.124 to 126.96.36.199 would change the SYS.WRH$_SQL_PLAN table. This table is large on our production system so I wanted to find out if some time-consuming update to this table would occur that would slow down our production upgrade but not be detected on our test systems. We recently performed this upgrade on our development database and I (more...)
The Unix utilities ps and top report memory differently with HugePages than without.
Without HugePages ps seems to include the SGA memory under the SZ column:
UID PID PPID C SZ RSS PSR STIME TTY TIME CMD
oracle 1822 1 0 846155 16232 0 07:19 ? 00:00:00 ora_d000_orcl
oracle 1824 1 0 846155 16228 0 07:19 ? 00:00:00 ora_d001_orcl
oracle 1826 1 0 846155 16236 0 07:19 ? 00:00:00 ora_d002_orcl
oracle 1828 1 0 846155 (more...)
On Monday we had some performance problems on a system that includes a database which uses shared servers. The top wait was “virtual circuit wait”. Here are the top 5 events for a 52 minute time frame:
Top 5 Timed Foreground Events
||Avg wait (ms)
||% DB time
|virtual circuit wait
|db file sequential read
Just got the invitation to the first AZORA (Arizona Oracle user group) meeting on October 23. Here is the link: url
It’s 2 pm at Oracle’s office, 2355 E Camelback Rd Ste 950, Phoenix, AZ.
I’m looking forward to it!
I have an example of paging in some Exadata OS Watcher log files. We got an error in the alert log about a high load but reviewing AWR reports it did not seem like we had an unusual number of active sessions. Also, the CPU use seemed low but the system load was high. Reviewing the OS Watcher logs and finding a similar case on Oracle’s support site convinced me that our (more...)
Yesterday and today I’ve read or heard two people mention the force_match => TRUE parameter value for DBMS_SQLTUNE.IMPORT_SQL_PROFILE and how it forces a profile to work on all SQL statements that are the same except for their literal values. So, I ran a quick test using the coe_xfr_sql_profile.sql utility that comes with the SQLT scripts that are available for download on Oracle’s support site.
I’ve mentioned in earlier posts how we use (more...)
I saw this fun blog post about the Arizona Oracle User Group getting organized: blog post
I’m definitely interested in being involved. Please pass this on to any Oracle DBAs, developers, etc. that you know in the Phoenix area.