RE: Perl Issues
- From: JApplewhite@xxxxxxxxxxxxx
- To: GuangMei@xxxxxxx
- Date: Fri, 22 Feb 2008 11:06:45 -0600
Nope. Any query against All_Tables (or DBA_Tables or User_Tables) results
in a Full Table Scan of OBJ$. Having 150,000+ tables and 230,000+ indexes
in that one schema in the database results in about 420,000 rows in OBJ$.
Any query using Table_Name causes over 6,400 Consistent Gets. Not
horrible, but, when there are hundreds or thousands of such queries in a
session, it adds up to a huge impact. In a "normal" database you'd
probably not notice.
As Sys in one of your databases, set Autotrace On in SQL*Plus, do the
select below, and check out the Explain Plan - pretty convoluted, eh? See
how many rows your OBJ$ has and how many Consistent Gets the query takes -
there's a correlation, at least in my little brain.
Thanks.
Jack C. Applewhite - Database Administrator
Austin I.S.D.
414.9715 (phone) / 935.5929 (pager)
"Guang Mei" <GuangMei@xxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
02/21/2008 04:33 PM
Please respond to
GuangMei@xxxxxxx
To
<JApplewhite@xxxxxxxxxxxxx>
cc
<oracle-l@xxxxxxxxxxxxx>
Subject
RE: Perl Issues
Would
Select Count(*) From All_Tables Where Table_Name = '<TableName>’ and
rownum=1
Help?
Guang
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of JApplewhite@xxxxxxxxxxxxx
Sent: Thursday, February 21, 2008 4:26 PM
To: oracle-l@xxxxxxxxxxxxx
Cc: oracle-l-bounce@xxxxxxxxxxxxx
Subject: Perl Issues
Our Developers wrote our Special Ed Student Info. application in Perl -
about which I know zip. Without going into gory details of why, their
code repeatedly checks for the existence of specific tables (out of the
150,000+ tables in our Production Student Info.) by doing
"Select Count(*) From All_Tables Where Table_Name = '<TableName>'"
whick is very inefficient, especially with 150,000 tables.
First of all, I want them to not check for table existence and code in
Exception Handling, but they are resistent to my suggestions.
Second, if they don't code the Exception Handling, I want them to use a
utility function that we have that more efficiently checks for table
existence and returns a Boolean - True if exists, False if not exists.
My two questions:
1. Can Exception Handling be easily done in Perl? If so, any good
examples I can point them to?
2. They say that Perl can't handle Boolean values. Is that true? Any
guidance here?
Thanks.
Jack C. Applewhite - Database Administrator
Other related posts:
