I was testing SQL plan baselines on a base 220.127.116.11 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:
select distinct p.name sql_profile_name, s.sql_id from dba_sql_profiles p, DBA_HIST_SQLSTAT s where p.name=s.sql_profile;
Here is the output on my system:
SQL_PROFILE_NAME SQL_ID ------------------------------ ------------- coe_5up1944u1zb7r_1979920644 5up1944u1zb7r coe_b9ad7h2rvtxwf_3569562598 b9ad7h2rvtxwf coe_9f21udzrfcygh_2815756405 9f21udzrfcygh coe_366brswtxxuct_10194574 366brswtxxuct coe_2261y37rp45py_2815756405 2261y37rp45py
These are (more...)
Today I was trying to see if upgrading from 18.104.22.168 to 22.214.171.124 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
|Event||Waits||Time(s)||Avg wait (ms)||% DB time||Wait Class|
|virtual circuit wait||1,388,199||17,917||13||50.98||Network|
|db file sequential read||1,186,933||9,252||8||26.33||User I/O|
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.
This graph represents commit time compared to CPU utilization and redo log write time. I’ve included only the hourly intervals with more than 1,000,000 commits. At these peaks the number of commits ranges 1 to 1.6 million commits per hour so each point on the graph represents roughly the same commit rate. I’m puzzled by why the commit time bounces around peaking above 5 milliseconds when I can’t see any peaks (more...)