date:20020726

RE: Is it correct way of adding two tables without having self-jo

2002-07-26 Thread Sandeep Kurliye


Do you think there is any reason for adding same tables two times without having self 
join condition??? 

IMHO, Oracle development guys has done some mistake 


-Original Message-
From:   Straub, Dan [mailto:[EMAIL PROTECTED]]
Sent:   Fri 7/26/2002 22:44
To: Multiple recipients of list ORACLE-L
Cc: 
Subject:RE: Is it correct way of adding two tables without having self-jo

Have you done both an explain and sqltrace on the query to see how it is
being executed and where the time is being spent? I would start there.

Dan Straub 
McKesson Information Solutions 
541-681-8278 
44°03'N 123°05'W (or thereabouts) 
Confidentiality Notice: This e-mail message, including any attachments,
is for the sole use of the intended recipient(s) and may contain
confidential and privileged information.  Any unauthorized review, use,
disclosure or distribution is prohibited.  If you are not the intended
recipient, please contact the sender by reply e-mail and destroy all
copies of the original message.


-Original Message- 
 ] 
Sent: Friday, July 26, 2002 6:13 AM 
To: Multiple recipients of list ORACLE-L 
self-join

condition in Oracle HRMS 


Hi Guys, 

I need your comments on following view. This view has been written by
Oracle Development guys. I raised iTAR for this view problem with Oracle
HRMS group, and they suggested to raise it with RDBMS Group. When I
raised iTAR with RDBMS Group, I get answer that Oracle guys are only for
product support. And he has suggested to discuss this in Forums. I've
put this question in Metalink forum as well as
http://www.oracle.com/forums   . 

I am having tremendous problem with this view. Even if I select just
'select * from this_view;', it takes long time to give result.

May I have your comment please? 

Thanks. 

# 

Application: Oracle HRMS 
Version: 11.5.3 & 11.5.5 
View Name: PAY_ASSIGNMENT_ACTIONS_V2 

View Query: 
SELECT DISTINCT paa.ROWID row_id, paa.assignment_id,
paa.assignment_action_id, 
per.person_id, SUBSTR ( pay_gb_payroll_actions_pkg.get_salary (
asf.pay_basis_id, asf.assignment_id, rppa.date_earned), 1, 60 ) salary,

SUBSTR (INITCAP (hr_general.decode_pay_basis
(asf.pay_basis_id)),1,30)pay_basis, 
job.name job_title, per.full_name, per.title, 
SUBSTR (per.first_name, 1, 1) || ' ' || SUBSTR (per.middle_names, 1, 1)
initials, per.last_name, asf.assignment_number, asf.location_id,

asf.internal_address_line, per.national_identifier ni_number,
per.expense_check_send_to_address, 
rppa.date_earned date_earned, paa.payroll_action_id,
rpaa.assignment_action_id run_assignment_action_id, 
rpaa.payroll_action_id run_payroll_action_id, rppa.time_period_id
time_period_id, rppa.payroll_id payroll_id, 
NVL (rppa.pay_advice_date, ptp.pay_advice_date) pay_advice_date, 
ppg.segment1, ppg.segment2, ppg.segment3, ppg.segment4, 
ppg.segment5, ppg.segment6, ppg.segment7, ppg.segment8, 
ppg.segment9, ppg.segment10, ppg.segment11, ppg.segment12, 
ppg.segment13, ppg.segment14, ppg.segment15, ppg.segment16, 
ppg.segment17, ppg.segment18, ppg.segment19, ppg.segment20, 
ppg.segment21, ppg.segment22, ppg.segment23, ppg.segment24, 
ppg.segment25, ppg.segment26, ppg.segment27, ppg.segment28, 
ppg.segment29, ppg.segment30 

FROM per_jobs job, 
per_all_assignments_f asf, 
per_people_f per, 
pay_assignment_actions paa, /* prepayment assignment action */ 
pay_payroll_actions ppa, /* prepayment payroll action */ 
pay_assignment_actions rpaa, /* run assignment action */ 
pay_payroll_actions rppa, /* run payroll action */ 
pay_action_interlocks il, 
per_time_periods ptp, 
pay_people_groups ppg 

WHERE paa.payroll_action_id = ppa.payroll_action_id 
AND ppa.action_type IN ('U', 'P') 
AND ppa.action_status = 'C' 
AND ppa.payroll_id = rppa.payroll_id 
AND ppa.effective_date >= rppa.effective_date 
AND rpaa.assignment_id = paa.assignment_id 
AND ppg.people_group_id(+) = asf.people_group_id 
AND job.job_id(+) = asf.job_id 
AND per.person_id = asf.person_id 
AND paa.assignment_id = asf.assignment_id 
AND rppa.date_earned BETWEEN asf.effective_start_date 
AND asf.effective_end_date 
AND ptp.time_period_id = rppa.time_period_id 
AND rppa.date_earned BETWEEN per.effective_start_date 
AND per.effective_end_date 
AND rppa.payroll_action_id = rpaa.payroll_action_id 
AND rpaa.assignment_action_id = il.locked_action_id 
AND paa.assignment_action_id = il.locking_action_id 
AND il.ROWID = (SELECT SUBSTR ( MAX ( LPAD (aa.action_sequence, 15, 0)
|| loc.ROWID ), -18 ) /* Length of rowid */ latest_act

  FROM pay_assignment_actions aa, 
   pay_action_interlocks loc 
WHERE loc.locked_action_id =
aa.assignment_action_id 
AND loc.locking_action_id =
paa.assignment_action_id); 
. 


Observations: 
--- 
1.Table pay_assignment_actions has been referenced 2 ti

Open --> Parse -->Execute --> Fetch Rows --> Close

2002-07-26 Thread MacGregor, Ian A.


Yes, it is done at the parse call.  In some cases such as witn DBMS_SQL the calls are 
explicit .
One writes code to explicitly open the cursor, parse the cursor, execute the cursor, 
fetches rows from the cursor, and close the cursor.  The fetch rows call is often 
placed in a loop.  

When writing  most PL/SQL procedures, one  writes code  which explicitly opens the 
cursor, fetches rows from the cursor, and closes the cursor.  But the open call is 
really a combination of
open and parse.  I'm not sure where  the execute  happens.  I'm confused by the  
execute then fetch vs. execute_and_fetch dbms_sql calls.  In the first case the 
execute is not placed in the loop, in the latter it is. 

When writing a ref cursor when  "opens" then prints which also result in the other 
cursor calls being issued.  

If one is using a ref cursor.  The parse happens at 
OPEN : FOR ;
-
If  the above statement is successful, you can revoke permissions from the  user and 
the "print" statement will be successful.  However  the next time the "open" is issued 
 in will fail the semantics check of a soft parse.   This shows the parse happens at 
the "open" and not the print.  Again I wss using "open" in this sense.  

Ian MacGregor
Stanford linear Accelerator Center
[EMAIL PROTECTED]



Original Message-
Sent: Friday, July 26, 2002 1:49 PM
To: Multiple recipients of list ORACLE-L


Hmm, parsing is not done at the open (oopen call). It is done in the
oparse/osql call. But they are deffered until the execute. So it looks like
it is done at the execute. In OCI8 it is a different story all together.

Anjo.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, July 25, 2002 6:59 PM


> Possibly burying myself deeper:  Parsing is done at the open call.  If a
cursor needs to be
> re-opened, Oracle will check for permissions whether cursors are cached or
not.
>
> Some experiments.  First just using  bind variables in the statement.
>
> SQL> alter session set session_cached_cursors = 10;
>
> Session altered.
>
> SQL> VARIABLE V_EMPNO NUMBER
>
> BEGIN
> :V_EMPNO := 7934;
> END;
> /
> SQL> select ename from scott.emp where empno = :v_empno;
>
> ENAME
> --
> MILL
>
> As this is the first statement.  I would expect hard and soft parsing to
be taking place.
>
> SQL> BEGIN
>   2  :V_EMPNO := 7782;
>   3  END;
>   4  /
>
> PL/SQL procedure successfully completed.
>
> SQL> select ename from scott.emp where empno = :v_empno;
>
> ENAME
> --
> CLARK
>
> What type of parsing is done here.  The statement is in the buffer pool
>
> --
---
> If "scott" revokes privileges
>
> and the above statement is rerun
>
> SQL> /
> select ename from scott.emp where empno = :v_empno
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> --

> Scott restores privileges ...
>
> SQL> variable my_select refcursor;
> SQL> BEGIN
>   2  OPEN :my_select FOR SELECT ename from s
>   3  END;
>   4  /
>
> PL/SQL procedure successfully completed.
>
> SQL> print my_select
>
> ENAME
> --
> SMITH
> ALLEN
> WARD
> JONES
> MARTIN
> BLAKE
> SCOTT
> KING
> TURNER
> ADAMS
> JAMES
> FORD
> MILL
> CLARK
>
> 14 rows selected.
> --

> Print closes the cursor.
>
> If "scott" revokes permisssions at this point.
>
> SQL> BEGIN
>   2  OPEN :my_select FOR SELECT ename from scott.emp;
>   3  END;
>   4  /
> OPEN :my_select FOR SELECT ename from scott.emp;
> *
> ERROR at line 2:
> ORA-06550: line 2, column 45:
> PLS-00904: insufficient privilege to access object SCOTT.EMP
> ORA-06550: line 2, column 21:
> PL/SQL: SQL Statement ignored
> --
-
> However if  scott restores permissions
>
> SQL> BEGIN
>   2  OPEN :my_select FOR SELECT ename from scott.emp;
>   3  END;
>   4  /
>
> PL/SQL procedure successfully completed.
>
> and now revokes them here.
>
> The print statement will still work
>
> SQL> print my_sele
>
> ENAME
> --
> SMITH
> ALLEN
> WARD
> JONES
> MARTIN
> BLAKE
> SCOTT
> KING
> TURNER
> ADAMS
> JAMES
> FORD
> MILL
> CLARK
>
> 14 rows selected.
> --
--
> So how does one keep such cursors open.  Given a cursor such as
>
> BEGIN
> OPEN :my_select FOR SELECT ename from scott.emp
> where empno = :v_empno;
> END;
>
> How does one  display the information,  change the value of :v_empno, and
display  the infromation again without re-opening the cursor.
>
> In the distant past when I was writin

RE: Cursor Sharing| Soft Parsing

2002-07-26 Thread Rachel Carmichael


Connor,

Well, technically that answers the "why"... with Oracle's "because I
said so"  :)

the REAL question is why 3 and not 2 or 4 or 10 or.  ?

Rachel


--- Connor McDonald <[EMAIL PROTECTED]> wrote:
> "RTM" :-)
> 
> Performance Guide 9.2
> 
> "Caching Session Cursors
> 
> (blah blah blah)
> 
> Oracle checks the library cache to determine whether
> more than three parse requests have been issued on a
> given statement.
> 
> (more blah blah blah)"
> 
> hth
> connor
> 
> 
>  --- [EMAIL PROTECTED] wrote: > I wasn't aware
> of it requiring three calls before
> > being useful.
> > 
> > Why is that?
> > 
> > Jared
> > 
> > 
> > 
> > 
> > 
> > "Cary Millsap" <[EMAIL PROTECTED]>
> > Sent by: [EMAIL PROTECTED]
> > 07/25/2002 07:58 AM
> > Please respond to ORACLE-L
> > 
> >  
> > To: Multiple recipients of list ORACLE-L
> > <[EMAIL PROTECTED]>
> > cc: 
> > Subject:RE: Cursor Sharing| Soft
> > Parsing
> > 
> > 
> > Well, three times, right? I think it takes three
> > parse calls before
> > session_cached_cursors begins to help. But 3 is
> > still O(1). Once per
> > call is O(#executions).
> > 
> > 
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> > 
> > Upcoming events:
> > - Hotsos Clinic, Jul 23-25 Chicago
> > - Miracle Database Forum, Sep 20-22 Middlefart
> > Denmark
> > - 2003 Hotsos Symposium on OracleR System
> > Performance, Feb 9-12 Dallas
> > 
> > 
> > 
> > -Original Message-
> > Still
> > Sent: Thursday, July 25, 2002 2:38 AM
> > To: Multiple recipients of list ORACLE-L
> > 
> > On Wednesday 24 July 2002 22:08, MacGregor, Ian A.
> > wrote:
> > > Please define soft parsing.  Oracle needs to check
> > that  the user
> > > submitting a SQL statement has permissions to run
> > it.  It has to do
> > this
> > > every time a statement is run, bind variables or
> > not. 
> > 
> > No, code that uses bind variables need only parse
> > SQL statements
> > once if session_cached_cursors is set.  Further
> > executions of the same
> > SQL don't require a hard or soft parse.
> > 
> > Jared
> > 
> > > When "cursor-sharing"  converts a statement to use
> >  bind variables it
> > would
> > > save on hard parsing, if a match were found the
> > pool; also, it could
> > lessen
> > > the number of statements present in the pool.
> > >
> > > Ian MacGregor
> > > Stanford Linear Accelerator Center
> > > [EMAIL PROTECTED]
> > >
> > > -Original Message-
> > > Sent: Wednesday, July 24, 2002 9:23 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Mike, Kirti,
> > >
> > > Try page 441
> > >
> > > CURSOR_SHARING=FORCE does improve badly written
> > applications that use
> > lots
> > > of literals.
> > > However coding should be done using bind variables
> > in almost all
> > occasions.
> > >
> > > CURSOR_SHARING=FORCE reduces the hard parsing.
> > >
> > > What CURSOR_SHARING=FORCE does is rewrites all
> > queries to use bind
> > > variables before parsing.
> > >
> > > eg.  select ename from emp where empno = 10;
> > > rewritten as
> > > select ename from emp where empno =:SYS_B_0
> > > or in 8.1.6 , 8.1.7
> > > select name from emp where empno =:"SYS_B_0"
> > >
> > > So it substitutes the literal with bind variables
> > but incurs the cost
> > of
> > > soft parsing the statement.
> > > Soft Parsing too frequently limits the scalability
> > of applications and
> > > sacrifices optimal performance which could have
> > been achieved in the
> > first
> > > place if written using bind variables.
> > >
> > > Parse once and execute as many times as we like.
> > >
> > > Also check out Bjorn's paper on bind variables and
> > cursor sharing at
> > >
> >
> http://technet.oracle.com/deploy/performance/pdf/cursor.pdf
> > >
> > > So CURSOR sharing is not the "silver bullet" as
> > one may expect.
> > >
> > > Regards
> > > Suhen
> > >
> > > On Thu, 25 Jul 2002 10:23, you wrote:
> > > > Mike,
> > > > What is the version of the database? Some
> > versions of 8.1.7 had a
> > few
> > > > bugs when this parameter was set to FORCE. I
> > suggest searching
> > Metalink.
> > > > But it does work as advertised in later
> > releases. I would also
> > recommend
> > > > reviewing Tom Kytes' book to read about his
> > views in using this
> > parameter
> > > > at the instance level (my boss is reading my
> > copy, so I can't give
> > you
> > > > page #s).
> > > >
> > > > - Kirti
> > > >
> > > > -Original Message-
> > > > Sent: Wednesday, July 24, 2002 6:08 PM
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > >
> > > > Has anyone set Cursor Sharing to Force ?
> > > > I have a new system that we have to support
> > > > and there is alot literals filling up the
> > > > pool.I have never changed this parameter
> > > > from the default as many seemed to think the
> > > > jury was still out on it.   However, due to
> > > > my situation, I figured I would try it out.
> > > > If anyone has any experience with this one
> > > > I would be curious to

RE: Guys: Is this worth $1,749 for 8 year Oracle veteran DBA

2002-07-26 Thread Hately Mike

Hi,
the
requirement for an instructor-led course is only for people wishing to gain the
9i DBA OCP credential from scratch.
It's
not required for people taking the 8i course, upgrading from 8i or those who've
already taken an exam towards the 9i OCP credential.
And
the evidence is at http://www.oracle.com/education/certification/index.html?dba9i_ocpcoursereq.html
regards,
Mike
Hately
(9i
OCP DBA since January =) )

-Original Message-From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]Sent: 26 July 2002
13:56To: Multiple recipients of list ORACLE-LSubject:
RE: Guys: Is this worth $1,749 for 8 year Oracle veteran
DBA
Hmm. Good point. Anyone know how we can
verify that if you take the 8i exams and pass you can just do the upgrade exam
without the additional course? Is there a time when 8i exams will not be
offered any more or any other time constraint and does any one
know?

37 matches

Mail list logo