11,403 reputation
11832
bio website ddbcinc.com
location Rochester Hills, MI
age 37
visits member for 3 years, 3 months
seen 8 hours ago
Oracle ACE

Dec
10
comment Internal query on CDEF$ and CON$ very CPU bound
Exactly what version of Oracle are you using? I know you said 10.2 but what patchlevel? Have you gathered statistics on dictionary objects? Are those statistics accurate?
Nov
25
revised Normalization: 'zip code' as a primary key
added 206 characters in body
Nov
25
comment Normalization: 'zip code' as a primary key
At least in the United States, ZIP codes do change pretty regularly. And the ZIP code for an address will change somewhat commonly as subdivisions are built out. But more importantly, ZIP codes cross city, county, and state lines. So if you wanted to have something like this, you'd need zip_code, city, and state to be the composite primary key of your mapping table and have those three columns in the address table as well. That might still make sense for validation purposes, but ZIP code is not part of the geography hierarchy. oseda.missouri.edu/jgb/ZIP.resources.html
Nov
25
answered Normalization: 'zip code' as a primary key
Nov
20
answered Oracle Logical IO / Consistent Reads inconsistent?
Nov
19
answered Oracle 11g INDEX_SS Hint
Nov
18
answered Combine 2 SQL Queries
Nov
17
answered Oracle 10 column name resolution
Nov
12
comment My transaction's max time is going beyond a minute
There is a Who Is Active 11.11 that works on SQL Server 2005 and 2008. It seems unlikely that you would be able to run it against an Oracle database. sqlblog.com/blogs/adam_machanic/archive/2012/03/22/…
Nov
12
comment My transaction's max time is going beyond a minute
If you can monitor the system while you reproduce the error, you could also use the data from the dynamic performance views. That's harder to arrange (DBAs tend not to be particularly happy to have people in running ad-hoc queries against dynamic performance views at peak load when there are performance problems). And it requires that you be pretty efficient at it. AWR snaps all that data off so you can analyze it at your leisure potentially on a different system rather than trying to do your analysis while the issue is happening.
Nov
12
comment My transaction's max time is going beyond a minute
If you're licensed for it, the overhead of AWR statistics gathering is generally pretty minimal. If you've disabled AWR snapshots and ASH because of performance concerns, you're throwing away a lot of data that would help debug this issue. Enterprise Manager does have screens that display data from the AWR and ASH tables but you can also query the data directly. The easy way to see wait events, unfortunately, is to look at the AWR data. You could also trace the session (that will make the session much slower and can have a noticable impact on database performance).
Nov
12
comment My transaction's max time is going beyond a minute
Are you saying that you cannot use AWR/ASH data (possibly because of license issues)? Or that you don't want to? Or that you can but you're not sure how? I'd certainly want to zoom in on the data Oracle collects about individual executions to see if you're occasionally getting different query plans or whether there is some wait event that is happening periodically like row lock contention.
Nov
12
comment My transaction's max time is going beyond a minute
What do you mean by "When I look at individual queries they are fine"? What, specifically, are you looking at? Are you looking at the AWR/ ASH information for the executions of those statements during the period when the transaction time exceeds a minute? Or are you looking at the statements in isolation later? I'm not sure what you mean when you say that I/O, CPU, memory, and network are "fine tuned". Are you saying that some tool tells you that when things have plenty of additional capacity when performance is poor? What does AWR/ ASH tell you are the wait events?
Nov
11
answered Why can I select multiple columns from dual?
Nov
11
comment Are databases only growing in size?
@Jim - The fact that something is inefficient does not mean that it is impossible. It merely means that it is something that one would do as a periodic "housekeeping" process if it is actually necessary.
Nov
9
answered Are databases only growing in size?
Nov
7
comment Save a dynamic cursor for reuse?
If your goal is to allow individual users to save their settings so that they don't have to enter values for the "many other parameters" each time, it would seem reasonable to create a new table to store those parameters (possibly along with a name for the parameter set) and then let your users choose a parameter set in the application. You probably wouldn't implement that functionality inside the procedure you posted, it would probably be done in a different procedure that your application calls.
Nov
7
comment Save a dynamic cursor for reuse?
Where do you want to save the query? You could write the data to a new table, of course. Oracle is already storing the query in the shared pool if you run the same query again (with or without the same bind variable values). If the query you're actually using uses bind variables (a very good thing in general), a similar query that uses literals is not the same query. Running queries without bind variables will flood your shared pool with non-reusable SQL and may cause serious performance problems.
Nov
6
awarded  Enlightened
Nov
6
awarded  Nice Answer