RE: Design question: too many SQLs differ by updated columns

Mark, thanks for reminding me of checking for default values when inserting nulls. Good point!

I had a little email exchange with another list member and realized that update set column=current_value is a bad choice due to redo. I didn't give much thought to this "solution" so I forgot this drawback.

Can you give a reference to "gazintas and comzoutas tools" that I can find on the Internet? It sounds like something interesting. Thanks.

The update SQLs do take a lot of memory:

SQL> select command_type, sum(sharable_mem), count(*) from v$sql where parsing_schema_name = 'APP' group by command_type order by 1;

COMMAND_TYPE SUM(SHARABLE_MEM) COUNT(*)

------------ ----------------- ----------
           2          25703480        436  <-- insert
           3         661909775      23979  <-- select
           6         677744945       8515  <-- update
           7         332973710      10424  <-- delete
          47             17904          1

Ignore select. They're being rewritten to use bind variables so they'll come down quickly. I remember the delete SQLs are the same (using literal values).

Yong Huang

• "Mark W. Farnham" <mwf_at_rsiz.com> wrote:

> Okay, first regarding the inserts, you'll want to be sure you're not
> overriding default values with specified nulls.
>
> Now if your updates are really only the permutations of the columns in
> order, I think the full enumerations is one less than 2 to the number of
> columns of each table per table. Oracle will process redo for columns
> changed to the current value, so that can be an expensive choice, plus any
> of them that are constrained by foreign keys will make that probe as well.
>
> There is some middle ground for groups of columns updated together, such
> that you might add in a few non-updated columns (avoiding foreign key
> constrained columns unless they are really updated) to get the total number
> of combinations down a bit. Understand from the git-go that this is a
> slippery slope and a possible win if the current number of parsed statements
> you have is actually causing slowness. You probably want to avoid
> prefetching the "not new" columns values, so the update statement would need
> to use nvl(:MRN,MRN) forms of the column names.
>
> I noticed you're using camel case, which I find error-prone and untypeable,
> but de gustibus non est disputandem.
>
> If the sheer size is a concern, and you're willing to comprehensively build
> a set of views and a tool to translate the queries written by developers and
> generated by programs into terse column names you can make things really
> small. It is pretty important that you have both gazintas and comzoutas
> tools (See Bodine, J.,CBS, 1964) so that people can write queries in user
> friendly names and query the terse names back out of v$sqltext in readible
> fashion. JL's chapter in Oak Tables tales has a seminal example of how silly
> it is to actually try to read queries written in machine generated table and
> column names (and they weren't even short!).
>
> It's not that hard to write such a tool, and if you're at least up to a
> release that supports updates through views you should be okay. Before you
> go to that work you should verify that the size is a problem.
>
> Regards,
>
> mwf
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Yong Huang
> Sent: Saturday, May 31, 2008 7:30 PM
> To: oracle-l_at_freelists.org
> Subject: Design question: too many SQLs differ by updated columns
>
>
>
> One schema in our 10.2.0.4 database have 40,000 SQLs in v$sql taking 1.5GB
>
> sharable memory. A small number of them are due to not using bind variables.
>
> But many are insert and update statements like this:
>
>
>
> UPDATE PATIENT SET MRN=:MRN,Last_Name=:Last_Name,First_Name=:First_Name,...
>
> Occupation=:Occupation,Original_Reg_Date=:Original_Reg_Date,AD_Living_Will=:
> AD_Living_Will...
>
>
>
> Thousands of these SQLs differ by different columns in the SET clause. The
>
> columns seem to be listed in their order in the patient table. Since one SQL
>
> updates one set of columns, another updates another set, the permutation is
>
> huge.
>
>
>
> I told the developer to consider changing the insert, listing all columns
> and
>
> setting the inserted value to null if that column is missing. But for these
>
> update statements we can't think of a good way to corect them. We either
> have
>
> to redesign the data model, normalizing some columns into their own tables
>
> (e.g. occupation shouldn't belong in patient table), or query the row first
> and
>
> list all columns in the update set clause filling in the selected value for
>
> those columns that don't need to be updated. The first method is not doable
>
> financially and administratively. The second method raises buffer gets and
>
> probably causes worse overall performance. I also thought of MERGE. But the
> app
>
> already knows whether to insert or update rather than let the database
> decide.
>
> Besides, it still has the column list problem in the update part of merge.
>
>
>
> Any comments are welcome.
>
>
>
> Yong Huang
      

--
http://www.freelists.org/webpage/oracle-l