Home » SQL & PL/SQL » SQL & PL/SQL » ALL_OBJECTS vs ALL_SEQUENCES (Oracle 10.2)
ALL_OBJECTS vs ALL_SEQUENCES [message #429387] Tue, 03 November 2009 08:14 Go to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Hi,

As my requirement demands, I am extensively querying data dictionary in my PLSQL code.

I am trying to find out querying which view is faster. i.e. ALL_OBJECTS or ALL_SEQUENCES.

For my testing, I just created a plsql block, it runs a loop 1..20000 and select the same sequence name from ALL_OBJECTS and ALL_SEQUENCES.

The loop on ALL_OBJECTS ends in just 10 seconds but the other loop which selecting from ALL_SEQUENCES runs for 100000 seconds.

I tried in a couple of other instances and experienced the same performance.

P.S: Don't care much about the logic. My intentions is to know why querying on ALL_SEQUENCES is taking long time.
Re: ALL_OBJECTS vs ALL_SEQUENCES [message #429389 is a reply to message #429387] Tue, 03 November 2009 08:21 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Trace it.

But you should bear in mind that a lot of the data dictionary views aren't actually written with performance in mind.
Re: ALL_OBJECTS vs ALL_SEQUENCES [message #429393 is a reply to message #429387] Tue, 03 November 2009 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In your test first execute each query without measuring, just to fill the cache; then do the measures.
Post your queries.

Regards
Michel
Re: ALL_OBJECTS vs ALL_SEQUENCES [message #429401 is a reply to message #429393] Tue, 03 November 2009 09:11 Go to previous messageGo to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
-- USING USER_SEQUENCES

CONNECT CISET/CISET

CREATE OR REPLACE FUNCTION getnextseq_userobj

RETURN NUMBER 
AUTHID DEFINER
IS
i number:=dbms_utility.get_time;
j number;
v_obj_name varchar2(35);
v_query varchar2(1000);
v_seq         NUMBER;
v_ecode       NUMBER(38);
v_emesg       VARCHAR2(250);

begin
for j in 1..20000
loop
SELECT sequence_name into v_obj_name

		FROM user_sequences

		WHERE sequence_name = UPPER('COMPANYKEY_SEQ_324');
end loop;

v_query := 'SELECT CISETAVFRD105.'

                || v_obj_name

                || '.nextval FROM DUAL';


EXECUTE IMMEDIATE v_query INTO v_seq;
RETURN v_seq;

EXCEPTION

	   WHEN NO_DATA_FOUND THEN
		raise_application_error (-20033, '99992, SEQUENCE NOT FOUND');
           WHEN OTHERS THEN

                v_ecode := SQLCODE;

                v_emesg := SQLERRM;

                dbms_output.put_line(v_emesg);
end;
/


declare

a varchar2(100);
v_seqval number(10);
i NUMBER:=dbms_utility.get_time;

begin

v_seqval := getnextseq_userobj();

i:=round((dbms_utility.get_time-i)/100,2);
dbms_output.put_line(' ');
dbms_output.put_line(' USER_SEQUENCES queried in Seconds: ' || i);
dbms_output.put_line(v_seqval || ' Seq value');

end;
/


This example is to select from USER_SEQUENCES. Similarly I am selecting from ALL_OBJECTS and ALL_SEQUENCES.

Quote:
Time taken to execute:

-------------------------------

ALL_OBJECTS queried in Seconds: 1.61

128 Seq value



USER_SEQUENCES queried in Seconds: 0.72

129 Seq value



ALL_SEQUENCES queried in Seconds: 100.78

130 Seq value

[Updated on: Tue, 03 November 2009 09:13]

Report message to a moderator

Re: ALL_OBJECTS vs ALL_SEQUENCES [message #429405 is a reply to message #429401] Tue, 03 November 2009 09:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am selecting from ALL_OBJECTS and ALL_SEQUENCES.

Yes but I need the EXACT queries.

In addition, why do you include in your times the time for much things that have nothing with the loop? Remove all that is not the loop, remove the call and put the loop in the main program... Simplify, simplify...
And as I said, call once the query without measure and then start the loop.

Regards
Michel

[Updated on: Tue, 03 November 2009 09:48]

Report message to a moderator

Re: ALL_OBJECTS vs ALL_SEQUENCES [message #429489 is a reply to message #429405] Wed, 04 November 2009 03:27 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Running a simple timing harness test, just fetching a row from ALL_SEQUENCES a lot of times, and fetching a row from ALL_OBJECTS a lot of times, I find that selects from ALL_SEQUENCES are about 5x faster than ones from ALL_OBJECTS
Previous Topic: ORA-12704: Character set mismatch
Next Topic: attach pdf file from local computer and send as email
Goto Forum:
  


Current Time: Sat Dec 03 16:04:59 CST 2016

Total time taken to generate the page: 0.03886 seconds