Home » RDBMS Server » Performance Tuning » SQL too slow (10g 10.1.0.2.0 - xp)
SQL too slow [message #398305] Thu, 16 April 2009 00:04 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Hi

I have installed 10g on core2duo machine with 2gb ram. When I connect and run any query through SQL, it slowly displays the data. I wonder why it is so, while on other such machines in my office its(SQL) performance is toooo much fast. Please give me any solution before I reinstall Oracle.

Note: Other forms and reports run quickly and I feel no performance issue.

Thanks
Rz
Re: SQL too slow [message #398418 is a reply to message #398305] Thu, 16 April 2009 03:49 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
rzkhan wrote on Thu, 16 April 2009 12:04
Hi

I have installed 10g on core2duo machine with 2gb ram. When I connect and run any query through SQL, it slowly displays the data. I wonder why it is so, while on other such machines in my office its(SQL) performance is toooo much fast. Please give me any solution before I reinstall Oracle.

Note: Other forms and reports run quickly and I feel no performance issue.

Thanks
Rz



You did not post the original statement (at your office's machine) & the statement in your machine.

You did not post the output of explain plan/tkprof.

You did not describe the relational objects which invoked/retrieved/called data in your statement.
...
Re: SQL too slow [message #398508 is a reply to message #398305] Thu, 16 April 2009 08:46 Go to previous messageGo to next message
ahudspith
Messages: 26
Registered: January 2009
Location: Avoiding the tax man.
Junior Member
Based on the ZERO information you have provided it is difficult to give an answer.

However I suspect that it is server parameter related.

Your PGA may be too small.

The optimizer could be in an inappropiate mode.

Parallel_max_servers could be wrong.

sort_area_size may be inappropiate...

Plus any other one from a million things.

Nobody can answer this question.

I suggest that you check for differences in the two databases SPFILE and copy the settings from the quick one to the slow one.

If that doesnt help then look at the query being executed - explain it.

In fact - you may also consider reanalyzing all the objects in the database whilst your about it (inclusing SYSTEM_STATS).

Cheers.
Re: SQL too slow [message #398605 is a reply to message #398508] Thu, 16 April 2009 21:43 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Sorry for replying late. Here is some sample output with explain plan.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=12
          18)

   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes
          =1218)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1393  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL> 



please suggest any solution in light of above ...

Re: SQL too slow [message #398606 is a reply to message #398605] Thu, 16 April 2009 21:52 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
If you are looking for correct answer, you should post the correct information.

P/S: Post the first execution of statement, do not post the second result if the statement is a single query.

The one is nothing
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size



Prove it:
scott@META> set autotrace traceonly
scott@META> select * from emp;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        447  recursive calls
          0  db block gets
         82  consistent gets
         13  physical reads
          0  redo size
       1363  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         14  rows processed

scott@META> /

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1363  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

scott@META> alter system flush shared_pool;

System altered.

scott@META> select * from emp;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        447  recursive calls
          0  db block gets
         83  consistent gets
          0  physical reads
          0  redo size
       1363  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         14  rows processed

scott@META>


14 rows did not cause performance running slowly!

Re: SQL too slow [message #398609 is a reply to message #398305] Thu, 16 April 2009 22:30 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Here is at my machine

SQL> set autotrace traceonly

SQL> select * from emp;

14 rows selected.


Execution Plan
----------------------------------------------------------                                                                        
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=12                                                            
          18)                                                                                                                     
                                                                                                                                  
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes                                                            
          =1218)                                                                                                                  
                                                                                                                                  




Statistics
----------------------------------------------------------                                                                        
          0  recursive calls                                                                                                      
          0  db block gets                                                                                                        
          8  consistent gets                                                                                                      
          0  physical reads                                                                                                       
          0  redo size                                                                                                            
       1393  bytes sent via SQL*Net to client                                                                                     
        508  bytes received via SQL*Net from client                                                                               
          2  SQL*Net roundtrips to/from client                                                                                    
          0  sorts (memory)                                                                                                       
          0  sorts (disk)                                                                                                         
         14  rows processed                                                                                                       

SQL> 
SQL> /

14 rows selected.


Execution Plan
----------------------------------------------------------                                                                        
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=12                                                            
          18)                                                                                                                     
                                                                                                                                  
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes                                                            
          =1218)                                                                                                                  
                                                                                                                                  




Statistics
----------------------------------------------------------                                                                        
          0  recursive calls                                                                                                      
          0  db block gets                                                                                                        
          8  consistent gets                                                                                                      
          0  physical reads                                                                                                       
          0  redo size                                                                                                            
       1393  bytes sent via SQL*Net to client                                                                                     
        508  bytes received via SQL*Net from client                                                                               
          2  SQL*Net roundtrips to/from client                                                                                    
          0  sorts (memory)                                                                                                       
          0  sorts (disk)                                                                                                         
         14  rows processed                                                                                                       

SQL> alter system flush shared_pool;

System altered.

SQL> select * from emp;

14 rows selected.


Execution Plan
----------------------------------------------------------                                                                        
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=12                                                            
          18)                                                                                                                     
                                                                                                                                  
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes                                                            
          =1218)                                                                                                                  
                                                                                                                                  




Statistics
----------------------------------------------------------                                                                        
        464  recursive calls                                                                                                      
          0  db block gets                                                                                                        
         83  consistent gets                                                                                                      
          2  physical reads                                                                                                       
          0  redo size                                                                                                            
       1393  bytes sent via SQL*Net to client                                                                                     
        508  bytes received via SQL*Net from client                                                                               
          2  SQL*Net roundtrips to/from client                                                                                    
          2  sorts (memory)                                                                                                       
          0  sorts (disk)                                                                                                         
         14  rows processed                                                                                                       

SQL>


Re: SQL too slow [message #398611 is a reply to message #398609] Thu, 16 April 2009 22:45 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Cost=3.

You meant that the statement was slowly?
Re: SQL too slow [message #398672 is a reply to message #398305] Fri, 17 April 2009 02:00 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Yes,
every row scroll down very slowly
Re: SQL too slow [message #398769 is a reply to message #398305] Fri, 17 April 2009 08:47 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>every row scroll down very slowly
Scroll? Scroll for 14 lines?
Do not (ab)use GUI & no problem will exist/occur.
Previous Topic: Query is getting stuck after adding one filter condition is where clause
Next Topic: The difference of execution plans
Goto Forum:
  


Current Time: Mon Dec 05 02:40:16 CST 2016

Total time taken to generate the page: 0.06893 seconds